SQL Denial of Service Attacks

SQL Denial of Service (DOS) attacks - Using SQL for DOS Attacks

By Strictly-Software

Updated Article - First written in 2008

You should have heard about SQL injection attacks and most certainly DOS (Denial of Service) or DDOS (Distributed Denial of Service) attacks but you may not have heard about SDOS (SQL Denial of Service) attacks as something to be on the look out for when considering your websites OR database security.

The idea behind the SQL Denial of Service attack is pretty simple and that is to use any search forms or remote requests, maybe a JSON or SOAP request in a web service, that accesses your SQL database to execute a number of long running and CPU intensive queries so that the site becomes unusable to other users e.g a SQL Denial of Service attack as your request blocks other users from using the servive.

Running multiple attacks from proxies or BOTNETS or even firing multiple requests really quickly in parallel from one computers could be considered an SQL Denial of Service attack.

The reasons these queries cause such an effect is due to any one of these reasons:

• The CPU on the database server becoming maxed out running these intensive queries.
• All connections to the database become in use by the attacker and therefore no other connections can be spawned, therefore locking up the site to other users.
• Bad database design which is causing locking on the tables being scanned. Data is waiting to be updated/deleted/inserted into the table but your query/queries are causing the table to lock up as every row in it is scanned looking for results.

Who is vulnerable to an SQL Denial of Service attack?

Most websites have an SQL backend (MS SQL, MySQL, Oracle etc) and the majority of sites have some user interface that allow visitors to enter textual keywords that are then used as criteria to filter an SQL SELECT statement to return matching records. 7

A few examples of search forms are:

• Searching for content in a CMS system.
• Searching for jobs, members of a site or homes and properties to rent.
• Searching for news articles or comments posted on a message board.

If you are using a database rather than a textual index to return these results you may be at risk if you are using the LIKE keyword or in SQL 2005 a CLR regular expression function to pattern match.

As an example I will use one of my own jobboard sites and imagine that we used the database to handle keyword searches instead of a NoSQL index.

We have a search criteria form that allows the user to enter search terms which we will try and match within the job description column of our JOB table using the LIKE keyword.

( N.B. The database is SQL 2005 running on Windows 2003 and the table in question has 646,681 rows. )

If like a lot of sites we treat gaps in the search term to indicate multiple terms that then get treated as an OR search then if the user had entered "ASP SQL" in the keywords box we would run a search using the following SQL:

SELECT   TOP 10 JobPK,JobDescription
FROM     JOBS as j
WHERE    JobDescription LIKE '%ASP%'
        OR JobDescription LIKE '%SQL%'
ORDER BY JobPK

This particular search returned me 10 rows worth of data in under 1 second.

Now if the user had entered something like this for their search keywords

