When I first heard about SQL injection attacks and learned how to prevent them I was confused. I didn’t understand how introducing a potentially destructive string into a batch could produce different results depending on how it was introduced. Include ‘;DROP TABLE ToDrop
-- as a search condition in a WHERE clause and usually your query returns no data. Include it in the construction of a dynamic SQL string and you’ve lost not only your favorite spaghetti rum cookie recipe but everything that was in the table that just got dropped.
Eventually I learned. The reason the string fragment is sometimes dangerous and other times benign is that the string gets introduced to the query processor at different times and, depending on how and when it is introduced, it is treated differently.
The compilation process follows a basic pattern of parsing the T-SQL batch, binding your statements to real objects, optimizing the statements to produce a good-enough execution plan and, finally, executing the query. Understanding the entire compilation pipeline isn’t necessary to understand SQL injection attacks though. The important thing to know is that during the compilation stage the T-SQL gets parsed by the Query Processor. If someone can introduce an errant string into your T-SQL code text before it is parsed they can modify how your T-SQL code is interpreted. The parsing of the batch is the danger zone of SQL injection attacks.
A batch can contain multiple independent SQL Statements. SQL injection attacks take advantage of this by inserting another SQL statement into the batch as it is getting compiled so that will be executed as if it were supposed to be there. Executing a batch that contains two simple SQL statements can look something like this:
This produces an execution plan that looks like this:
What this shows us is that two independent SQL Statements were executed as part of a single batch. It also shows us that a carriage return isn’t necessary to separate the two SQL statements. In T-SQL the semi-colon functions as a statement separator.
Now, imagine giving an external user access to SSMS and the ability to write the commands directly into a query window that has access to your database. When we use EXEC and dynamic SQL we risk that exact scenario. The EXEC command creates a batch that is independent of the batch that launches it. This batch follows the same basic compilation process of every other batch with parsing being the first step. This query, plus a peek at the plan cache, demonstrates that this is the case.
SET @stmt = 'SELECT * FROM dbo.Test WHERE Col1 = 1'
SELECT * FROM dbo.Test WHERE Col1 = 1
Line 3 is the batch created by the T-SQL shown above. Line 2 is the independent batch created by the EXEC command in that T-SQL.
Knowing that when parsing a batch SQL Server treats the semi-colon as a statement separator and that the EXEC command will result in a new batch being created is the key to understanding how SQL injections attacks work. If we allow an outside individual to affect the string that gets fed to the EXEC command that individual can inject syntax that modifies how the batch is parsed and, ultimately, what gets executed. They can do this if we create a dynamic SQL statement that directly incorporates a string which is passed to us from some web form or win32 application. This sample simulates this scenario.
DECLARE @var char(1)
DECLARE @stmt varchar(100)
SET @var = '1'
SET @inject = ''''';DROP TABLE ToDrop--'
SET @stmt = 'SELECT * FROM dbo.Test WHERE Col2 = ' + @inject + ' AND Col1 = ' + @var
In this sample I am declaring @inject and @var as variables but these could easily be input parameters of an encapsulating stored procedure. When @inject and @var get substituted into @stmt the resulting string becomes:
This creates a T-SQL batch that when passed into EXEC is parsed like every other batch submitted to SQL Server. The semi-colon causes what was intended to be a single command to be separated into two separate commands. The
-- at the end of the injection causes the remainder of the command to be treated as comments. This keeps the injected string from causing a syntax error since there could easily be more T-SQL in the string whose syntax is no longer compatible with the newly formed T-SQL. I simulate this by adding a another search condition. If this second statement is syntactically correct and binds to objects that really exist in the database, it will execute. The query plan produced by the sample above looks like this:
-- AND Col1 = 1 at the end of Query 2. If AND Col1 = 1 had been added to the end of DROP TABLE ToDrop there would have been a syntax error.
So how do we protect ourselves from SQL injection attacks? If we must use dynamic SQL then using sp_executesql is the key. Rewriting the previous SQL statement using sp_executesql looks something like this:
DECLARE @var int
DECLARE @stmt nvarchar(100)
DECLARE @paramdef nvarchar(500)
SET @var = N'1'
SET @inject = N''''';DROP TABLE ToDrop<code>--</code>'
SET @paramdef <a href="mailto:=N'@param1">=N'@param1</a> varchar(100),@param2 int'
SET @stmt = N'SELECT * FROM dbo.Test WHERE Col2 = @param1 AND Col1 = @param2'
EXEC sp_executesql @stmt,@paramdef,@param1 = @inject,@param2=@var
When the statement in @stmt is parsed during compilation @param1 and @param2 are practically ignored. SQL Server understands that these are placeholders for values that will be included later and produces a parameterized query.
Notice there is no semi-colon separating two statements in the batch. There is no drop table statement. And notice the execution plan is safe.
When this query is finally executed the values in @inject and @var are substituted for @param1 and @param2. The previously dangerous string is now introduced as a simple search condition that returns no results because there is no record where Col2 = ”;DROP TABLE ToDrop
In the sp_executesql example there is no direct path from @inject to the batch. It is never included during the parsing of the select statement and thus cannot be injected into the batch. This doesn’t mean that sp_executesql by itself is the panacea for all injection woes. It must be used correctly. It is possible to execute the unsafe statement from the first example with sp_execute as well. To make it safe you must use parameters properly.
If you don’t want to use parameterized queries but still want to be protected from SQL Injection you can use QUOTENAME and REPLICATE to sanitize your input. Examples of that are here.