Cleaning Up After a SQL Injection Attack, Part 2

Got a call today off our previous article in this series from Branden of Hot Media Group, Inc., aChicago-based web application development, networking, and graphic design firm who found himself with a database full of malware infections, but the characteristics of his attack didn’t match what we had written about, so he called us up. We reviewed his symptoms and were able to tweak the code we provided previously to work with this new set of issues.

We weren’t able to see how the site was attacked, nor did we worry about how the site would be steeled against future occurrence (always use stored procedures and/or parametrized queries, kids!) — this was purely a cleanup job.

This is the code we had:

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=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
PRINT ('UPDATE ['+@T+'] SET ['+@C+']=REPLACE(['+@C+'],'''', '''')') FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor 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 OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN PRINT ('UPDATE ['+@T+'] SET ['+@C+']=cast(replace(cast(['+@C+'] as nvarchar(4000)),'''','''') as ntext)')
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

And that worked fine, but it had some shortcomings — mostly it only stripped out a single bit of invasive code, and our new friend had quite a bit of code to deal with, so instead of the almost quaint looking malware code:

<script src="hxxp://evilsite.evl/b.js"></script>

We had this jumble of code in every ntext field in his database:

<script type='text/javascript' src='https://google-anallytics.bad/urchin.js'></script>
<div style='display:none;'><a href='http://tests4all.bad/1/'>journals on losing post-pregnancy weight</a>
<a href='http://tests4all.bad/2/'>personal trainer certification atlanta</a>
<a href='http://tests4all.bad/3/'>quit smoking water vapor rings</a>
<a href='http://tests4all.bad/4/'>eyes in the darkness</a>
<a href='http://tests4all.bad/5/'>cheated map on dota 6.54b</a>
<a href='http://tests4all.bad/6/'>occupations for bored teen boys</a>
<a href='http://tests4all.bad/7/'>cgw southeast partners ilp</a>
<a href='http://tests4all.bad/8/'>does iq tests accurately measure intelligence</a>
<a href='http://tests4all.bad/9/'>free total psychic reading</a>
<a href='http://tests4all.bad/10/'>minnesota past life regression</a>
<a href='http://tests4all.bad/11/'>date of abraham lincolns death</a>

After trying to figure out the best way to escape all the single quotes, Branden — an accomplished ColdFusion developer — suggests “why don’t we just drop everything to the right of the <script> tag?”

Sounded like a great idea and worked very well. Since his infection had only affected NTEXT fields, we focused on cleaning them up, as well as making the script as easy to manage as possible. So I rewrote it to make it more friendly to the end-user,

DECLARE @T VARCHAR(255),@C VARCHAR(255), @sql varchar(2000)
DECLARE @ObjectionableText varchar(1000)
Set @ObjectionableText = '<script type=''''text/javascript'''' src=''''http://google-anally' -- make sure your single quotes are escaped with another single quote
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
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @T,@C
WHILE(@@FETCH_STATUS=0)
BEGIN
set @sql = ('UPDATE ['+@T+'] SET ['+@C+']= left(cast(' +@C+ ' as varchar(8000)), charindex('''+@ObjectionableText+''', cast(' +@C+ ' as varchar(8000)))-1) where '+@C+ ' like ''%'+@ObjectionableText+'%''')
print @sql
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

So, let’s take this apart real quick…

We declare some variables:

DECLARE @T VARCHAR(255),@C VARCHAR(255), @sql varchar(2000)
DECLARE @ObjectionableText varchar(1000)

Now, this next line is the important one — this is where we tell the script where we want to kill from. In our example above, we could have used <script as a starting tag, but the client was afraid some of the data might have legitimate <script> tags in the data, so we needed to get a little more specific; this string appeared in the data: “<script type=’text/javascript’ src=’http://google-anally…” so we decided to use that. However, you might notice that there were SINGLE QUOTES in the string. Since SQL Server uses a single quote as a string delimiter, we need to make sure we use FOUR single quotes in the next line everytime there’s a single quote:

Set @ObjectionableText = '<script type=''''text/javascript'''' src=''''http://google-anally' -- make sure your single quotes are escaped with another single quote

We use FOUR single quotes because this script will generate a binch of UPDATE statements for you, and the UPDATE statements need to have THEIR single-quotes escaped, so we need to tell our variable to output TWO single quotes, which means using FOUR single quotes in the variable. (Our escape uses 2 quotes and the escape later uses 2 quotes, so that equals 4.)

(Don’t follow? Doesn’t matter. Trust me. In your ObjectionableText, use FOUR single quotes where you see ONE.)

Now, like the old code, we set the cursor up as before; and since we only need NTEXT fields, we’re only looking for columns where xtype = 99:

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
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @T,@C
WHILE(@@FETCH_STATUS=0)
BEGIN

But now, we have to change the SQL statement we want to use to (a) keep 8k worth of ntext — if you think you have more than 8K, change the number accordingly in SQL2005+, SQL2000 has an varchar limit of 8K for a varchar field… so we UPDATE the field to a new value, computed by doing a simple LEFT and using the CHARINDEX of the text we shoved in the @ObjectionableText variable (minus 1) to come up with it. To make sure we don’t pass an invalid value to CHARINDEX we need to make sure the rows we’re working on actually have the polluted text — and that’s where the LIKE at the end comes in.

set @sql = ('UPDATE ['+@T+'] SET ['+@C+']= left(cast(' +@C+ ' as varchar(8000)), charindex('''+@ObjectionableText+''', cast(' +@C+ ' as varchar(8000)))-1) where '+@C+ ' like''%'+@ObjectionableText+'%''')

NOTE: Bear in mind we’re doing a TABLE SCAN on this table since we’re doing a mid-string lookup, so performance may be bad. It beats going thru everything by hand, but if you have a large table (10,000+ rows) it might take some time.

Now, I print the SQL statement. I could execute the statement (EXEC @sql) instead, but since I don’t want you cutting-and-pasting this code without knowing what it has the potential to do, I will go for the more benign PRINT and let you either change it to EXEC or cut and paste the resulting SQL statements into a new Query Analyzer/Management Studio window..

print @sql

And then we loop thru the rest of the cursor and cleanup after ourselves:

FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

That’s it. Copy and paste the above code into Query Analyzer or SQL Server Management Studio and run it; you’ll get a list of SQL statements back which look like this:

UPDATE [Banners] SET [AdCode]= left(cast(AdCode as varchar(8000)), charindex('<script type=''text/javascript'' src=''http://google-anally', cast(AdCode as varchar(8000)))-1) where AdCode like '%<script type=''text/javascript'' src=''http://google-anally%'
UPDATE [Banners] SET [AdCodeNetscape]= left(cast(AdCodeNetscape as varchar(8000)), charindex('<script type=''text/javascript'' src=''http://google-anally', cast(AdCodeNetscape as varchar(8000)))-1) where AdCodeNetscape like '%<script type=''text/javascript'' src=''http://google-anally%'

Paste them into a new QA/SSMS window and run them, and your data should then be clean.

REMINDER! In this case, we assume the malicious code was merely appended to the end of the NTEXT fields, not that fields were truncated and appended to like in the last article. If that’s the case, data loss may still be possible in that the injection attack might have caused data fields to be truncated.

Thanks to Branden for trusting us with his data, and if you’re in the market for