Identifying SQL Injection

This article covers the most common SQL Injection attacks. These attacks, mostly targeted towards sites scripted with Classic ASP, utilize a Hex encoded SQL query which injects javascript tags into the database's fields that use the text, ntext, varchar, or nvarchar data types.

How to Identify SQL Injection

Identifying SQL Injection is fairly easy. Commonly you may have received complaints of the site attempting to load malware on visitors' computers. This is because the injection attack has inserted tags into the database fields that load externally linked javascript. Typically the site will not have any obvious visual clues of attack, however the performance of the site may be degraded. If you view the source of the site and search for ".js" you will quickly find the injected scripts.

You have now identified that the site has indeed been SQL injected. The database may need to be restored. You will need to investigate further to find out when the attack started to you can restore the appropriate backup (a clean one).

Faster SQL Injection identification

  1. Open the site up in Firefox
  2. Open up FireBug
  3. First screen in firebug shows the load times of all files and requests made due to the current page load. Look in the second column for a domain name that is suspicious.
  4. View the source of the page and find that domain name. If it is in a format, then it is likely SQL injection.
  5. Open up the database and run the stored procedure below. If it returns the number of corrections, you have just cleaned there database of one string. Be sure to refresh the page in Firefox and double check that there aren't other strings that have been injected.

    CREATE PROC SearchAndReplace

    (

        @SearchStr nvarchar(1000),

        @ReplaceStr nvarchar(1000)

    )

    AS

    BEGIN

        SET NOCOUNT ON

        DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @tColumnName 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 @tColumnName = '

            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    

    WHILE (@TableName IS NOT NULL) AND (@tColumnName IS NOT NULL)

            BEGIN

                SET @tColumnName =

                (

                    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 ('ntext', 'text')

                        AND    QUOTENAME(COLUMN_NAME) > @tColumnName

                )

                IF @tColumnName IS NOT NULL

                BEGIN

                    SET @SQL=    'UPDATE ' + @TableName +

                            ' SET ' + @tColumnName

                            + ' = REPLACE(cast(' + @tColumnName + ' AS NVARCHAR(Max)), '

                            + QUOTENAME(@SearchStr, ') + ', ' + QUOTENAME(@ReplaceStr, ') +

                            ') WHERE ' + @tColumnName + ' LIKE ' + @SearchStr2

                    EXEC (@SQL)

                    SET @RCTR = @RCTR + @@ROWCOUNT

                END

            END    

        END

        SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurrence(s)' AS 'Outcome'

    END

What do I do next?

Once you have confirmed that the site has been attacked, it is important to take ALL of the following steps to ensure that the issue is properly handled.

  1. Find out when the attack started. It is common for an attack to go unnoticed for several days. This is because, as stated earlier, the site may not have any obvious visual indications of attack.
  2. Secure a clean backup of the database.

Although the above may seem like a lot of work it can be accomplish in very little time with the right tools. Once you are familiar with the process, it may only take you 15-20 minutes to complete the entire process.