Thursday, January 5, 2017

Exploiting difficult SQL injection vulnerabilities using sqlmap: Part 1


A number of times when discovering "tricky" SQL Injection vulnerabilities during penetration tests, I have taken the approach of exploiting them by writing custom tools.  This usually after spending 5 minutes blindly poking at the vulnerability with sqlmap, and then stopping when it didn't immediately magic the answer for me.

OK, there have been a number of times where sqlmap has NOT been a suitable tool to use for various reasons, such as very particular filtering or data retrieval requirements, but there has also been a number of cases where I probably gave up on it too fast because I didn't properly understand how it worked or the extent of its capabilities. And this resulted in me taking much longer than necessary to exploit the vulnerability.

While writing custom tools can certainly be "fun" (for some definitions of "fun"), and while it provides some good coding practice and is an excellent way to ensure that you understand the injection flaw and its exploitation extremely well, its also very time consuming.  Writing your own injection tool often involves redoing a lot of work that has already been done by others - the digital equivalent of reinventing the wheel. You need to put together a capable HTTP sending/receiving framework, you need to parse HTML responses, you need to discover the (database specific) SQL commands that will allow you to retrieve data within the limitations imposed by the vulnerability, you need to be able to extract, group, infer, convert and/or join the retrieved data and you need to mentally map out the logic needed to tie all these parts together and turn it into working code with a usable interface. Its a deceptively large amount of effort, especially when blind injection is involved, and I would consistently underestimate how long it would take to perform.

Given that sqlmap already has all this functionality, being in particular a very effective tool for retrieving data via all types of SQL injection vulnerabilities,  I recently decided that it might be a good idea to spend some of my time to gain an improved understanding of the tool, so that in future I would be able to make more frequent use of it.

For my vulnerability test bed, I used some of the SQL injection labs from the Pentester Labs website, namely the Web for Pentester and Web for Pentester II exercises, because those particular exercises are freely downloadble, easy to self host and provide some great examples of SQLi vulnerabilities that require use of some of sqlmap's custom options for exploitation.

This will be the first in a series of posts where I share some of what I learned during this process. This first post will mainly seek to introduce and explain the relevant sqlmap options that I used and outline a process that can be used to get sqlmap to identify an SQL injection flaw that you have discovered through other testing activities.  Future entries will provide examples of actually using this to exploit SQL injection vulnerabilities that sqlmap cannot easily detect on its own.

Note: While I will use their content as examples, the intent here is NOT to explain how to discover or do manual exploitation of the SQLi vulnerabilities in the PentesterLab exercises - because that has already been written up in the PentesterLab courseware available at their web site. If you don't already know how to do manual discovery of SQLi vulnerabilities, you can check out their site, or any of the many other SQLi references on the Internet to learn this (for the record though, I think the PentesterLab stuff is a fantastic introduction to web application pentesting, and I wish I had access to it when I first started doing webapp testing).

Useful sqlmap options

Before I jump into working through specific examples, I wanted to describe the purpose of some sqlmap options.  More advanced use of sqlmap, in terms of actually tweaking its operation in order to make a difficult injection operate, will require that you actually understand how these options work. In essence, this is the README I wish I had received when I moved beyond the bare basics in my use of the tool, as I definitely would have used sqlmap much more extensively had I understood these particular options as well as I do now. Hopefully you can now benefit from my having learned this the "hard" way, e.g. via trial and error.

Prefix and suffix

The prefix (--prefix) and suffix (--suffix) options configure the strings that should be included with each SQL injection payload in order to begin, and then terminate, the Injection.  So what does this mean exactly?

Take this simple example of an injectible query:

$query = "SELECT first_name, last_name FROM users WHERE name = '" . $_GET["username"] . "'";

Whats an example of an injection string that would work here?  Something like the following would work as a simple POC of a union injection.


This closes the single quoted string before our injection point with a single quote ('), seperates the next statement with a space ( ), adds our injection query of a UNION SELECT with a column count matching that of the existing SELECT query, and then comments out the remainder of the original query to ensure syntactical correctness.  The prefix in this case is the single quote and space (' ) used  before the UNION SELECT, and the suffix is the characters (a space, two dashes, another space and the letter "a") used to comment out the remainder of the original query ( -- a).

The following options can be used to configure sqlmap to use this prefix and suffix:

 --prefix="' " --suffix=' -- a'

