Finding SQL Injection Issues

This section discusses different approaches that you can use to find SQL injection bugs . The main goal is to find a way that input can break out of the application s SQL statement, thus enabling attackers to inject their own logic. In general, it is easier to find SQL injection bugs while looking through the application s code; however, this topic cannot be discussed until you understand the different ways to cause a SQL injection bug.

This section discusses the following:

  • General black box testing approach using a tool to monitor the SQL statements being executed, and different techniques that can be used to alter the logic of SQL statements

  • Using code reviews to find SQL injection bugs in how the SQL statements are constructed

Using a Black Box Testing Approach

When testing an application that uses a database, you should be able to monitor the computer running SQL Server to determine the statements that are executed. Following is the general testing approach you can use to find SQL injection issues in your application by monitoring the SQL statements:

  1. Determine the SQL statements the application is executing.

  2. Determine whether user -supplied data is used when constructing each SQL statement.

  3. Try the techniques discussed in this section for any user data that is used in the SQL statement to attempt to execute arbitrary SQL statements.

Determine Which SQL Statements Are Executed

Many database systems enable you to monitor the events, including the SQL statements, that are executing. Several utilities can be used to monitor these SQL trace events. The following examples use SQL Server Profiler for SQL Server; however, you can use other tracing tools if your application uses a different database system. Regardless of which tool you use, the general concept is the same.

Because it is possible for queries to be nested, it is important to make sure you configure the tracing tool to monitor all SQL statements that are executed. For instance, a stored procedure sp_Main could call another stored procedure sp_SubProc , and that stored procedure could execute additional SQL statements. An injection can occur at any of these levels. By default, SQL Server Profiler does not trace all the queries caused by a SQL statement, such as a stored procedure that is called within another stored procedure. To reach this level of detailed tracing, you can use the trace template SQLInjection.tdf, which is available on this book s companion Web site. The important thing is to make sure that you trace all the events for starting stored procedures and Transact -SQL (T-SQL) statements.

When the application executes any SQL statements, SQL Server Profiler will show what exactly is executed. If the application executes a stored procedure, SQL Server Profiler will show all of the statements the stored procedure also executes, including calls into other stored procedures. Figure 16-1 shows an example of queries getting executed.

image from book
Figure 16-1: SQL Server Profiler showing the SQL statements when a stored procedure is executed

Once you can see the trace events that occur when the application executes a SQL statement, the next step is to look for places where user-supplied data is used in the statement.

Tip  

Because SQL Server Profiler shows all of the trace events occurring on the computer running SQL Server, it is a good idea to filter the data to show just the trace events executed by your application. Also, if possible, run SQL Server Profiler on a server that is not being used by others.

Determine Whether User-Supplied Data Is Used in SQL Statements

Figure 16-1 shows an example of an application that executes a SELECT statement, and SQL Server Profiler shows the following trace events (output, which is shown in this book with a gray background, has been reduced to save space):

   SELECT * FROM Customers WHERE ContactName = 'Ted Bremer'   

Once you find a place in which an attacker can control the data used to construct the SQL statement, such as the value Ted Bremer for the ContactName , the next step is to attempt to break out of the statement.

Using Techniques to Break Out of SQL Queries

