5

Automated SQL Injection Detection

Introduction

SQL Injection is still a common web application vulnerability these days, despite the fact that it’s already around for ages. The more general ‘Injection’ vulnerability is still at #1 in the OWASP TOP 2013, partly because of the huge risk that is involved – a database usually contains sensitive data that can be leveraged to conduct further attacks, either on the web application layer (privilege escalation) or even the OS layer (remote code execution) when certain prerequisites are met. However, because of limited adoption of best security practices by web application developers and, more importantly, the default use of parameterized queries in popular DB frameworks such as LINQ, SQL Injection is slowly becoming less prevalent in the wild.

Automated Exploitation

Exploiting an SQL injection has become relatively easy thanks to some magnificent automated tools such as SQLNinja and SQLMap, the latter being my personal favorite. Many of these automated tools are highly configurable and often allow you to circumvent blacklists/filters or even provide you with an OS shell if you’re lucky, amongst many other features. SQLMap contains an impressive collection of payloads to detect and exploit SQL vulnerabilities – their templates can be found in the payloads.xml file. SQLMap currently contains specific payloads for six injection techniques: boolean-based blind, time-based blind, error-based, UNION query, stacked queries and out-of-band. The SQL injection vulnerabilities that are not exploitable after some tweaking with SQLMap or one of its competitors are very, very rare.

Automated Detection

However, before you can exploit an SQL injection vulnerability in an automated fashion, you must detect it first. Detecting new injection vulnerabilities with aforementioned automated tools is not the best option, because of a couple of reasons. Usually these tools take a lot of time, simply because of the fact they will enumerate a significant number of payloads by default. Currently, SQLMap gives you some options to limit the number of selected payloads: –level, –risk, –dbms and –technique (to my knowledge), but even if you fully understand and utilize all these options, you still have no complete fine-grained control over the payloads that are sent as well as their respective detection method implementations. Additionally, these tools are not always fool-proof. For example, I found SQLMap’s blind time-based detection method in combination with multiple threads and some background (browsing) traffic to produce a significant number of false positives sometimes, which is quite unpleasant. Finally, when you have a web application which utilizes something out of the ordinary such as a custom CSRF mitigation implementation or an exotic authentication method, these tools usually will not be able to reach all potential injection points.

What I (and undoubtly many others) usually do is select the values that may be used to fetch data from a backend manually, and then use a web application fuzzer to replace them with specific payloads to detect SQL injection vulnerabilities. Once detected, I revert to SQLMap or another automated exploitation tool. I am used to Burp Intruder for these kind of fuzzing attacks, but I’ve also occasionally used the Zed Attack Proxy Fuzzer for this task. The advantage of using an intercepting web proxy as fuzzer instead of an automated tool such as SQLMap, is that you have full control over the payloads sent and the detection method used. Also, they are used to dealing with exotic HTTP attacks such as circumventing CSRF protection, second-order fuzzing, amongst many others – as it is their ‘core business’. To my knowledge, an intercepting web proxy allows to automate detection of SQL injections via three general methods, which I compare below. If you immediately want to try out the described techniques, I recommend SQLFiddle. It supports various up-to-date versions of MSSQL, SQLite, PostgreSQL, MySQL and Oracle.

Error-based

This method is based on injecting payloads that break out of the original query and generate an SQL error on the server-side which can be detected in the content of pages returned by the web application.

Prerequisites

Verbose SQL error messages must be turned on. The SQL error must be echoed into the response.

Payloads

Characters or words that break SQL syntax. Usually a single or double quote will do the trick, as this is part of the SQL syntax of all popular database flavors.

  • MySQL: You have an error in your SQL syntax – SQLFiddle
  • Oracle: ORA-01756: quoted string not properly terminated – SQLFiddle
  • PostgreSQL: ERROR: unterminated quoted string – SQLFiddle
  • SQLite: SQLite exception – SQLFiddle
  • MSSQL: Invalid SQL statement or JDBC escape, terminating ”’ not found – SQLFiddle

Detection

