What Is SQL Injection?

Say we have a stored procedure that queries the Stack Overflow database . We have two separate parameters, @DisplayName and @Location, so folks can search for people by name, location, or both.

For performance reasons, we decide to build dynamic SQL:

CREATE OR ALTER PROC dbo.UserSearch 
	@DisplayName NVARCHAR(40) = NULL,
	@Location NVARCHAR(100) = NULL AS
BEGIN
DECLARE @StringToExecute NVARCHAR(4000);
SET @StringToExecute = N'SELECT * FROM dbo.Users WHERE 1 = 1 ';
 
IF @DisplayName IS NOT NULL
	SET @StringToExecute = @StringToExecute + N' AND DisplayName = ''' + @DisplayName + N''' ';
 
IF @Location IS NOT NULL
	SET @StringToExecute = @StringToExecute + N' AND Location = ''' + @Location + N''' ';
 
PRINT @StringToExecute;
EXEC(@StringToExecute);
END
GO

When we run it, it works, and we can see the query on the Messages tab in SSMS:

Finding Bobby Tables

But look what happens when the bad guy runs it:

Well, that’s not good

The bad guy can:

• Close the DisplayName search string by adding a couple of apostrophes
• Add a semicolon to end the first query
• Add his own evil query, like drop database or create login or whatever
• Finish the batch and ignore any subsequent syntax errors by throwing in a couple of minus signs

That’s a really simplistic example, but it can get way worse, as Bert Wagner explains in this GroupBy session on SQL injection .

The first step in avoiding this problem is to avoid using user inputs as-is. Pass the parameters in to sp_executesql instead, like this:

CREATE OR ALTER PROC dbo.UserSearch 
	@DisplayName NVARCHAR(40) = NULL,
	@Location NVARCHAR(100) = NULL AS
BEGIN
DECLARE @StringToExecute NVARCHAR(4000);
SET @StringToExecute = N'SELECT * FROM dbo.Users WHERE 1 = 1 ';
 
IF @DisplayName IS NOT NULL
	SET @StringToExecute = @StringToExecute + N' AND DisplayName = @DisplayName ';
 
IF @Location IS NOT NULL
	SET @StringToExecute = @StringToExecute + N' AND Location = @Location ';
 
PRINT @StringToExecute;
EXEC sp_executesql @StringToExecute, 
	N'@DisplayName NVARCHAR(40), @Location NVARCHAR(100)', 
	@DisplayName, @Location;
END
GO

sp_executesql lets you pass in a list of parameter definitions, and then pass the parameters in safely. Now, when the bad guy calls it, here’s what the query looks like:

Whew – safer.

The bad guy doesn’t get a list of your databases.

Avoid EXEC, and use sp_executesql instead. Then, for more learning, check out:

• The Curse and Blessings of Dynamic SQL by Erland Sommarskog
• Exploits of a Mom by XKCD – the origin of Bobby Tables
• Books Online’s coverage of SQL Injection