SqlParameter does not allows Table name - other options without sql injection attack?

Question

I got a runtime error saying "Must declare the table variable "@parmTableName". Meaning having table name as sql parameter in the sql-statement is not allowed.

Is there a better option or suggestion than allowing sql injection attack? I don't want to do this C# script for sql statement " DELETE FROM " + tableName + " ";

using(var dbCommand = dbConnection.CreateCommand())
{
   sqlAsk = "";
   sqlAsk += " DELETE FROM @parmTableName ";
   sqlAsk += " WHERE ImportedFlag = 'F' ";

   dbCommand.Parameters.Clear();
   dbCommand.Parameters.AddWithValue("@parmTableName", tableName);

   dbConnection.Open();

   rowAffected = dbCommand.ExecuteNonQuery();
}

Secern

Create a stored procedure. – Voile

have you looked at your own code here..? also have you even debugged the code.. table name is not assigned that is what the error is complaining about.. what is the value of tableName that you are assigning in the AddWithValue method..?? Create a Stored Procedure.. also you still need to pass in the tableName to that stored procedure.. – Crenelation

@DJKRAZE, even if tableName is assigned a value, it can't be passed as parameter, only data is allowed as SqlParameter not Field names and table names. – Villalobos

@Guanxi: What would that stored proc look like to prevent SQL injection? I would expect exactly the same problem to occur there. – Giusto

Habib I know that I am staying that he should use a Stored Procedure – Crenelation

Why would I want to create TOO MANY stored procedures? It was already becaming a hassle to work with by having another one. I also agree w/ @JonSkeet . – Secern

Here's the link of why parameterize table name wouldn't work. sqlteam.com/article/introduction-to-dynamic-sql-part-1 – Secern

Answer 1

Go for a white list. There can only be a fixed set of possible correct values for the table name anyway - at least, so I'd hope.

If you don't have a white list of table names, you could start with a whitelist of characters - if you restrict it to A-Z, a-z and 0-9 (no punctuation at all) then that should remove a lot of the concern. (Of course that means you don't support tables with odd names... we don't really know your requirements here.)

But no, you can't use parameters for either table or column names - only values. That's typically the case in databases; I don't remember seeing one which did support parameters for that. (I dare say there are some, of course...)

Giusto

You're right about the whitelist. The bad part is to keep adding table names in the long run, recomplie/deploy. It would be a hassle & unsustaintable. But you GOT me thinking. Google searches with your right idea is pretty hard. But finally I found it at #14003741 . See accepted answer & @RafaelAdel's comment. "Yes it does. You should check if tblname is a table in your database (SELECT * FROM INFORMATION_SCHEMA.TABLES).". I can make do with "WHERE Table_Name = @parmTableName" to it, if it exists, use it!! – Secern

Answer 2

As others have already pointed out that you can't use Table Name and Fields in Sql Parameter, one thing that you can try is to escape table name using SqlCommandBuilder, like:

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

using (var dbCommand = dbConnection.CreateCommand())
{
    sqlAsk = "";
    sqlAsk += " DELETE FROM " + escapedTableName; //concatenate here
    sqlAsk += " WHERE ImportedFlag = 'F' "; 

    dbCommand.Parameters.Clear();

    dbConnection.Open();

    rowAffected = dbCommand.ExecuteNonQuery();
}

Villalobos

That can still lead to SQL Injection attack. Everything we can think of can still leave hole (or loophole) in the script. :-) – Secern

@fletchsod, could you give me an example how would this lead to SQL Injection ? – Villalobos

Answer 3

(sqlAsk is string, right?) if it's right so let's try this:

using(var dbCommand = dbConnection.CreateCommand())
{
   sqlAsk = "";
   sqlAsk += " DELETE FROM <table_name> ";
   sqlAsk += " WHERE ImportedFlag = 'F' ";

   string table_name = "Your table name here";  //<- fill this as u need 
   sqlAsk = sqlAsk.Replace("<table_name>", table_name); // it will replace <table_name> text to string table_name

   dbConnection.Open();

   rowAffected = dbCommand.ExecuteNonQuery();
}

Eiser

This is not any different than what the OP mentioned. Instead of string concatenation, you are using string replacement. Both are just as vulnerable to SQL injection. – Globoid