_[^|?$%"*[(Z*m1_=]-%RT$)|[{34}\?_]||%TY-3(*.>?_!]_

Then the SQL will be

SELECT   TOP 10 JobPK,JobDescription
FROM     JOBS as j
WHERE    JobDescription LIKE '%_[^|?$%"*[(Z*m1_=]-%RT$)|[{34}\?_]||%TY-3(*.>?_!]_%'    
ORDER BY JobPK

This SQL when run returned 0 rows and took 2 minutes 12 seconds to return!

This means that every single row in the database has been searched which is exactly the aim of the
SQL DOS Attack.

Also during the execution of this one query the CPU on the SQL server was maxed out at 100% ! 

You can test this yourself  on your own databases by using the task manager or if you have a decent understanding of the SQL Activity Monitor use that,

Whilst this one query was running other users of the system had problems connecting to the database server and I logged the following errors:

[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).

These errors are down to the SQL Server being so busy that it cannot open new TCP/IP ports.

This mayhem was due to 1 query causing the SQL server to max out. 

Now if you can imagine that the attacker could spawn multiple queries that consumed all available pooled connections each running a query taking 2+ minutes then you can see the scale of the problem and how a Distributed SQL Denial of Service attack could be a disaster for your site.

Any part of your website or web service that used a database connection would be out of action for some time.


Changing the effectiveness of an attack

The SQL LIKE DOS attack can be modified in a number of ways:

1. The longer the string being matched the longer the query execution time.
2. Query plans get cached and re-used to increase performance so changing the pattern by a character or two each time will cause a recompile and prevent a cached plan being used.
3. Combining patterns in an OR statement will increase the execution time. Make sure each pattern is different.
4. Adding % around the search string will increase the effectiveness especially at the beginning of the string as it will prevent any available index from being used.

Solutions to the SQL DOS attack

In general you should always set a timeout limit for

1. Connections (the length of time allowed for connecting to a database)
2. Commands (the length of time allowed for a query/procedure to run)

The defaults for ADO are 30 seconds each.

If you must use an SQL database LIKE search for your search forms then validate the user input in the following ways:

• Strip all non alpha numerics from the input. Or if you know what types of input you should be searching for you could test the search term against a regular expression first to make sure it matched a legitimate term.
• Set a maximum length for search terms that can be entered. If you are allowing long strings make sure there are an appropriate amount of spaces for the length e.g one space every 15 characters. If you have a string of 100 characters and only one space then something is wrong!!
• If allowing non alpha characters then make sure characters such as % _ [ ] that are also used as wildcards in LIKE statements are either escaped or stripped out.
• Also strip out concurrent non alphanumeric characters.
• Make sure you have words that consist of 3 concurrent alphanumeric characters or more.

You could also try and prevent automated search requests by using one of the following:

• Only allow logged in registered users to use the search form.
• Use session variables to limit the number of searches a user can carry out in a set time period. If the user does not have sessions enabled prevent them from searching.
• Use JavaScript to run the search. Most bots will not be able to run script so the search will be disabled.
• Use session variables again so that only one search at a time per user can be requested. Set a flag on search submission and also when the results are returned. If another search is requested within that time prevent it from executing. Again if sessions are disabled prevent searching.

A better option is to not use LIKE statements at all but use a full text index to handle searching.

As well as being more secure due to the fact that symbols are treated as noise characters and therefore ignored you can offer your users a more feature rich search function. You can use SQL Servers inbuilt full text indexing or a 3rd party index systems such as DTSearch.

Setting up full text indexing on a table in SQL 2012 is very easy for example

1. Make sure the Full Text Index service is installed on the server.
2. Right click the table you wish to create an index for and select "Define Full-Text Index"
3. Select the columns you wish to create the index on. In my jobboard example it will be the JobDescription column.
4. Select how you want changes to the underlying data to be updated within the index. I chose "Automatic" as it means that whenever the data in the JOB table changes the index will get updated at the same time. You could set manually and then create a schedule to update the index.
5. Give the full text catalog a name. I create a new Full Text Catalog per table that needs an index. If you have multiple indexes that are quite small then you can share a catalog.
6. If you have not chosen to automatically update the index when the data changes then create a schedule to do this for you.
7. Create the index!

Once created you can access the data in a variety of ways by joining your index to your other database tables. However this is not an article on Full Text indexing so for a quick example that will replace our earlier search SQL that used LIKEs and OR's.

SELECT    TOP 10 JobPK, JobDescription,Rank
FROM      JOBS as j
JOIN      FREETEXTTABLE(JOBS,JobDescription,'SQL ASP') as K
 ON      K.[KEY] = j.JobPK
ORDER BY Rank DESC

This query will search the index I just created for the search term "SQL ASP" and return the top 10 results ordered by the Rank value SQL gives each result depending on how close it matches the term.

There are many features built into SQL Servers Full Text Indexing implementation which I won't cover here. However this serves as an example of how easy it is to convert a LIKE based search to an index based search.


Conclusion

As you can see it could be very easy, depending on how you validate any search terms entered by your users, for a malicious user or BOT to create havoc by executing long running and CPU intensive queries against your database .

If crafted correctly an effective SQL DOS attack could cause your system to become unavailable for a period of time.

Any part of your site that offers a search facility should be tested for vulnerabilities by entering various search terms and then measuring the effect.

Although deadly at the time of execution these attacks are temporary and can be prevented with good application design and input validation.


Further Information

If you are wanting to create a rich Google like searching system for your site I suggest reading the following article which is very detailed:

http://www.sqlservercentral.com/articles/Full-Text+Search+(2008)/64248/

For more details about SQL Denial of Service attacks read the following article:

http://www.slideshare.net/fmavituna/dos-attacks-using-sql-wildcards/

For more information about pattern matching in general and the problems with regular expressions and CPU read my blog article:

http://blog.strictly-software.com/2008/10/dangers-of-pattern-matching.html

Script - Find Text in Database

Find and replace text in database

The following procedure is a very useful stored procedure that is my first port of call when I am tasked to investigate sites that have fallen victim to SQL injection attacks. The script has 4 different methods which are outlined in the comments. However I would first use method 1 which will output a list of any tables and columns within the database and the number of rows that contain the offending hack string. From that data you can then decide whether you need to run the other methods that either output all UPDATE statements needed to remove the hack or run them straight off. The other method outputs every single affected row within the database which is useful as you can determine whether a clean will work or not by the placement of the injected code.


Script Details

I have created 2 versions of the proc one for SQL 2005 and one for 2000/7. The only real differences are that I can use NVARCHAR(max) in the 2005 version and the system views are slightly different.
You could choose to update the 2005 version to use the CLR and a regular expression UDF to speed up the text searches.

Download SQL 2005 Version

Download SQL 2000 Version


Searching and replacing multiple strings

I have also created another script that allows you to search for and if necessary replace multiple strings in one go. Some sites are charging $300 for code like this and you may have even seen the adverts on this site :). So if I have saved you or your company some money then please feel free to make a donation!.

