CODEWITHSUNDEEP
c#sql-server
rln
rln

Reputation: 73

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

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:

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

Upvotes: 7

Views: 446

Answers (2)

Yosi Dahari
Yosi Dahari

Reputation: 6999

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.

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1500675

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.

Upvotes: 7

Related Questions