Using SQL Injection attack code to repair database

Thursday, September 25th, 2008

Now that Google has started flagging sites that are linking to badware in their index I’ve been getting quite a few calls from people who have been flagged and need to get back on track.

These are often sites that were written a while back (not by myself) when developers didn’t think as much about SQL injection as they do now. Sometimes the code was just poorly written by someone who didn’t know better. However it happened, each site has its own challenge.

Fixing the security hole is generally straight forward — I usually just have to identify where the SQL isn’t properly escaped and fix that code. The hard part I have had fixing these sites is fixing the database itself. Some clients have backups, and some I can fix with SQL Log Rescue, but generally a lot of small clients simply don’t have great control over their server and often don’t have any backups.

I had one such of these clients this week where their database had hundreds and hundreds of tables, all with malware code injected into the data. I was initially going to write a script to clean all the data, but after looking at the malware attack, I was able to use their own code to fix the database.

In the server log files. I noticed this request was coming in for every script several times a day. Looks like it just wanders the internet hoping that that id=2 in the query string won’t be escaped in the code.



If the id isn’t escaped, as it wasn’t in this situation, a query like this will hit your SQL server:



hmm. okay. so what is it executing as the value of @S?
If you change the statement from EXEC(@S) to PRINT(@S) you get this:
Warning: Do not run this on your server, it will mess up ALL of your data

span style="color: #FF0000;">'u''update ['+@T+'] set ['+@C+']=''"></title><script src=""></script><!--''+['+@C+'] where '+@C+' not like ''%"></title><script src=""></script><!--'''


I thought this code was pretty clever. It selects a cursor that contains every varchar and text field in every table on the database, then loops over the cursor and issues an update command to append their bad script to the end of the data in each field.

Luckily, it is in their best interest to leave your data in place and just place their code at the end – it increases the chance you won’t know your site is infected.

Since all of the original data is still in the database, I was able to tweak their code a little bit to write a script to fix the data:

span style="color: #FF0000;">'u''update ['+@T+'] set ['+@C+']=Replace(['+@C+'], ''<script src=""></script>'', '''') where '+@C+' like ''%<script src=""></script>%'''-- exec(@SQL)

Human readable DATEDIFF function for SQL Server

Wednesday, September 24th, 2008

All the time, when I’m writing reports I need to display a ‘time elapsed’ metric in a short easy to read manor. Usually I do this with a simple convenience function in whatever language I am writing the GUI in. For reports that are purely in SQL, I have created just such a convenience function for SQL server: dateDiffHumanReadable( startDate, endDate, precision ).

This makes it easy to display date spans.

To see how old I currently am, I would call:

span style="color: #FF0000;">'6/2/1978 16:00:00'

This will return my current age:
30y 114d 38m 11s 620ms

This comes in handy when I have a log table with columns like this:

  • process
  • timeStart
  • timeEnd

Just using DATEDIFF I would have to do this:

span style="color: #FF0000;">''

With this function, I can get more informational results just doing this:


The third parameter is a precision value for when you don’t need right down to the millisecond returned. Calling this:

span style="color: #FF0000;">'6/2/1978 16:00:00''y')

This will return just the year of my current age:

Full code

span style="color: #008080;">-- =============================================
-- Author:		T. Curran
-- Create date: 3/20/2008
-- Date modified: 3/12/2009
-- Description:	Generates a human readable difference between two dates, in the form '1y 5d 3h 2m 6s 10ms'
-- =============================================
'ms'''-- @dateScratch is used as a holding place for us to increment the date so we don't alter @dateStart
-- years
-- days
-- milliseconds
-- seconds  
-- minutes  
-- Build the output string based on the precision
	-- years
'y''d''h''m''s''ms''y '-- days
'd''h''m''s''ms''d '-- hours
'h''m''s''ms''h '-- minutes
'm''s''ms''m '-- seconds
's''ms''s '-- milliseconds
'ms''ms '-- the above string concat always ends with a space, if the space is there at the end remove it

Adam correctly pointed out that my function didn’t work when the time span was less than 1 day because:
SELECT DATEDIFF(dd, ’11:50:48.000′, ’12:12:45.000′)
Will return 1 rather than 0.

I have updated the function to use DATEDIFF to calculate the year, then use a ‘ms’ calculation to count up for the other values.