Production debugging is one of the two tasks I submitted for the SigSegv1 CTF. Though the challenge was beta-tested (and judged to be of sufficient quality by the testers), it apparently elicited significant backlash among the players of the CTF.
It ended up being flagged only once, by train whose writeup is available in French here. Although the writeup is indeed correct, it sort of lacks a deeper understanding of the issue and why it can arise. This note aims at providing a bit more context to the problem.
Please note that I will not go into all the details of database isolation, as plenty of documentation is available already.
For those of our readers who didn't participate in the CTF, the sources of the challenge are available here.
TL;DR of the writeup
Solving the challenge requires exploiting a race condition between a
signUp transaction that is rolled back and a
signIn transaction that is properly executed. This is not a possibility in a database with usual default settings, so the challenge requires setting a MySQL session variable of the challenger's choosing (some are filtered, obviously) to the right value, which in this case should be transaction_isolation.
The 4 pillars of database transactions
Databases generally need to be resilient to errors (i.e. the state of the database should not be altered by a transaction should it be interrupted midway by a power failure). To provide that resilience, we ensure that transactions have a set of properties: Atomicity, Consistency, Isolation, and Durability (ACID).
- Atomicity guarantees that a transaction must be executed fully or not at all, like a single unit;
- Consistency ensures that transactions map a valid state to another valid state (i.e. transactions are valid only if they respect all the constraints specified in the db);
- Isolation ensures that two concurrent transactions produce the same result as if they were executed sequentially;
- Durability guarantees that committed transactions are preserved in the case of a system failure.
While it is usually desirable to preserve all these properties, the isolation property is sometimes relaxed to reduce the locking overhead, as it may be a performance hit. In that spirit, 4 different isolation levels are defined in the SQL standard (from highest to lowest, using a lock-based concurrency control implementation):
- Serializable, which basically guarantees the isolation property in any possible case, releasing read and write locks at the end of a transaction and requiring range-locks when needed;
- Repeatable reads, which doesn't deal with range-locks and can therefore not prevent phantom reads (out of scope);
- Read committed, which keeps write locks until the end of the transaction, but not read locks, and can therefore not prevent non-repeatable reads (out of scope);
- Read uncommitted, which is the lowest isolation level and allows dirty reads.
A dirty read is essentially the way to get the flag. It occurs when a transaction reads uncommitted changes made by one or more other transactions, and these changes end up being rolled back. Here follows an example from Wikipedia:
In the end, transaction 1 has a wrong view of the data, as no row exists with an
id of 1 and an
age of 21 in table
Applied to "Production debugging"
As I said earlier, applying this principle to my Production debugging challenge simply required finding the right variable name to change the transaction isolation in MySQL (I want to stress that this challenge would have still worked had I used another DBMS, albeit probably with a different variable name).
In the end, you end up logged in as
admin on a system whose database is completely empty. Isn't that a bit poetic?
I would like to leave you with this thought: "Sometimes, to find the flag, you have to drop ACID!" ;-)