A customer writes in: "my last three orders are missing." Someone has to open a database and look.
We built an AI agent that does that part. It reads the ticket, forms a hypothesis, queries the customer's own systems, and writes up what it found with the queries attached as evidence. Useful. Also, on its face, a terrible idea — you have an LLM reading untrusted text from strangers on the internet, and you are handing it a database connection.
We shipped it anyway. The interesting work was not the agent. It was the roughly 400 lines standing between the agent and the data, and the thing we got wrong twice before getting it right.
All of this is Apache-2.0 and sitting in our repo, so you can read the real thing rather than take our word for it. The two files are backend/app/services/sql_guardrails.py
and backend/app/services/db_connector_service.py
.
Start here, because everything else follows from it.
You cannot write "only generate SELECT statements" in a system prompt and call that a control. You can write it — we do — but it is a hint about intent, not a guarantee about behaviour. The model reads customer messages. Customer messages can contain instructions. Somewhere between those two facts is every prompt-injection writeup of the last three years.
So the rule we settled on: every guarantee is enforced outside the model, on a parsed AST. Not on the string. Not by asking nicely. If a property matters, it gets checked by code that the model cannot talk to.
That sounds obvious written down. It is also the part most "AI + your database" demos skip, because the demo works fine when nobody is attacking it.
The first thing we do with model-generated SQL is throw away the string.
statements = sqlglot.parse(raw_sql, read=dialect)
if len(statements) != 1:
return SqlValidationResult(ok=False, reason="Exactly one SQL statement is allowed")
statement = statements[0]
if not isinstance(statement, exp.Select):
return SqlValidationResult(ok=False, reason="Only plain SELECT statements are allowed")
Then a denylist of node types that must not appear anywhere in the tree: Insert
, Update
, Delete
, Merge
, Create
, Drop
, Alter
, TruncateTable
, Grant
, Command
, Transaction
, Set
, Into
, Lock
.
Into
and Lock
are on that list for a reason. SELECT * INTO evil FROM orders
is a SELECT, and it writes. SELECT * FROM orders FOR UPDATE
is a SELECT, and it takes locks. Both parse as exp.Select
. Both would have walked straight through a naive sql.strip().upper().startswith("SELECT")
check.
At the end, the statement is re-rendered from the AST with comments=False
, and that rendering is what executes. Two things fall out of that. Comments never reach the server, which matters more than it sounds — MySQL executes /*! ... */
. And whatever runs is canonical SQL derived from a tree we inspected, not the raw string we were handed.
Every table reference has to be on the connector's allowlist. The obvious version of this check is also the broken one, because table references hide in places people forget to look.
WITH x AS (SELECT * FROM pg_shadow) SELECT * FROM x
The top-level FROM is x
. Looks clean. The real read is inside the CTE body.
SELECT * FROM (SELECT id FROM orders UNION ALL SELECT oid FROM pg_shadow) t
Same trick, different wrapper.
statement.find_all(exp.Table)
walks all of it — CTEs, subqueries, joins, union arms. CTE names are exempted, since a reference to x
resolves to a CTE we already walked, but only when unqualified. A CTE called users
does not authorize secret_schema.users
. That is a real test case, and it exists because it is exactly the sort of thing you would get wrong.
Bare names resolve against default schemas, so an allowlist entry of public.orders
also authorizes orders
. But hidden.orders
is refused. Same table name, different schema, no.
This is the part worth the whole article.
Support data is multi-tenant in the worst way: one orders
table, every customer's rows in it. The agent investigating Ann's missing orders must not be able to read Bob's. And the naive approach — check that the model's WHERE clause contains the right customer filter — cannot be made to work.
Think about what you would have to prove. That no OR
widens the predicate. That no join reintroduces unfiltered rows. That no correlated subquery reaches around it. That no UNION arm adds a branch without it. You are writing a static analyser for arbitrary SQL semantics, and you will lose.
So we stopped validating the predicate and started rewriting the relation:
FROM orders o -> FROM (SELECT * FROM orders WHERE customer_email = 'ann@x.com') AS o
Every scoped base table gets swapped for a pre-filtered subquery. The model's own SQL is then applied on top of that.
Now OR 1=1
is not a bypass. It is not even interesting. The outer predicate can say whatever it likes, because the relation it selects from only ever contained one customer's rows:
def test_or_true_cannot_widen_the_scope(self):
result = scoped("SELECT id FROM orders WHERE 1=1 OR customer_email = 'victim@x.com'")
assert result.ok
assert "FROM (SELECT * FROM orders WHERE customer_email = '%s')" % OWNER in result.sql
Note that the query is allowed. We are not blocking it. We are making it harmless, which is a much better place to be than pattern-matching hostile predicates forever.
Three details that took us longer than they should have:
It fails closed. A scoped table with no customer identity on the ticket is refused outright. The tempting behaviour — no scope value, so skip the filter — reads every customer's rows. That is the bug you only find in production, so there is a test pinning it shut for None
, ""
and " "
.
The value is a literal node, never string-formatted. exp.Literal.string(value)
escapes on render. Feed it x' OR '1'='1
and you get 'x'' OR ''1''=''1'
— one string, no injection. We are rewriting SQL to defend against injection, so it would be somewhat embarrassing to introduce one while doing it.
Scoping happens last. The allowlist and masking checks run against what the model actually wrote. If we rewrote first, those checks would be inspecting our own wrappers.
Some columns should never reach the model at all. Email, phone, whatever your compliance people circled.
The naive implementation masks by output column name, after the query runs. It survives about ninety seconds of adversarial thinking:
SELECT email AS harmless FROM customers -- alias
SELECT substr(email, 1, 3) FROM customers -- expression
SELECT id FROM customers WHERE email = 'a@b.c' -- probe by inference
The last one never selects the column at all. It just asks yes/no questions until it knows the value.
So masked columns cannot be referenced, anywhere. Not in SELECT, not in WHERE, not inside a function.
Then there is the category we did not think of first, and which is the reason this section exists:
SELECT to_jsonb(t) FROM customers t
SELECT row_to_json(customers) FROM customers
SELECT customers FROM customers
SELECT customers::text FROM customers
SELECT (c.*)::text FROM customers c
SELECT array_agg(customers) FROM customers
Every one of those collapses an entire row into a single value. Name-based masking looks at the output column, sees to_jsonb
, finds nothing called email
, and passes the whole record through with the email inside it.
In Postgres a bare table name used as a value is the row. SELECT customers FROM customers
is legal and returns row tuples. That one genuinely surprised us.
The fix is two rules on the AST. A star nested inside anything other than a top-level projection is refused. And a bare, unqualified column reference whose name matches a table, alias or CTE in scope is refused, because that is a whole-row value wearing a column's clothes.
SELECT *
, t.*
and count(*)
stay legal. The first two expand to real column names that get redacted by name on the way back; the last is a count, and counts do not leak rows.
Everything so far is one system. One system means one bug away from nothing.
So the session is independently read-only, at the driver:
options="-c statement_timeout=5000 -c default_transaction_read_only=on"
cursor.execute("SET SESSION TRANSACTION READ ONLY")
cursor.execute("SET SESSION max_execution_time = 5000")
If the AST validator has a hole we have not found, a write still fails at the server. The statement timeout does the same job for the resource-exhaustion cases the function denylist is aimed at — pg_sleep
, benchmark()
, and the rest of that family.
And the executor fetches max_rows + 1
even though the validator already forced a LIMIT. Belt and braces, deliberately.
Two things, stated plainly, because a security post with no open questions is a marketing post.
The denylist is a denylist. BLOCKED_FUNCTIONS
covers the Postgres and MySQL functions we know about for sleeping, reading files, opening network connections and poking at server config. Denylists are structurally incomplete. It is defence in depth behind the read-only session, not the thing holding the line, and we would rather have an allowlist here — we have not found a workable one that does not break ordinary queries.
Row scoping is opt-in, and that is a sharp edge. row_scope
is empty until you set a customer-identifying column per table. An operator who connects a database and skips that step gets an agent that can read every row of every allowlisted table. The docs say to set it. Defaults beat documentation, and this default is the wrong way round. It is the change we would most like to make.
The whole corpus lives in backend/tests/services/test_sql_guardrails.py
— mutations, table smuggling, function abuse, masked-column exfiltration, row-scope bypasses. If you find one it does not cover, that is a genuinely useful issue to open, and we would rather hear it from you than from a customer.
If you are building an agent that touches real data, the shape of this is reusable even if none of the code is:
None of this makes an LLM trustworthy. That is the point. It makes trustworthiness unnecessary, which is the only version of this that scales.
I build ChatterMate, an open-source AI customer support platform with human handoff — Apache-2.0 and self-hostable. Everything above is in the repo at github.com/ChatterMate/chattermate.chat, so go and find the hole I missed.