Possible SQL injection using SQL parameters

This post will discuss the possibility of creating an SQL injection using the name of a parameter.

We have known about SQL injection since forever: a programmer is constructing an SQL command using user provided information and some malicious hacker inserts a specially crafted string that transforms the intended query into something that returns the content of a user table or deletes some data on the server. The solution for this, in the .NET world, is to use parameterized queries. Even if someone dynamically creates an SQL query, they should use parameter names and then provide the parameters to the SQL command. The idea behind this is that any value provided by the users will be escaped correctly using parameters. Today I found that this solution works perfectly for parameter values, but less perfectly for parameter names. Try to use a parameter with a name containing a single quote and you will get an error. Let's analyse this.

Here is a piece of code that just executes some random SQL text command, but it also adds a parameter containing a single quote in the name:

using (var conn = new SqlConnection("Server=localhost;Database=Test;UID=sa;Trusted_Connection=True;"))
{
    conn.Open();
    using (var comm = new SqlCommand())
    {
        var paramName = "a'";

        comm.Connection = conn;
                    
        comm.CommandText = "SELECT 1";
        comm.Parameters.Add(new SqlParameter(paramName, SqlDbType.NVarChar, 100)
        {
            Value="text"
        });

        comm.ExecuteNonQuery();
    }
    conn.Close();
}

As you can see, the text content of the SQL command is irrelevant. the name of the parameter is important and for this I just used a single quote in the name. Running SQL Profiler, we get this query string that is actually executed:

exec sp_executesql N'SELECT 1',N'@a'' nvarchar(100)',@a'=N'text'

In this example the name of the parameter is properly handled in the string defining the name and type of the parameters, but it is NOT escaped during the parameter value declaration. A small change of the code with paramName="a='';DELETE * FROM SomeTable --" results in an interesting query string in the SQL Profiler:

exec sp_executesql N'SELECT 1',N'@a='''';DELETE FROM SomeTable -- nvarchar(100)',@a='';DELETE FROM SomeTable --=N'text'

Strangely enough, when inspecting the SomeTable table, the values are still there, even if copying the text into SQL Management Studio actually deletes the values. A similar construction using stored procedures leads to a completely legal SQL that is recorded by SQL Profiler, but it doesn't really do anything:

using (var conn = new SqlConnection("Server=localhost;Database=Test;UID=sa;Trusted_Connection=True;"))
{
    conn.Open();
    using (var comm = new SqlCommand())
    {
        var paramName = "a='';DELETE FROM SomeTable --";

        comm.Connection = conn;
        comm.CommandText = "DoTest";
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.Add(new SqlParameter(paramName, SqlDbType.NVarChar, 100)
        {
            Value="text"
        });

        int k = 0;
        using (var reader = comm.ExecuteReader())
        {
            while (reader.Read()) k++;
        }
        Console.WriteLine(k);
    }
    conn.Close();
}

... with the resulting SQL:

exec DoTest @a='';DELETE FROM SomeTable --=N'text'

I have demonstrated a method of sending a maliciously crafted parameter name to the SqlCommand class and managing to send to the SQL Server a query that should achieve a destructive result. So why doesn't it actually do anything?

The explanation is in the EventClass column of the SQL Profiler. While a normally executed SQL command (let's say from SQL Management Studio) has event classes of SQL:BatchStarting and SQL:BatchCompleted, the query resulting from my attempts have an EventClass of RPC:Completed. It appears that the RPC method of sending queries to the SQL Server doesn't allow for several commands separated by a semicolon. The result is that the first command is executed and the rest are apparently being ignored. The TDS protocol documentation shows that the RPC method is a system of executing stored procedures by sending a binary structure that contains stuff like the name of the procedure, the parameters and so on. Since an SQL text is actually translated into a call to the sp_executesql stored procedure, RPC is used for both types of SqlCommand: Text and StoredProcedure.

I don't have the time to explore this further, but I wonder if this can be used for any type of SQL injection. To make sure, try to check the names of the parameters, if they come from user input.