Adding Read-Only Guardrails to LLM-Generated SQL with sqlglot

Title

Adding Read-Only Guardrails to LLM-Generated SQL with sqlglot

Meta Description

Use sqlglot to parse the SQL syntax tree and build read-only guardrails for LLM-generated queries: single-SELECT validation, traversal to block forbidden nodes, blocking file-read functions, CTE-aware table allowlists, row caps, and a Cartesian product cost proxy—plus execution accuracy evaluation for correctness.

Article Content

Connect an LLM to a database and sooner or later you'll receive a DROP TABLE or a DELETE without a WHERE—sometimes because the user asked for it, sometimes because a prompt injection is hiding in a column value, and sometimes just because the model had a "bright idea." A naive defense like sql.strip().lower().startswith("select") fails in at least three cases: SELECT 1; DROP TABLE orders, hiding an INSERT inside a CTE, and SELECT * FROM read_csv('/etc/passwd') in DuckDB.

A more robust approach is to parse the SQL first, then audit the syntax tree. The open-source library sqlglot can produce a dialect-aware AST. Below is a read-only query guardrail built on it, using DuckDB as the example.

Core Design: Parse First, Then Audit the Syntax Tree

Step 1: A Single Statement, with a SELECT at the Root

Use sqlglot.parse(sql, read=DIALECT) to get a list of statements, filter out empty ones, and require exactly one. The root node must be exp.Select, exp.SetOperation, or exp.Subquery. Reject SQL that fails to parse outright—if the parser can't understand it, it shouldn't be allowed to execute.

stmts = [s for s in sqlglot.parse(sql, read=DIALECT) if s is not None]
if len(stmts) != 1:
    raise GuardError("multiple_statements", ...)
tree = stmts[0]
if not isinstance(tree, (exp.Select, exp.SetOperation, exp.Subquery)):
    raise GuardError("not_select", ...)

Note the design of the code field on the error—it's not just the rejection reason, but also labeling data you can use later for evaluation.

Step 2: Walk the Entire Tree, Not Just the Root

A SELECT at the root isn't enough—data-modifying CTEs and SELECT INTO can hide deep inside. Use tree.walk() to traverse every node and reject on any hit against the forbidden list. The list includes Insert, Update, Delete, Merge, Create, Drop, Alter, TruncateTable, Command, Copy, Set, Use, Pragma, Attach, Detach, transaction types, permission types, and more.

sqlglot renames node classes across versions, so build the tuple with hasattr(exp, name) filtering, pin the tested sqlglot version, and rerun the guardrail tests on upgrade.

A CTE (Common Table Expression) is a named subquery defined in a WITH clause. It's a breeding ground for write-operation injection because the SQL standard allows embedding data-modifying statements inside the CTE body—for example, WITH x AS (DELETE FROM orders RETURNING *) SELECT * FROM x. The syntax tree for such a statement still has a Select at the root, so checking only the root node type would let it through. Likewise, SELECT ... INTO new_table creates a new table in some dialects, yet its root node also looks like a query. Only a depth-first traversal of every node via tree.walk() can catch a Delete, Insert, or similar node buried in the middle. This also explains why the guardrail must audit the entire tree rather than stop at validating the outermost statement type.

Step 3: Block File and Network Read Functions

DuckDB's read_csv, read_parquet, and glob are the most insidious: superficially they're SELECTs, but they're actually filesystem side channels. Block them by function name, and also intercept literal table references like FROM 'data.csv'.

_BLOCKED_FUNCS = {"read_csv", "read_csv_auto", "read_parquet", "read_json",
                  "read_text", "read_blob", "glob", "parquet_scan", "query"}

If your target is Postgres or Snowflake, the blocklist will differ (pg_read_file, COPY, stages, etc.), but the principle is the same: enumerate the side-effect entry points of the engine you're using, then layer on a table allowlist.

DuckDB's ability to query files as tables comes from its "replacement scan" mechanism: table functions like read_csv('path') and read_parquet('path') read local or remote files directly in the query plan without any pre-created table. On top of that, DuckDB also supports referencing files directly with a string literal, such as FROM 'data.csv', which the parser automatically expands into the corresponding read function. This means an attacker only needs to get the model to generate a seemingly harmless SELECT to potentially read arbitrary files on the server (such as /etc/passwd or SSH keys) or probe the directory structure via glob. These side channels don't modify data, so a read-only database role won't stop them either—they must be blocked at the SQL layer with a double defense based on function name and literal table references.

Step 4: A CTE-Aware Table Allowlist

Walk the exp.Table nodes and require each table name to be in the allowed set. CTE names look like tables in the syntax tree, so you must first collect the aliases of exp.CTE and exclude them—otherwise every WITH query would be incorrectly rejected.

