Hardening Enterprise Text-to-SQL Agents: AST Sandboxing, Read-Only Replicas, and Guardrails Against ERP Data Leaks

Few architectural concepts sound more seductive to enterprise leadership than "Text-to-SQL": enabling non-technical executives, financial controllers, and support teams to query mission-critical ERPs, CRMs, and clinical databases using plain English. An engineering team connects an LLM agent to a corporate PostgreSQL or SAP HANA instance via an open-source framework, executes a few sample queries, and declares victory.

Then the system meets enterprise production reality. A customer support ticket containing an unescaped indirect prompt injection tricks the agent into dumping payroll tables. An ambiguous natural language prompt causes the model to generate a 5-table Cartesian join across 40 million unindexed rows, locking table indexes and bringing the production ERP to a grinding halt. Meanwhile, hallucinated schema foreign keys send autonomous agents into recursive retry loops that burn through API token budgets.

1. Anatomy of an Enterprise Text-to-SQL Sandbox: Beyond Naive Prompting

Naive Text-to-SQL pipelines feed database schemas directly into an LLM prompt and execute the raw generated string via a standard database driver. This creates three critical failure vectors:

• Indirect SQL Injection: User-supplied input or ingested CRM documents manipulate the agent into generating destructive DDL (DROP, ALTER) or DML (UPDATE, DELETE) statements.
• Runaway Resource Exhaustion: Queries missing partition keys or index constraints trigger catastrophic full-table scans that exhaust server memory and lock transaction logs.
• PII & Credential Exfiltration: The agent queries sensitive columns (hashed passwords, SSNs, credit card tokens) that have no relevance to the user's analytical request.

[Autonomous Agent / User Natural Language Request]
        |
        | 1. Generate Raw SQL Candidate
        v
+----------------------------------------------------------------------------------+
|                     HARDENED AST QUERY GATEWAY (FastAPI / sqlglot)               |
|                                                                                  |
|  +--------------------+    +----------------------+    +----------------------+  |
|  | AST Syntax Parser  |    | AST Node Denylist    |    | Column & Table       |  |
|  | (Strict Read-Only) |    | (Blocks DDL/DML/CTE) |    | Redactor (PII Guard) |  |
|  +--------------------+    +----------------------+    +----------------------+  |
|           |                            |                          |              |
|           v                            v                          v              |
|  +----------------------------------------------------------------------------+  |
|  | Query Rewriter Engine                                                      |  |
|  | - Forces Enforced LIMIT (Hard Cap: 100 Rows)                               |  |
|  | - Injects Tenant Isolation Filter (WHERE tenant_id = 'org_91a')           |  |
|  | - Sets Statement Timeout Parameter (SET statement_timeout = '500ms')      |  |
|  +----------------------------------------------------------------------------+  |
+----------------------------------------------------------------------------------+
        |
        | 2. Verified, Rewritten & Sandboxed SQL Query
        v
+----------------------------------------------------------------------------------+
|                   EPHEMERAL READ-ONLY DATABASE REPLICA                           |
|  - Strictly Read-Only User Privileges (REVOKE ALL, GRANT SELECT ONLY)           |
|  - Physical Isolation from Production Master (Zero Master CPU Impact)            |
+----------------------------------------------------------------------------------+
        |
        | 3. Raw Structured Result Set
        v
[Sanitized JSON Formatter ==> Agent Reasoning Context]
    

2. Concrete Latency & Resource Budget: Sub-300ms SLA

Enterprise analytical tools require immediate conversational responses. Our AST validation and schema rewriting pipeline operates in single-digit milliseconds, adding virtually zero overhead to the query cycle:

3. Production Implementation: AST Query Sanitizer & Rewriter

Below is a complete, production-grade Python implementation of an Abstract Syntax Tree (AST) SQL sanitizer utilizing sqlglot. It validates that queries are strictly SELECT statements, verifies that prohibited columns are never queried, injects mandatory tenant filters, and clamps the result set to a deterministic limit:

