# One CTE name can slip past a text-to-SQL agent's read-only guard

> SQLGlot calls itself a transpiler, not a validator, so the safety rules on its syntax tree, scope resolution included, are yours to write.

Original: https://fdetimes.net/en/guides/text-to-sql-agent-select-only-sqlglot/

The SQLGlot README is blunt about it: SQLGlot is a transpiler, not a validator. The tool you plan to use as a security gate says it was never built for the job. If you want it to guard anything, you have to write the rules on the syntax tree yourself.

Why is instructing the agent in its prompt not enough? SurrealDB's write-up on jailbroken agents puts it briefly: a system prompt is only a suggestion; it is no fence. Once a text-to-SQL agent is wired into a client's database, the rules have to sit in a layer the model cannot reach.

This guide builds that layer on your laptop: a Python function that takes a SQL string and either returns the approved query or raises an error. The result is a guard that permits exactly one SELECT statement, only on the `orders` and `customers` tables, with no unfamiliar function calls. You need Python 3, `pip install sqlglot` and about an hour.

The code here is a stripped-down version for learning. Pin the sqlglot version in your requirements file and treat the six test queries at the end as the yardstick: every time you upgrade, run them again before you deploy.

## Why parse instead of using regex?

SQLGlot is a parser written in pure Python with no other dependencies. It turns a SQL string into an AST that code can walk. A guide to safe text-to-SQL on DEV Community recommends exactly this: parse the generated query into an AST to check its semantics, for instance to make sure it contains only a SELECT.

A regex sees only characters. A syntax tree tells you the actual structure: whether the word `orders` is a table name, a column name or a string literal. The whole guard below rests on that difference.

## Step 1: reject on a parse error, never guess

The README says that when the parser detects a syntax error, it raises `ParseError`. That is the guard's first branch: no attempt to repair, no retry, straight rejection. You also want exactly one statement, to block things like `SELECT 1; DROP TABLE orders`.

```python
import sqlglot
from sqlglot import exp
from sqlglot.errors import ParseError

class Rejected(Exception):
    pass

def parse_single(sql: str) -> exp.Expression:
    try:
        statements = sqlglot.parse(sql, read="postgres")
    except ParseError as e:
        raise Rejected(f"parse error: {e}")
    statements = [s for s in statements if s is not None]
    if len(statements) != 1:
        raise Rejected("exactly one statement required")
    return statements[0]
```

Check: calling `parse_single("SELECT 1; SELECT 2")` must raise `Rejected`. This uses `sqlglot.parse`, which returns a list of statements, rather than the `parse_one` shown in the README, because the guard needs to count statements.

## Step 2: accept only a tree rooted in SELECT

Because the parser is lenient, a successful parse proves nothing. The second rule inspects the tree itself: the root must be a SELECT, and walking the whole tree with `walk` must not turn up a single data-writing node.

The project's AST Primer explains that `find` and `find_all` are both built on `walk`, which makes `walk` the tool for the finest-grained control.

```python
FORBIDDEN = (exp.Insert, exp.Update, exp.Delete,
             exp.Drop, exp.Create, exp.Command)

def require_select(tree: exp.Expression) -> None:
    if not isinstance(tree, exp.Select):
        raise Rejected(f"root is {type(tree).__name__}, not SELECT")
    for node in tree.walk():
        if isinstance(node, FORBIDDEN):
            raise Rejected(f"forbidden node: {type(node).__name__}")
```

The `FORBIDDEN` list is deliberately short, because the root-must-be-SELECT rule does most of the work.

A root rule this strict may also block legitimate read queries such as `UNION`, depending on how sqlglot builds the tree for them. Add a `UNION` query to your test suite to find out for certain on the version you have pinned.

Overblocking is intentional: widen the rules once the client needs it, not before.

## Step 3: the table allowlist, and the CTE trap

The README includes an example that uses `find_all(exp.Table)` to list every table in a query, and it is the natural starting point for an allowlist. The first version everyone writes looks like this.

```python
ALLOWED = {"orders", "customers"}

def naive_tables(tree):
    cte_names = {c.alias_or_name for c in tree.find_all(exp.CTE)}
    for t in tree.find_all(exp.Table):
        if t.name in cte_names:
            continue  # assumed to be a CTE, skip
        if t.name not in ALLOWED:
            raise Rejected(f"table not in allowlist: {t.name}")
```

Why `cte_names`? The AST Primer warns that a `Table` node is not always a table in your database, because CTE names also show up as `Table`. Without skipping them, every valid `WITH` query would be blocked.

But skipping by name opens a hole. Take a constructed test case: the agent generates `WITH salaries AS (SELECT * FROM salaries) SELECT * FROM salaries`. Inside the CTE body, `salaries` is the real payroll table; the function above sees a name matching a CTE and waves all three occurrences through.

**Key point:** Two identical names are not necessarily the same thing; the guard has to know what each name points to.

## Step 4: let the scope module decide which names are real tables

The AST Primer introduces the scope module, a semantics-aware way of walking the tree that knows what each name refers to: `build_scope` determines whether a name is a physical table, a CTE or a subquery. That is precisely the question the guard needs answered.

