Parse the generated statement before the database ever sees it

An analyst who writes a query by hand works inside a safety net that nobody designed. They run the statement, watch the row count climbing in the console, and cancel it once the answer is plainly larger than the question deserved. Nothing in the database performed that check; a person did, using knowledge of what the question meant. A system that lets a language model write the statement and then executes it keeps the statement and loses the person, so every constraint that person applied by reflex now has to exist as code between the generated string and the connection.

The code that replaces the reflex is a validator: a function handed the statement a model produced, which either returns a statement to execute or refuses with a reason. Everything it can decide is structural — how many statements, whether any writes, which objects they name, how many rows they may return, how long they may run. Nothing it can decide is semantic, and every rule below is silent on whether the number that comes back answers the question asked. A team that has watched a validator stay green for a month starts reading green as the answer is trustworthy, and it never meant that.

String checks and what they measure#

The first version of that code is always a string check, and it is worth writing out why it fails, because the failure is not obvious from reading it.

# every one of these defeats the check below
CASES = [
    "/* report */ SELECT 1; DROP TABLE analytics.fct_orders",
    "\n\n  select * from fct_orders",
    "WITH x AS (SELECT 1) SELECT * FROM x",
    "WITH moved AS (DELETE FROM staging.tmp RETURNING *) SELECT * FROM moved",
    "SELECT * FROM fct_orders INTO OUTFILE '/tmp/x.csv'",
]

def looks_safe(sql: str) -> bool:          # do not ship this
    s = sql.strip().lower()
    return s.startswith("select") and "drop" not in s and "delete" not in s

A string check can lose in two ways, and the second does the real damage: nobody keeps a check that blocks legitimate work, so they loosen it, and the loosenings are where the wrongly accepted statements come from. Take the last case first, since it needs no history. SELECT * FROM fct_orders INTO OUTFILE '/tmp/x.csv' begins with select once stripped and lowercased and contains neither banned word, so looks_safe returns true and the statement writes a file onto a disk the database server owns. The function answered the question it was asked — six characters at the front, two substrings anywhere — and that question has no bearing on whether the statement writes. The second case passes differently: .strip() removes the leading newline, so what is left is a genuine read with no bound on how much it returns, a shape that takes a service down without touching a row and one a check built around writing has no word to catch.

The third case is refused, and it is a read. WITH introduces a common table expression — a named subquery, written once at the top and referred to by name in the query below it — and naming intermediate steps that way is the normal form for anything an analyst would call readable. Refusing it means refusing a large share of the queries the system exists to run, so whoever maintains this adds or s.startswith("with") before the end of the week.

That repair is what admits the fourth case. WITH moved AS (DELETE FROM staging.tmp RETURNING *) SELECT * FROM moved is a data-modifying CTE, meaning a common table expression, the named subquery a WITH introduces, which PostgreSQL permits to delete rows and return them. Its opening keyword and top-level shape are identical to the third case, even though one reads and one deletes, and the only thing separating them is a node buried inside the parentheses that no test looking at how a string begins can reach. The substring bans are all that now stands in front of a check whose entire purpose was to block deletes, and they are under pressure of their own: they also reject any query mentioning a column named dropped_at or deleted_flag, so they get loosened to word boundaries. Word boundaries still catch the bare DELETE here, which is why the loosening does not stop — the next false rejection has the word inside a string literal or a table name, and repairing that one means asking where in the statement it appears. Answering that requires the statement's structure, and now the check is a small parser written by someone who was not trying to write a parser.

