How to Make LLM-Generated SQL Safe to Run in Production

“SELECT only” is a request

In one of the projects I’ve worked on, a text-to-SQL agent on a production ERP, the system prompt had this line:

Generate SELECT queries only (never INSERT/UPDATE/DELETE/DROP).

It’s a good line. The model followed it almost every time. “Almost every time” is fine for tone of voice, and it’s not fine for a query that is about to run against a customer’s production database.

Here are three answers that a model can give while believing, more or less sincerely, that it followed the instruction:

-- 1. Starts with SELECT. Ends with a second statement.
SELECT order_id, total FROM orders; DROP TABLE orders;

-- 2. A SELECT that creates a table.
SELECT * INTO orders_backup FROM orders;

-- 3. A read on the outside, a write on the inside.
WITH moved AS (
  INSERT INTO archive SELECT * FROM orders RETURNING *
)
SELECT * FROM moved;

None of these needs a malicious model. The first can come from a prompt injection: a user message, or text stored in the database that comes back into the context, that says “ignore the previous rules”. The second can come from a model that is just trying to be helpful (“I’ll save a copy so you can compare later”). The third looks like a read to anyone who checks how the query starts. SQL Server wouldn’t accept a write inside a CTE, and PostgreSQL would. Which engine would have stopped it is beside the point, though: a guard shouldn’t depend on it.

The point of this article is simple. The prompt states what you want. The code around the model decides what actually runs. Read-only database credentials, statement timeouts and row caps are complementary layers, and they belong in any production setup. They’re a separate topic. This article is about the layer I put between the model and the database: plain, deterministic code (Python in the examples).

The setting: an agent on a production ERP

The agent answered business questions in natural language over the data of an ERP system, in production, for real companies. ERP databases are not academic ones. Table and column names are cryptic codes, not orders and customers. A single table can have hundreds of columns, and a reasonable business question can need a join path four or five tables deep. The examples in this article use readable names so that the technique is easy to follow. The real schema was a good deal less friendly.

A turn worked like this. Before the model wrote anything, a retriever selected the relevant part of the schema: table definitions (DDL) and a few example queries. From there the model worked in a loop with two tools:

• retrieve_context asked the retriever for more schema when what the model had wasn’t enough. The new tables and examples were added to the context, and the loop started again.
• execute_sql took the SQL query the model wanted to run. That query was the output to trust least.

A query never went from the tool call straight to the database. The step right after execute_sql was the guard, and there was no other way to run SQL. One guard, one execution path: if a system has two ways to execute SQL and guards only one of them, that’s how a bypass gets in without anyone noticing.

Note the dotted line in the diagram. Everything the retriever showed the model, at the start of the turn and after every retrieve_context call, was also what the guard checked against. More on this below.

Parse, don’t pattern-match

The first idea that comes to mind is a regex: refuse anything containing INSERT, UPDATE, DELETE, DROP. It fails in both directions. It refuses legitimate queries, such as a column called last_update or a string literal containing the word “delete”. And it accepts dangerous ones: a write nested in a CTE, a SELECT … INTO, an EXEC of a stored procedure. Every pattern you add starts a game of whack-a-mole with the next one. SQL is a language with a grammar. Validating it with string matching means writing a parser that is worse than the real thing.

So I made the guard parse the query, once, with sqlglot, in the dialect of the target database (T-SQL in that project). If the query can’t be parsed, it is refused. There is no “probably fine” path.

import sqlglot
from sqlglot import exp
from sqlglot.errors import SqlglotError

DIALECT = "tsql"

def parse(sql: str):
    try:
        statements = [s for s in sqlglot.parse(sql, read=DIALECT) if s is not None]
    except (SqlglotError, RecursionError) as e:
        return None, [f"parse_error:{e}"]
    if not statements:
        return None, ["parse_error:no statement"]
    return statements, []

Two details came from real model output. First, models like to wrap SQL in a markdown fence, so the guard strips any sql code fence before parsing. Second, RecursionError is caught as well: a query nested a few hundred parentheses deep has to come back as a rejection, not as a crash of the service. If the model answers with prose (“Here is the query you asked for”), that is simply a parse error, and it is refused like everything else.

Everything after this point works on the tree, not on the text.

