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

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

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

Solution 2:

Since you are using an SqlConnection, the assumption is that this is an SQL Server database.

Given that assumption, you could validate the table and field names using a regular expression that follows the SQL Server identifier rules as defined in MSDN. While I am a complete and utter novice at regular expressions, I did find this one that should come close:

[\p{L}{\p{Nd}}$#_][\p{L}{\p{Nd}}@$#_]*

However, a regular expression will not address SQL Server keywords and it does not ensure that the table and/or column actually exists (although you indicated that wasn't much of an issue).

If this were my application, I would first ensure the end user was not trying to perform injection by rejecting any request that contained semi-colons (;).

Next, I would validate the table existence by removing the valid name delimiters (", ', [, ]), splitting the table name by a period to see if a schema was specified, and executing a query against INFORMATION_SCHEMA.TABLES to determine the existence of the table.

For example:

SELECT 1 
FROM   INFORMATION_SCHEMA.TABLES 
WHERE  TABLE_NAME = 'tablename' 
AND    TABLE_SCHEMA = 'tableschema'

If you create this query using parameters, then you should further protect yourself from injection.

Finally, I would validate the existence of each column name by performing a similar set of steps, only using INFORMATION_SCHEMA.COLUMNS to determine the validity of the column(s) once the table has been determined to be valid.

I would probably fetch the list of valid columns for this table from SQL Server, then verify that each request column was in the list within my code. That way you could tell exactly which columns were in error and provide that feedback to the user.

Solution 3:

For SQL Server, it's pretty simple to sanitize an identifier:

// To make a string safe to use as an SQL identifier :
// 1. Escape single closing bracket with double closing bracket
// 2. Wrap in square brackets
string.Format("[{0}]", identifier.Replace("]", "]]"));

Once wrapped in brackets and escaped, the only thing that won't work as an identifier is an empty/null string.