Blocking automated SQL injection attacks
Published Mar 23 2019 11:29 AM 1,771 Views
First published on MSDN on Apr 27, 2010
SQL injection attacks have been on the rise in the last two years, mainly because of automated tools. We first witnessed these automated attacks in December 2007, and since then very little has changed in the way that these attacks work. Attackers use these automated tools to query search engines for interesting URLs and blast each one with various SQL injection payloads, with the end goal of injecting malicious JavaScript into all string columns in all tables. Microsoft has provided guidance ( ) 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:


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


select, from sysobjects a,syscolumns b

where and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)

OPEN Table_Cursor




exec('update ['+@T+'] set ['+@C+']=rtrim(convert(varchar,['+@C+']))+''<script src=***jp.js></script>''')



CLOSE Table_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:


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

-Bala Neerumalla

Version history
Last update:
‎Mar 23 2019 11:29 AM
Updated by: