Note: Since there have been several comments on this, I'm using parameterization at the application layer in the security sense of using the CreateParameter method. I'm not talking about parameterized queries with respect to execution plans or the specific use of sp_executesql. I thought I made that clear with me saying "proper parameterization at the application layer" but since there have been several comments on that, it must not be.

One of the main defenses touted against SQL injection attacks is to use proper parameterization at the application layer. But while this gets most of the cases, there are clearly examples where this alone fails. For instance, consider the stored procedure:

CREATE PROC dbo.usp_ExecuteSQL
  @SQL NVARCHAR(4000)
AS
BEGIN
  EXEC(@SQL);
END;

Now I know the natural response by most folks is, "I would never see that in a production application." Perhaps not. But I have. In one organization's main application I found this procedure not once, but twice (albeit with different names). There was a standard in place that all database access from the application had to be done through stored procedures. The standard was met. But naturally because of such access, you can imagine what the permissions looked like within the database. And you can imagine the abuse that could have been performed through these types of stored procedures.

I know this is an extreme example, but it reflects that just focusing on parameterization isn't enough. For this stored procedure, the parameter would be defined properly, but that wouldn't stop SQL code from running in the back-end. Now I know the argument is, "Well, it's because the stored procedure used dynamic SQL." True enough. However, there are lots of cases out there where dynamic SQL solutions are running in production. For instance, I've seen things like the following:

USE [AdventureWorks2008];
GO

CREATE PROC dbo.usp_ReturnPeople
  @Sort NVARCHAR(100)
WITH EXECUTE AS SELF
AS
BEGIN
  DECLARE @SQL NVARCHAR(MAX);
  
  SET @SQL = 'SELECT Title, FirstName, MiddleName, LastName, Suffix  
              FROM person.Person
              ORDER BY ' + @Sort;
  EXEC (@SQL);
END

This stored procedure falls prey to situations like where @Sort can be sent in as '1;DELETE FROM person.Person;' which would effectively delete all the data in that table. And since @Sort would pass the parameterization check, using parameters doesn't serve as an effective control to prevent the SQL injection. Now I know there are techniques involving XML or using the CASE statement in the ORDER BY clause, but these aren't immediately obvious. As a result, it is possible to see an example like I've given above than those solutions. And yes, one could pare down the size of the parameter and that would help greatly, but when folks are trying to be flexible, these things aren't necessarily on their radar. Nor would a consideration be made to use EXECUTE AS with a defined user account that only has SELECT permissions against the table. And unless there is stringent code review, these types of things may slip into production and once they are there, they have to stay there until there is time to do another build.

Therefore, if we rely strictly on parameterization as our defense, we can still be beaten, even without someone working maliciously on the back-end code. Both examples I've given were written by well-meaning people who were trying to develop a reasonable solution. And the solutions they came up with solved their problems and they moved on. They didn't consider the security ramifications. Maybe they weren't aware enough to them to code defensively against them. Or maybe they were under a stringent deadline and were trying to get the job done. Whatever the situation, the vulnerability is there. So how do we guard against that sort of thing? Two things come to mind:

1. Run scans against the code quickly looking for key words like EXEC or EXECUTE. That would have flagged both of these stored procedures. The issue then is whether or not there would have been time to get these stored procedures rewritten to meet a deadline, if there was one. However, if it was early enough in the cycle, such automated code searches would have caught both examples. And this is the perfect time to look for other bits of problem code like when we see CURSOR or a SELECT *.
2. Perform input validation at the application layer. This one is much harder because most key words are normal words so if you're dealing with a string value and you see the word select or delete, is that valid or not? However, in the case of the sorting stored procedure, a check for any of the tell-tale signs of an injection attack, like the use of characters such as a semi-colon or single quote or a greater than or less than sign could have caught this. Looking for these types of flags will help, but they aren't the total solution.

Now none of this is new. In fact, David Litchfield talked about second order SQL injection attacks at BlueHat and has published work on lateral SQL injection attacks in Oracle. The crux of all of this is to say there isn't one single vaccine to cure SQL injection. Parameterization is effective, yes, but a cure all it's not. There can be structures in the database that permit SQL injection attacks despite parameterization. And therefore, we must not stop at just using parameterization, but we must investigate further to ensure that we catch those vulnerabilities.