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
– MethodMan, Commented Jul 30, 2013 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
– Habib, Commented Jul 30, 2013 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
– Jon Skeet, Commented Jul 30, 2013 at 13:05
Habib I know that I am staying that he should use a Stored Procedure — MethodMan
– MethodMan, Commented Jul 30, 2013 at 13:06

Jon Skeet · Accepted Answer · 2013-07-30 13:04:57Z

15

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, 2013 at 13:04

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

fletchsod Over a year ago

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 stackoverflow.com/questions/14003241/… . 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!!

Habib · Accepted Answer · 2013-07-30 13:13:12Z

11

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, 2013 at 13:13

Habib

224k30 gold badges420 silver badges446 bronze badges

2 Comments

fletchsod Over a year ago

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

Habib Over a year ago

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

orange.lemur · Accepted Answer · 2015-01-19 11:35:25Z

-5

(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, 2015 at 11:35

orange.lemur

71 bronze badge

1 Comment

Andacious Over a year ago

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.

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related