C# SqlCommand - cannot use parameters for column names, how to resolve?

Question

Hi, Is there any way how to do that? This does not work:

SqlCommand command = new SqlCommand("SELECT @slot FROM Users WHERE name=@name; ");
prikaz.Parameters.AddWithValue("name", name);
prikaz.Parameters.AddWithValue("slot", slot);

The only thing I can think of is to use SP and declare and set the variable for the column. Seems to me a bit ackward.

Answer 1

You cannot do this in regular SQL - if you must have configurable column names (or table name, for that matter), you must use dynamic SQL - there is no other way to achieve this.

string sqlCommandStatement =  
   string.Format("SELECT {0} FROM dbo.Users WHERE name=@name", "slot");

and then use the sp_executesql stored proc in SQL Server to execute that SQL command (and specify the other parameters as needed).

Dynamic SQL has its pros and cons - read the ultimate article on The Curse and Blessings of Dynamic SQL expertly written by SQL Server MVP Erland Sommarskog.

Answer 2

As has been mentioned, you cannot parameterise the fundamental query, so you will have to build the query itself at runtime. You should white-list the input of this, to prevent injection attacks, but fundamentally:

// TODO: verify that "slot" is an approved/expected value
SqlCommand command = new SqlCommand("SELECT [" + slot +
           "] FROM Users WHERE name=@name; ")
prikaz.Parameters.AddWithValue("name", name);

This way @name is still parameterised etc.