Safely Letting Users Run SQL Against Your Analytics

The feature and its problem

A SQL editor is the most powerful thing you can give an analyst: no ticket, no waiting, just the question they actually have. It is also arbitrary code execution against your data, which is why most tools do not offer it.

The problem is tractable, but not with the obvious approach.

Why blocklists fail

The first instinct is a regex that rejects dangerous words:

if (/\b(DROP|DELETE|UPDATE|INSERT)\b/i.test(sql)) reject();

This does not work. DROP/**/TABLE passes. So does a WITH clause hiding a write, or a nested statement, or any of a dozen encodings. Blocklists enumerate badness, and badness is not enumerable.

Parse and allowlist instead

Parse the SQL into an AST and accept only what you recognise:

• Statement type must be SELECT. Anything else is rejected before execution.
• One statement only — no semicolon-separated batches.
• Every referenced table must be on an allowlist.
• No ATTACH, no file functions, no system tables.

The difference is decisive: a blocklist fails open on anything you did not anticipate. An allowlist fails closed.

Scoping to one site

Allowlisting tables is not enough on a multi-tenant deployment — a valid SELECT against a permitted table can still read another customer's rows.

The strongest approach is to never expose the raw tables. Give each session views already filtered to their site, and allowlist the views rather than the tables underneath.

If you must expose tables, inject the site predicate into the parsed AST rather than appending text to the query string. String concatenation is defeated by a trailing comment.

Resource limits

A syntactically valid query can still take down the instance:

• Statement timeout — kill anything past a few seconds, with a clamped maximum a client cannot raise.
• Row cap — LIMIT applied by the server, not trusted from the query.
• Result size cap — bytes, not just rows; a hundred wide rows can exceed a thousand narrow ones.

Auditing

Log every query with the user, the site, the duration, the row count, and the outcome. Two reasons: you will need it after an incident, and the slow queries tell you which reports people actually want as first-class features.

The read-only property

The single most valuable guarantee is that this path cannot write. In an architecture where writes go to PostgreSQL and reads go to an analytics store, the editor only ever touches the read side. Even a hypothetical bypass reaches a replica, not the source of truth.