First published on MSDN on Apr 27, 2010
) and some tools (
) to combat these attacks.
Today I would like discuss another technique that one can use to block these automated SQL injection attacks against web applications using Microsoft SQL Server as the backend. Before I go into 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 document on SQL injections
that details various classes of SQL injection vulnerabilities and how to address them in design, development and testing phases.
Any generic SQL injection attack that has to work on multiple web sites 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:
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));
This statement declares a string variable (@S) containing 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,
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)
DECLARE Table_Cursor CURSOR FOR
select a.name,b.name from sysobjects a,syscolumns b
a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)
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 of the generic automated SQL injection attacks. But it won’t catch targeted attacks that inject the UNION operator or inference payloads to read the backend 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,
, 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 check it out at
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.