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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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:
But look what happens when the bad guy runs it:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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:
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
4 Comments. Leave new
I think this is not the best example since you start with “For performance reasons, we decide to build dynamic SQL:” and then you use sp_executesql that is going to parameterize the query. Probably we are going to end up with the same performance issues of using the sp directly in the first time, Or am I missing something?
I’m not demonstrating performance tuning in this post. The example SI strictly to show SQL injection. We have lots of other cool posts about dynamic SQL if you’re interested in performance tuning. Thanks!
I am (and every reader is) interested in both.. It will be cool to have an article that shows how to use secure dynamic SQL while preserving the benefits of using dynamic SQL to improve performance. Showing a way to secure dynamic SQL that voids the advantages of using dynamic SQL seems like a loophole. Anyway it was just a suggestion.
SQL Angel – sure, check out Erland Sommarskog’s short post: http://www.sommarskog.se/dynamic_sql.html