Using SQL for Denial of Service (DOS) attacks

You should have heard about SQL injection attacks and most certainly DOS (Denial of Service) attacks but you may not have heard about SQL Denial of Service attacks as something to be on the look out when considering website security. The idea behind the attack is pretty simple and that is to use any search forms available on your website to get your SQL database to execute a number of long running and CPU intensive queries so that the site becomes unusable to other users. The reasons being either:
-The CPU on the database server is maxed out running these intensive queries.
-All connections to the database are inuse by the attacker and therefore no other connections can be spawned therefore locking up the site to other users.


Who is vulnerable to this form of attack?

Most websites have an SQL backend 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. A few examples of search forms are:
-Searching for content in a CMS system.
-Searching for jobs, members or properties.
-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 jobboard sites and imagine that we used the database to handle keyword searches. 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. Also during the execution of this one query the CPU on the SQL server was maxed out at 100% ! 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. Any part of your website that used a database connection would be out of action for some time.


Changing effectiveness of the attack

The 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
-Connections (the length of time allowed for connecting to a database)
-Commands (the length of time allowed for a query/procedure to run)

The defaults for ADO are 30 seconds each.

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

• Strip all non alpha numerics from the input.
• Set a maximum length for terms entered. If 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 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 indexer such as DTSearch.

Setting up full text indexing on a table in SQL 2005 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 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 effectively causing 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

Tweet