Download code to search and replace for multiple strings

I have added a branch within this proc that checks the version of SQL server and calls the appropriate proc. However you are probably only going to want to use the procedure that your server supports so comment out or remove the following code:

IF patindex('%SQL Server 2005%',@@Version)>0
 SELECT @SQL_VERSION = 2005
ELSE IF patindex('%SQL Server  2000%',@@Version)>0
 SELECT @SQL_VERSION = 2000

And also further down within the loop remove the call to the proc that your not using.

IF @SQL_VERSION = 2005
 BEGIN

       EXEC dbo.usp_sql_find_text_in_database
         @Mode,
         @Value,
         @ReplaceString
 END
ELSE
 BEGIN

       EXEC dbo.usp_sql_find_text_in_database_2000
         @Mode,
         @Value,
         @ReplaceString
 END

Example Usage
So you need to hunt down and remove the following injected hack strings:

<script src="http://www.usaadp.com/ngg.js"></script>
<script src="http://www.bnsdrv.com/ngg.js"></script>
<script src="http://www.cdport.eu/ngg.js"></script>

Just call the usp_sql_find_multiple_text_in_database proc in the following way

EXEC dbo.usp_sql_find_multiple_text_in_database
@MODE = 4,
@FindString = '<script src="http://www.usaadp.com/ngg.js"></script>||<script src="http://www.bnsdrv.com/ngg.js"></script>||<script src="http://www.cdport.eu/ngg.js"></script>',
@SplitOn = '||',
@ReplaceString = ''

Which will hunt for each string in turn in all textual columns (char,nchar,nvarchar,varchar,ntext,text) and replace it with an empty string.

If you don't want to carry the UPDATE out straight away you could use a different option by changing the @MODE flag:

1 = Output an overview list of each table and column containing the string and the no of rows found for each. This is a good way of checking how much data has been corrupted.

2 = Output all the rows containing the string. This may be quite a lot if your whole database has been comprimised.

3 = Output the update statements needed to remove the string.

4 = Find and replace all occurrances of the string.


So there you go a way to clean up your infected SQL databases and save yourself $299 at the same time. As I am saving you some money buying a clean up product and possibly lots of money due to lost business revenue then please consider making a donation so that I can continue publishing scripts like this for free.