Identifying SQL Injection

This article covers the most recent wave of 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 <script src="URL HERE"></script> 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.