← Default view

Atlas / Text-to-SQL Hardening / Query Validation

Five-Layer SQL Validation Pipeline

survey · 20 citations · 8 min read

// TL;DR Regex blocklists drop from 98% to 60% accuracy against LLM-generated queries. Intent errors masquerade as working code — most broken queries execute cleanly and return plausible wrong data. Chain five deterministic layers: statement-type allowlist → AST structural check → semantic schema binding → policy injection → read-only DB connection. All validation must run after LLM generation, in code — never delegated to a second LLM call. [1][3]

Why Single-Layer Validation Fails

60%
regex detection accuracy against LLM-generated queries — down from 98%
[1]
50K+
production queries studied — most broken ones execute cleanly and return plausible wrong data
[2]
6
distinct failure classes — each requires a different layer to catch it
[2]

The Validation Pipeline

0
Intent ClassificationPRE-SQL

Before generating SQL, classify whether the user's message warrants a database query. Off-topic, ambiguous, or schema-mismatched inputs refused early — before any SQL tokens are emitted. [3]

off-topic queries schema-mismatched inputs ambiguous intent
LatentRefusal: hidden-activation probe, 88.5% F1, ~2ms overhead [4] · "Query Carefully" parse-time unanswerable detection [18]
1
Statement-Type AllowlistDEFAULT DENY

Parse the generated SQL and enforce a default-deny policy on statement types. Regex is insufficient — use a real AST parser. [5]

DROPTRUNCATEALTER GRANT / REVOKEINSERT / UPDATE / DELETE multi-statement (;)EXEC / dynamic SQL SELECT ✓
sqlglot ⭐ 9.3k · 31 dialects · AST top-level statement type check [6]
2
AST Structural ValidationAST PARSE

Traverse the parse tree against what this user's role is authorized to see. Covers SELECT, WHERE, JOIN ON, subqueries, and CTEs — catches hallucinated fields immediately. [7]

unauthorized tableshallucinated columns PII in projections / CTEsfinancial column exposure
sqlglot sqlparse pg_query sqlparse is non-validating — will miss edge-case constructs [9]
3
Semantic Schema Binding + Compile GateSEMANTIC

Catalog-aware validator resolves every reference against live DB metadata. Engine-native dry-run (PARSEONLY / EXPLAIN) catches dialect-specific syntax, permission grants, and insane cost estimates. [10]

UNKNOWN_COLUMNAMBIGUOUS_COLUMN TYPE_MISMATCHMISSING_REQUIRED_FILTER CARTESIAN_JOINRESTRICTED_COLUMN
SET PARSEONLY ON EXPLAIN returns allow / deny / warn / repair codes with structured error info [11]
4
Policy InjectionAST REWRITE

Do not trust the LLM to consistently include required filters. Mechanically rewrite the parsed AST — policies evaluated against the AST, never raw prompt text. A regex-matched tenant_id in the user message is not an enforced predicate. [7]

missing tenant_id filterunbounded time windowsmissing status='active'
WHERE tenant_id = :ctx.tenant ✓ AND created_at >= :ctx.start_date ✓
sqlglot transform() survives alias and CTE resolution [6]
5
Database-Level EnforcementDB BACKSTOP

Backstop if any prior layer has a bug. Physical write isolation — not a policy setting. Cannot be bypassed by application code. [12]

GRANT SELECT only ✓ [12] PostgreSQL / SQL Server RLS ✓ [17] read replica (physical isolation) ✓ [3] LIMIT 1000 result cap ✓ [3]
⚠ RLS has exploitable CVEs: CVE-2024-10976 (bypass below subqueries), CVE-2025-8713 (statistics leakage) — defense-in-depth, not primary control [13]

Failure Mode → Layer Matrix

Failure ClassError CodeCaught By
Schema hallucination (column/table not in DB)UNKNOWN_COLUMNLayer 3 — Semantic
Wrong join path (fabricated FK relationships)CARTESIAN_JOINLayer 3 — Semantic
Missing required filter (tenant_id, date range)MISSING_REQUIRED_FILTERLayer 3 — Semantic + Layer 4 — Policy
Wrong table selection (payments vs revenue_recognition)WRONG_SCHEMA_OBJECTLayer 3 — Semantic
Unsafe statement type (DELETE / DROP from ambiguous intent)FORBIDDEN_STMT_TYPELayer 1 — Allowlist
Syntax errors (unbalanced parens, invalid keywords)PARSE_ERRORLayer 2 — AST + Layer 3 — Compile Gate
PII column in SELECT, WHERE, CTE projectionRESTRICTED_COLUMNLayer 2 — AST
Off-topic / schema-mismatched user inputINTENT_MISMATCHLayer 0 — Intent

Implementation Reference — Layers 1 & 2 (sqlglot)

