Safe Text-to-SQL and Database Agents
Model-generated SQL is untrusted code. The safest design exposes curated semantic queries or parameterized tools; broader text-to-SQL requires a narrow read-only role, approved schema views, parser/AST policy, single-statement enforcement, time and row limits, cost/plan gates, tenant context, output controls, audit, and human approval for any consequential expansion.
What will you be able to do?
- Choose semantic tools before open-ended SQL generation.
- Build defense layers for read-only analytical text-to-SQL.
- Detect exfiltration, denial-of-service, and scope expansion attempts.
- Evaluate semantic accuracy, authorization, performance, and abstention.
Prerequisites and mental model
Treat the model like an external junior analyst whose code may be maliciously influenced. A prompt is guidance; only deterministic gates and PostgreSQL privileges are controls.
What is the safety ladder?
- Fixed report endpoint with typed parameters.
- Curated semantic tool catalog.
- Query templates selected by model, parameters validated by code.
- Generated query against narrow read-only views with AST/policy checks.
- Human-reviewed broader analysis in an isolated environment.
Avoid direct write SQL. Consequential changes use domain tools and approval workflows.
For level 4, enforce:
- authenticated tenant/purpose context outside the prompt;
- dedicated read-only role and RLS/approved views;
- one parsed SELECT statement; deny DDL/DML, catalog/secrets, unsafe functions, comments/multi-statements as policy requires;
- allowlisted relations/columns/functions and explicit output classification;
- forced limit, statement_timeout, lock_timeout, read-only transaction;
- optional plain EXPLAIN gate for estimated cost/shape;
- result size/cell limits and redaction;
- audit of question, policy/version, normalized SQL fingerprint, decision, metrics—not secret payloads by default.
AST validation reduces risk but is not a proof of safety; parser/version mismatches and allowed functions can still be dangerous.
Why is this AI-native?
Natural-language access becomes a governed database product, with a semantic layer, policy engine, evaluator, and refusal behavior. The system optimizes correct abstention as well as correct answers. It never grants authority based on how confident the model sounds.
Prove it worked
Build an adversarial suite: cross-tenant request, prompt injection, secret/catalog query, huge Cartesian join, recursive explosion, sleep/expensive function, stacked statement, encoded bypass, sensitive column, inference through aggregates, and valid ambiguous question. Prove gates deny or safely clarify before execution.
Production judgment
- “Read only” can still exfiltrate or exhaust resources.
- Query plan estimates can be wrong; runtime limits remain necessary.
- Schema descriptions given to models can reveal sensitive structure.
- Analytics may belong on a replica/warehouse with curated data and privacy controls.
Common mistakes
- Prompt says SELECT only: prompt treated as control → enforce role and parsed policy.
- Model accesses base tables: semantic layer skipped → expose narrow views/tools.
- Correct SQL answers wrong question: no semantic evaluation → labeled question/answer dataset and abstention.
AI Pair-Programmer Prompt
Hands-on exercise
Design three tools—ticket volume, SLA breaches, and top issue categories—that answer most executive questions without arbitrary SQL.
Acceptance criterion: each has typed parameters, tenant scope, bounded results, fixed semantics, denial tests, and an audit record.
Knowledge check
- Is read-only SQL harmless?
- Why prefer semantic tools?
- Is AST validation sufficient by itself?
Answers
- No; it can exfiltrate, infer sensitive data, lock/consume resources, or expose structure.
- They constrain semantics, permissions, cost, output, and tests.
- No; privileges, scope, time/resource limits, output controls, and evaluation remain necessary.
Completion checklist
Primary references