Blind Error-based SQL Injection with NULLIF()

The other day I found an interesting way to use error-/boolean-based SQL injection, blindly, to exfiltrate database artifacts. What do I mean by that? Well, imagine you have a controller that presents the same information over and over again, regardless of what inputs you pass it - except when you make it trigger an incorrect SQL statement which results in a blank page.

The typical way of exploiting this condition is to use a time-based or boolean-based approach. Time-based attacks use an if-then-else block that sort of looks like this (in pseudo code) to infer the value of a database item:

if (db_value == 'guess') {
  sleep(10); // sleep ten seconds
} else {
  return;
}

In other words, by measuring the time it takes to render the page we can infer whether or not our guess was correct. If it is correct the page will render in 10 seconds, otherwise we'll get the page result right away.

The second approach, boolean-based, looks at how the response differs. People typically use it to fingerprint the presence of a SQL injection vulnerability where they don't have access to any error information. For example, if we take a look at these two HTTP request-response pairs, we can guess that the parameter q is most likely vulnerable to SQLi based on the server's Content-Length header (50 vs. 0):

Client: GET /?q=normal HTTP/1.1
Client: Host: foo.com
Client:
Server: HTTP/1.1 200 OK
Server: Content-Length: 50

vs.

Client: GET /?q='injection HTTP/1.1
Client: Host: foo.com
Client:
Server: HTTP/1.1 200 OK
Server: Content-Length: 0

Finally, there are out-of-band techniques which leverage channels like DNS, HTTP, etc. to exfiltrate data to a third-party server. There are usually two limitations with this approach:

A lot of the times database servers are unable to route traffic out to the Internet in hardened environments due to firewall restrictions.
Some databases don't support or disable the functionality required to initiate network traffic.

So how can we leverage boolean-/error-based SQL injections to exfiltrate data? In my hunt to find a solution, I discovered this nifty trick (disclaimer: I'm not sure if this has been discovered before but a Google search didn't seem to yield any results): one can use a combination of the SQL CAST() and NULLIF() functions to extract information.

How? Imagine you have a SQL injectable statement:

$sql = 'SELECT * FROM cities WHERE id=' + $id;

And let's say we want to guess the value of the CURRENT_USER (the database account used to login into and query the database). Then we can use the following value for $id:

CAST(NULLIF(CURRENT_USER, 'guess') AS INT)

Making the final SQL query look like this:

SELECT * FROM cities WHERE id=CAST(NULLIF(CURRENT_USER, 'guess') AS INT)

So how does this work? NULLIF(first, second) returns NULL when its first and second parameter values are equal. If they're not equal, NULLIF() returns the value of the first parameter. For example:

SELECT NULLIF('a', 'a') -- returns NULL
SELECT NULLIF('a', 'b') -- returns 'a'

CAST(<value> as <type>) will try to cast a <value> into a specific data <type>. If <value> cannot be cast into <type> then an exception is thrown. There is only one exception - NULL can be cast into any type and it will return NULL. For example:

CAST('11' AS INT) -- returns 11
CAST('sweet' AS INT) -- SQL exception raised!
CAST(NULL AS INT) -- returns NULL

By pairing the two functions together we can now use blind boolean/error-based injection to exfiltrate data, like so:

SELECT * FROM cities WHERE id=CAST(NULLIF(CURRENT_USER, 'guess') AS INT)

If our guess is equal to CURRENT_USER then NULL will be cast into an integer - but if our guess is incorrect then the value of CURRENT_USER will be used in the cast operation and this will throw an exception resulting in a page error. We can also use this technique for extracting numbers by prefixing or suffixing the value we want to extract:

SELECT * FROM cities WHERE id=CAST(NULLIF('s'||id, 's12') AS INT)

Obviously, you can use the SUBSTRING() function to guess the value of data item one character at a time, like so:

SELECT * FROM cities WHERE id=CAST(NULLIF(SUBSTRING(CURRENT_USER, 1, 1), 's') AS INT)

Et voila!

Conclusion

We've demonstrated how the combination of the CAST() and NULLIF() SQL functions can be used to extract database information blindly using an error-/boolean-based approach. This approach can be further augmented with the use of SUBSTRING() to make character-by-character guesses and accelerate the process of database exfiltration.

As I've said before, I'm not sure if this has been discussed before but a rehash wouldn't hurt anyone :). If you have any questions, feel free to drop me a line!

Menu

Conclusion