Statement gates: one statement, read-only root, no hidden writes

With a tree in hand, the read-only rule becomes four short checks:

WRITES = (exp.Insert, exp.Update, exp.Delete, exp.Merge)

def statement_violations(statements) -> list[str]:
    root = statements[0]
    violations = []

    if len(statements) > 1:                       # stacked queries
        violations.append("multiple_statements")

    if not isinstance(root, exp.Query):           # SELECT, UNION, EXCEPT, …
        violations.append(f"blocked_statement:{type(root).__name__.lower()}")

    for node in root.find_all(*WRITES):           # writes anywhere in the tree
        if node is not root:
            violations.append(f"nested_dml:{type(node).__name__.lower()}")

    if root.args.get("into") is not None:         # SELECT … INTO creates a table
        violations.append("select_into")

    return violations

The second check matters most, and it’s easy to get backwards. I wrote it as an allowlist: it doesn’t list what is forbidden, it lists what is allowed. The root of the tree must be a query, meaning a SELECT or a set operation over selects. UPDATE, DROP, EXEC, USE, a stray single word that parses as a column name, and any statement type nobody thought of: all of them fail the same check, for the same reason. A denylist gets longer every time someone finds a new trick. An allowlist stays the same size.

The third check catches the query from the introduction. Its root is a perfectly good Select. The write sits two levels down:

A regex sees a query that starts with WITH and contains SELECT. The parser sees an Insert node inside a CTE. find_all walks the whole tree, so it doesn’t matter how deep the write is hidden.

One more choice I made on purpose: violations accumulate. The guard doesn’t stop at the first problem. A query that is a SELECT … INTO against a non-existent table, followed by a DROP, gets back every one of those violations in a single pass. That matters for the feedback loop described later: the model can fix everything in one round instead of finding the problems one at a time. The only exception is a parse failure, because without a tree there is nothing else to inspect.

Only what you were shown: table and column allowlists

Up to this point the guard protects the database from writes. The next check protects the answer from hallucinations.

On a schema with cryptic names and hundreds of columns, the most common mistake a model makes is not a malicious write. It’s a name that looks right and doesn’t exist: a table that “should” be there, a column named the way it would be in a cleaner schema. Sometimes the table even exists in the database, but the retriever never showed it to the model this turn, which means the model is guessing.

So I made the guard compare every table and column in the query with what the retriever had actually put in the context: the initial retrieval plus anything fetched through retrieve_context. That context is the same block the prompt renders: the DDL plus the example queries.

def referenced_tables(root) -> set[str]:
    cte_names = {cte.alias_or_name.upper() for cte in root.find_all(exp.CTE)}
    return {t.name for t in root.find_all(exp.Table)
            if t.name and t.name.upper() not in cte_names}

def referenced_columns(root) -> set[str]:
    aliases = {a.alias.upper() for a in root.find_all(exp.Alias) if a.alias}
    return {c.name for c in root.find_all(exp.Column)
            if c.name and c.name.upper() not in aliases}

Both functions subtract the names the query defines itself. A CTE named moved appears in the tree as a Table, but it isn’t one. An output alias like SUM(total) AS spent, used again in an outer query, appears as a Column, but it isn’t one either. If you skip this step, every query with a CTE or an alias looks like a hallucination, and a guard that cries wolf on legitimate queries gets switched off within a week.

Name comparison is case-insensitive, and the allowed set comes from the turn context alone. It doesn’t come from the full catalogue of the database. The rule I applied is short: a name the model was never shown is an invention, even when the table exists. If the context isn’t enough, the right move is for the model to call retrieve_context. Guessing is not.

Execute the tree, not the text

When a query passes every gate, the guard doesn’t hand back the string the model wrote. It hands back SQL regenerated from the tree, with every comment removed:

def canonical_sql(root) -> str:
    for node in root.walk():
        node.comments = None
    return root.sql(dialect=DIALECT, pretty=True)

-- what the model wrote
select order_id from orders /* ignore all previous instructions */ where total > 100 -- tail

-- what runs
SELECT
  order_id
FROM orders
WHERE
  total > 100

