Congratulations to Zsolt Peter, a developer for Cosys Ltd. in St. George, Romania, and Kristofer Andersson, a senior software developer for RR Technologies in Fort Lauderdale, Florida. Zsolt won first prize of $100 for the best solution to the April Reader Challenge, "Protecting Against SQL Injection." Kristofer won second prize of $50. Here’s a recap of the problem and the solution to the April Reader Challenge.

Problem:
Jeff is a database architect, responsible for designing and developing database solutions using SQL Server 2000. Jeff's main concern when performing code reviews is the use of dynamic SQL in stored procedures that his Web application uses for search purposes. He's worried that the dynamic SQL will put his system at risk for SQL injection attacks, in which an attacker compromises the system's security by executing unauthorized code.

The following example uses the sample Northwind database objects to show how a user exploits the dynamic SQL in a stored procedure. The Web page in this example lets the user specify searches for customers or suppliers based on relationship, city, company, or contact name. The Web page executes the following stored procedure, SearchCustomersAndSuppliers:

CREATE PROCEDURE SearchCustomersAndSuppliers
(@Relationship varchar(9) = NULL
,@City nvarchar(30) = NULL
,@CompanyName nvarchar(80) = NULL
,@ContactName nvarchar(60) = NULL
,@Debug bit = 0)
AS
BEGIN
  DECLARE @SQL nvarchar(4000), @WhereClause nvarchar(500)
  SET @SQL = N'SELECT City, CompanyName, ContactName, 
    Relationship FROM "Customer AND Suppliers BY City"'
  SET @WhereClause = N' WHERE 1=1'
  IF @Relationship IS NOT NULL
    SET @WhereClause = @WhereClause + ' AND Relationship = 
      ''' + @Relationship + ''''
  IF @City IS NOT NULL
    SET @WhereClause = @WhereClause + ' AND City LIKE N''' 
      + @City + ''''
  IF @CompanyName IS NOT NULL
    SET @WhereClause = @WhereClause + ' AND CompanyName LIKE N''' 
      + @CompanyName + ''''
  IF @ContactName IS NOT NULL
    SET @WhereClause = @WhereClause + ' AND ContactName LIKE N''' 
      + @ContactName + ''''
  IF @Debug = 1
    PRINT @SQL
  IF @Debug = 1
    PRINT @WhereClause
  EXEC(@SQL + @WhereClause)
END
GO

The parameters for city, company, and contact name let users conduct wildcard searches by using LIKE patterns, meaning they can search for words "like" cty and still get city. The @Relationship parameter limits the search to a specific value.

Using the SearchCustomersAndSuppliers stored procedure, Jeff issues the following command to return the generated queries and a list of customers from the Customers table, which demonstrates the security danger of forming dynamic SQL without specific checks:

  EXEC SearchCustomersAndSuppliers
    @CompanyName = N'%'';SELECT * FROM customers;PRINT ''',
    @Debug = 1

By injecting SQL code into the search parameters, an attacker can potentially perform unauthorized actions depending on the permissions of the user account, the Web page, or application executing the stored procedure.

Help Jeff write the dynamic SQL to prevent SQL injection attacks. Ultimately, he wants to suggest to the developers a more secure dynamic SQL approach as a standard technique for stored procedures that require dynamic SQL.

Solution:
To execute the dynamically generated SQL statement in the SearchCustomersandSuppliers stored procedure, Jeff uses the sp_executesql system stored procedure, which modifies the original stored procedure, but retains the dynamic SQL and search capabilities. Sp_executesql is more powerful than the EXEC statement that dynamically executes an arbitrary string that can be reused multiple times or have embedded parameters. Additionally, the parameter values that Jeff uses with sp_executesql can be either variables or literal values. With the sp_executesql stored procedure, Jeff stops the parameter values from being used to directly form the SQL statement. Here is the new stored procedure, SearchCustomersAndSuppliers_1:

CREATE PROCEDURE SearchCustomersAndSuppliers_1
(@Relationship varchar(9) = NULL
,@City nvarchar(30) = NULL
,@CompanyName nvarchar(80) = NULL
,@ContactName nvarchar(60) = NULL
,@Debug bit = 0)
AS
BEGIN
  DECLARE @SQL nvarchar(4000), @WhereClause nvarchar(500)
  SET @SQL = N'SELECT City, CompanyName, ContactName, 
    Relationship FROM "Customer AND Suppliers BY City"'
  SET @WhereClause = N' WHERE 1=1'
  IF @Relationship IS NOT NULL
    SET @WhereClause = @WhereClause + ' AND RelationShip = 
      @Relationship$'
  IF @City IS NOT NULL
    SET @WhereClause = @WhereClause + ' AND City LIKE @City$'
  IF @CompanyName IS NOT NULL
    SET @WhereClause = @WhereClause + ' AND CompanyName LIKE 
      @CompanyName$'
  IF @ContactName IS NOT NULL
    SET @WhereClause = @WhereClause + ' AND ContactName LIKE 
      @ContactName$'
  SET @SQL = @SQL + @WhereClause
  IF @Debug = 1
    PRINT @SQL
  EXEC sp_executesql
    @SQL,
    N'@RelationShip$ varchar(9), @City$ nvarchar(30), 
      @CompanyName$ nvarchar(80), @ContactName$ nvarchar(60)',
    @RelationShip$ = @RelationShip,
    @City$ = @City,
    @CompanyName$ = @CompanyName,
    @ContactName$ = @ContactName
END
GO

Jeff retains most of the code that performs the checks for the input parameters, but the WHERE clause generation statement now uses placeholder variables instead of search values. SQL Server passes the variable values to the sp_exceutesql call. The dynamic SQL statement that searches only by company name looks like:

SELECT City, CompanyName, ContactName, Relationship
 FROM "Customer AND Suppliers BY City"
 WHERE 1=1 AND CompanyName LIKE @CompanyName_

The sp_executesql call passes the value for the @CompanyName variable (which is the stored procedure input parameter) at the time of execution. Now if Jeff executes the statement that lets him read the Customers table, the statement won’t return any results because the input value is considered as a CompanyName column value, which prevents the injection of SQL code by intruders. To see Jeff’s new dynamic SQL stored procedure in action, execute the following statement:

EXEC SearchCustomersAndSuppliers_1
  @CompanyName = N'%'';SELECT * FROM customers;PRINT ''',
  @Debug = 1

Note that because the search that contains the injected SQL statement is treated as a string value by the sq_executesql call, the new stored procedure doesn’t return the same rows as the original. If you run both the stored procedures with valid search strings, you’ll get the same results.