Grep output of pages for error messages. Burp Intruder contains a list by default. Kaotic also made one and describes the whole technique.

Conclusion

The very limited number of payloads to cover all database flavors is a big advantage, but the prerequisite of verbose error messages is often not met in current web server infrastructures and in that case, renders this technique useless.

Boolean-based

This method is based on injecting payloads that alter the outcome of the original query which results in different returned page content.

Prerequisites

The vulnerable parameter must have influence on the content of the page and therefore be used in a boolean expression, which usually limits the scope of this approach to parameters used in the WHERE clause of queries.

Payloads

Replace a parameter’s value with a boolean subquery that yields true for the entire query:

Two adjacent OR clauses must be used because AND has a higher binding precedence than OR. This SQLFiddle should explain it all.

Detection

To detect the vulnerability, one must identify a significant difference in content length between the base request’s response and the response of a request containing an injected payload. The best approach is to take a base request with an invalid parameter value, so its response is as small as possible. When the parameter is vulnerable to SQL injection, our payloads make the SQL query return more results, which in turn will be visible in the received response. Take the following testbed website of Acunetix as an example:

http://testaspnet.vulnweb.com/ReadNews.aspx?id=2

We want to test the GET id parameter, so first we replace the valid value with an invalid one, in order to obtain a base request that is as small as possible:

http://testaspnet.vulnweb.com/ReadNews.aspx?id=1337

Hereafter, we send this base request to Burp Intruder (or another tool that is capable to conduct fuzzing attacks with custom payloads), select the ID parameter’s value as injection position, load up our twelve payload strings and start the attack:

BurpIntruderAcunetixId

BurpSuiteAcunetixBooleanPayloads

BurpIntruderAcunetixAttackResults

One can clearly see that our first payload request yielded a response much bigger than the invalid base request, and all other payload requests yielded a very small response. In the bottom of the attack results window, we can see that only the HTTP headers are returned for these requests, which may indicate a failure on the server-side whose errors are suppressed – no verbose error messages here. When analyzing the payloads that caused a small response more closely, we see that they all contain characters that may break an SQL query: quotes and brackets. We can guess that this is what happened, resulting in an empty response.

When opening the requested that yielded a bigger response in our browser, we see that a page containing a valid news article is returned:

http://testaspnet.vulnweb.com/ReadNews.aspx?id=999999 or 1=1 or 1=1

This may imply that we found an SQL injection, but it is still unsure. Some web applications display a default article when certain conditions are (un)met, for example. In order to be more certain, I usually perform a simple but quite reliable manual test, different for the following situations:

  • Integer: replace a valid parameter’s integer value with a mathematic equation that results in the same value and verify whether the two responses are the same, which would imply that the parameter’s value is directly interpreted by an SQL backend and SQL injection is very likely..
  • Strings: split a valid parameter’s string value in two parts, and add an SQL string concat directive in between. An identical response for both requests would again give you reason to believe you have just hit an SQL injection. Pentestmonkey’s SQL Cheat Sheets are great to quickly lookup the string concat syntax for various popular database flavors, amongst other useful queries. Alternatively, Roberto Salgado’s SQL Injection Knowledge Base is also a very good resource for MySQL, MSSQL and Oracle.

These manual tests generally work well because they never break out of the local injection environment. You don’t need to comment out any part of the original query or insert brackets to revalidate it, as mathematic calculations and string concat operators have a higher precedence than boolean operators in all major database flavors. Their main problem is that they are hard to automate. One could write a plugin for burp suite (or any other capable intercepting web proxy) that performs the aforementioned substitution and compares the two responses for both parameter values – but nowadays, two responses for identical parameter values often even don’t match, because of cookies, CSRF tokens, advertisements, dynamic messages, TOR Javascript code injections, PRISM, … you name it. Target-specific thresholds could be configured, but in this case some configuration is still needed – no silver bullet here.

When performing the request below, we get the exact same response as for our base request with id=2:

http://testaspnet.vulnweb.com/ReadNews.aspx?id=3-1

This is usually the moment that I revert to SQLMap.

