Checking for field existance in ORDER BY

[jalbao]

in my query i am using ORDER BY #variable#.

as you can guess, the ORDER BY is dynamic. The #variable# value is a string sent by the app user.

with out going into details as to why i am doing this in the way that i am doing (does that even make sense :| ), i want to be able to check if the value of the variable in the ORDER BY statement is a valid field name.

is there a SQL condition that i can use for this purpose?

 

[jjonesal]

The syscolumns system table contains a row for every column in every table (or view). You could check this table for the existence of the column before you execute your select with your variable in the order by clause. Just a thought...There might be a better way, but....Does the user also enter the table name? It's in sysobjects, of course. J. Jones
jjones@cybrtyme.com

 

[tlbroadbent]

Use the INFORMATION_SCHEMA views to help determine if a column exists.

If exists (
Select * From dbname.information_schema.columns
where TABLE_NAME='tblname' And TABLE_SCHEMA='dbo' And COLUMN_NAME='colname')
Begin
<column exists logic>
End
Else
Begin
<Coulumn doesn't exist logic>
End Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[rajeevnandanmishra]

Hi albao,
As you said, &quot;with out going into details as to why i am doing this in the way that i am doing&quot;, i am asking, Is it possible to pass the column number like 1 or 2 or 3 ... Because >99.99% of the time user sort the result on the basis of the select list. And if you pass the column number of select list in order by, you don't need to check for the validity.

 

[jjonesal]

Also, are users allowed just to type in anything as a column name??.....What about using tlbroadbent's idea about accessing the INFORMATION_SCHEMA views and populate a list box - then you know the data coming in is good right from the start. Just a thought....you probably already thought of that. J. Jones
jjones@cybrtyme.com

 

[jalbao]

thanks for all the responses:

here's a little more info on what i am doing:

first of all my application is being ran over the web. in the application, there is a page that displays a list of employee information; name address phone etc.
currently, the user of the app that is viewing the list of users, he has the option of clicking a column name (name phone address etc) for the purpose of sorting the list by the column name that he clicked.

when the user clicks the column name it sends a url variable that contains a value equal to the name of a column in the db table. that url variable is then used in then ORDER BY sql query.

for all practicle purposes, this method works fine. the only 'real' flaw is when a user clicks a column name (which sends the url varible), the variable is displayed in the browser address bar, so if a user wanted to have fun and change the name of the url variable there would be nothing stopping him. which implies, if he typed in a value that does not exist in the table fields, a sql error occurrs.

so the bottom line is i want to trap for this and make sure that nobody will force a sql error.