Parameter as Column Name?

Greetings,

I am developing a search form that uses a DDL to select a column name and a text box to define a search term. Code follows:

1cmdSearch = New SqlCommand( "select * from sites where @.columnName like @.sTerm order by site_name", conSites)23cmdSearch.Parameters.Add( "@.columnName", SqlDbType.Text).Value=ColumnParam4cmdSearch.Parameters.Add( "@.sTerm", SqlDbType.Text).Value=searchParam

The problem I'm running into is that the @.variable appears to imply single quotes about the value. This is fine for @.sTerm, I just removed the quotes from the concatenation and it reads the value correctly, but it appears to break @.columnName and the query returns no values.

If I manually substitute an appropriate column for @.columnName sans single quotes, it works.

How can I eliminate the implied single quotes from @.columnName so that the query will read it properly?

You need to build your statement dynamically within your xp and execute it using sp_executesql system stored procedure. (Don't use the column variable in your sql.)

You should create a stored procedure to handle the building and execution of the string to make the process easier and reusable. Use parameters (like you did in your current sql statement, just not for a column or table name) to prevent injection attacks.

http://msdn2.microsoft.com/en-us/library/ms175170.aspx

|||

Thanks for the advice, but I can't use stored procedures for this project, or I would've done it that way from the beginning.

Is there any way to define the @.variable to eliminate the implied quotes?

ps2goat:

You need to build your statement dynamically within your xp and execute it using sp_executesql system stored procedure. (Don't use the column variable in your sql.)

You should create a stored procedure to handle the building and execution of the string to make the process easier and reusable. Use parameters (like you did in your current sql statement, just not for a column or table name) to prevent injection attacks.

http://msdn2.microsoft.com/en-us/library/ms175170.aspx

|||

You can build the SQL dynamically with the column name, but still keep parameters for everything else. This will still limit sql injections.

A quick example:

cmdSearch =New SqlCommand("select * from sites where " & ColumnParam &" like @.sTerm order by site_name", conSites)' continue with code as normal, but no column parameter.

|||

Thanks, this did the trick. I'd tried doing it this way before but couldn't work out the syntax.

Much obliged.

ps2goat:

You can build the SQL dynamically with the column name, but still keep parameters for everything else. This will still limit sql injections.

A quick example:

cmdSearch =New SqlCommand("select * from sites where " & ColumnParam &" like @.sTerm order by site_name", conSites)' continue with code as normal, but no column parameter.

sql