Protecting Against SQL Injection

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)ASBEGIN  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)ENDGO

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)ASBEGIN  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$ = @ContactNameENDGO

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.

In addition to the dynamic SQL approach, replacing each single quote in the input with two single-quote characters is a method that handles the input parameter values. By replacing each single-quote with two single quotes, any parameter value injected by a SQL statement can be converted to a string value. You can do this by using the REPLACE() system function. The statement below shows how to perform a REPLACE() on the @CompanyName input parameter:

SET @CompanyName = REPLACE(   @CompanyName, '''', '''''' )

A shortcut for the REPLACE() function is using the QUOTENAME() system function, because QUOTENAME() uses a specified character to delimit a string value and escapes any occurrence of the delimiter in the input string. Be careful when you use QUOTENAME(), because QUOTENAME() can handle only 128 Unicode characters. If you pass a string longer than 128 characters to QUOTENAME(), the system function will convert the input to Unicode, then truncate it to 128 Unicode characters. In addition, the code will also convert other values you use with the statement that contains QUOTENAME() to Unicode based on T-SQL data-type precedence rules—resulting in further truncations. The following statement shows how using QUOTENAME() to escape any single quote in the input string causes truncation problems:

DECLARE @s char(512)SET @s = REPLICATE(‘x’, 512)SELECT QUOTENAME(@s,''''), DATALENGTH(QUOTENAME(@s,   '''')), LEN(QUOTENAME(@s, ''''))

To solve the specific problem I describe in this month’s Reader Challenge, Jeff can also use a static SQL statement to perform the search. To rewrite the SELECT statement in the challenge, you can use one of the two following methods:

-- Method 1  SELECT City, CompanyName, ContactName, RelationShip   FROM "Customer AND Suppliers BY City"   WHERE RelationShip = @RelationShip    AND (@City IS NULL OR City LIKE @City)    AND (@CompanyName IS NULL OR CompanyName LIKE @CompanyName)    AND (@ContactName IS NULL OR ContactName LIKE @ContactName)

-- Method 2  SELECT City, CompanyName, ContactName, RelationShip   FROM "Customer AND Suppliers BY City"   WHERE RelationShip = @RelationShip    AND City LIKE ISNULL(@City, City)    AND CompanyName LIKE ISNULL(@CompanyName, CompanyName)    AND ContactName LIKE ISNULL(@ContactName, ContactName)

Assuming that the columns are indexed, both of these static SQL methods suffer performance problems because the WHERE clause uses OR conditions with various columns and ISNULL() functions on columns, which can result in index scans and conservative query plans. So use these methods with care. Also consider that these methods don’t scale as well as the dynamic SQL approach in which the generated plan varies depending on the search parameters. A better way to rewrite the SELECT statement, and retain optimal performance, is to use a statement such as the following:

  SELECT City, CompanyName, ContactName, RelationShip   FROM "Customer AND Suppliers BY City"   WHERE RelationShip = @RelationShip    AND City LIKE ISNULL(@City, '%')    AND CompanyName LIKE ISNULL(@CompanyName, '%')    AND ContactName LIKE ISNULL(@ContactName, '%')

You can extend this SELECT statement approach to other data types by using appropriate boundary values. The statement also generates a fixed plan that performs well for various combinations of the input parameters.

MAY READER CHALLENGE:

Now, test your SQL Server savvy in the May Reader Challenge, "Trading Stocks" (below). Submit your solution in an email message to [email protected] by April 15. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Jane is a database programmer for a company that provides stock-trading services online. A SQL Server 2000 database stores the transactions users create. The database contains two tables, Stocks and TradeSummary, that contain information about traded stocks and the trade details. Here’s the tables' schema with sample data:

CREATE TABLE Stocks (    StockId int NOT NULL PRIMARY KEY CLUSTERED,    Market varchar(30) NOT NULL,    Ticker varchar(10) NOT NULL,    DisplayName varchar(30) NOT NULL,    UNIQUE(Market, Ticker))INSERT INTO Stocks VALUES( 1, 'NASDAQ', 'MSFT', 'Microsoft' )INSERT INTO Stocks VALUES( 2, 'NASDAQ', 'INTC', 'Intel' )INSERT INTO Stocks VALUES( 3, 'Tokyo', '6758', 'Sony' )INSERT INTO Stocks VALUES( 4, 'India', 'TTPW.BO', 'Tata' )GOCREATE TABLE TradeSummary (    StockId int NOT NULL REFERENCES Stocks( StockId ),    UserId int NOT NULL,    IsBuy char(1) NOT NULL CHECK( IsBuy in ('Y', 'N')),    TradeDt smalldatetime NOT NULL DEFAULT( current_timestamp ),    Price decimal(10, 2) NOT NULL,    Qty int NOT NULL)CREATE CLUSTERED INDEX TradeSummary_StockDt ON TradeSummary( StockId, TradeDt)INSERT INTO TradeSummary VALUES( 1, 1, 'Y', '2004-1-1 10:00', 26.35, 100 )INSERT INTO TradeSummary VALUES ( 1, 2, 'Y', '2004-1-1 11:00', 26.35, 50 )INSERT INTO TradeSummary VALUES ( 1, 2, 'N', '2004-1-1 12:00', -26.35, 50 )INSERT INTO TradeSummary VALUES ( 1, 3, 'Y', '2004-1-1 13:00', 26.35, 25 )INSERT INTO TradeSummary VALUES ( 1, 4, 'Y', '2004-1-1 10:00', 26.35, 125 )INSERT INTO TradeSummary VALUES ( 2, 1, 'N', '2004-1-1 09:00', -28.95, 100 )INSERT INTO TradeSummary VALUES ( 2, 2, 'Y', '2004-1-1 10:00', 28.95, 50 )INSERT INTO TradeSummary VALUES ( 2, 3, 'Y', '2004-1-1 11:00', 26.35, 25 )INSERT INTO TradeSummary VALUES ( 1, 1, 'Y', '2004-2-1 23:00', 26.35, 200 )INSERT INTO TradeSummary VALUES ( 1, 2, 'N', '2004-2-1 11:00', -26.35, 50 )INSERT INTO TradeSummary VALUES ( 1, 3, 'Y', '2004-2-1 12:00', 26.35, 25 )INSERT INTO TradeSummary VALUES ( 1, 4, 'Y', '2004-2-1 23:00', 26.35, 125 )INSERT INTO TradeSummary VALUES ( 2, 2, 'Y', '2004-2-1 10:00', 28.95, 50 )INSERT INTO TradeSummary VALUES ( 2, 3, 'Y', '2004-2-1 23:00', 26.35, 25 )GO

Jane needs to create a report that provides daily summary information about the various stock trades. For each stock, she wants daily stock details, the opening buy or sell price, the closing buy or sell price, the volume of stocks purchased, the highest buy or sell price, and the lowest buy or sell price. Help Jane write a query that provides these report details.