CODEWITHSUNDEEP
c#sql-serverado.netsql-server-2016
D.R.
D.R.

Reputation: 21204

Escape database object names

I need to build a SQL Server 2016 script using string concatenation from within C# (the script will be executed via ADO.NET). I cannot use query parameters, which I would normally do, as the script is more of a setup script and contains non-parameterizable statements.

What's the way to go to escape names such that:

"ALTER DATABASE " + Escape(databaseName) + " ADD ..."

is not vulnerable to SQL injection? How to implement Escape? Currently I'm using square brackets around all names, however, that is not enough of course...

Upvotes: 2

Views: 1582

Answers (2)

Milan
Milan

Reputation: 118

If you are on regular .NET, use SqlCommandBuilder.QuoteIdentifier:

Given an unquoted identifier in the correct catalog case, returns the correct quoted form of that identifier. This includes correctly escaping any embedded quotes in the identifier.

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommandbuilder.quoteidentifier

Upvotes: 2

Rob
Rob

Reputation: 45771

You can use the QUOTENAME SQL Server built-in function to achieve this.

As it looks very much like you're building up the script in C#, prior to executing it in SQL, you'll probably want to have a C# function that makes a trip down to the database to do this, perhaps also storing the resultant values in a Dictionary<string, string> to eliminate round-trips for strings that have already been quoted.

For example:

private Dictionary<string, string> _quotedNames = new Dictionary<string, string>();

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

    return _quotedNames[name];
}

private string GetSqlQuotedNameFromSqlServer(string name)
{
    /// Code here to use your Data access method of choice to basically execute
    /// SELECT QUOTENAME(name) and return it
}

In fact, just to show this using the classes in the System.Data.SqlClient namespace, here's a class that performs this behaviour, when given a connection string to use to talk to SQL Server:

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 GetSqlQuotedName(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();
            }
        }
    }
}

This can then be called by doing this:

var sne = new SqlNameEscaper(@"CONNECTION_STRING_HERE");
var bracket = sne.GetSqlQuotedName("[");

Or, in the context of your example:

var sqlNameEscaper = new SqlNameEscaper(@"CONNECTION_STRING_HERE");
var text = "ALTER DATABASE " + sqlNameEscaper.GetSqlQuotedName(databaseName) + " ADD ...";

There is also a question on dba.stackexchange.com that's worth having a read of on this very subject: Should we still be using QUOTENAME to protect from injection attacks?

Upvotes: 2

Related Questions