Now, these particular examples of prefixes and suffixes (or ones that are functionality identical) are ones that sqlmap will be able to figure out itself, so you will rarely need to specify values like this.  However, this hopefully does help you in understanding what these options do, because they are quite important ones to grasp if you want to use sqlmap for more difficult injections. In fact, I put these options first in the list of ones I wanted to describe because as I was working through this process of learning how to make sqlmap identify certain injection vulnerabilities, these were the ones that I used the most.  Also, finally learning what these did was an "AHA!" moment for me, as I have been aware of the options existence for an embarassingly long time without understanding what they did.

Note: Why use NULL values in the UNION SELECT? NULL is a great value to use in UNIONS when trying to determine the correct number of columns in an injection, as it can sit in place of a number of different field types, such as numbers, strings and dates.
Note2: Why the extra space and the "a" character after the comment? Sometimes, inserted comments at the end of an injection are not properly recognised by the database unless there is a whitespace character to follow. Since whitespace characters on their own are sometimes not easily identifiable when displayed on screen (depending on what other text follows) its helpful to include other text afterwards so you can easily see there is something following the comment. You will see sqlmap do this when you look at some of the injection strings it uses.

Specifying Injection technique and tests

There are a number of different SQL injection techniques available for use in sqlmap, which are configured via the --technique option, and sqlmap comes with a number of different in built tests for exploiting vulnerabilities using those techniques. By default, sqlmap will enable all possible techniques when trying to identify an injection vulnerability, and will run all associated tests that meet the configured risk and level settings (discussed later).

If you have manually discovered a SQL injection flaw in a website and want to use sqlmap to exploit the vulnerability, you may already know the correct technique, as well as the most appropriate payload configuration to use, and this is where specifying these options manually can be useful. Manual specification of these settings helps prevents less effective techniques from being chosen by sqlmap, and cuts down on the amount of traffic sent by sqlmap during its detection period.