The leading comment defeats the prefix test, and shows how much harder that parser is. /* report */ SELECT 1; DROP TABLE analytics.fct_orders is refused because the stripped string starts with a slash, which has nothing to do with the DROP TABLE on the end. Strip comments first and you must decide whether a /* inside a quoted string opens one, which it does not.

What the five share recurs at every layer of this problem: the quantity the check measures stays constant while the property it stands in for varies freely. The measured quantity is a rendering — some characters at the front of a string, plus two words present or absent anywhere in it. The property that matters is what the statement does to a database: write rows, write a file, return an unbounded result. A statement can hold the rendering fixed and move the behaviour anywhere, because a rendering is one of many ways of writing a structure down and the database acts on the structure. startswith("select") feels like a claim about what a query does; it is a claim about how it was typed.

Parsing the statement into a tree#

Parsing runs that map backwards. A parser reads a rendering and produces the structure behind it, discarding exactly what defeated the string check: whitespace, comments, letter case, quoting. Two statements differing only in those respects parse to one tree, so a rule written to interrogate the tree is invariant under every transformation those repairs were chasing.

The alternative is to use a parser that already exists, walk the resulting tree — the abstract syntax tree, or AST, which is the structured form a parser produces from a statement's text, with a typed node for each clause, table reference and function call — and allow-list what may appear in it. sqlglot parses per-dialect and gives typed nodes, so the rules read as claims about structure.

import sqlglot
from sqlglot import exp

FORBIDDEN = (
    exp.Insert, exp.Update, exp.Delete, exp.Merge, exp.Create, exp.Drop,
    exp.Alter, exp.TruncateTable, exp.Grant, exp.Copy, exp.Command,
)
ALLOWED_SCHEMAS = {"analytics", "analytics_marts"}
MAX_ROWS = 10_000

class Rejected(Exception): ...

def validate(sql: str, dialect: str = "snowflake") -> str:
    statements = sqlglot.parse(sql, dialect=dialect)
    if len(statements) != 1:
        raise Rejected(f"expected 1 statement, got {len(statements)}")

    tree = statements[0]
    if tree is None or not isinstance(tree, (exp.Select, exp.Union, exp.With)):
        raise Rejected("top level must be a SELECT")

    for node in tree.walk():
        if isinstance(node, FORBIDDEN):
            raise Rejected(f"forbidden node: {type(node).__name__}")
        if isinstance(node, exp.Into):
            raise Rejected("SELECT ... INTO is not permitted")

    ctes = {c.alias_or_name.lower() for c in tree.find_all(exp.CTE)}
    for t in tree.find_all(exp.Table):
        if t.name.lower() in ctes:
            continue
        schema = (t.db or "").lower()
        if schema not in ALLOWED_SCHEMAS:
            raise Rejected(f"table {t.sql()} is outside the permitted schemas")

    limit = tree.args.get("limit")
    if limit is None:
        tree = tree.limit(MAX_ROWS)
    elif int(limit.expression.name) > MAX_ROWS:
        raise Rejected(f"LIMIT above {MAX_ROWS}")

    return tree.sql(dialect=dialect)

The count is checked for equality rather than a maximum: a semicolon is one character converting a request to read into a request to read and then do something else. The root must then be a Select, a Union or a With — one query, several combined, or a query preceded by named subqueries.

The forbidden node types are the two families of statement that write: DML, the data manipulation language of INSERT, UPDATE, DELETE and MERGE, which changes rows; and DDL, which changes the shape of the database itself with CREATE, DROP, ALTER and TRUNCATE. A statement that reads contains neither, at any depth.

At any depth is what the string check could not express and tree.walk() can: the fourth case is a With at the root with a Delete several levels down, so a root-type test accepts it while a walk meets the Delete wherever it sits.

The function returns SQL regenerated from the tree rather than the model's original string, which closes a gap that catches people out. If you validate one string and execute another — even a string that differs only by whitespace or by an appended LIMIT concatenated on the end — you have validated something that is not what runs. Serialising from the parsed tree means the thing you inspected and the thing you execute are the same object, which matters here because tree.limit(MAX_ROWS) produces a statement the model never wrote.

The schema restriction earns its place separately from the node checks. A query reaching into raw.stripe_events or ops.pii_exports is structurally a perfectly good SELECT, and the only thing that distinguishes it is where it points.

That restriction carries a design decision, not a requirement. (t.db or "") yields an empty string for a table written without a schema, which is not in ALLOWED_SCHEMAS, so the second of the five cases is refused despite being a harmless read. Safety does not require a model to qualify table names; this check does, because it must decide at validation time which object a name refers to, and an unqualified name is resolved later against a search path it cannot see.

The exemption above it is where the function is weakest — the previous section's stumble one level up. ctes collects every CTE alias anywhere in the tree, and any table whose bare name appears in that set skips the schema check. Something like it is necessary, since a CTE alias is not a table and has no schema. What makes it weak is that membership is tested by name alone, against a set gathered from the whole statement rather than the scope a reference sits in, so a statement opening a CTE aliased with the same bare name as a real table in a forbidden schema hands that table a free pass — t.name for raw.stripe_events is stripe_events. The measured quantity, a bare name in a tree-wide set, holds still while the dangerous property moves underneath it.

The table below is best read across the gaps between rows, not down the verdict column.

A gold query, in the second row, is a hand-written reference query for a known question, the standard a generated one is measured against. The first two rows are both accepted and open with different keywords: acceptance has stopped being a fact about how a statement begins. The second and fourth are the important comparison, since both are WITH statements and only one survives, and the gap between them is a single node at depth — the whole justification for walking a tree rather than typing its root. The third and fifth are refusals from different layers, one about how many statements there are and one about where a statement points. The gap between Reject and Rewrite in the last row is a policy choice: refusing sends the question back to the asker, injecting a bound answers it with a truncated result, at a price paid later and by somebody else.

Allow-listing shapes and deny-listing functions#

The node checks above are written as a deny-list of forbidden types, which reads naturally and is the weaker of the two arrangements. A deny-list is a bet that you have enumerated everything dangerous, and every parser upgrade that adds support for a new construct is a new entry you did not write. Inverting it — accept Select, Union, With, Join, Where, Group, Having, Order, Limit, Window, Case, Cast, Column, Literal, Table, Alias, Subquery and a named set of functions, reject anything else — means that a construct the parser learns about next year arrives as an unknown node type and is refused rather than admitted.

The difference concerns only the constructs on neither list. Anything nobody considered is absent from both, and under a deny-list absent means permitted where under an allow-list it means refused.

The allow-list has a cost that is worth stating, because it is the reason people do not use it: it rejects legitimate queries the day you turn it on. A QUALIFY clause, a LATERAL FLATTEN, a PIVOT, a MATCH_RECOGNIZE — all read-only, all perfectly reasonable, all unknown to a list assembled from the first fifty gold queries. The practical route is to run both for a fortnight, log every statement that the allow-list would reject and the deny-list accepted, and add the constructs that show up in queries an analyst confirms are fine. Across 300 evaluation questions and two weeks of pilot traffic that produced nine additions, after which the list stopped growing.

That last clause is the premise the arrangement rests on: an allow-list is workable only if the set of constructs a real workload uses is small and closes quickly. Nine additions and then nothing is evidence that it does here, no evidence that nine is the number elsewhere, and a fortnight cannot see an annual report.

Functions need their own treatment in either arrangement, since they are where a read-only statement acquires side effects. PostgreSQL's pg_read_file, pg_sleep, lo_import, dblink and pg_ls_dir are all callable from inside a SELECT. Snowflake's SYSTEM$ family and external functions reach outside the warehouse. The list of functions the analytical queries actually use is short — aggregates, date arithmetic, string manipulation, COALESCE, CASE-adjacent helpers, a few window functions — so an allow-list here is roughly forty names and covers everything the gold set uses.

They are also where the argument from node types runs out. Every other construct is a shape, and a shape either writes or it does not; a function call is one node type carrying a name, and COALESCE computes where pg_read_file opens a file on the server. The check descends from the shape to the name — matching on a name again, except that this one is read out of a parsed node.

Asking the planner for an estimate#

Structural limits do not bound work. A query that joins two fact tables on a non-selective predicate, with a LIMIT 100 on the outside, is structurally impeccable and will scan every row of both. The planner already knows this, and asking it is a cheap pre-flight. It has no choice: choosing between plans means estimating what each costs, which means estimating how many rows each step produces. EXPLAIN asks to be shown those estimates and then to stop.

def preflight(stmt: str, params: dict, max_bytes: int = 200 * 2**30) -> None:
    with POOL.connection() as conn, conn.cursor() as cur:
        cur.execute(f"EXPLAIN (FORMAT JSON) {stmt}", params)
        plan = cur.fetchone()[0][0]["Plan"]
    est_rows = plan["Plan Rows"]
    est_bytes = est_rows * plan["Plan Width"]
    if est_bytes > max_bytes:
        raise Rejected(
            f"planner estimates {est_bytes / 2**30:.0f} GiB scanned; "
            f"add a date filter or narrow the question"
        )

EXPLAIN without ANALYZE does not execute anything and costs a few milliseconds. The arithmetic in the middle is where a units mistake would live and does not: Plan Rows is a count of rows and Plan Width the average size of one row in bytes, so their product is bytes, and max_bytes defaults to 200 * 2**30 where 2**30 is a gibibyte in bytes. The message divides by that same constant before printing GiB, so both sides stay in one unit.

One word in that block names a quantity the code does not compute. plan is the root of the plan tree, so Plan Rows there is the number of rows the query returns, and multiplying by Plan Width gives the estimated size of the result set. The refusal text calls that volume scanned, a different measurement: a query reading six years of orders and returning one aggregated row scans an enormous amount and returns almost nothing, and this gate would wave it through. Read as a bound on the result it is the right instrument for the failure it prevents, a client asked to hold more rows than it has memory for. Read as a bound on work it is the wrong one, and it has never given a visibly wrong verdict only because the two quantities move together on the statements that trip it.

The estimate is often wrong by an order of magnitude, which is fine here: the threshold is set high enough that only the genuinely unbounded queries trip it, and on this system it fires on about 0.9% of statements, almost all of them missing a date predicate on a table with six years of history. The message it produces is actionable in a way that a 30-second timeout is not, because it names the cause.

Tolerating that error is a claim about the traffic rather than about the estimator. It holds when the gap between an ordinary query and an unbounded one exceeds an order of magnitude, so ten-fold noise cannot carry a statement across the line, and the firing rate is the evidence: fewer than one statement in a hundred reaches a threshold that far out.

Warehouses with per-query cost make this a budget question rather than a latency one. On a platform billing by bytes scanned, a dry-run estimate converts directly to money, and the sensible rule is to refuse anything above a per-question ceiling — say $0.50 — and to report the estimate in the refusal so the asker knows they asked for something expensive rather than something forbidden.

That is a different measurement from the one in the code above: a dry run reports the bytes the engine will read, the root-node product the bytes the client will receive. An aggregate over the whole history is expensive to run and tiny to return, and a wide select over a short date range is the reverse, so a system eventually has to ask both.

Placeholders and the values that come from outside#

Text-to-SQL inverts the usual injection story, since the model is authoring the statement rather than interpolating a user string into a template. That does not remove the need for parameter binding; it changes which values need it. Literals that originate in the user's message or in retrieved documents — a customer name, an order reference someone pasted, a ticket ID — arrive as text from outside and end up inside quotes in a generated statement.

Interpolation is the mechanism. When a value is copied into the text of a statement it becomes part of the statement, subject to the grammar around it, so a customer reference containing a quote character closes its string literal early and whatever follows is read as SQL. Binding removes the copy: the placeholder is part of the structure, the value travels beside the statement, and the engine substitutes it once parsing has finished. A value arriving after parsing cannot change what was parsed.

The arrangement that holds is to have the model emit placeholders and a separate value map, then bind at execution:

# model output
{
  "sql": "SELECT COUNT(*) FROM analytics.fct_orders "
         "WHERE customer_id = :customer_id AND order_date >= :since",
  "params": {"customer_id": "CUS-40118", "since": "2025-07-01"}
}

stmt = validate(model_out["sql"])
with conn.cursor() as cur:
    cur.execute(stmt, model_out["params"])

Binding also makes the validator's job easier, because a tree containing :customer_id has a smaller surface than one containing an arbitrary quoted string, and it makes the query log readable: every execution of the same question shape carries the same normalised SQL text, which is what lets you group and count them later.

Rows and seconds on the connection#

The connection is part of the control. Rows and time are bounded there rather than in the statement, because a statement-level LIMIT does not bound the work the database does before the limit applies. ORDER BY order_total DESC LIMIT 10 over 2.4 million rows still sorts 2.4 million rows.

The reason lies in the order the engine works. To know which ten rows carry the largest totals it must compare every candidate against the others, and there is no way to do that without examining the set. LIMIT describes the final step, and by then nothing expensive is left to prevent. A bound that stops work in progress must sit where it can watch it, and there are two such places: a clock the engine consults as it runs, and the client's willingness to keep asking for rows.

POOL = ConnectionPool(
    dsn=BOT_DSN,                    # a dedicated role, see the next chapter
    min_size=1, max_size=4,         # this service cannot monopolise the warehouse
    kwargs={"options": "-c statement_timeout=30000 -c default_transaction_read_only=on"},
)

def run(stmt: str, params: dict) -> list[tuple]:
    with POOL.connection() as conn, conn.cursor(name="bot") as cur:
        cur.itersize = 1000
        cur.execute(stmt, params)
        rows = cur.fetchmany(MAX_ROWS + 1)
    if len(rows) > MAX_ROWS:
        raise Rejected("result exceeded row cap")
    return rows

A 30-second statement timeout, a pool of four so that a burst of Slack questions cannot starve the scheduled ETL, a named server-side cursor so rows stream in blocks of 1,000, and a fetch of one row beyond the cap so that hitting the cap is detectable rather than silent. On this system the p50 query is 240ms and the p99 is 4.1 seconds, so a 30-second timeout fires on roughly 0.3% of questions, which is the right order: it should be a rare event that means something, not a routine ceiling half the analytical questions bump into.

The extra row earns its place: without it, a result of exactly MAX_ROWS rows and one cut off at MAX_ROWS rows are the same observation. The timeout carries an implied unit, which is where a factor-of-a-thousand mistake lives: statement_timeout=30000 is a bare integer, PostgreSQL reads a bare integer as milliseconds, and thirty thousand milliseconds is the thirty seconds the prose claims.

The three latency figures also agree, and confirming that is a minute well spent. A p99 of 4.1 seconds says one query in a hundred runs longer than 4.1 seconds; the timeout fires on roughly three in a thousand, a smaller share, so the thirty-second line sits further out in the tail than the 99th percentile. Were it firing more often than a p99 of 4.1 seconds allows, one of the two figures would have been measured over a different population. A residual survives the agreement: a query killed at thirty seconds never records a duration, so the percentiles are computed over survivors, and the measured tail is the shortest the real one could be.

Rejections and the reasons they carry#

A validator that only refuses is half a component. Every rejection carries a reason specific enough to be useful in two directions. Fed back to the generator as a single repair attempt — under the same bound as an execution error — the message "table raw.stripe_events is outside the permitted schemas" produces a corrected query about 60% of the time, usually against the mart view that covers the same data.

Aggregated, the rejection reasons are a description of what is wrong with the surrounding system rather than with any one question. Over the pilot, rejections broke down as 41% missing or excessive LIMIT, which is a prompt problem and was fixed by putting the row cap in the system instructions; 28% out-of-schema references, which is a retrieval problem, since the model was being shown table names it had no grant for; 19% unknown constructs during the fortnight of allow-list tuning; 8% multiple statements, almost all of them the model helpfully appending a second query to show its working; and 4% everything else. Two of those categories were fixed upstream in an afternoon each, which is the return on emitting reasons rather than a boolean.

That column sums to a hundred with nothing left over, and the exactness carries information: every rejected statement was counted once, under one reason, so the validator stops at the first rule that fires rather than at every rule a statement broke. That makes the order of the checks part of the measurement. The schema loop runs before the limit check, so a statement that both pointed outside the permitted schemas and carried no bound is counted as out-of-schema and never reaches the LIMIT bucket. The 41% therefore counts statements whose only defect was the bound, and reordering the two checks would move traffic between the buckets without a statement changing. The two fixed upstream were together just under seven rejections in ten. The 19% is the category meant to disappear, since it measures the fortnight of tuning above.

The counters also give you a regression alarm that costs nothing. A model version change that pushes the rejection rate from 3% to 11% overnight is visible before anyone notices a quality difference in the answers, because the validator sees every statement and the users see only the ones that survive. Those two sets of percentages have different denominators: the breakdown divides rejections into reasons and sums to a hundred, while the 3% and the 11% are shares of all statements. What the jump does to the breakdown is the more useful half of the signal, since one category swelling names the thing that changed.

Where an injected bound produces a wrong total#

Injecting a LIMIT into a query the caller then aggregates is a way to produce a wrong number using only correct components. A grouped query with 12,000 groups, truncated to 10,000 rows, summed client-side to produce a total, yields a total that is 17% low and carries no indication that anything was dropped.

The arithmetic behind that figure holds under a condition that is easy to walk past. Twelve thousand groups cut to ten thousand loses two thousand, and two thousand out of twelve thousand is one in six, a little under seventeen in a hundred. The shortfall in the total equals the share of groups dropped only when the dropped groups carried an average share of that total, so 17% is the equal-weight case and nothing more. Order by total descending and the groups falling off the end are the smallest, so the shortfall is far under seventeen in a hundred and the answer looks nearly right, which is worse; order it ascending and the largest groups go. Leave the order unspecified — which is what an injected bound produces, since the rewrite adds a LIMIT and never an ORDER BY — and the engine may return any ten thousand of the twelve thousand, so the shortfall is unpredictable in direction and need not repeat.

The fetch of MAX_ROWS + 1 above exists for this: when the result is exactly at the cap, the system must treat the result as truncated, and either refuse to aggregate over it or say so in the answer. Truncation is a fact about the result and belongs to the checks that read results.

Two further limits. The row cap, the timeout and the read-only default are properties of one connection pool, so anything reaching the warehouse by another route is outside all of them — a job somebody schedules six months later, a second service handed the same credentials. And every gate here runs before execution, against structure and estimates, so a statement impeccable in shape can still return something nobody expected.

That distinction is the boundary of everything in this chapter. What has been established is what the statement may do to the database: one read, inside two schemas, bounded in rows and seconds, with no path to write, to the filesystem, or to a second statement. Nothing here has any opinion about whether the number coming back means what the question asked. A statement that joins the right tables, filters the right dates and returns one wrong figure passes every rule above, in 190ms, and the validator has behaved exactly as designed. Every figure here is a rate at which statements were stopped and none is a rate at which answers were right, so a dashboard showing a refusal rate of 3% and a timeout rate of 0.3% would look identical on a system whose answers were wrong every time.

The other limit is where this code runs. validate is a Python function in the same process as the model call, importing a third-party parser, maintained by the same team that maintains the prompt. It has the properties of application code, which include the property that a gap in it is silent. The CTE exemption above is one such gap: a statement reaching a forbidden schema through a matching alias is accepted, executed, and logged as an ordinary read, and nothing downstream can tell. That is what makes a check of this kind the wrong last line of defence.