Public SQL and Query Surfaces

The Risk

A gate that reads the raw request is defeated by one encoded character. If your filter looks for a keyword and the caller sends it with a single letter percent-encoded, the text no longer matches and the payload walks through to the engine, which decodes it happily. An encoded slash turns a path your gate does not match into a path your router does route. A plus sign that your gate treats as a plus and the backend treats as a space splits one token into two. And a stray control character inside a query is either a broken client or a probe, and neither should reach an executor.

The Solution

Normalise before you match, and match on both the raw and the decoded form. Decode percent-encoding, then decode again (double encoding is the standard evasion), treat plus as space the way the backend will, and turn an encoded slash into a real one so the gate sees the same path the router sees. Decode per character so that one malformed escape cannot switch decoding off for the whole string. Refuse control characters outright. Then test the gate with a fixture that runs the REAL matching function extracted from the deployed code, with cases that must hit and cases that must miss, because a hand-copied fixture drifts away from what runs and stays green while doing it.

The Fix

def normalise(s):
    out = s.replace("+", " ")
    for _ in range(2):                        # double-encoded is the common evasion
        out = decode_percent_per_char(out)    # a bad %ZZ leaves that triplet alone, decodes the rest
    return out

def gate(path, query):
    p = normalise(path); q = normalise(query)
    if has_control_chars(q): return refuse()
    for form in (query, q):                   # raw AND decoded
        if hostile_pattern.search(form): return refuse()
    return allow()

# Fixture: extract the real gate() from the deployed source and run it against
#   MUST_HIT  = ["read%5Fcsv(", "/v1%2Fquery/", "SELECT%0A..."]
#   MUST_MISS = [ordinary queries taken from live, successful traffic]
# Prove the fixture can fail: remove one normalisation line and watch a MUST_HIT go green.

The Risk

Analytical engines can fetch a URL from inside a query. Every filter you write for that is a prediction about what a hostile query looks like, and a prediction has gaps: a function you did not list, an encoding you did not decode, a feature the next engine version adds. If the process that executes caller-supplied SQL can reach the internet, one missed prediction is a server-side request to wherever the caller chooses, including the cloud metadata address that hands out credentials.

The Solution

Remove the capability instead of predicting the payload. The container that executes caller SQL gets no outbound network at all: a firewall rule keyed on that container refuses every destination except its own database and the internal log relay. Reject the cloud metadata addresses for EVERY container by destination, because that rule cannot go stale when containers move. Keep the SQL filter as well; egress denial is the layer that holds when the filter misses. The container will still try to resolve external names, fail slowly, and hold a worker for its timeout, so bound that timeout too.

The Fix

# Host firewall (Docker example). Pin to the executor container's address.
iptables -I DOCKER-USER -s <executor-ip> -d 172.16.0.0/12 -j RETURN   # its own db + log relay
iptables -I DOCKER-USER -s <executor-ip> -j REJECT                    # everything else
# Metadata endpoints, for EVERY container, by destination (cannot go stale):
iptables -I DOCKER-USER -d 169.254.169.254 -j REJECT

# Verify from INSIDE the container, never by reading the rule:
docker exec <executor> curl -m 5 https://1.1.1.1/        # must fail
docker exec <executor> psql -h <own-db> -c 'select 1'     # must work

The Risk

A public SQL endpoint refuses things all day, and every refusal teaches the caller something. If the message names the token you matched, a prober learns your list one word at a time. If the edge says one thing and the backend says another, they learn which layer caught them and what is worth encoding past. If your function list is a blocklist, every function you did not think of is allowed, and engines add functions every release.

The Solution

Sort refusals into three kinds and give each one fixed wording. Hostile (a fetch, an attach, an engine setting, a write on a read-only surface): refuse with one short generic sentence and nothing else, byte-identical at every layer, so nothing about the match leaks. Capability (a function or feature you simply do not permit): refuse and name the CATEGORY, never the matched token, because an analyst deserves to know why. Limit (rows, response size, time): refuse and name the limit and the value, because that is the one the caller can act on. For functions, keep a positive list of what is allowed and refuse everything else by default; a new function is refused until you admit it, which catches the ones nobody predicted. Keep the lists in one file under version control and compile both the edge and the backend from it, then check the deployed copies against it on a schedule, because two hand-typed copies drift in the first week.

The Fix

# One source file, two consumers:
bands.json      -> compiled into the edge worker at deploy; loaded by the backend at start
messages:
  A hostile     "Request refused."                     # nothing else, ever; identical everywhere
  B capability  "That feature is not available here."  # names the category, never the token
  C limit       "Row limit is 1000 - add LIMIT."       # the value the caller can act on

ALLOWED_FUNCS = {"count", "sum", "avg", "min", "max", "date_trunc", ...}   # positive list
for fn in functions_in(parsed_sql):
    if fn not in ALLOWED_FUNCS: return refuse_B("function not available")

# Drift check (scheduled): fetch the DEPLOYED edge source and the loaded backend list,
# assert every pattern in bands.json is present in both, page on mismatch.