A brief listing of the injection techniques available for use by sqlmap is listed below in order of preference. You can select the appropriate ones by using the --technique switch followed by a listing of the letters associated with the method/s you wish to use.  The default is all options, (e.g. "--technique=BEUSTQ").  The descriptions provided below are only intended as high level reminders of each technique
  • Stacked queries (S) - This involves stacking whole new SQL queries onto the end of the existing injectable query. Its the preferred method to use if available, because there are a number of exploitation actions that wont be available to you using any other method, however the use of this method does require support from the database and API. You may not necessarily be able to see the results of your stacked query in the page response, so when actually retrieving data (as opposed to performing other operations such as INSERTS) you may want to use another technique such as Unions.
  • Union query based (U) - This involves retrieving data by joining a second select statement to the original, via the UNION SELECT statement. You need to be able to see the results from the original SELECT query (and hence your UNION) in the page response for this method to be usable.
  • Error based (E) - This technique retrieves data by manipulating database error messages to directly display that data. To use this method, you need to be able to see database error messages in page responses.
  • Inline queries (I) - This technique uses inline database queries to retrieve data - essentially a query embedded within another query like this "SELECT (SELECT password from user) from product".  I have not personally had the occasion to use this option in sqlmap, and while inline queries can be used more widely than this in manual injection scenarios, it appears that you need to be able to see the inline queries result in the page response for this to be usable through sqlmap.
  • Boolean blind (B) - This retrieves data from the database by asking a series of True/False style questions in your injections, and determining the result (True or False) based on identifiable changes in the response. To use this option, you need to be able to be able to trigger some sort of identifiable state change in HTTP response content from logically different, but syntactically correct database queries (e.g. a different page response only resulting from an invalid database query doesn't count here).  This technique will require more requests and time to perform than those previously listed, as the data must be retrieved indirectly via boolean inference.
  • Time based blind (T) - This technique is similar to boolean blind, in that it retrieves data via posing a number of True/False style questions to the database, however instead of determining the answers to these questions via the content of a response, it is done using the amount of time a response takes. This is done through associating deliberate delays with particular answers via database statements that consume a noticeable amount of time, like sleep. This is the most time consuming method of data retrieval, and is sensitive to errors introduced by network load. Without careful custom configuration, you may find sqlmap selecting this technique for trickier injection vulnerabilities that can be exploited by more efficient means.

Selecting a particular technique, or set of techniques will limit the payloads that sqlmap will use to those associated with that/those technique/s. It is also possible to further filter the attempted payloads via the --test-filter and --test-skip options to target payloads that contain (or do not contain) particular text within their name.

If, for example, you know your target SQLi vulnerability exists within the 'ORDER BY' clause of a query, why not filter for only these test payloads by using:

--test-filter='ORDER BY'

In addition, if you write your own custom test payload for an injection, you can use only that particular payload by setting a filter for a unique string you have added to the name.

Note: To have the best chance of being able to configure sqlmap to detect and exploit a given difficult vulnerability, its important that you properly understand the type of injection you wish to use and the requirements for its exploitation. This is because for injection vulnerabilities that sqlmap cannot find on its own you have to be able to create an effective POC exploit manually to use as a basis for correctly setting sqlmap's configuration . Hopefully this brief summary of the available injection types is appropriately clear and detailed in order to provide a sufficient refresher, but if you are unclear on these techniques you may wish to do further research on any techniques you are unfamiliar with before continuing.

Risks and levels

The risks and levels settings in sqlmap will control which test payloads will be attempted during the detection run to identify an SQLi vulnerability. Each test payload has a configured level and risk setting, and if the configured threshold is not met for that payload during a particular run of the tool, that particular payload will not be used.

Risk in sqlmap refers to the risk of a failure, potential database damage or error in data retrieval associted with using an associated payload.  Available risk settings range from 1 to 3, with 1 (the lowest level) being the default.

Level refers to the number of requests required to use that associated payload for exploitation. Available level settings range from 1 to 5, with 1 again the default.

A common recommendation given in various usage guides is to increase the risk and level settings if sqlmap does not identify a vulnerability in its default configuration, however in my experience for trickier injection vulnerabilities this change alone is often not sufficient.

Detection options

Using the boolean blind injection technique will often require that you tell sqlmap what to look for in the HTTP response content in order to distinguish a True condition from a False. There are a number of options in sqlmap that allow you to configure this behavior, such as --string and --not-string (configuring strings that should appear in True and False responses respectively), --regexp (allowing you to set a regular expression to match to determine the True condition), --code (provide a HTTP status code to match True), --text-only (compare responses based on text content) and --titles (compare responses based on page title).

A neat thing you can do with the --string and --not-string settings is to use Python hexadecimal backslash quoting to do multi line matching. Here is an example showing how to match a section of HTML that includes newlines (\x0a) and tabs (\x09).


When your detection needs are more complex than what can be satisfied by the above options, there is also another sqlmap feature that with a little bit of imagination you can abuse in order to perform more complex comparative logic, which leads us to...

Second order injection

sqlmap contains a --second-order option, which is intended to be used to enable exploitation of second order SQL injection vulnerabilities, where the results of an SQL injection need to be retrieved from a different URL than that is used to actually perform the injection.  The option allows you to provide a single URL which will be requested by sqlmap after each injection payload is sent, and then parsed as per normal configured sqlmap behavior.

By setting the --second-order option to point to your own locally run custom forwarding and parsing server, you can make use of this option to return arbitrary content to sqlmap, perhaps based on data you have automatically retrieved from the target site. This capability can be used to do things such as retrieve data from a dynamically changing second order URL at the target site, or to retrieve content from the remote site and perform complex parsing or logic checks on it, passing through to sqlmap something that it can process using its inbuilt functionality.

This link contains a modifiable second-order forwarding server that I wrote in Python to work with sqlmap, which can be run locally from the command line.  It starts its own http server locally on the loopback address, and when it receives a request from sqlmap it can request data from another website, then return the (optionally) parsed data back to sqlmap.  It is based on Python classes that I wrote specifically to facilitate reuse and modification, so if you can code simple Python you can change it to do any parsing or fetching job you wish.

Tamper scripts

Tamper scripts in sqlmap allow you to make programmatic changes to all the request payloads sent by sqlmap, in order to facilitate the bypass of web application firewalls and other filters.  If you are dealing with filters that prohibit, for example, all whitespace within an injection string, there is a tamper script configured that can help (--tamper=space2comment).  A reasonably up to date listing of available tamper scripts and their purpose is available here.

Custom written test payloads

sqlmap comes configured with a large number of test payloads that it can use to perform injections. These are defined within xml files named after the associated injection technique stored in xml/payloads under the sqlmap root path.  You can add your own payloads into these files by copying the xml nodes of an existing test (one thats simlar to the one you want to create) and modifying it as required.  There is an example of doing this here, and a specific example of how to use custom test payloads to exploit a boolean blind issue inside the ORDER BY clause will be provided in a future post.

Verbosity and debugging injection checks

One extremely useful option for troubleshooting sqlmap's detection process is the output verbosity option.  The specific setting I use most frequently when getting an injection working is -v3, which will show each raw payload that is sent by sqlmap. This allows you to compare the payloads sent by sqlmap to your own POC SQL injection string developed during discovery of the vulnerability, to determine where sqlmap is incorrectly diverging.  If you need to use tamper scripts as well to bypass a filter, you can try verbosity level -v4 to also see the HTTP requests sent, as -v3 verbosity will not show the affect of tamper scripts.

Note: You can also configure sqlmap to work through an intercepting proxy for debugging purposes.  However, while I generally always have Burp Suite running when Im testing any web application, I usually prefer to avoid filling up my proxy history and slowing down the operation of sqlmap by doing this. Sometimes, if I really want to have a close look at requests and responses, I will run up a separate proxy instance using something like ZA Proxy.

Auto answering

Under certain circumstances, sqlmap will ask you the same set of one or more repeated questions every time you run the tool. Some of these questions are without their own associated command line options, and therefore without an obvious way to inform sqlmap of the desired behavior so you don't have to repeatedly answer the same question the same way every time sqlmap prompts you.  The --answers option allows you to provide a standard response to these questions - to use it, pick a unique term from the question itself, and provide this along with the desired response.

For example, to preemptively answer Yes to allow sqlmap to attempt to "optimize" timing settings during blind timing based injections, use the following.


Session flushing

sqlmap keeps session information about each url, including which techniques and payloads have been confirmed to work and what data has been retrieved from the site.  If a non optimal payload type has been associated with a particular url within the relevant session, you may want to clear that session information in order to try and get a new payload to work.  You can flush all data associated with a URL, and force the detection process to run again, using the following option.


Other options

Some other options I commonly use are the parameter option which specifies which parameter is used to perform the injection (e.g. -p 'vulnerable_parameter') and the options to specify the database (e.g. --dbms='mysql') and the Operating System (--os='linux') in use on the remote server.  These all help sqlmap to avoid making extraneous requests beyond what you already know will be effective based on your knowledge of the target web application.  Sometimes of course the injection point is not within a parameter, in which case sqlmap has other options which can be used to target its operation, such as the asterisk character (*) which can be used to set manual injection point within a request.

Tweaking sqlmap options to detect tricky injections

Before you can use sqlmap to effectively exploit an injection issue, you must get it to detect the vulnerability, which associates one or more injection techniques and payloads with the URL associated with the issue.  Once this has occurred, the detection process does not need to run again, and sqlmaps options for exploitation and data retrieval can be immediately used on subsequent executions of the tool.

The following is the process I use for taking a manually discovered SQL injection vulnerability and configuring sqlmap to exploit it.
  1. Develop the manual exploit to the point where a POC for the best applicable exploitation technique exists. For a UNION SELECT vulnerability, this means you want to discover the number of columns in the UNION, and perhaps also the datatypes of each column (numeric, text, date, etc). For a boolean blind, you will want to be able to trigger different pages responses for True and False conditions, and determine how you could differentiate the True response from the False. For a time based blind, you want to get a response to delay for a given period of seconds based on the success or failure of some comparison you make, etc. This step will also include working out whether any specific characters are restricted by some sort of filter or other application issue, and hence are unusable in performing the injection.

  2. Run sqlmap, configuring the backend database type (--dbms), Operating System (--os), and technique (--technique) options to specifically target the manually discovered issue. Set the parameter (-p)  option as well if the injection is in a URL or POST data parameter, or use other options such as the injection point asterisk (*) as appropriate to tell sqlmap exactly where the injection is located. This helps focus the detection process, minimising requests sent and time taken by ignoring non-vulnerable parameters and payloads that target other databases or are associated with unwanted injection techniques. You may also need to provide proxy details, cookies or other authentication options, CSRF management options, safe URL settings to avoid lockouts, etc as appropriate, to ensure that sqlmap can correctly send and receive HTTP requests and responses. If you have already created a manual injection POC in a separate tool you should already know all the correct settings to use for this purpose.  Leave all other options at the default. I do all my manual testing using Burp Suite Professional, so I use the CO2 plugin and its SQLMapper component to quickly set the relevant command line options.  From this point on in the process, as soon as you get sqlmap to detect the vulnerability, you can skip the remaining steps (hopefully thats obvious). 

  3. Run the detection again, however this time use the -v3 verbose option on so you can see the payloads being sent.  Scroll through the output, looking for an injection string thats similar in layout to the POC developed earlier, which will cause the response you require. At this point you may see the names of likely looking payloads that are not being sent here because the --level or --risk settings are too low. If so, raise these values and try again and see if you can find an appropriate payload that comes as close as possible to what you need.

  4. If at this point you still do not see a payload that looks like it will be able to provide the output needed to make the injection succeed, you will need to write your own. Pick an example from the xml file named after the appropriate injection technique thats as close as possible to what you need, and modify as required.  The earlier section on custom test payloads contains references that help describe this process, and a future post in this series will also have a specific example.

  5. Once sqlmap is sending a payload that is logically similar to your POC, the goal is to now tweak the relevant sqlmap options to get the request syntactically correct for the injection.  At this point you will want to set the --test-filter option in order to send only your chosen payload, and try and determine what needs to change with the payload to make it work. By "work" I mean that you must be creating injected queries that are syntactically correct and the results must not involve database errors, displayed to you or otherwise, UNLESS you are doing error based injection and that error is displayed to you and contains your chosen content. This troubleshooting may involve taking the payload from the sqlmap verbose output and pasting it into your manual testing tool (i.e. Burp Suite Professional's Repeater) to see if it returns a syntactically correct result. Sometimes however, you can just eyeball it and tell where there are some obvious issues. The next step provides guidance on how to fix syntax issues.

  6. If the payload being sent is resulting in a SQL query that is NOT syntactically correct, there are 3 primary reasons for this.  Work out which issue (or combination of issues) is causing the problem, and work to resolve these as discussed below before moving on to the next step.

    • The first possible reason is that the prefix and suffix have been set incorrectly (either manually by you or automatically by sqlmap). You know this is the case if the text used at the start of the payload to break into the injection, or the text at the end used to terminate it, are syntactically different from your POC. Correctly set the suffix and prefix options to fix this - the right values should be easy to identify as they will be included in your manual POC. Be aware here that certain test payloads are configured to place random values at the start of the payload output. If you set the --prefix option and don't see the configured string at the very start of the payload output you are using in sqlmap's verbose output, you know that the payload configuration itself is the cause (specifically, the where option in the payload configuration), which is the second possible reason.
    • Second, the definition of the test payload itself is causing an error for some reason. I have seen the sqlmap default payloads break in some cases, but the most likely way for this to occur is when you have written the payload yourself. If the text or logic or the placement of the random values used by sqlmap in the meat of the payload is causing the issue, the problem might be with the definition of the test payload (or you might be focusing on using the wrong payload and another one you have overlooked is more appropriate). Modify the payload, try a different one, or create a your own custom new one to fix this.
    • Third, there is some sort of filter implemented in the space between when you send the request and when the resultant query reaches the database that is causing an otherwise syntactically correct payload to be rejected. This is where tamper scripts can be used to (hopefully) filter out or replace the offending characters. Don't forget to bump your verbosity setting to -v4 in order to see HTTP requests in the output if you need to troubleshoot these. You can either use one of the existing tamper scripts (if a suitable one exists) or write your own. If the filtering is particularly prohibitive, you may need to consider writing a payload that makes use of inventive SQL to avoid your given bad patterns.

  7. Once your queries are syntactically correct, the next step is ensuring that sqlmap can correctly interpret the results it is receiving (and, in the case of second order injections, that it is receiving the correct results at all!). Setting aside second-order injections for the moment (we will cover this in more detail in a future example), sqlmap is generally pretty good at this for all of its techniques other than boolean blind injection. For these, you will often need to tell it how to distinguish True from False responses. This is where the detection options such as --string, --not-string and --regex discussed earlier come into play - use these to help sqlmap identify the appropriate responses.

Once you have completed these steps sqlmap should have correctly detected your vulnerability and be ready to exploit it.

This completes this entry in the series, stay tuned for the next post, where I will show some examples.