Is text-to-SQL safe to run against a production database?
Updated · DBShifts
Short answer
Not on its own. When a model writes raw SQL, the query can join anything, scan everything, bypass masking with SQL functions, or change data if the user allows it. Safer designs give the model structured tools — filter, aggregate, follow a real foreign key — and enforce allowlists, masking and limits on the server.
Key facts
- In text-to-SQL, the model decides the shape of every query.
- A row limit bounds the size of the answer, not the work the database does to produce it.
- SQL functions and expressions can expose a masked column indirectly.
- Structured tools make the set of possible queries small enough to check against a policy.
- Read-only database users stop writes but not expensive or over-broad reads.
What can go wrong
Over-broad joins pull in tables nobody meant to expose. An unindexed filter on a huge table triggers a full scan even when only ten rows come back. Expressions over a masked column leak it in another form. And with the wrong credentials, a generated statement can change data.
Structured tools instead of SQL
Instead of a SQL string, the model fills in a small, typed request: which table, which columns, which filters, which aggregate, which foreign key to follow. Every field can be checked against the allowlist, the mask and the limits before anything runs.
When SQL is fine
A person running SQL with their own credentials, or an agent working on a disposable masked copy, is a different risk. The problem is model-written SQL against shared production data with nothing in between.
Checklist
- 1
Prefer structured query tools over raw SQL for agents
- 2
If you must accept SQL, parse it and allow only known statement shapes
- 3
Run with a read-only user
- 4
Estimate query cost before running, not just cap the result size
- 5
Mask on the server and refuse expressions over masked columns
- 6
Log the request the model made, not only the rows returned
Common mistakes
Validating SQL with another prompt
Asking a model whether a generated query is safe is not a control. Use a parser and rules, or avoid SQL altogether.
Capping rows and calling it done
LIMIT 10 on a billion-row scan still scans a billion rows. Check cost before the query reaches the database.
Joining on anything that shares a name
Joins should follow real, introspected foreign keys. Joins invented from similar-looking column names return wrong answers that look right.
How DBShifts does it
- The DBShifts Agent Gateway accepts no raw SQL; agents use structured tools for filters, projections, named aggregates and single-hop joins along real foreign keys.
- A cost guard checks a query before it runs, because a row cap does not stop the database doing a full scan first.
- Data Console's plain-English Ask uses the same governed, read-only tools, so it generates no SQL at all.
Questions people also ask
Can't I just check the generated SQL?
You can parse it and allow only safe shapes, but every SQL feature you permit is more to reason about. Structured tools make the safe set small by design.
Are structured tools too limited for real questions?
Filtering, aggregating and following foreign keys cover most operational questions. For anything they can't express, a person can run the query.
Related questions
Give AI agents data access you can defend
Scoped tokens, PII masked on the server, every request audited. Free to start.
Start free18 answers in this series. See all.