Geek Answers Handbook

Why does String.Format work, but SqlCommand.Parameters.Add is not?

I have a Project table with two columns - ProjectId and ProjectName - and I am writing a function that creates and executes SqlCommand to query the database for the project identifiers with the given name. This command works, but is vulnerable to SQL Injection :

 string sqlCommand = String.Format("SELECT {0} FROM {1} WHERE {2} = {3}", attributeParam, tableParam, idParam, surroundWithSingleQuotes(idValue)); SqlCommand command = new SqlCommand(sqlCommand, sqlDbConnection); using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { DataTable attributes = new DataTable(); adapter.Fill(attributes); ... }

attributeParam , tableParam , idParam and idValue are all rows. For example, they can be "ProjectId" , "Project" , "ProjectName" and "MyFirstProject" respectively. surroundWithSingleQuotes surrounds the string with '' , so surroundWithSingleQuotes(idValue) == "'MyFirstProject'" . I am trying to write this function as general as possible, since I might want to get the whole given attribute from the table in the future.

Although the above String.Format works, it is not:

 string sqlCommand = String.Format("SELECT @attributeparam FROM {0} WHERE " + "@idparam = @idvalue", tableParam); command.Parameters.Add(new SqlParameter("@attributeparam", attributeParam)); command.Parameters.Add(new SqlParameter("@idparam", idParam)); command.Parameters.Add(new SqlParameter("@idvalue", surroundWithSingleQuotes(idValue))); SqlCommand command = new SqlCommand(sqlCommand, sqlDbConnection); using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { DataTable attributes = new DataTable(); adapter.Fill(attributes); ... }

I'm not sure why. I am not getting an error, but when I populate my DataTable with the SqlDataAdapter , the DataTable does not contain anything. Here are a few approaches that I have taken, to no avail:

  • After this answer and the Microsoft documentation , using AddWithValue or using Parameters.Add and SqlParameter.Value .
  • selectively replacing {0} , {1} , {2} and {3} in String.Format either the actual value or the parameter string.

Elsewhere in my code, I used parameterized queries (albeit with one parameter) without problems.

+7
c # sql-server
source share
2 answers

Basically, parameters in SQL work only for values ​​- not the identifiers of columns or tables. In your example, only the final parameter represents the value.

If you need to be dynamic in terms of column and table names, you will need to assemble this part of SQL yourself. Be very careful for all the common causes associated with SQL injection attacks. Ideally, allow only the well-known whitelist of table and column values. If you need to be more general, I would suggest performing a very strict check, and specify identifiers to avoid conflicts with keywords (or completely forbid them, ideally).

Keep, of course, SQL parameters for values.

+7
source share

This is a valid statement: SELECT * FROM SomeTable WHERE SomeColumn=@param

While it is not: SELECT * FROM @param

This means that you can use parameters for values, and not for table names, name names, column names, etc.

0
source share

More articles:


All Articles