Sanitize table/column name in Dynamic SQL in .NET? (Prevent SQL injection attacks)

Question

I am generating some Dynamic SQL and would like to ensure that my code is safe from SQL injection.

For sake of argument here is a minimal example of how it is generated:

var sql = string.Format("INSERT INTO {0} ({1}) VALUES (@value)",
    tableName, columnName);

In the above, tableName, columnName, and whatever is bound to @value come from an untrusted source. Since placeholders are being used @value is safe from SQL injection attacks, and can be ignored. (The command is executed via SqlCommand.)

However, tableName and columnName cannot be bound as placeholders and are therefor vulnerable to injection attacks. Since this a "truly dynamic" scenario, there is no whitelist of tableName or columnName available.

The question is thus:

Is there a standard, built-in way to check and/or sanitize tableName and columnName? (SqlConnection, or a helper class, etc.) If not, what is a good way to perform this task without using a 3rd party library?

Notes:

• All SQL identifiers, including the schema, should by accepted: e.g. [schema].[My Table].column is just as "safe" as table1.
• Can either sanitize the identifiers or detect an invalid identifier. (It does not need to ensure that the table/column is actually valid in context; the resulting SQL can be invalid, but must be "safe".)

---

Update:

Just found this, and thought it was somewhat interesting: There is a SqlFunctions.QuoteName function in .NET4 (EF4?). Okay, it doesn't really help me here...

Answer 1

I'm not sure if you're still looking into this, but the DbCommandBuilder class provides a method QuoteIdentifier for this purpose. The main benefits of this are that it's database-independent and doesn't involve any RegEx mess.

As of .NET 4.5, you have everything you need to sanitize table and column names just using your DbConnection object:

DbConnection connection = GetMyConnection(); // Could be SqlConnection
DbProviderFactory factory = DbProviderFactories.GetFactory(connection);

// Sanitize the table name
DbCommandBuilder commandBuilder = factory.CreateCommandBuilder();

string tableName = "This Table Name Is Long And Bad";
string sanitizedTableName = commandBuilder.QuoteIdentifier(tableName);

IDbCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM " + sanitizedTableName;

// Becomes 'SELECT * FROM [This Table Name Is Long And Bad]' in MS-SQL,
// 'SELECT * FROM "This Table Name Is Long And Bad"' in Oracle, etc.

(Pre-4.5, you'll need some other way to get your DbProviderFactory -- maybe from the data provider name in your application configuration or hard-coded somewhere.)