Search code examples
c#sqlsql-servert-sqlsql-injection

Dynamic SQL with C# SqlCommand

I'd like to create a dynamic SQL query with c#'s SqlCommand where even the table is a parameter, so as to avoid injection attempts. Like below:

comm.CommandText = "SELECT * FROM @tbl WHERE cond=@cond";
comm.Parameters.AddWithValue("tbl","TABLENAME");
comm.Parameters.AddWithValue("cond","CONDITION");

However, I have found that this is not allowed. I've looked into using Dynamic SQL with an execute, but that seems to be only for stored procedures. Can I use Dynamic SQL with an Execute using parameters for the table name with an SqlCommand? If not, how can this be done to avoid SQL injection problems?

Thanks!

Solution

  • Use SqlCommandBuilder.QuoteIdentifier method to escape table names. 

    SqlCommandBuilder builder = new SqlCommandBuilder();
string tableName ="SomeTable";
string escapedTableName = builder.QuoteIdentifier(tableName);

    Later you can use the escaped table name in your string like:

    comm.CommandText = "SELECT * FROM "+ escapedTableName +"  WHERE cond=@cond";