# security/sql_ast_sanitizer.py
import sqlglot
from sqlglot import exp
from typing import Set, Tuple
from pydantic import BaseModel, Field

class SanitizationResult(BaseModel):
    is_safe: bool
    sanitized_sql: str = ""
    rejection_reason: str = ""
    audited_tables: list[str] = Field(default_factory=list)

class EnterpriseSQLSanitizer:
    """
    Validates and rewrites generated SQL queries at the AST level
    to prevent SQL injection, table locking, and PII exfiltration.
    """
    PROHIBITED_COLUMNS: Set[str] = {
        "password_hash", "ssn", "credit_card_number", 
        "auth_token", "api_key", "secret_key"
    }

    ALLOWED_TABLES: Set[str] = {
        "sap_general_ledger", "salesforce_accounts", 
        "inventory_items", "flight_maintenance_logs"
    }

    @classmethod
    def sanitize_and_rewrite(
        cls, 
        raw_sql: str, 
        tenant_id: str, 
        max_rows: int = 100
    ) -> SanitizationResult:
        try:
            # 1. Parse raw SQL into Abstract Syntax Tree (PostgreSQL / ANSI dialect)
            parsed_expressions = sqlglot.parse(raw_sql, read="postgres")
        except Exception as parse_error:
            return SanitizationResult(
                is_safe=False,
                rejection_reason=f"Syntax parsing failure: {str(parse_error)}"
            )

        if not parsed_expressions or len(parsed_expressions) > 1:
            return SanitizationResult(
                is_safe=False,
                rejection_reason="Multiple statements or empty query detected. Script injection blocked."
            )

        expression = parsed_expressions[0]

        # 2. Strict Statement Type Audit: Only SELECT is permitted
        if not isinstance(expression, exp.Select):
            return SanitizationResult(
                is_safe=False,
                rejection_reason=f"Forbidden statement type: {expression.key.upper()}. Only SELECT queries allowed."
            )

        # 3. Reject Destructive Sub-Expressions (e.g., INTO OUTFILE, Command executions)
        for prohibited_node in (exp.Command, exp.Insert, exp.Update, exp.Delete, exp.Drop, exp.Alter):
            if expression.find(prohibited_node):
                return SanitizationResult(
                    is_safe=False,
                    rejection_reason=f"Forbidden sub-expression {prohibited_node.__name__} detected."
                )

        # 4. Table Whitelist and Column Denylist Verification
        queried_tables = []
        for table in expression.find_all(exp.Table):
            table_name = table.name.lower()
            if table_name not in cls.ALLOWED_TABLES:
                return SanitizationResult(
                    is_safe=False,
                    rejection_reason=f"Access to unauthorized table '{table_name}' blocked."
                )
            queried_tables.append(table_name)

        for column in expression.find_all(exp.Column):
            col_name = column.name.lower()
            if col_name in cls.PROHIBITED_COLUMNS:
                return SanitizationResult(
                    is_safe=False,
                    rejection_reason=f"Access to sensitive PII column '{col_name}' strictly blocked."
                )

        # 5. Mandatory Deterministic LIMIT Injection / Clamping
        current_limit = expression.args.get("limit")
        if current_limit is None:
            expression = expression.limit(max_rows)
        else:
            try:
                limit_val = int(str(current_limit.expression))
                if limit_val > max_rows or limit_val <= 0:
                    expression.set("limit", exp.Limit(expression=exp.Literal.number(max_rows)))
            except ValueError:
                expression.set("limit", exp.Limit(expression=exp.Literal.number(max_rows)))

        # 6. Inject Mandatory Multi-Tenant WHERE Filter
        tenant_filter = sqlglot.parse_one(f"tenant_id = '{tenant_id}'")
        expression = expression.where(tenant_filter, append=True)

        return SanitizationResult(
            is_safe=True,
            sanitized_sql=expression.sql(dialect="postgres"),
            audited_tables=queried_tables
        )

