Escaping identifiers in SQL when building up commands in C#

I answered a question on Stack Overflow recently about how to "encode" identifiers so that they aren't a source of SQL injection. As it's something I'm likely to keep "in the bank" I wanted to put up a blog post about it to make it easy to refer to, expand and make more specific to my needs in the future.

The trick to doing this is to use the built-in SQL function QUOTENAME to process the identifier and ensure that it's tidily square bracketed. In the comments on the question the asker suggested that they might work out what characters are allowed / aren't allowed and go from there. If that's ever the route to take, use a whitelist rather than a blacklist. You're in a much better place if you've forgotten to allow a character that should be permitted (end result: can't use the letter 'A' for example) than if youve' forgotten to block a character that shouldn't be permitted (end result: somone works out how to construct an injection attack). That said - using the tools available, such as QUOTENAME and paramaterised queries, is almost always the way to go to ensure that you're writing code that's injection proof.

Using QUOTENAME from C#

The class below wraps up calling down to an instance of SQL Server and asking it to use the QUOTENAME function to quote the object name:

public class SqlNameEscaper
{
    private Dictionary<string, string> _quotedNames = new Dictionary<string, string>();
    private string _connectionString = string.Empty;

    public SqlNameEscaper(string connectionString)
    {
        _connectionString = connectionString;
    }

    public string Get(string name)
    {
        if (!_quotedNames.ContainsKey(name))
        {
            _quotedNames[name] = GetSqlQuotedNameFromSqlServer(name);
        }

        return _quotedNames[name];
    }

    private string GetSqlQuotedNameFromSqlServer(string name)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            using (var command = new SqlCommand("SELECT QUOTENAME(@name)", connection))
            {
                command.Parameters.AddWithValue("@name", name);
                var result = command.ExecuteScalar();

                return result.ToString();
            }
        }
    }
}

Once you've got this class available to you, it's relatively easy to use:

var sne = new SqlNameEscaper(@"CONNECTION_STRING_HERE");
var userNamedTable = "['jdfiw@_BadTableName";

var query = string.Format("SELECT Column1, Column2 FROM {0}", sne.Get(userNamedTable));

Which will result in the query variable containing:

SELECT Column1, Column2 FROM [['jdfiw@_BadTableName]

Which is a nicely escaped version of a particularly ugly table name.