Avoiding Common Mistakes About SQL Injection

When developers try to prevent SQL injections bugs, they might employ several methods . Nonetheless, a clever attacker can potentially thwart a developer s attempts because none of these techniques fully protect the application from SQL injection. Instead, they give a false sense of security. This section discusses shortcomings of these techniques so that you are aware they are not a solution that prevents SQL injection bugs . Even if all of the following techniques are used, the code can still be vulnerable.

Here are some common albeit still insecure methods a developer might use to attempt to make an application safe:

• Escape all single quotation marks in the input

• Remove semicolons to block multiple queries

• Use only stored procedures

• Remove unwanted stored procedures

• Place the computer that runs SQL Server behind a firewall to prevent access

Escape Single Quotation Marks in Input

In previous examples, attackers are able to inject their own SQL statements by using a single quotation mark to break out of the current statement. One way developers attempt to prevent single quotation marks from causing a SQL injection is to escape single quotation marks. In SQL Server, you can escape a single quotation mark by using another single quotation mark. For instance, the earlier search engine example took the input directly from the query string to build the SQL statement. Instead, a developer might do the following:

 // Get keyword from query string, and escape single quotation marks. string strKeyword = Request.QueryString["keyword"].Replace("'", "''"); string sqlQuery = "SELECT * FROM Links WHERE Keyword LIKE '%" +     keyword + "%'"; 

If the input value of keyword is oh'boy , the SQL statement would look like the following:

 SELECT * FROM Links WHERE Keyword LIKE '%oh''boy%' 

If the single quotation mark is escaped, the attacker can t break out of the SQL statement, right? Not necessarily . This might be true for data that appears between single quotation marks, but other types don t have to be quoted, such as numerical values, as shown in the following example:

 SELECT * FROM Products WHERE ProductID = 5; DROP TABLE Products 

All input should be properly checked to make sure it is valid; otherwise , unverified input could lead to SQL injection.

Other database systems might escape single quotation marks differently. MySQL, for example, uses a backslash (\) to escape a single quotation mark. In that case, if the input is 'DROP TABLE from Links-- , the statement would become:

 SELECT * FROM Links WHERE Keywords LIKE '%\'DROP TABLE Links--%' 

Because the single quotation mark is escaped using the backslash, the single quotation mark did not cause the attacker to break out of the statement. However, what would happen if a backslash is also used to escape the backslash? If the input was \'DROP TABLE from Links-- , the query would be the following:

 SELECT * FROM Links WHERE Keywords LIKE '%\'DROP TABLE Links--%' 

Notice, the application escaped the single quotation mark in the user s input with a backslash. However, the attacker was able to escape the backslash, thus still causing the SQL injection.

Remove Semicolons to Block Multiple Statements

In SQL, it is considered good syntax to end a statement with a semicolon even though the semicolon is actually optional. Often, developers mistakenly think that the proper syntax to create multiple statements is to use a semicolon to separate each statement. Consider the following example:

 SELECT * FROM Customers; SELECT * FROM Orders; 

As mentioned earlier in the chapter, the semicolon is used to denote two separate SQL statements. In this example, one will select all the records from the Customers table, whereas the other will select all records from the Orders table.

If a developer assumes that a semicolon is considered bad because it can be used to separate SQL statements, any input might be rejected if it contains a semicolon. However, the flaw in that logic is thinking that a semicolon is necessary to separate multiple SQL statements in a query when semicolons are really optional. As such, the following SQL statement will also execute both queries:

 SELECT * FROM Customers SELECT * FROM Orders 

Attackers can bypass a flawed filter that attempts to reject requests that contain a semicolon. The mistake developers make is thinking that the semicolon is needed to cause multiple statements to execute; it isn t.

When you are testing, if you see semicolons are being removed or rejected, be alert to the possibility that this flawed logic is in use, along with its corresponding bugs.

Use Only Stored Procedures

Often, stored procedures are used because a developer believes they prevent SQL injection bugs, but that isn t true. Earlier in this chapter, we discussed how the logic of a stored procedure can contain a SQL injection bug if it uses EXEC , EXECUTE , or sp_executesql . In addition to causing a SQL injection inside of a stored procedure, an attacker can also cause an injection into the way an application calls the stored procedure. Although it is a bit trickier to get a statement to run when the input is used in a stored procedure, it isn t impossible . Look at the following call to the stored procedure sp _ GetAccount , which takes two arguments for the user and password:

 // Get user and password from query string. string user = Request.QueryString["user"]; string password = Request.QueryString["password"]; string sqlQuery = "exec sp_GetAccount '" + user "', '" + password + "' "; 

