Protecting Against SQL Injection

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 challenge@sqlmag.com 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' )
GO
CREATE 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.