Letting Analysts Run Ad-Hoc SQL Without Handing Them the Keys

Sooner or later someone needs to ask the database a question nobody built a report for. The fastest answer is raw SQL; the fastest disaster is also raw SQL. KibiPay's ad-hoc query endpoint lets an analyst run their own SELECT against production data while making the dangerous things structurally impossible — not policed by convention, but rejected by code and blocked by the database itself.

Defense in layers

Safety here is not one check; it is four independent layers, each of which would be enough to prevent a write on its own.

Layer 1 — a query sanitizer

Every query passes through a sanitize() guard before it is allowed near the database. The rules are strict and easy to reason about:

• Single statement only. A trailing semicolon is stripped; any semicolon left inside the query is rejected. That kills stacked-statement injection like SELECT 1; DROP TABLE payments outright.
• Reads only. After stripping a leading parenthesis, the statement must begin with SELECT or WITH. Anything else — including EXPLAIN — is refused.
• No mutating keywords, anywhere. A word-boundary blocklist rejects insert, update, delete, drop, alter, create, truncate, grant, revoke, comment, copy, call, do, vacuum, reindex, refresh, merge, lock if they appear at all.
• Bounded output. If you don't supply a LIMIT, one is appended (default 1,000). If you supply one larger than the hard maximum of 10,000, it is rewritten down.

The guard is deliberately conservative: it will even reject a blocklisted word that happens to sit inside a string literal. In a safety filter, a false positive is a minor annoyance; a false negative is a breach. We chose the annoyance.

Layer 2 — a read-only transaction

On Postgres, before the sanitized SQL runs, the service issues SET TRANSACTION READ ONLY. Even if a mutating statement somehow slipped past the parser, the transaction would refuse to perform it.

Layer 3 — a statement timeout

It also sets SET LOCAL statement_timeout = '15s'. A pathological join or a cartesian mistake gets cancelled by the database rather than pinning a core and starving everyone else.

Layer 4 — a replica with no write path

The endpoint doesn't talk to the payments primary at all. It runs against the physical read replica, which is an async standby with no application write credentials. The in-code comment calls the sanitizer "belt to that suspenders" — the replica is the suspenders, and it means a write is impossible at the server, not merely disallowed at the parser.

What comes back

A successful call returns {columns, row_count, rows, sql} — and notably it echoes back the rewritten SQL, so the analyst can see the LIMIT that was applied and understand exactly what ran. Ad-hoc, yes; opaque, no.

The result is a data platform an analyst can actually use without a DBA babysitting each query, and without anyone losing sleep over what a curious SELECT might turn into. For the reporting side of the same replica, see the four regulatory reports, and for the BI layer on top, Apache Superset.

A query, walked through the guard

To make the layers concrete, follow a query through. An analyst submits SELECT scheme, count(*) FROM payments GROUP BY scheme. The guard trims it, confirms there's no embedded semicolon, checks that it begins with SELECT, scans it against the mutating-keyword blocklist (nothing matches), notices it has no LIMIT, and appends the default cap. The rewritten SQL — now ending in LIMIT 1000 — is what runs, inside a transaction that has already been told SET TRANSACTION READ ONLY with a fifteen-second statement timeout, against the read replica. The response echoes that rewritten SQL back so the analyst can see exactly the cap that was applied. Now imagine a hostile variant: SELECT 1; DROP TABLE payments. The guard sees the interior semicolon and rejects it as multiple statements before a single byte reaches the database. Even a cleverer single-statement attempt that smuggled a mutating keyword would hit the blocklist, and even if something impossible slipped through all of that, the read-only transaction on a write-less replica would refuse it. Four independent layers, and an attacker has to beat all four — a bar so high that the practical worst case of a hostile query is not a breach but a wasted fifteen seconds before the statement timeout cancels it.