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();
}

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.. — MethodMan, Jul 30 '13 at 13:04
@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. — Habib, Jul 30 '13 at 13:05
@Guanxi: What would that stored proc look like to prevent SQL injection? I would expect exactly the same problem to occur there. — Jon Skeet, Jul 30 '13 at 13:05
Habib I know that I am staying that he should use a Stored Procedure — MethodMan, Jul 30 '13 at 13:06
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 . — fletchsod, Jul 30 '13 at 14:02
Here's the link of why parameterize table name wouldn't work. http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1 — fletchsod, Jul 30 '13 at 15:35

score 13 · Accepted Answer · answered Jul 30 '13 at 13:04

13

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...)

answered Jul 30 '13 at 13:04

Jon Skeet

1,421,763
867
9,128
9,194

5

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 http://stackoverflow.com/questions/14003241/must-declare-the-table-variable-table . 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!! – fletchsod Jul 30 '13 at 14:30

score 10 · Answer 2 · answered Jul 30 '13 at 13:13

10

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();
}

answered Jul 30 '13 at 13:13

Habib

219,104
29
407
436

2

That can still lead to SQL Injection attack. Everything we can think of can still leave hole (or loophole) in the script. :-) – fletchsod Jul 30 '13 at 14:04
5

@fletchsod, could you give me an example how would this lead to SQL Injection ? – Habib Jul 30 '13 at 14:05

score -4 · Answer 3 · answered Jan 19 '15 at 11:35

-4

(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();
}

answered Jan 19 '15 at 11:35

orange.lemur

9
1

3

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. – Andacious Apr 08 '16 at 20:11

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

3 Answers3

Linked

Related