Conclusion

The limited number of necessary payloads to cover all database flavors is again a big advantage. The main downside of this method is that it may still miss some injection points, which are located in different fields than the WHERE clause or do not influence the response of a web page. However, when you see a that parameter obviously influences the response of a request, this one is definitely worth trying out – e.g., a search form with many fields. I often just enter some invalid value for each field, send the request to intruder and launch these payloads against them (spider mode). It must be noted that this method may still result in false positives – manual verification needs to be done afterwards in order to fully confirm the vulnerability.

Time-based

This method is based on injecting payloads that trigger a delay time for the SQL Server while processing our query, which in turn slows down the response time of our request.

Prerequisites

The parameters under assessment must not have strong length limits nor filtering, as the payloads are rather long. Almost all payloads also induce a temporary heavy load on the SQL backend, so if you’re testing on a critical production environment, consider using one of the previous, less performance-hogging detection techniques.

Payloads

1) Time-delay queries

Some database flavors such as MSSQL (waitfor delay) and MySQL (benchmark) contain directives to instruct the SQL Server to wait a significant amount of time before returning the answer of the query, as described by Fun Over IP (foip) on his blog in 2010. However, SQLMap has evolved since that blogpost and currently contains time-delay payloads for almost all database flavors it supports today (as of writing): MySQL, Oracle, PostgreSQL, Microsoft SQL Server, IBM DB2, SQLite, Firebird, Sybase and SAP MaxDB. I wrote a script to extract these time-based payloads that SQLMap uses, which you can download at the end of the blogpost. It instruments SQLMap to attack the local webserver’s landing page with time-based queries and high verbosity, which allows the script to grab the exact payloads being sent and save them according to database flavor. I used these payloads as an inspiration to construct one specific time-delay payload of approximately 15 seconds for each database flavor, in the form of a complete query:

  • MySQL: select benchmark(15000000,md5(0x4e446b6e))
  • PostgreSQL: select pg_sleep(15)
  • SQLite: select like(‘abcdefg’,upper(hex(randomblob(150000000))))
  • MSSQL,Sybase: select count(*) from sysusers as sys1,sysusers as sys2,sysusers as sys3,sysusers as sys4,sysusers as sys5,sysusers as sys6,sysusers as sys7
  • Oracle: select count(*) from all_users t1,all_users t2,all_users t3,all_users t4,all_users t5
  • DB2: select count(*) from sysibm.systables as t1,sysibm.systables as t2,sysibm.systables as t3
  • Firebird: select count(*) from rdb$fields as t1,rdb$types as t2,rdb$collations as t3
  • SAP MaxDB: select count(*) from domain.domains as t1,domain.columns as t2,domain.tables as t3
  • SQL-92 Compliant DBs:  select count(*) from INFORMATION_SCHEMA.tables as sys1, INFORMATION_SCHEMA.tables as sys2, INFORMATION_SCHEMA.tables as sys3, INFORMATION_SCHEMA.tables as sys4, INFORMATION_SCHEMA.tables as sys5, INFORMATION_SCHEMA.tables as sys6

Note that the last six payloads all use the same approach: induce a time delay by a heavy join operation on tables that are present and readable by default. They only differ in the naming of the default tables, because each database flavor uses another naming convention. The last payload covers all databases that are compliant with the SQL-92 standard, since this standard demands the presence of the INFORMATION_SCHEMA database schema.

2) Local  injection environment

In order to optimize the success rate and minimize the amount of payloads, I reused the neat ‘local injection environment’ manual tests trick which I described in the previous method’s section. As it turns out, all major SQL database vendors allow integer and string values to be replaced by subqueries that return something: MySQL, MSSQL. Note that the return value of the subquery must be of the same type in database flavors that do not do implicit data conversions, otherwise the whole query will yield a syntactic error before the subquery is even executed: PostgreSQL. However, we can circumvent this limitation by making sure that we cover every possible value injection context: integer & order by, string (single & double quoted), and column. This is exactly what the following payloads achieve:

  • Column: (SUBQUERY) as test
  • Integer, order by: (SUBQUERY)
  • Single-quoted string: ‘[STRINGCONCATOPERATOR](SUBQUERY)[STRINGCONCATOPERATOR]’
  • Double-quoted string: “[STRINGCONCATOPERATOR](SUBQUERY)[STRINGCONCATOPERATOR]”

