Recover from a SQL Injection Attack on SQL Server
August 12th 2008 05:19
Recover from a SQL Injection Attack on SQL Server
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:
Really Long Link
Really Long Link
Really Long Link
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:
Searching and finding a string value in all columns in a SQL Server table
SQL Server Find and Replace Values in All Tables and All Text Columns
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 Really Long Link
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
Really Long Link
Really Long Link
Really Long Link
Really Long Link
Really Long Link
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
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:
Really Long Link
Really Long Link
Really Long Link
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:
Searching and finding a string value in all columns in a SQL Server table
SQL Server Find and Replace Values in All Tables and All Text Columns
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 Really Long Link
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
Really Long Link
Really Long Link
Really Long Link
Really Long Link
Really Long Link
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
| 54 |
| Vote |
Shared on
Subscribe to this blog
Comment by TimmyH
Tech News
Can you HACK it?
Genyration