get column from database using a string

Question

My Code:

    SqlCommand command = new SqlCommand("SELECT min(Score) FROM MenAthletics WHERE [(@sportevent)] < (@result);", connect);
        command.Parameters.AddWithValue("@sportevent", sportEvent);
        command.Parameters.AddWithValue("@result", result);

the @result works fine (just a double variable) the @sportevent doesnt't work (error: invalid columnname) (sportEvent is a string)

how can I choose a column by giving in a string?

It seems like your actual question should be "Can parameters be used in column names?" — Mansfield, May 30 '13 at 12:06
http://stackoverflow.com/questions/10092869/can-i-pass-column-name-as-input-parameter-in-sql-stored-procedure — Apocalyp5e, May 30 '13 at 12:09
You don't. You should really fix your data model and add sport event as a table and create a FK column in MenAthletics table. That said, I'd guess you need to fix it even more. If you have a table called MenAthletics you probably need an "Event" table.... — LoztInSpace, May 30 '13 at 12:41

score 4 · Answer 1 · edited May 30 '13 at 12:19

4

You can parameterize values in SQL statements, but you cannot parameterize column or table names. You need to change the column name in the SQL string itself, for example, with string.Format:

SqlCommand command = new SqlCommand(
    string.Format("SELECT min(Score) FROM MenAthletics WHERE [{0}] < (@result);", sportEvent)
,   connect
);
command.Parameters.AddWithValue("@result", result);

Make sure that the column name does not come from user's input, otherwise you would open up your code to SQL injection attacks. In case the column name does come from user's input, you can validate the string against a list of available table columns, which could be made statically or by examining the structure of your table at runtime.

edited May 30 '13 at 12:19

dash

89,546
4
51
71

answered May 30 '13 at 12:06

Sergey Kalinichenko

714,442
84
1,110
1,523

Just out of curiousity, how should one best prevent SQL injection in this instance if the column name is user input? – Mansfield May 30 '13 at 12:08
2

@Mansfield Since the list of columns is finite, you could validate the input against a complete list, and error out if the user-supplied name is not among the available columns. – Sergey Kalinichenko May 30 '13 at 12:12
1

Also, ensure that the credentials you connect to the database with only have enough permission to do what you need to, and not DROP objects etc. Also, if this is a drop down and you have a web application, make sure you check that the value sent from the client actually exists in your list after a request is submitted - simple matter to tamper with HTTP forms. – dash May 30 '13 at 12:16

score 1 · Accepted Answer · answered May 30 '13 at 12:06

1

You could dynamically build the SQL query, instead of passing the column name as a parameter.

answered May 30 '13 at 12:06

aquaraga

4,138
23
29

thanks! didn't do that because I was scared for SQL injection but the columnname is given in by a dropdown. – user2323240 May 30 '13 at 12:16
1

Ooh that's a bit scary - you should ideally map the incoming drop-down input into a column name on the server-side before you attempt to use it in the SQL query. Although it is a combo-box and you would expect that the user is constrained by the options, a hacker could use plugins like 'Tamper' and work around the limitations. – aquaraga May 30 '13 at 12:23

dash · Answer 3 · 2013-05-30T12:15:32.617

You can't use a column name as a parameter; you should instead consider constructing your query this way:

SqlCommand command = 
      new SqlCommand(
         String.Format(@"SELECT min(Score) 
                         FROM MenAthletics WHERE [{0}] < @result;",     
                         sportEvent), 
                      connect);
command.Parameters.AddWithValue("@result", result);

This kind of sql is called "dynamic sql" and can be an effective way of constructing queries on the fly.

However, there are pitfalls. As well as validating the user input, also make sure that the user you are connecting to the database with only has enough permissions to carry out the actions you want to do.

Another approach, which is less elegant, but can be placed directly into a stored procedure, is to use a CASE statement;

For example:

SELECT min(Score)
FROM MenAthletics 
WHERE 
    CASE 
      WHEN @sportEvent = 'SomeColumnName' THEN SomeColumnName
      WHEN @sportEvent = 'SomeColumnName2' THEN SomeColumnName2
    END < @result;

This gets very tedious to both create and maintain on large tables. The advantage is that the query is not dynamic.

score 0 · Answer 4 · answered May 30 '13 at 12:07

This is because value in the sportEvent string which you are passing as a parameter is not matching with actual column existing in your database table.

Make sure that both of them matches and then only this error will go.

Otherwise dont pass table's column name as a parameter, directly write it in the query and let its column value be a parameter.

Hope it helps.

get column from database using a string

4 Answers4