After securing our web servers and application code, it’s time to clamp down on SQL injection as a threat. As a quick reminder, inside SQL Server stored procedures, only execution of dynamic SQL poses any threat; static stored SQL code is invulnerable to SQL injection attacks. For protecting dynamic SQL stored procedures, there are two methods available: properly escaping your EXEC statement, and sp_executesql.
QUOTENAME And REPLACE
Kim Tripp does an excellent job at showing you how to protect yourself against SQL injection attacks by making appropriate use of QUOTENAME and REPLACE. I highly recommend that you read that blog post, as she does a better job than I could of explaining it.
The method I prefer to use is sp_executesql. I call this the Tripp-Bertrand debate, as Kim Tripp argues that you should use exec, but Aaron Bertrand takes the opposite approach. I’ll concede that there are circumstances in which sp_executesql is slower, but I would respond by arguing that it’s a lot easier to get right. I’ll admit that I don’t work in a performance-critical environment, but I think even if I did, I would still go for the easy-to-understand answer first and move to the potentially better-performing option if and only if we do experience performance problems—premature optimization being the root of all evil, after all…
The sp_executesql command is pretty easy to understand. It takes three primary parameters: @statement, @params, and a series of @param(x). Here is an example of sp_executesql in action:
declare @BeginID int = 3, @EndID int = 15; declare @sql nvarchar(max); set @sql = N'select * from Production.ProductSubcategory where ProductSubcategoryID between @BeginID and @EndID'; exec sp_executesql @sql, N'@BeginID int, @EndID int', @BeginID, @EndID;
By adding the parameters @BeginID and @EndID directly into the statement (@sql), we allow the SQL Server engine to parameterize these variables correctly, ensuring that users cannot escape out of the query.
The reason I think sp_executesql is easier than quotename-and-replace is that you can easily see if you’re doing something wrong with sp_executesql. I can look at this query in SSMS and see that all of the parameters are inside the statement text. If I see a variable outside the statement text, I can tell immediately that there could be a problem, and ask the developer to rewrite his query. Compare the image above with the image below, and you can see the difference immediately: @Filter in the below image is outside the statement, so it’s potentially vulnerable.
As long as all of your parameters stay inside the statement while using sp_executesql, there is no way for a user to perform a SQL injection attack. I have already shown this to be the case: back in Part 4B, I showed you how .NET parameterizes queries. It uses sp_executesql correctly, and no matter what we tried to do to the webpage, it simply would not succumb to SQL injection attacks.
The final piece of protection is to establish a good set of permissions for your stored procedures. If your procedure only hits three tables in AdventureWorks, it probably doesn’t need to connect to other databases (or even other tables). At the very least, you could set up read-only accounts for the stored procedures which are not supposed to do inserts/updates/deletes, and for the insert/update/delete group, restrict that account so it can’t perform actions like dropping tables or databases, or creating logins.
For this, there are two good resources. The first is Kim Tripp’s EXECUTE AS section in the post I linked to above (and again just now), and the second is Erland Sommarskog’s outstanding work, entitled Giving Permissions through Stored Procedures.