HTB Cyber Apocalypse 2021 — emoji voting
(this writeup also serves as an introduction to blind SQL injection, those who want to skip to the solution can do so here)
This was a 2-star challenge challenge in the web category of the Cyber Apocalypse 2021 CTF.
The challenge appears as a website used to vote for emojis:
Inspecting the challenge source code in database.js tells you where the flag is hidden:
The same file also reveals the use of a non-parameterized query, and thus a vector for SQL injection:
If you were to open your browser’s developer console while at the voting page, you will see repeated calls in the background to /api/list:
As some other participants have observed, you can use sqlmap to solve this challenge (endpoint /api/list), but you came here to learn more about how this works under the hood, right? ;)
This challenge is different from conventional SQL injection challenges in two main ways:
- You will not be able to return the values of anything else outside of the
emojistable - “Traditional” injection techniques such as the use of
UNION,ORetc. will result in a syntax error because the injected input is placed after theORDER BYclause
Therefore the only attacker-controllable output from any injected query is the ordering of the emoji dataset.
For those who are new to SQL injection, the solution to this challenge comes in three parts:
- Obtaining the name of the flag table
- Obtaining the flag itself from the flag table
- Dynamically modify the ordering of the result set
Details
(those who have done this before may want to skip to the solution)
If you are new to databases in general, you may already know of the SHOW TABLES command mentioned in many tutorials (the SQLite-equivalent is .tables). The command won’t work in this case, though, so we determine the schema by querying the built-in schema table. For those familiar with other popular RDBMS, you will know of this as the INFORMATION_SCHEMA table.
Once you obtain the name of the flag table, you will be able to SELECT the flag value itself.
There’s still one problem, however: we cannot actually return values of anything outside the emoji table, so how do we determine the flag table’s name? The solution to that comes in two parts.
First, we need to look into dynamically modifying the ordering of the result set. For this we turn to the SQL CASE statement:
(CASE WHEN expression THEN <value if true> ELSE <value if false> END)The magic in CASE lies in the ability to perform another SELECT query within expression. By using certain SELECT queries that can be later manipulated to return truthy or falsey values, we will be able to gain information about the contents of the database.
Example:
SELECT * FROM emojis ORDER BY (CASE WHEN 1=1 THEN id ELSE name END) DESC
…and if false:
For this challenge, I decided to order different columns depending on the truth value of the expression (id if true, name if false). Depending on your preference, you can choose different columns, or even change the query to return results in ascending order instead if the expression is false.
Now that we have a way of determining the truth values of expressions, we look at retrieving information from the database. The goal here is to determine the string values of the flag, so the HEX and SUBSTR commands came in handy now:
SELECT * FROM emojis ORDER BY (CASE WHEN (SELECT HEX(SUBSTR(name, 1, 1)) FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND name NOT LIKE 'emojis')='66' THEN id ELSE name END) DESCLet’s breakdown this query:
SELECT … FROM sqlite_master WHERE type='table' and name NOT LIKE 'sqlite_%' and name NOT LIKE 'emojis'—query the schema table and return details about the unknown table¹HEX(SUBSTR(name, 1, 1))— return the hex value² of the first character of the name column='66'— check if hex value is equal to 66 (ASCII lowercase F)
The expression would return true (and return your emojis in descending ID). To execute this attack, we turn to automation.
Solution
For readability and ease of debugging, I wrote one script for each part of the challenge. The script makes requests to /api/list, enumerating against all possible characters and checking if the first emoji object returned in the JSON output has an ID of 12 (expression returns true):
The table name will differ as it is randomized upon container launch. Flag: CHTB{order_me_this_juicy_info}
Notes
- In this case we already know that the name of the flag table starts with
flag_, but this technique works for all cases even when you don’t know the table name at all. - HEX() returns uppercase characters, so be sure to match accordingly — after fiddling with so many lowercase hex-encoded values in the crypto challenges, I made a mistake of assuming lowercase and that resulted in strange guesses.