4. Four Catastrophic Production Failure Modes & Architectural Defenses

Failure Mode 1: Tautological Injection via Unescaped Prompt Variables

The Breakdown: An end-user enters a prompt like: "Find invoices for client ' OR 1=1 --". Naive prompt templates concatenate this input into the LLM system prompt. The model outputs a query that bypasses where-clauses, returning every company invoice in the system.

The Defense: Programmatic AST Filter Injection. As demonstrated in our implementation, the gateway parses the query into a tree and programmatically appends an immutable tenant_id = 'xyz' node using Boolean AND logic. Even if the LLM generates a tautology in user predicates, the top-level tenant filter is enforced by the parser itself.

Failure Mode 2: Cartesian Product Join Explosions

The Breakdown: In complex ERPs like SAP or NetSuite, tables often link through composite foreign keys. An agent attempting to correlate journal entries and vendors fails to specify join conditions, executing an accidental Cartesian product across millions of records that saturates database memory and locks production tables.

The Defense: Hard Query Cost Limits & Ephemeral Read Replicas. Queries never run against the primary database. They target an isolated read-replica operating under a mandatory statement_timeout = '500ms'. Any query that fails to resolve within half a second is terminated at the PostgreSQL engine level.

Failure Mode 3: Schema Hallucination & Retry Token Storms

The Breakdown: When an LLM guesses column names that do not exist, the database driver returns a syntax error. Naive agent loops feed the error back into the model to "self-correct," triggering 10 consecutive retry loops that consume tens of thousands of tokens and freeze user sessions.

The Defense: Local Schema Grounding & Single-Retry Circuit Breakers. We prune database schemas dynamically, providing the LLM with only the exact DDL definitions for relevant tables. If a generated query fails AST validation, the circuit breaker halts execution immediately and routes the request to human review.

Failure Mode 4: Information Leakage via Verbose SQL Stack Traces

The Breakdown: When a query fails, standard frameworks return raw database driver exceptions to the client interface (e.g., "syntax error at or near 'sap_payroll_staging_v2' at character 42"), mapping internal database architecture directly to external attackers.

The Defense: Sanitized Synthetic Error Gateways. The gateway suppresses all raw database driver exceptions. The client receives standardized, generic error responses while the raw failure telemetry is cryptographically signed and logged internally for security auditing.

5. Economics: Dedicated Self-Hosted Gateway vs. Commercial Text-to-SQL SaaS

Enterprise SaaS products that promise "AI Database Chat" (such as hosted Text-to-SQL platforms) charge excessive monthly seat fees while routing your internal database schemas through third-party multi-tenant servers:

6. Grounded Proof-of-Work: Mission-Critical Enterprise Deployments

These query-hardening architectures are battle-tested across client platforms engineered by Ashish Sharma Digital Product Studio:

• Forge-OS Enterprise (Multi-Tenant Decoupled SaaS Infrastructure) Engineered multi-tenant database access gateways enforcing strict tenant isolation, connection pooling, and sub-10ms permission checks across high-concurrency client organizations.
• Fly4Smiles Surgical Center (Decoupled Clinical Intake & RAG Outside WordPress) Extracted patient eligibility data and clinical decision queries into an isolated, HIPAA-compliant microservice, ensuring zero patient health information (PHI) is exposed to public web endpoints or prompt injection exploits.
• Rotor Wing Services / JavAirTec Group (Aviation Maintenance Compliance) Architected technical documentation verification systems under FAA/EASA Part 145 standards, cross-referencing aircraft maintenance logs against manufacturer manuals with strict read-only query sandboxes.
• Capital Insights (Dublin Wealth Coaching Stress-Test Engine) Constructed deterministic scenario modeling engines evaluating complex multi-decade wealth projections with zero runtime database degradation.