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:
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:
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:
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
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:
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:
Solutions to the SQL DOS attack
In general you should always set a timeout limit for
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:
You could also try and prevent automated search requests by using one of the following:
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
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.
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
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 JobPKThis 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 JobPKThis 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:
- The longer the string being matched the longer the query execution time.
- 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.
- Combining patterns in an OR statement will increase the execution time. Make sure each pattern is different.
- 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 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
- Make sure the Full Text Index service is installed on the server.
- Right click the table you wish to create an index for and select "Define Full-Text Index"
- Select the columns you wish to create the index on. In my jobboard example it will be the JobDescription column.
- 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.
- 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.
- If you have not chosen to automatically update the index when the data changes then create a schedule to do this for you.
- 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 DESCThis 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
,
Posts
