Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Recover from a SQL Injection Attack on SQL Server


By:   |   Last Updated: 2008-08-11   |   Comments (3)   |   Related Tips: More > SQL Injection

Problem

Lately it seems like SQL Injection attacks have been increasing.  Recently our team has worked through resolving a few different SQL Injection attacks across a variety of web sites.  Each of these attacks had a number of similarities which proved to point back to the same source.  With this information in hand, the resolution should be much quicker.  As such, if your web site is attacked with SQL Injection, how should you address it?  How can the identification, analysis, recovery and resolution be streamlined?  What are some lessons learned?

Solution

As a point of reference, SQL Injection in a nutshell is an exploit where unhandled\unexpected SQL commands are passed to SQL Server in a malicious manner.  In the situations we recently experienced the attacks had many similarities and were approached in 4 major components.  First was identifying the issue, next was analyzing the situation to determine the root cause as well as outline options for recovery and resolution, this is followed by recovering the data and preventing a future problem.  The other two items that were important in some of the situations was the overall communication plan and how to resolve the problem if it occurs again.  Let's jump into each one of those items.

Identification

In the situations we experienced, the users or customers actually reported the issue to the IT team.  Once the issue is determined then analysis needs to be conducted in the following places:

  • Firewall logs and configurations
  • IIS logs
  • Web pages
  • SQL Server tables

In terms of sample code, hex strings were casted and passed to SQL Server.  After reviewing the code, it was a simple cursor to update varchar, nvarchar, text or ntext columns with URL's to other web sites.  Her are some references with updated listings of malicious URLs:

Analysis

After reviewing the IIS logs it was possible to translate the code, but if you have a performance monitoring tool that capture long running SQL statements they may have proven the most beneficial to help pinpoint the exact code issued.  If not you will need to spend some time searching for strings across your database or use the strings from this article as a point of reference.

Once the string is determined then these two scripts can offer a great deal of value to identify the infected columns and/or recover from the issue:

On a related note, one quick way to determine if your web site has been infected is with the following Google query from a SANs article:

site:yoursite "script src=http://*/""ngg.js"|"js.js"|"b.js"

One note from our experiences is the file names (i.e. *.js) have changed, so searching for additional files may be necessary.  Based on your analysis be sure to include the appropriate file names.

Recovery

In the situations we were faced with two schools of thought were brought to the table as options to recover from the data issue.  First, was recovering from either the disaster recovery solution (product, server, instance, database, etc) or from log shipped databases.  The second school of thought was to identify and correct the data.  Let's examine each option for pros and cons.

Below is the backup\restore or high availability option analysis:

  • Pro - If you have a product or technology that can get you back online quickly and if you know exactly when the data was infected this could be a quick and simple way to recover the data in a short amount of time.
  • Con - If you do not know when you were infected a quick recovery and data loss could be difficult.
  • Con - Recovering from a backup may be necessary based on the issue, but in our experiences data was appended not rearranged, deleted or inserted.  So removing the malicious string was all that was needed

Below is the data correction analysis:

Resolution

Below outlines possible steps to help with the overall resolution:

  • Development\DBA
    • Validate the SQL commands that are being passed by the front end
    • Validate the length and data type per parameter
    • Convert dynamic SQL to stored procedures with parameters
    • Remove old web pages and directories that are no longer in use because these can be crawled and exploited
    • Prevent any commands from executing with the combination of or all of the following commands: semi-colon, EXEC, CAST, SET, two dashes, apostrophe, etc.
    • Based on your front end programming language determine what special characters should be removed before any commands are passed to SQL Server
      • Depending on the language this could be semi-colon, dashes, apostrophes, etc.
      • Consider building a function to perform this action for both character and numeric data
  • Network Administration

Communication Plan

With some of the larger organizations the communication plan was actually as important as the resolution to some people in the organization.  So keep that in mind.  For some people not knowing what was happening caused a great deal of anxiety and derailed the IT team from doing their job.  So be proactive in your communications and set a schedule so one is not set for you.  Here were some of the communications that were important in our situations:

  • Communicate an issue occurred, the team is working to determine the issue and an update should be available in 2 hours
  • Communicate what happened, the steps the team is taking to resolve the problem and solution should be in place in 30 minutes
  • Communicate when the problem was resolved, the steps taken to resolve the problem and future steps to prevent a future problem

Fast Recovery

If for some reason the resolution implemented does not resolve the problem and the SQL Injection attack occurs again, the quickest path may be to do the following:

  • Shut down the web sites
  • Review the IIS logs to determine the commands issued and which web page\command has the vulnerability
  • Convert the code to determine which tables were affected and the command issued
  • Find and replace the string in your tables
  • Correct the web page\command that has the vulnerability
  • Test to validate the issue no longer occurs
  • Deploy the web page\command
  • Re-enable the web sites
Next Steps


Last Updated: 2008-08-11


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, August 22, 2008 - 12:13:54 PM - admin Back To Top

saney,

Thank you for your feedback and sharing your experiences.

Thank you,
The MSSQLTips.com Team


Friday, August 22, 2008 - 7:59:34 AM - saney Back To Top

We've been seeing an average of 15 attempts a day for the last four weeks using this exploit. We implemented logging of all unhandled errors on our websites, which alerted us to the attacks.

This is the best example of why dynamic SQL is so risky. After we backtracked the attack to servers in Beiruit and New Zealand, I sat down with my team and walked through the attack, how it would have affected us had it succeeded and reiterated why I was so stringent on using stored procs as we were developing the sites.

The best way to recover from a SQL Injection attack is to prevent it in the first place.

 

 


Tuesday, August 19, 2008 - 1:53:43 PM - Jonathon Back To Top

Excellent tips. If I may plug a particular web application security solution, Devfense from Boonbox checks for SQL injection, cross site scripting and other attacks, and helps ensure security compliance.

It combines award-winning enterprise class technology with expert IT security consulting to find the vulnerabilities in the code, locate the problems and help fix them. For organizations that have had their websites hacked (or hopefully for organizations that are trying to be a little more pro-active in web security), Devfense could be a very good option.


Learn more about SQL Server tools