How to drop SQL table from C# without opening to code injection?

Question

Edit: I marked that the linked question was useful, but I didn't intend to mean that it completely answered my question here, which after reviewing others' comments and answers here, I realize I need two different escaped versions of table, one as a safe single-quoted identifier, and one as a safe single-bracketed identifier.

I need to drop a SQL server table from C# with the table name as a parameter.

This is my code:

private static void DropSqlTableIfExists(string connectionString, string table)
{
    string query = "if object_id (@table, 'U') is not null begin drop table @table; end";
    using SqlConnection conn = new(connectionString);
    using SqlCommand command = new(query, conn);
    command.Parameters.AddWithValue("@table", table);
    conn.Open();
    command.ExecuteNonQuery();
}

The above code gives me an error, Incorrect syntax near '@table'.

I could of course put the table name directly into the query string, but that would allow code injection, which I need to avoid.

What is the best way to go about this?

EDIT 2:

Based on feedback, I updated the code to look like this:

private static void DropSqlTableIfExists(string connectionString, string table)
{
    string tableSafeQuoted = $"'{table.Replace("'", "''")}'";
    string tableSafeBracketed = $"[{table.Replace("]", "]]").Replace(".", "].[")}]";

    string query = $"if object_id ({tableSafeQuoted}, 'U') is not null begin drop table {tableSafeBracketed}; end";
    using SqlConnection conn = new(connectionString);
    using SqlCommand command = new(query, conn);
    conn.Open();
    command.ExecuteNonQuery();
}

tbh, I'm not sure if it's 100% safe, due to the oddity of the . replacement.. but I think unless someone gives me a good reason not to, I will leave it like this..

When inserting the table name into the string, you'll need to escape it properly. Unfortunately, how to do this will depend on the type of database you're using (e.g. SQL Server), and possibly how it's configured (e.g. is ANSI Quotes enabled). Can you provide those details? — StriplingWarrior, Sep 23 '22 at 15:09
out of curiosity, are stored procs off limits for you? this is one place i'd use a stored proc so it could validate and also limit permissions for the web app running this. — Nikki9696, Sep 23 '22 at 15:14
@StriplingWarrior I'm using SQL Server 2019. Edited the original question to contain this info. — ruttergod, Sep 23 '22 at 15:15
@Nikki9696 a stored procedure wouldn't help. You can't pass a table name as a parameter no matter how you execute the statement — Panagiotis Kanavos, Sep 23 '22 at 15:22
@PanagiotisKanavos you could last I wanted to. Perhaps it's a sql server thing. Similar to https://stackoverflow.com/questions/6046510/stored-procedure-to-drop-table — Nikki9696, Sep 23 '22 at 15:31
No it's not. Tables and columns in SQL are the same as types in strongly typed languages. Just like other languages, a parameter is only meant to pass values, not types — Panagiotis Kanavos, Sep 23 '22 at 15:35
Quite an old answer, but i think SqlParameters does not support table names at all. (Or they have changed that since 2013): https://stackoverflow.com/questions/17947736/sqlparameter-does-not-allows-table-name-other-options-without-sql-injection-at — Viper, Sep 23 '22 at 15:36

score 1 · Accepted Answer · answered Sep 23 '22 at 15:53

You'll need to escape/encode the table name as an identifier. In SQL Server, this is typically done by replacing any closing brackets (]) in the name with two consecutive brackets (]]), and then surrounding the result with brackets ([...]).

You can either write a simple method to do this yourself, or take on a dependency like ScriptDom, which allows you to do this:

var escapedTableName = Identifier.EncodeIdentifier(tableName);

If your SQL Server database is set to use double-quoted identifiers, do something similar, but with double-quotes instead of brackets. Or, with ScriptDom:

var escapedTableName = Identifier.EncodeIdentifier(tableName, QuoteType.DoubleQuote);

Note that encoding the table name makes any .s act as if they're part of the table name itself. If your table name is supposed to have qualifiers (database, schema), each of those must be escaped individually, so you'll probably want to have them passed in as a separate argument, or create a separate type to represent the combination of these names.

Thanks, this is the kind of thing I think I will do. Before you had posted this answer, I updated my code to have two versions of `table`, namely `tableSafeQuoted` where single quote was replaced with two single quotes and sandwiched itself between two single quotes, and `tableSafeBracketed` where single right bracket was replaced with double right bracket and sandwiched itself in square brackets. However, I needed your comment about `.`s as the table name is actually fully qualified with the db name, schema, and table name, so I will need to update for that. THANKS!! — ruttergod, Sep 23 '22 at 16:29
To clarify, the reason I need two different escaped versions is because the use of `table` in the `if` clause needs to be in single quotes, but it needs to be brackets in the `drop` command. — ruttergod, Sep 23 '22 at 16:35

score 0 · Answer 2 · answered Sep 23 '22 at 15:51

There is probably a more elegant way to do this, but here's one possible way:

declare @dynamicsql nvarchar(max)
set @dynamicsql = 'DROP TABLE dbo.' + QUOTENAME(@table) + '';
if object_id (@table, 'U') is not null
begin
    EXEC sp_executesql @dynamicsql;
end

so in your specific case it will be:

private static void DropSqlTableIfExists(string connectionString, string table)
{
    string query = @"
        declare @dynamicsql nvarchar(max)
        set @dynamicsql = 'DROP TABLE dbo.' + QUOTENAME(@table) + '';
        if object_id (@table, 'U') is not null
        begin
            EXEC sp_executesql @dynamicsql;
        end
    ";
    using SqlConnection conn = new(connectionString);
    using SqlCommand command = new(query, conn);
    command.Parameters.AddWithValue("@table", table);
    conn.Open();
    command.ExecuteNonQuery();
}

I hope it helps!

How to drop SQL table from C# without opening to code injection?

2 Answers2