How to Test for SQL Injection Bugs - Step 1

Posted by Serge Truth on January 11, 2011 at 9:47 AM

Test for SQL InjectionSQL Injection is a common type of web database related vulnerability.   SQL injection is common to web site applications that interact with a database backend.  These sites construct SQL commands or queries based on user input.  For example they might search a product database based on a description a user typed in a web form.  An application is vulnerable to SQL injection when it builds SQL queries without filtering the user's input, without using store procedure, or without binding parameters variables.  This can lead to an attacker adding additional clauses, conditions, or even commands that execute along the original query.    Attackers can use SQL Injection bugs to execute different types of attacks such as data theft and tampering, authentication bypass, information disclosure, and elevation of privileges.

Below is a summary of steps needed for testing for SQL injection bugs

  • Step 1:  Understand SQL injection attack scenarios
  • Step 2:  List high risk components and entry points
  • Step 3:  Start testing and exploring
  • Step 4:  Tune of test data

Step 1:  Understand Attack Scenarios

It is important to understand attack scenarios so that you know what to look for when testing for SQL injection.  Here are two common attack scenarios:

  • Search Engine scenario
  • Form Authentication scenario

Search engine scenario

Most web sites have search pages that take a search query from the user, create a SQL query with it, execute the query at a SQL backend database, and return the results to the user.  These components will end up creating a SQL similar to this:

SELECT * item, description FROM products WHERE description LIKE usersearch (from input)

For instance, a web server takes search data from the user either from a form or from a URL that looks like this:

http://www.teammentorexample.com/products/search.php?prod=bike (PHP)
http://www.teammentorexample.com/products/search.asp?prod=bike (ASP)

The server might use code like this in the search component:

$query = "select item, description from productTable where description LIKE '%" . $_GET["prod"] . "%'"   (PHP)
var query = "select item, description from productTable where description LIKE '%" + Request.QueryString("prod") + "%'"  (ASP)

In both of the cases above the attacker can modify the URL and inject SQL statements that will be concatenated to the query and executed as part of the SQL query.  If the attacker changes the value passed in prod by:

any OR 1=1

Then the query will return all of the rows in the table since the second condition 1=1 will be always true: 

SELECT * item, description FROM products WHERE description LIKE 'any' OR 1=1

Forms authentication scenario

Another example of a high risk component is the code used in forms authentication. For example, the code below, accepts credentials to build a SELECT query to validate the user.  The data is passed to the login.php page through a web form's POST using username and password variables. 

A SQL injection bug is present in this code:

$query = "SELECT * FROM Accounts WHERE UserName = '" . $_POST['name'] . "'" . " AND password = '" . $_POST['pass'] . "'"; 
$result = odbc_exec($connection, $query);
if ($result_count == 0) {
      print('unable to login');
}
else {
      print('user logged in');
}

An attacker can submit the same string as in the first scenario:

any OR 1=1

This will result in the following statement constructed:

SELECT * FROM Accounts WHERE UserName = any or 1=1

The statement returns all rows from the Accounts table.  The if condition in the code will be false and the attacker will be able to log in without a valid password.

Stored procedure scenario

SQL injection bugs aren';t necessarily restricted to the application layer.  For instance, a stored procedure used at the database layer may also suffer from SQL injection.  Consider the following procedure that takes a parameter for a product name and searches a database by building a dynamic SELECT statement inside:

CREATE PROCEDURE sp_SearchProducts @prodname varchar(400) = NULL AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT ProductID, ProductName, Category, Price ' +
              ' FROM Product Where ProductName LIKE ''' + @prodname + ''''
EXEC (@sql)

The can invoke the procedure at the application layer like this:

exec sp_SearchProducts('any'' OR 1=1--')

The final statement will look like this:

SELECT ProductID, ProductName, Category, Price FROM Product Where ProductName LIKE 'any' OR 1=1—'

This will return all rows of the product table. 

Be careful with this type of scenario since it is a common misbelieve that using stored procedures and bound parameters solve SQL injection automatically.

Topics: developer guidance, application security

Serge Truth

Written by Serge Truth

Serge is a Content Lead here at Security Innovation. He is an IT and Information Security professional, certified by the Committee on National Security Systems Instruction.