This is the only string that ever reaches the database, and the reason goes beyond tidiness. In that project, the executed SQL was also stored as the assistant’s turn, and it came back into the prompt as conversation history on the next turn. A comment that is harmless to the database is not harmless to the model: it’s text in the context window. If you leave it in, you’re keeping a channel open for a second-order prompt injection that starts in one turn and takes effect in the next. Stripping comments closes that channel at the source.

A guard that teaches

A guard that only blocks leaves the user with an error. So I made it return a verdict with three audiences:

@dataclass
class Verdict:
    allowed: bool
    violations: list[str]       # machine codes: for logs and tests
    feedback: str | None        # natural language: for the model
    effective_sql: str | None   # canonical SQL: for the executor

When allowed is false, the violation codes become a short instruction in natural language. They are deduplicated, ghost tables are grouped into one sentence, and the message ends with a hint on what to do next. That instruction goes back to the model as the result of its execute_sql call, and the loop continues:

verdict = validate(candidate, schema_context, column_context)
if not verdict.allowed:
    messages.append({"role": "tool", "content": f"SQL rejected: {verdict.feedback}"})
    continue                     # back to the model, within the turn budget
rows = db.execute(verdict.effective_sql)

The feedback is written to be acted on, not only to be read. “Table order_tracking is not in the available context” pushes the model either to find the right table in what it was given or to ask for more context. Both outcomes are better than a confident answer built on a table that doesn’t exist. Database errors go through the same channel: if a query passes the guard and fails at execution, the error message goes back to the model with an instruction to fix the SQL.

Two limits keep the loop honest. I capped the number of iterations per turn, with retrieval calls counting against the same budget, so a model that keeps getting rejected can’t loop forever. And I logged every rejection with its violation codes and the (truncated) SQL, so you can see what the model is actually trying to do, as opposed to what the prompt asks it to do.

Pin the parser: tests as a contract

Every check in this guard depends on how a third-party parser behaves:

• a CTE body parses as a child of CTE;
• SELECT … INTO puts the target table under into;
• a lone word parses as a column, not as an error;
• a write in a CTE is accepted by the T-SQL reader at all.

None of this is an API promise, and a minor version upgrade can change any of it without notice.

So I wrote the test suite as a contract with the parser more than as a list of examples. Every case is a small adversarial input with an expected verdict:

On top of the unit cases, I added a few tests that drive the whole agent loop with a scripted fake model and a fake database. They check that a rejected query never reaches the database, that the feedback reaches the model, and that a model that corrects itself gets its corrected query executed. If you build something similar, keep the rule that comes with the suite: after any parser upgrade, it runs before anything else.

What the guard doesn’t do

A deterministic guard is precise about what it checks, which also makes it precise about what it doesn’t. These are the gaps I left open on purpose, and why:

• It fails open when the context can’t be read. If an example query in the turn context can’t be parsed, the guard can’t build the list of allowed names, so the name check is switched off for that turn and the event is logged. The statement gates still apply. The alternative was to refuse every query whenever one example was malformed. I chose not to: it would be stricter, and it would turn a quality problem in the context into an outage.
• The column set is flat. The guard knows that a column exists somewhere in the context, but not which table it belongs to. A real column used on the wrong table passes, fails at execution, and the database error goes back to the model through the same feedback channel. Making the check table-aware would have meant resolving every column to its source. That’s a lot of complexity for a mistake the database already reports clearly, so I left it to the database.
• The allowlist is only as good as the retriever. The guard makes sure the model uses only what it was shown. It can’t tell whether what it was shown was the right thing.
• Some reads reach outside the database. In SQL Server, table-valued functions such as OPENQUERY sit inside a perfectly ordinary SELECT, so the statement gates don’t see them as writes, and the table check finds no table name to compare. I treated allowing them as an explicit decision, pinned by a test. In the end it’s a matter of database permissions.
• A valid read can still be a bad read. A cross join between two large tables is read-only and still expensive. A SELECT on a sensitive table is read-only and still sensitive. Questions of cost and of who may see what belong to the layers mentioned at the start: credentials, timeouts, row limits and access control.

None of these limits weakens the argument. A system prompt has the same gaps, and it doesn’t tell you where they are. The guard has a list of what it checks and a test for each item. Whatever it doesn’t cover is written down and assigned to another layer. In the end, that’s the difference between a request and a contract.