Note however that, if injected successfully, we can significantly change the result of the original query. Therefore, I modified the payloads one last time to return a very unlikely value as a result, so in case of injecting in a DELETE statement, we will not accidentally hit one or multiple records which are then deleted.

This gives the following final four payloads for PostgreSQL:

You can see them live at work against INSERT, UPDATE, DELETE and SELECT here (unquote the injected queries you want to test).

You can download all final payloads quartets for all aforementioned database flavors at the bottom of this post. Note that the case of MySQL is a bit different than the others, because MySQL doesn’t have a native string concat operator. In order to still detect field string injection vulnerabilities, I replaced the string concatenator operator with a simple ‘and’: SQLFiddle.

Detection

Let’s repeat the same exercise of the previous section: detect the blind SQL injection on the Acunetix testbed ASP website:

http://testaspnet.vulnweb.com/ReadNews.aspx?id=2

We send this as base request to Burp Intruder, select the ID parameter’s value as injection position, load up our 41 payloads and launch the attack:

BurpIntruderPayloadPositionTimeBased

BurpIntruderPayloadsTimeBased

BurpIntruderResultsTimeBasedFirstAttack

When adding the ‘response completed’ column to the results view and sorting on this tab, we see that five requests took significantly longer than the other 36. However, one of these payloads is for integer injection, one for double-quoted string injection, and three for single-quoted string injection – that doesn’t make much sense. Why would one parameter be vulnerable to all these attacks?

It is because of the default number of threads Burp Intruder deploys – coincidentally: five. Because one of these payloads was stalling the back-end database, the other four concurrent requests also took about the same time before receiving a response, as they were all waiting on the SQL DB to respond to their query. Nevertheless, these results already give us a big hint that SQL injection is around the corner. To verify which of the five queries is the right one, repeat the attack, but now with only one concurrent thread. You can setup the number of threads in the options tab:

BurpIntruderTimeBasedAttackThreads

Restart the attack by selecting Attack->Repeat. This yields the following results:

BurpIntruderResultsTimeBasedSecondAttack

We have now officially identified an integer injection flaw in a MSSQL backend database, which we exploited to execute a query that took more than 20 seconds to complete. I usually revert to SQLMap now for the exploitation phase.

Conclusion

The main advantages of the time-based method in combination with the new payloads, is that they will reliably detect all present  injection vulnerabilities – it’s just a matter of launching them against any field you think might be involved in a database action. Do not only test GET and POST parameters with them; HTTP headers such as User-Agent and Referer are more and more logged to a database these days. If you know the database flavor beforehand, only four payloads are necessary – otherwise you’ll have to use the collection of payloads for all database flavors, currently 41 in total, or a subset of it if you can exclude any. However, they also have some drawbacks. The payloads themselves are quite big, which makes them more susceptible to length and content filtering. They also induce a real performance hog on the server-side, which may be undesirable in some occasions. Nevertheless, they are my preferred payloads whenever the situation allows me to use them, simply because they give by far the highest level of assurance.

Download section

Zip archive containing SQLMAP payload extraction script (tested on BT5 and Kali): extract-blind-sqlmap-payloads

Zip archive containing all discussed boolean and time-based payloads: SQLi payloads

Belgian. IT Security. Bug Bounty Hunter.

5 Comments

  1. Very nice, could you possibly also make all the 41 Time-based variations available for download as a text file?

    • Hi Daniel,

      Thanks for the compliments. You can find a list containing all 41 time-based payloads in the zipfile that is mentioned in my post for download. The 41 payloads are gathered in the file named ‘ALL’.

      Cheers,

      Arne

Leave a Reply to Ramesh Cancel reply

Your email address will not be published. Required fields are marked *