Text-to-SQL Safety for AI Agents - Sanitization, Read-Only Access, and Ambiguous Joins
Text-to-SQL Safety for AI Agents
Text-to-SQL engines let AI agents query databases using natural language. The technology is impressive - but running it against a 28-table production database introduces real risks that most tutorials skip over.
The Sanitization Problem
When an LLM generates SQL from natural language, you cannot trust the output. The model might generate DROP TABLE statements, UPDATE queries, or subqueries that scan entire tables and tank performance. Every generated query needs sanitization before execution.
At minimum, parse the SQL AST and reject anything that is not a SELECT statement. Better yet, run the agent's connection through a read-only database replica. No amount of prompt engineering substitutes for infrastructure-level access controls.
Ambiguous Joins Are the Real Challenge
On complex schemas, the same natural language query can map to multiple valid SQL queries with different joins. "Show me customer orders" might join through a direct foreign key or through an intermediate fulfillment table - and return different results.
The practical fix is providing the LLM with explicit join paths as part of the schema context. Instead of just giving it table definitions, give it a map of which joins are canonical for common query patterns. This reduces ambiguity and improves accuracy significantly.
Query Result Limits
Always enforce LIMIT clauses. An innocent-sounding query like "show me all transactions" against a table with millions of rows will exhaust memory and block other queries. Set a hard maximum at the execution layer, not just in the prompt.
Logging Everything
Every generated query should be logged with the original natural language input, the generated SQL, the execution time, and the row count. When something goes wrong - and it will - you need the audit trail to understand what happened and prevent it from happening again.
The rule of thumb: treat AI-generated SQL the same way you would treat user-submitted SQL in a web form. Never trust it. Always validate it.
Fazm is an open source macOS AI agent. Open source on GitHub.