Thursday 8 May 2008

The tao of SQL Injection exploits

Gentle reader I have often succumbed to large doses of smugness from time to time when it comes to Microsquishy products and their propensity to attract script kiddies and their evil ware. However over the past to days (oh whisper it please) I have found myself somewhat sorry for MS and all its doings.

Were you to cut me in half you would see Yellow thru and thru however there are bits of me that have dabbled in the world of PHP, RPG/CL, PERLE and SQL. So it was because of this experience I was called in on the periphery of an investigation as to why a web site was suffering from near continuous SQL-injection attacks.

Investigation on the web showed that there has been in the last few weeks a dramatic rise in web sites affected by a particular type of SQL-injections attack.. as reported here and here.

The immediate reaction was go blame IIS and ASP (which lets face it is only natural). However it does not now seem to be a vulnerability at all but really sloppy code on ASP pages.Now my developer half cringes when I see this. It is all to easy to take the easy way out and leave a steaming great hole in your code that lets script kiddies in. So here are some of the things I have found out about this particular attach which could.. from a notes point of view be applicable if you are using some form of SQL that is passed to say a DB2 database or ODBC connection.

Lets look at a simple bit of SQL

SELECT * from Menu Where Language = 'French'

Basically this Select all fields from the Menu table where field Language is equal to French
now you have an ASP page that uses an SQL statement like this to return data from the back end DB to the ASP page. The Language is passed into the ASP page by the URL something like this
the key pair is then used by the ASP code to build the Select statement and retrieve the data.

var LangId;
LangId = Request.form ("lang");
var sql = "select * from Menu where Language = '" + LangId + "'";
Which using the URL above as an example would result in the Variable sql being SELECT * from Menu Where Language = 'French'

This is fine and works wonderfully well...HOWEVER you created the tables and you forgot to set them to be read only for public facing operations.. so any SQL statments your ASP code passes back will get processed with your authority..
This is your first and probably worst mistake you were more interested in the wonderful look and feel (Sorry Chris) you where creating to be bothered creating SAFE connections.

OK the Script Kiddies to a search on Google for anything that say contains ".asp?id=" this will return 300 million results now most of these pages will be fine, well written examples of the ASP page wrights craft. However it seems given the figures that are there are hundreds of thousands of embarrassingly badly written ASP pages out there.

The fact that Google has an entry for a page that has in its url .asp? indicates that there is a public facing ASP access point that accepts variables... "lovely", thinks the script kiddie, "I am having some of that!"

The copy the url into notepad and ammend it thus';Drop Table Menu--

Now on the ASP page when
var sql = "select * from Menu where Language = '" + LangId + "'";
Is processed is results in variable SQL containing
"Select * from menu where language ='French';Drop Table Menu--"
Note the clever use of the orphan ' after the French in the url. In SQL as in JS the semicolon indicated the end of one statement and the start of another, the double -- indicates the end of the command string (ie STOP) . So when processed this example will select all fields where field Language =' French' and then it will delete the table called MENU from the database.

That was a simple example to show what SQL injection is and how easy it is to do. The example assumes that the script kiddie knows you have a table called MENU but this is very very unlikely. Unfortunately Script kiddies are the spawn of Satan and have acquired SQL skills .. the sql that is being used to infect sites is like this...and is much much more devious than the example above
2C004000 43002000760061007200630068006100720028003200350035002900200044004500430
40004300200045004E0044002000 43004C004F005300450020005400610062006C0065005F00430
6C0065005F00430075 00720073006F007200%20AS%20NVARCHAR(4000));EXEC(@S);--

The hex string when @URLdecoded yields something like:

DECLARE @T varchar(255)'@C varchar(255) DECLARE Table_Cursor CURSOR FOR
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)
BEGIN exec('update ['+@T+'] set ['+@C+']=rtrim(convert(varchar'['+@C+']))+'
'[script][/script]''')FETCH NEXT FROM
Table_Cursor INTO @T'@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor
This is the yucky evil nasty horribly simple bit.. the SQL does the following
1. Gets all the tables in the database
2. Gets all the fields in each of the tables it finds
3. For every text field that it finds it updates the field appending the nasty [script] tags to the field

In this instance the nasty is the [script][/script] ... this will now appear where ever ASP implants the field's contents on the form thus infecting the page. Taking my menu example lets say there is one text field in that table MENUTEXT that has been infected and will be loaded when the ASP page is loaded.. ever iteration on MENUTEXT on the page will have the nasty code in it.. So if the menu has 50 items there are 50 [script] tags that will all run when this page is next loaded in a browser.

In the instance I was looking at there were 47 [script] loaders in one page. The script that was loaded basically added 22 hidden [iframes] into the HTML page with document.write() statements and these pointed at some really YUCKY malware.

Like all really nasty things, this code is simple and very effective. The user goes to a trusted site .. like these and you start to get
"do you trust this activex..." etc, you think to yourself .. yes I trust the International Monetary Fund Web site.. so you click yes and wallop your PC could have a dose of nasties.

As a user you have done everything you should have to practice safe surfing and you still have
1001 popups for "Deviant Hamster and Gaffer Tape Insertion pictures".

The natural reaction was to blame MS when this flare up started and people did and yes I was amongst them. Loads of IIS admins waited for the inevitable patch, which never came. Then MS and lots of other commentators started to point out that it was caused by sloppy code
and bad DB security. Now this would be embarrassing if it was just one or two sites but since Mid April somewhere between 500k and 1000k websites have been affected!

That is one hell of a shitload of bad programming, bad security, bad testing and bad auditing!

So developers everywhere (including we Lotus geeks) I think we need to feel sorry for our ASP colleagues and take this as a salutatory lesson that the way to hell is paved with sloppy coding!

Disqus for Domi-No-Yes-Maybe