Recover from a SQL Injection Attack on SQL Server
Written By: Jeremy Kadlec -- 8/11/2008
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
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
- 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.
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
