Recover from a SQL Injection Attack on SQL Server

By: Jeremy Kadlec | Updated: 2008-08-11 | Comments (3) | Related: > 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:

Pro - With the SQL Server Find and Replace Values in All Tables and All Text Columns script for SQL Server 2005, the malicious data can be identified and corrected with confidence.
Con - You need to make sure the SQL Injection is as outlined in this article to be sure you have the correct approach.
Con - It is wise to execute a database backup before making any changes or to maintain the data for forensics purposes.

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
- Prevent traffic from particular IP addresses or domains
  - See if email based alerts can be sent if traffic comes from these sources
- Review the firewall settings to determine if SQL Injection attacks can prevented
  - If you have a maintenance agreement with your firewall vendor see if you can update your product to prevent or alert on SQL Injection
- Research products or services to scan your code and web site on a regular basis to prevent the issue

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

If you are looking for more information about SQL Injection check out these URLs:
If you do experience a SQL Injection attack be sure to remember it will probably take the entire technology team/department to resolve the overall issue. Be sure to communicate to the entire team and ask everyone to take on their responsibilities so the problem can be recovered from quickly and resolved permanently.
The reality is that SQL Injection has been a problem for a significant amount of time, so be sure to keep it in mind as you code so future projects will not have to be modified in an emergency manner if a problem occurs. To take this a step further, by following coding best practices you have a good chance of preventing the next big issue as well. So a little extra time on the front end of the project can go along way over the life of the project.
Have you recently experienced a SQL Injection attack? We would like to hear if your experiences are similar to the ones outlined in this tip or if they were very different and required a different solution. Please share your thoughts by clicking on the forum link below.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2008-08-11