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

Question

I have a Project table with two columns -- ProjectId and ProjectName -- and am writing a function that constructs and executes a SqlCommand to query the database for the ids of a Project with a 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 strings. For example, they might be "ProjectId", "Project", "ProjectName", and "MyFirstProject", respectively. surroundWithSingleQuotes surrounds a string with '', so surroundWithSingleQuotes(idValue) == "'MyFirstProject'". I am trying to write this function as general as possible since I might want to get all of a given attribute from a table in the future.

Although the above String.Format works, this doesn't:

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 get no error message, but when I fill my DataTable using a SqlDataAdapter, the DataTable contains nothing. Here are various approaches I've taken, to no avail:

Following this answer and Microsoft's documentation, using AddWithValue or using Parameters.Add and SqlParameter.Value.
Selectively replacing {0}, {1}, {2}, and {3}in String.Format with either the actual value or the parameter string.

In other places in my code, I've used parametrized queries (although with just one parameter) no problem.

You cannot use a parameter for the name of a column. You will still have to use the `string.Format` for that, just like for the table name. — juharr, Oct 15 '15 at 15:53

score 7 · Accepted Answer · answered Oct 15 '15 at 15:53

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

If you need to be dynamic in terms of your column and table names, you'll need to build that part of the SQL yourself. Be very careful for all the normal reasons associated with SQL injection attacks. Ideally, only allow a known whitelist of table and column values. If you need to be more general, I'd suggest performing very restrictive validation, and quote the identifiers to avoid conflicts with keywords (or prohibit those entirely, ideally).

Keep using SQL parameters for values, of course.

score 0 · Answer 2 · answered Oct 15 '15 at 15:57

0

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

Whereas this is not: SELECT * FROM @param

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

answered Oct 15 '15 at 15:57

Yosi Dahari

6,794
5
24
44

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

2 Answers2