```python
from sqlglot.optimizer.scope import build_scope

def check_tables(tree: exp.Expression) -> None:
    root = build_scope(tree)
    if root is None:
        raise Rejected("could not build scope")
    for scope in root.traverse():
        for name, source in scope.sources.items():
            if isinstance(source, exp.Table) and source.name not in ALLOWED:
                raise Rejected(f"table not in allowlist: {source.name}")
```

Within each scope, a source that is an `exp.Table` is a real table; a source that is a child scope is a CTE or subquery, and its body is checked on a later pass of the loop. Note the `root is None` branch: failing to build a scope is another form of uncertainty, so it too ends in rejection.

Run the `salaries` example again: the CTE body should now be rejected, because there `salaries` is a physical table rather than the CTE name.

## Step 5: do not forget functions

A SELECT can still do dangerous things through function calls. According to hoop.dev, PostgreSQL by default grants execute permission on functions and procedures to the public role, so a read-only role can still call functions. The guard should block function calls the parser does not recognize.

```python
def check_functions(tree: exp.Expression) -> None:
    for node in tree.find_all(exp.Anonymous):
        raise Rejected(f"function not in allowlist: {node.name}")

def guard(sql: str) -> str:
    tree = parse_single(sql)
    require_select(tree)
    check_tables(tree)
    check_functions(tree)
    return tree.sql(dialect="postgres")
```

This version is simplified and rests on an assumption you need to verify: that functions sqlglot does not recognize become `exp.Anonymous` nodes.

The `pg_sleep` test below exists to probe exactly that assumption; if it gets through, this check is not enough. In production, invert it into a function allowlist, for example permitting only `COUNT`, `SUM` and `DATE_TRUNC`, and have the database itself revoke execute permissions.

## Six test queries before calling it done

Run `guard` on these six queries and record the results. `SELECT id, total FROM orders` must pass. `DELETE FROM orders` must be blocked at step 2, and `SELECT * FROM salaries` at step 4. The `salaries` CTE query must be blocked, as must `SELECT pg_sleep(10)`, and a garbage string such as `SELEC * FRM` must stop at step 1 or step 2.

The last one teaches the most important lesson. You do not need to know what a lenient parser will turn garbage into, because every uncertain branch leads to rejection. The most common mistake is the reverse: blocking only what has been listed as bad and letting everything else through.

Save these six queries as pytest tests today. Any query that gives an unexpected result marks a spot where the code relies on a wrong assumption about how sqlglot builds the tree, and these tests are what tell you whether the guard still holds after each upgrade of your pinned sqlglot version.

## Your guard is not the last line of defense

SurrealDB frames the problem clearly: if the database refuses to run a statement, it no longer matters how the agent was tricked into generating it. The DEV Community guide likewise advises a database user with SELECT permission only on specific, non-sensitive tables, and a read-only connection as the final layer of defense.

Even that layer has limits. The PostgreSQL documentation for `SET TRANSACTION` describes read-only as a high-level notion that does not prevent every write to disk. So stack three layers: the AST guard, a role with minimal privileges and a read-only transaction, and treat none of them as the only one.

## What this skill looks like at a client site

Before wiring a text-to-SQL agent into a client's database, have an answer ready for one question: which tables can the agent touch? An allowlist file in code review, an attack test suite running in CI and the database role's grants are things other people can verify, which a system prompt cannot offer.

If you are preparing to move into an FDE role, put this guard and its tests on GitHub and describe on your CV exactly what it does: blocks non-SELECT statements, resolves CTEs through scope, fails closed on parse errors. When applying for positions that put agents onto real client data, it is a worthwhile example to bring to an interview.

On your first day at a client, do not open the prompt first. Ask for the list of tables the agent actually needs, then write the allowlist and the grants to match that list exactly.

**Try this week:**

- Install sqlglot, pin the version in requirements, copy the guard function from this guide and run the six test queries, including the CTE named 'salaries'
- Write 10 more attack queries against your guard and save them as pytest tests
- For an agent running in production, check its database role: what permissions it has beyond SELECT, and which functions it can call

## Sources

- [SQLGlot README (tobymao/sqlglot)](https://github.com/tobymao/sqlglot/blob/main/README.md)

- [SQLGlot AST Primer](https://github.com/tobymao/sqlglot/blob/main/posts/ast_primer.md)

- [Even a jailbroken LLM can't exceed its database permissions (SurrealDB)](https://surrealdb.com/blog/even-a-jailbroken-llm-cant-exceed-its-database-permissions-heres-how)

- [Safe Text-to-SQL: giving an agent database access without dropping tables or leaking PII (DEV Community)](https://dev.to/kowshik_jallipalli_a7e0a5/safe-text-to-sql-giving-an-agent-database-access-without-dropping-tables-or-leaking-pii-i47)

- [LangChain SQL agent on PostgreSQL: limiting what it can execute (hoop.dev)](https://hoop.dev/blog/langchain-sql-agent-on-postgresql-limiting-what-it-can-execute)

- [SET TRANSACTION (PostgreSQL documentation)](https://www.postgresql.org/docs/current/sql-set-transaction.html)
