1

Suppose I want to drop a table, whose name is a variable value, such as "#t".

SqlCommand cmd = new SqlCommand( "drop table " + TableName ); //Not safe
SqlCommand cmd = new SqlCommand( "drop table [" + TableName +"]" ); //Not safe
EXEC( 'drop table [' + @tablename +"]" ); --Not safe

None of the above are safe from SQL injection, so what is the alternative?

People say "you have to use dynamic SQL", but the examples still usually involve unsafe concatenation like the third example above.

The closest thing to an answer I can find (https://stackoverflow.com/a/14026855/88409) basically says you have to do the check yourself, and there's no way around it (i.e. ensure the variable is valid through parsing it or getting it from a trusted source), but I'm not satisfied with that answer.

Community
  • 1
  • 1
Triynko
  • 18,766
  • 21
  • 107
  • 173
  • Background: I built a C# class called TempIDTable whose constructor accepts an SqlConnection and an integer list of ids. It generates a temporary table with a given name, optionally with a row number column, and does a bulk insert of the ids. You can wrap the whole thing in a using block in C#, and the dispose method handles dropping the table. Within the using block, you can use regular SqlCommand select statements that join with the temporary table on the id column to select the records you want, and when dealing with large lists of ids, its much faster than doing it any other way. – Triynko Oct 22 '13 at 22:23
  • The class had used a constant "#s" for the name, but I wanted to make the table name a variable, and to ensure the drop statement doesn't misbehave, due to programmer error or whatever, I wanted to enforce the rule that the table name had to be a valid/quoted table name and additionally it has to be a temporary table starting with a '#'. – Triynko Oct 22 '13 at 22:24
  • FYI... Temporary tables are automatically deleted when the connection is closed. See the "Temporary Tables" paragraph on this page: http://technet.microsoft.com/en-us/library/ms174979.aspx – David Oct 23 '13 at 15:03

1 Answers1

3

The QUOTENAME function will make sure your variable is treated as a "valid SQL Server delimited identifier".

It is then up to you to decide if the table given is one they should be allowed to delete. Maybe with SQL permissions on the table or a whitelist of tables that can be deleted...

David
  • 34,223
  • 3
  • 62
  • 80
  • So basically: EXEC( 'drop table ' + QUOTENAME(@tablename) ) would be safe. I wonder if there is an equivalent function in .NET anywhere, and I also wonder whether QUOTENAME is sensitive to whether the use quoted identifier option is on in the database, and specifically how it acts when one of the alternative quotes is passed to it such as "<", "(", and "{". – Triynko Oct 22 '13 at 22:15
  • Yes, that code should be safe. I have never seen a .Net equivalent of that function. – David Oct 23 '13 at 15:02