In the preceding section, we demonstrated how you can monitor the database to see the SQL queries that are executed to determine whether input could cause an injection. As mentioned earlier, the goal of SQL injection is to change the logic of the SQL statement by using data that can be supplied by the attacker. Most commonly, these bugs are in the following two places:

  • Strings     An attacker can close the string by supplying a single quotation mark (') and can start a new statement.

  • Numbers     An attacker might be able to use a semicolon and start a new SQL statement. The semicolon is used to denote two separate SQL statements; however, it is also optional and is discussed in more detail later in this chapter.

However, other techniques that can be used to attempt a SQL injection are also discussed in this section.

String Fields     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:

  • SELECT * FROM Customers WHERE ContactName = 'aaa'

  • DROP TABLE Customers--'

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:

  • SELECT * FROM Users WHERE Username = 'Bryan' AND Password = 'idontknow' OR 1=1--'

  • SELECT * FROM Users WHERE Username = 'Bryan' AND Password = 'idontknow' OR '1'='1'

  • SELECT * FROM Users WHERE Username = 'Bryan'--' AND Password = 'idontknow'

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.

Number Fields     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:

  • SELECT * FROM Accounts WHERE AccountID = 5 OR 1=1 And Password = 'idontknow'

  • SELECT * FROM Accounts WHERE AccountID = 5-- And Password = 'idontknow'

  • SELECT * FROM Accounts WHERE AccountID = 5 OR AccountID = 1-- AND Password = 'secret'

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.

Using SQL Comments     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.

Table 16-1: Comment Styles in Database Systems

Example syntax

Description

--This is a comment

Two hyphens comment out the rest of the line.

/ This is a comment /

Comments out a section of text that is part of a single line.

/

Comments out multiple lines.

This is a comment

 

/

 

This is a comment

Comments out a single line.

-- This is a comment

Two hyphens followed by a space comment out the rest of the line.

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.

ORDER BY Clause     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.

LIKE Clause     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.

Table 16-2: SQL Server Wildcard Characters for the LIKE Clause

Wildcard character

Description

 

Matches any string of zero or more characters.

( underscore )

Matches any string of zero or more characters.

 

Matches any single character within the specified range (for example, a f ) or set (for example, abcdef ).

 

Matches any single character not within the specified range (for example, a f ) or set (for example, abcdef ).

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.

SQL Functions     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.

Using Square Brackets     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 

Using Double Quotation Marks     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.

Injection in Stored Procedures     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.

Important  

Whenever you see EXEC , EXECUTE , or sp_executesql used in a stored procedure, make sure you check that any input passed in as an argument is properly escaped when used in the SQL statement.

Injection by Data Truncation     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.

Batch Transactions     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 ( \r\n is a carriage return/line feed):

 <batch> \r\n <DELIMITER> \r\n <batch> \r\n <DELIMITER> \r\n ... 

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\r\nGO\r\nDROP TABLE Authors\r\nGO\r\n ? 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 \r\n 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.

Using Code Reviews

The most effective approach to looking for SQL injections bugs is to use white box testing. Using this approach can make it easier to find the bugs, but it requires you to have a good understanding of the application and access to the source code. Throughout this chapter, the discussion provides many examples of clues to look for in code when hunting for SQL injection bugs. When performing a code review, you need to look at the code that constructs and executes the SQL statements as well as the executing SQL code, such as stored procedures. The basic approach when looking for SQL injection bugs by reviewing the code is this:

  1. Search the code for places where SQL statements are constructed and executed.

  2. Determine whether the SQL query is constructed using data supplied from user input.

  3. Analyze the user-supplied data as it reaches the SQL statement to see whether it is sanitized or used as is.

Identifying Places That Construct and Execute SQL Statements

Before you can determine whether a SQL query uses user data, you first need to find where in the application the SQL statements are constructed. To identify places in the source code that make a connection to a database, you can search for the common strings listed in Table 16-3.

Table 16-3: Common Search Terms for Various SQL Technologies

Technology

Common SQL objects

ADO

ADODB.Connection

 

ADODB.Command

 

ADODB.Recordset

C

SqlConnection

 

SqlCommand

 

SqlClient

ColdFusion

cfquery

DBC

java.sql.Connection

 

java.sql.Statement

 

java.sql.ResultSet

OLE DB

ICommand

 

ICommandText

 

IRowSet

Transact-SQL

OPENDATASOURCE

 

OPENQUERY

 

OPENROWSET

Your application might use other methods to query a database, such as a wrapper around existing libraries. So you would need to search for those custom wrapper functions, instead of the ones listed in Table 16-3.

When you are looking through the SQL statements that are getting executed, look for the following commands that can allow for arbitrary statements to be executed or for data to be truncated:

  • EXEC

  • EXECUTE

  • sp_executesql

  • xp_cmdshell

  • QUOTENAME

  • REPLACE

Sanitizing User-Supplied Data

Secure coding practices dictate that the application use parameterized queries, also known as prepared statements. Doing so is one of the best ways to prevent SQL injection bugs because the parameters will properly handle escaping the input used in a SQL statement. SQL parameters also bind the parameters to the correct data type used in the query and handle any necessary escaping of characters. Here is an example written in C# of using a parameterized SQL query in the original Web-based search engine mentioned at the beginning of the chapter:

 // Get the keyword from the user to search for. string strKeyword = "%" + Request.QueryString["keyword"] + "%"; // SQL statement to execute. @keyword is used to indicate a parameter. string sqlQuery = "SELECT * FROM Links WHERE Keyword LIKE @keyword"; // Open connection using connection string. SqlConnection connection = new SqlConnection(...); connection.Open(); // Create a SqlCommand with parameterized query. SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = sqlQuery // Set the SQL parameter for @keyword to the user's input. cmd.Parameters.Add("@keyword", SqlDbType.NVarChar).Value = strKeyword; // Execute the SQL query. SqlDataReader reader = cmd.ExecuteReader(); // Do something with the results of the query DoSomething(reader); // Close the objects when done. read.Close(); connection.Close(); 

From a testing perspective, dynamic building SQL queries using user-supplied data should always use SQL parameters. If they don t, you should have this fixed. Doing so is a great way to help reduce the risk of SQL injection bugs. Otherwise, your application should strongly validate all user input prior to using it.



Hunting Security Bugs
Hunting Security Bugs
ISBN: 073562187X
EAN: 2147483647
Year: 2004
Pages: 156

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net