import sqlglot ALLOWED_TABLES = {"orders", "products", "customers"} FORBIDDEN_TYPES = { sqlglot.exp.Drop, sqlglot.exp.Delete, sqlglot.exp.Update, sqlglot.exp.Insert, } def validate(sql: str) -> tuple[bool, str]: parsed = sqlglot.parse_one(sql) if type(parsed) in FORBIDDEN_TYPES: return False, f"Forbidden: {type(parsed).__name__}" tables = {t.name.lower() for t in parsed.find_all(sqlglot.exp.Table)} if not tables.issubset(ALLOWED_TABLES): blocked = tables - ALLOWED_TABLES return False, f"Unauthorized tables: {blocked}" return True, "ok" # Layer 3 — Semantic dry-run (engine-native) # PostgreSQL: EXPLAIN SELECT ... → validates column refs, plans cost [10] # SQL Server: SET PARSEONLY ON; SELECT → parse phase only, no execution [11] # Both catch dialect-specific errors and permission gaps custom parsers miss.

Tools

sqlglot GitHub repository

github.com/tobymao/sqlglot — 31-dialect AST parser, mutation API, type inference · ⭐ 9.3k [6]

LibraryASTDialectsStarsBest For
sqlglot Full31 ⭐ 9.3k Traversal, mutation, type inference — layers 1–4 [6]
sqlparse PartialDB-agnostic ⭐ 3.9k Lightweight tokenization; non-validating [9]
pg_query Full (PG)PostgreSQL — Exact PG parse tree via libpg_query [19]
sqlfluff NoLinting only — SQL style / dialect linting, not safety gates

Emerging Threats

⚠ ToxicSQL Backdoor CRITICAL
79.41%
attack success rate from poisoning just 0.44% of training data. Stealthy semantic + character-level triggers generate predefined malicious SQL on target inputs while behaving normally on clean queries. [14] Published at SIGMOD 2026. [15]

Implication: fine-tuned models are not inherently safer. Application-layer validation remains mandatory regardless of model provenance or fine-tuning source.
↺ Repair Loop Escape MODERATE
2–3 max
repair attempts before escalating to human review. Returning validation errors to the LLM can produce queries that remove safety filters to avoid the error. [8]

Re-validate every repaired query with identical strictness. Treat policy violations as separate error class — return to application logic, not to the LLM.

Evidence Logging (SOC 2 / HIPAA)

Every validation decision — accepted or rejected — must produce a cryptographically-signed, timestamped record. Log the LLM prompt alongside the SQL so backdoor attacks can be traced to their trigger inputs. [5][16]
→ user identity + role context → natural language input → generated SQL (pre-injection) → policy decisions + violation codes → final executed SQL → result row count + latency → which layers accepted / rejected → repair attempt history

Implementation Ladder

▶ Crawl
  • Expose 2–3 specific business queries
  • Hard-code allowed tables/columns
  • Manually review SQL in staging for 2 weeks
▶▶ Walk
  • Role-scoped schema subset
  • Automated AST validation
  • Semantic binding enabled
  • Audit logging active
▶▶▶ Run
  • Roll out across all roles
  • SQL Compile Gate added
  • Anomaly alerts on audit stream
Do not skip Crawl. A narrow, manually-reviewed allow-set is far safer than a broad schema with "should-be-fine" LLM constraints. The use cases you don't anticipate are the ones that will expose you. [3][20]

In This Expedition

expedition
Naive text-to-SQL failure modes
~97% of wrong LLM-generated SQL executes cleanly. A field guide to failure modes and the hardening stack.
expedition
Schema grounding strategies
Stop an LLM from hallucinating columns: schema linking, retrieval, execution feedback.
survey
Read-only enforcement and least-privilege roles
Purpose-built read-only roles, column/row filtering, read replicas as physical write boundaries.
expedition
pgvector semantic row search
Nearest-neighbor row retrieval, HNSW/IVFFlat indexing, hybrid SQL+vector queries.
recon
Observability and audit logging
Debug production; prove compliance and accountability.
recon
Multi-tenant and row-level security
PostgreSQL RLS: cost-efficient isolation, session variables, policy testing.

Sources

  1. [1] arxiv.org — SQL injection detection degrades to 60% against LLM queries
  2. [2] usedatabrain.com — 50K production query LLM SQL evaluation
  3. [3] timdietrich.me — SQL Agent Safety Architecture
  4. [4] arxiv.org — LatentRefusal: pre-generation safety via hidden activations
  5. [5] queryshield.dev — Prevent SQL Injection in LLM Queries
  6. [6] github.com/tobymao/sqlglot — Python SQL parser, 31 dialects
  7. [7] lotuslabs — Enterprise text-to-SQL privacy architecture
  8. [8] dpriver.com — 10 text-to-SQL production risks
  9. [9] github.com/andialbrecht/sqlparse — non-validating SQL tokenizer
  10. [10] dpriver.com — SQL semantic validation for LLM queries
  11. [11] sqlshack.com — SET PARSEONLY ON overview
  12. [12] dev.to — Safe Text-to-SQL: read-only DB access
  13. [13] medium.com — When RLS fails in SaaS: CVE-2024-10976, CVE-2025-8713
  14. [14] arxiv.org — ToxicSQL backdoor attacks in text-to-SQL
  15. [15] dl.acm.org — ToxicSQL @ SIGMOD 2026
  16. [16] medium.com — Prompt injection meets SQL injection
  17. [17] querio.ai — RLS in multi-tenant SaaS analytics
  18. [18] arxiv.org — Query Carefully: detecting unanswerable text-to-SQL
  19. [19] pypi.org — pg_query: Python wrapper for libpg_query
  20. [20] oligo.security — OWASP LLM Top 10 (2025)