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!
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.
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.
Some characters are forbidden as they trigger the
nice try skid message. These are ` ` (
%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
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
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.
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.
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.
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.
We can use this trick to dump the database version (which appears to be
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?
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:
This works because the column names of the table derived from the subselect are the values of the leftmost
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:
And the flag is
After the CTF end a member of Dragon Sector posted this on IRC:
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.
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 :)