Step 5: A Row Cap the Model Can't Raise

Don't append a LIMIT to the user's SQL—the user might already have LIMIT 10000000. The right approach is to wrap the entire query:

def _cap_rows(tree, max_rows):
    inner = tree.sql(dialect=DIALECT)
    return f"SELECT * FROM ({inner}) AS _capped LIMIT {int(max_rows)}"

The inner layer can lower the cap but can't raise it. Execute the SQL regenerated by tree.sql() rather than the original string, ensuring that what was "validated" is exactly what gets "executed."

The key distinction here is "appending" versus "wrapping." If you concatenate LIMIT N at the end of the user's SQL, when the original query already contains LIMIT 10000000 you'll get a syntax error or semantic ambiguity. But if you wrap the entire query as a subquery and then apply an outer LIMIT, the outer limit always takes effect—a stricter inner cap is preserved, and a looser inner cap is overridden by the outer one, so the model can't break through by writing a large LIMIT in its own SQL. Another important point is executing the SQL re-serialized by tree.sql() rather than the original string: the parser and the executor may interpret the same text differently (comments, dialect differences, implicit type conversions), and regenerating ensures the audited syntax tree corresponds strictly to the statement actually executed.

Step 6: A Cost Proxy for Detecting Cartesian Products

The engine's EXPLAIN isn't necessarily cheap, comparable, or even available. A deterministic proxy is good enough: count the row count of each referenced table and sum them, but switch to multiplication whenever you hit a join with no ON or USING.

total = total * max(n, 1) if cartesian else total + n
if max_cost is not None and total > max_cost:
    raise GuardError("cost_cap", ...)

It's crude—it overestimates some queries and underestimates others—but it turns "full-table cross join" into a rejection instead of an incident.

Measuring Correctness: Execution Accuracy

Guardrails only prevent harm; they don't judge right from wrong. Judging correctness requires comparing result sets rather than SQL text—two completely different SQL statements can be equally correct. Write a result_sets_equal function that rounds floats to 6 decimal places before comparing, avoiding chasing phantom 1e-12 differences.

A golden case is a question plus a reference query, with ordered: true marked when order matters. Run the agent, run the golden SQL, compare the results. A guardrail rejection shows up as a failure in the form of an error code, which lets you distinguish "the model wrote DDL" from "the model wrote a wrong join."

What Guardrails Can't Do

Face the limitations honestly: they can't replace a read-only database role—use both together. They don't understand row-level security and can't determine whether a seemingly legitimate query leaks data the user shouldn't see, and the cost proxy isn't a query planner. Treat the guardrail as one layer of defense, and maintain a small test file of known-bad queries (semicolon chains, CTE-wrapped inserts, read_csv), running it every time you upgrade sqlglot.

The author has packaged this approach as the Data Analyst Agent Kit, including the complete forbidden list, a DuckDB demo repository, Vega-Lite chart output, a deterministic mock LLM that runs offline, and an execution-accuracy evaluation framework. If you just want the ideas, the code above is enough to build your own in an afternoon: pin the sqlglot version, write the bad-query tests first, and keep the read-only database role.

A read-only database role is a separate line of defense beyond the guardrail: on the database side, create a dedicated account with GRANT SELECT, so that even if the guardrail is bypassed and a DROP TABLE is generated, the database will reject it with a permission error. The two complement each other—the guardrail gives a readable error code early at the application layer and stops the query from reaching the database, while the database role is the backstop when the guardrail itself has a bug or is broken by an upgrade. Row-Level Security, meanwhile, is a native database capability that restricts the data a user can see at row granularity; the guardrail audits SQL syntax structure and can't know whether the rows returned by a legitimate SELECT fall within what the current user is entitled to see—that kind of judgment can only be delegated to the database's role and policy configuration.

One-line Summary

Parse SQL with sqlglot and walk the syntax tree to build a read-only query guardrail that defends against injection and side effects.

Paragraph Summary

The article notes that when connecting an LLM to a database, a naive string-prefix check can't defend against semicolon chains, write operations hidden in CTEs, or DuckDB file reads. A more robust approach uses sqlglot to parse SQL into a dialect-aware AST, then audits in six steps: require a single statement with a query at the root, traverse the entire tree to block forbidden nodes, block file and network reads by function name, apply a CTE-aware table allowlist, enforce a row cap by wrapping rather than appending, and detect Cartesian products with a cost proxy that sums or multiplies row counts. Guardrails only prevent harm and don't judge correctness—correctness must be measured by comparing result sets rather than SQL text—and must be used alongside a read-only database role.