SQL Server’s sp_executesql Does Not Protect You from SQL Injection

Many coders of SQL have learned we can dynamically construct SQL statements inside of stored procedures and then execute the constructed SQL.  In Microsoft’s SQL Server product there are two commands we can choose for running the constructed SQL:

• EXEC (EXEC is an alias for EXECUTE, both do the same thing).
• sp_executesql.

We SQL Server “experts” often advise coders to use sp_executesql instead of EXEC when running dynamically constructed SQL statements to reduce the risk of SQL Injection, and this is good advice.  But it is not the use of sp_executesql that prevents SQL injection, it is the use of parameters with sp_executesql that helps protect against SQL Injection.  You can still construct SQL dynamically and run that SQL using sp_executesql and be affected by a SQL Injection attack.

If you use parameters to substitute all the values in the SQL and then use sp_executesql you have probably eliminated the SQL Injection risk; but as a developer this means you may be unable to dynamically construct the SQL you want to run.

When you use sp_executesql parameters correctly, you can only replace data values in your SQL statement with values from parameters, not parts of the SQL itself.  Thus we can do this to pass in a value for the UserName column:

declare @sql nvarchar(500)
declare @dynvalue nvarchar(50)
select @dynvalue=’testuser’
SET @sql = N’SELECT * FROM appusers WHERE UserName = @p1′;
EXEC sp_executesql @sql, N’@p1 nvarchar(50)’, @dynvalue

But the following code will return an error when trying to pass in the name of the table:

declare @sql nvarchar(500)
declare @dynvalue nvarchar(50)
select @dynvalue=’appusers’
SET @sql = N’SELECT * FROM @p1′;
EXEC sp_executesql @sql, N’@p1 nvarchar(50)’, @dynvalue

Msg 1087, Level 16, State 1, Line 1
Must declare the table variable “@p1”.

If you are dynamically constructing SQL, and you are changing parts of the SQL syntax other than the value of variables, you need to manually write the code yourself to test for the risk of SQL injection in those pieces of the SQL.  This is difficult to do and probably best handled by the application calling the stored procedure.  I recommend that the calling program do the following at a minimum before calling a stored procedure that dynamically constructs SQL:

1. Validate the length of the parameter. Don’t allow input longer than the maximum length expected.  If the stored procedure allows a column to be passed in that is used for sorting in an ORDER BY clause, and all of your column names are less than or equal to 10 characters in length, then make sure that the length of the parameter passed in does not exceed 10 characters.
2. Don’t allow a single single quote, make sure to replace a single single quote with two single quotes.
3. Don’t allow other special characters or even commands such as a semicolon or the UNION keyword or two hyphens that represent a comment in SQL.
4. Don’t allow ASCII values greater than 255.

That short list is not sufficient to prevent all SQL Injection attacks, but it will block a lot of them and gives you an idea of the challenge involved in preventing SQL Injection attacks from being effective.

If you would like to see for yourself how the EXEC and sp_executesql statements behave I have provided a script you can use to get started with.  Related to this article, the most important query to understand is the last one because it shows a case of SQL injection even though the dynamically generated SQL is ran using sp_executesql.

–1. Create tables and add rows
DROP TABLE InjectionExample
GO
DROP TABLE Users
GO
CREATE TABLE InjectionExample ( MyData varchar (500) NULL)
GO
INSERT INTO InjectionExample VALUES(‘the expecteddata exists’), (‘data only returned via sql injection’)
GO
CREATE TABLE Users( username varchar(50) NULL,[password] varchar(50) NULL)
go
INSERT INTO Users VALUES (‘user1′,’password1’), (‘user2′,’password2’), (‘user3′,’password3’)
GO
–2. Run a test using EXEC with data the programmer expects
declare @sql nvarchar(500)
declare @p1 nvarchar(50)
select @p1 = ‘expecteddata’
select @sql = ‘SELECT * FROM InjectionExample WHERE MyData LIKE ”%’ + @p1 + ‘%”’
exec (@sql)–returns 1 row as expected
GO

–3. Run a test using EXEC with data the hacker used for sql injection
declare @sql nvarchar(500)
declare @p1 nvarchar(50)
select @p1 = ”’ or 1 = 1–‘
select @sql = ‘SELECT * FROM InjectionExample WHERE MyData LIKE ”%’ + @p1 + ‘%”’
exec (@sql)–returns all rows – vulnerable to sql injection
GO

–4. Run a test using sp_executeSQL to prevent this SQL Injection
declare @sql nvarchar(500)
declare @p1 nvarchar(50)
select @p1 = ‘expecteddata’
select @sql = N’select * from InjectionExample WHERE MyData LIKE ”%” + @param1 + ”%”’
exec sp_executesql @sql, N’@param1 varchar(50)’, @p1
GO

–5. Run a test using sp_executeSQL to prevent this SQL Injection – hacker data returns no results
declare @sql nvarchar(500)
declare @p1 nvarchar(50)
declare @pOrd nvarchar(50)
select @p1 = ”’ or 1 = 1–‘
set @pOrd = ‘MyData’
select @sql = N’select * from InjectionExample WHERE MyData LIKE ”%” + @param1 + ”%” order by ‘ + @pOrd
exec sp_executesql @sql, N’@param1 varchar(50)’, @p1
GO

–6. But sp_executesql does not protect against all sql injection!
–In this case, sql is injected into the @pOrd variable to pull data from another table
declare @sql nvarchar(500)
declare @p1 nvarchar(50)
declare @pOrd nvarchar(50)
set @p1 = ‘expecteddata’
set @pOrd = ‘MyData; SELECT * FROM Users’
select @sql = ‘select * from InjectionExample WHERE MyData LIKE ”%” + @param1 + ”%” order by ‘ +@pOrd
exec sp_executesql @sql, N’@param1 nvarchar(50)’, @p1