If an attacker uses the value Bryan' DROP TABLE Accounts-- for the user parameter, the query would fail because the SQL statement has invalid syntax. The statement would look like the following when executed:

 exec sp_GetAccount 'Bryan' DROP TABLE Accounts--', '' 

Notice that sp_GetAccount actually takes two arguments, so the call to the stored procedure can t execute because the attacker s injected data causes the query to use only one parameter.

Now, if the syntax of the stored procedure is known or guessed, the input could supply valid arguments to call the stored procedure. Then the attacker s SQL statement could be injected. For the preceding example, the attacker could supply the value Bryan', '' DROP TABLE Accounts-- for the query to succeed. The query that is executed is the following:

 exec sp_GetAccount 'Bryan', '' DROP TABLE Accounts--', '' 

Although the call to sp_GetAccount probably won t return any results, the second statement to drop the Accounts table will succeed, depending on the permissions of the account used to connect to the database. (SQL permissions are discussed later in this chapter and also in Chapter 13, Finding Weak Permissions. )

Remove Unwanted Stored Procedures

A good defense- in-depth measure is to remove unwanted or potentially dangerous stored procedures so that you limit what attackers can do if they are able to cause a SQL injection bug. For instance, the stored procedure xp_cmdshell allows arbitrary system commands to be executed. If this stored procedure is deleted, attackers can t call it, right? Maybe. Maybe not. Removing unwanted stored procedures is a good method for defense in depth, but it is useless if the attacker is able to cause a SQL injection bug in an application that connects to the database as a high privilege account because the attacker can actually just add back the stored procedure to the database. For instance, executing the following stored procedure on Microsoft SQL Server 2000 will cause the xp_cmdshell stored procedure to be re-added if it was deleted:

 sp_addextendedproc xp_cmdshell, 'xplog70.dll' 

Don t let removing unwanted stored procedures be the only defense protecting your application from SQL injection issues.

Place the Computer That Runs SQL Server Behind a Firewall

Having a properly configured firewall can really protect your application from attackers, but as soon as you open a single port, attackers have an entry point into your system. Most Web applications require port 80 and port 443 (for Secure Sockets Layer, SSL) to be open . The network can even have another firewall between the Web server and the back-end database server to prevent any connections to the computer running SQL Server other than the ones that originate from the Web server. However, if the Web server has a Web application that connects to the back-end computer running SQL Server and if the application contains a SQL injection bug, an attacker can run code on the back end. Remember, firewalls aren t designed to prevent SQL injection bugs, so they should not be your application s method of defense against this type of attack.

Attacker needs to know the database schema  

As discussed previously, attackers are able to get their SQL queries to execute because they are able to provide input that is valid syntax when calling a stored procedure. But if attackers can t install your application on their own machine and the database schema isn t already known, they won t easily be able to execute well- formed queries, right? Wrong. Although it might not be easy, an attacker can use specific techniques to determine the schema.

For example, if the attacker causes an invalid syntax error when sp_GetAccount is called, the Web application might display the following error message:

 Line 1: Incorrect syntax near 'Bryan'. Unclosed quotation mark before the character string ''. 

Although it might not be obvious at first, this message actually reveals useful information to an attacker. First, the attacker knows that the input caused a SQL error, which is a sign that the application has a SQL injection vulnerability. Second, the error is relatively explicit in describing what is wrong with the statement. To learn more about determining a SQL statement using the error message, you can read Web Application Disassembly with ODBC Error Messages by David Litchfield, which was presented at BlackHat in 2001 and is available at http://www.blackhat.com/presentations/win-usa-01/Litchfield/bh-win-01-litchfield.doc .

Even if all error messages are caught and are not returned to the user, an attacker can still detect the schema. For example, an attacker could use a technique known as blind SQL injection . The basic concept behind blind SQL injection is to add SQL statements to the input and observe the application s behavior to determine whether it is vulnerable. To understand how this is done, consider the following SQL statement:

 SELECT * FROM Orders WHERE ID = 5 AND 1=1 

This query selects all the records from the Orders table where the ID is equal to 5 . The AND 1=1 part of the query always evaluates to true, so everything works just fine. Now, imagine that the attacker s input is used to build the query. If the attacker supplies the value 5 , the order information is properly returned. If the attacker supplies the value 5 AND 1=1 and the same information is returned, the attacker might surmise that the application is vulnerable to a SQL injection bug because it didn t reject the input.

For more information about blind SQL injection, see the white paper by Kevin Spett of SPI Dynamics at http://www.spidynamics.com/whitepapers/Blind_SQLInjection.pdf .