Text-to-SQL agents: risks and safeguards

How a text-to-SQL agent works

A text-to-SQL agent takes a question in everyday language and uses an LLM to write a database query. Your application runs that query and the agent then presents the result, often as a sentence, table or chart. More capable agents can inspect the schema, correct a failed query and try again, all through tool calls.

The appeal is obvious: people can ask questions of data without knowing SQL. The danger is equally clear, because you are allowing generated code to run against a system that may hold your most sensitive data. Treat each generated query as untrusted input, whoever asked the question.

The main risks

The last row deserves special attention. The other risks tend to produce errors or incidents that someone will notice. A wrong answer may simply be accepted and repeated in a report.

Layer 1: restrict the database role

The database's own permissions are your strongest control, because they hold even if the model is manipulated and your other checks miss something. A prompt saying "only write SELECT statements" is a request, not a control. For the reasons set out in LLM guardrails and prompt injection defence, it should never be what stands between an attacker and your data.

• Use a dedicated read-only role. Create one just for the agent, with no write or administrative permissions.
• Grant only what is needed. Give access to specific views and tables. Do not grant access to everything and hope to exclude what matters.
• Prefer curated views. A view can leave out sensitive columns, mask values and join tables in the correct way, so the model has fewer chances to go wrong.
• Query a replica where possible. A read replica or analytics copy protects your production workload from expensive queries. Be aware that replicas and copies can lag behind, and say so if freshness matters.
• Limit other capabilities. Check for database functions or features that reach the file system, the network or other databases, and remove access to them.

Layer 2: enforce who can see what

If the application serves more than one customer or role, the model must not be responsible for applying that boundary. A question like "show all invoices" should only ever reach the current user's invoices, and asking the model to include the right filter is not reliable.

Depending on your database, use row-level security tied to session settings or per-tenant views. Set the identity from your authenticated session and never from model output or the user's question. Then test it deliberately by asking, in as many ways as you can, for data belonging to someone else.

Results are also a route for data to leave. If query results go to a hosted model for summarising, make sure your privacy and provider terms allow that, or show results directly to the user without sending the rows back to the model.

Layer 3: validate each query before running it

Validation adds a second barrier and gives you useful errors to return to the model. Use a proper SQL parser for your dialect, since keyword matching on strings is easily fooled by comments, unusual casing or nested statements. Common checks include:

• Exactly one statement, of a permitted type.
• Only tables and views from an allow-list.
• Sensitive columns are not selected.
• Only approved functions. A SELECT can still call functions with side effects or functions that deliberately waste time, and some databases support SELECT INTO, which writes data.
• A row limit is present, or is added by your code.

Here is an illustrative outline. The parse_sql function is a placeholder for a real parser, and the allowed lists are examples:

# Illustrative only. parse_sql stands in for a real SQL parser.
ALLOWED_TABLES = {"sales_summary", "product_catalog"}
ALLOWED_FUNCTIONS = {"count", "sum", "avg", "min", "max", "date_trunc"}

class QueryRejected(Exception):
    pass

def validate_query(sql):
    statements = parse_sql(sql)
    if len(statements) != 1:
        raise QueryRejected("Send exactly one statement.")
    query = statements[0]
    if not query.is_plain_select:
        raise QueryRejected("Only read queries are allowed.")
    unknown_tables = set(query.tables) - ALLOWED_TABLES
    if unknown_tables:
        raise QueryRejected(f"Tables not available: {sorted(unknown_tables)}")
    unknown_functions = set(query.functions) - ALLOWED_FUNCTIONS
    if unknown_functions:
        raise QueryRejected(f"Functions not available: {sorted(unknown_functions)}")
    return query

Validation complements database permissions but does not replace them. Parsers and dialects have edge cases, so assume a query might eventually slip through and make sure the role limits what could happen if it does.

Layer 4: limit resources

• Statement timeout. Set it on the role or session, so that it applies whatever query is sent.
• Row limits. Cap the rows returned, and tell the user and the model when results have been cut short. Silent truncation can turn a partial result into a wrong conclusion.
• Attempts. Limit how many times an agent may correct and retry a query in one run.
• Concurrency and quotas. Use a connection pool with a limit for the agent, and per-user rate limits.
• Cost estimates. Where your database supports it, check the query plan and refuse queries estimated to be very expensive.

Reducing wrong answers

A valid query is not necessarily a correct one. Models can easily pick the wrong date column, double-count through a join or misread what "active customer" means in your business. These steps improve accuracy:

• Provide meaning as well as schema. Clear table and column descriptions, business definitions and example queries all help. Giving the model only relevant tables also reduces confusion.
• Use a semantic layer where you can. Let the model choose from defined metrics and dimensions instead of writing raw SQL. A calculation is then defined once by people who understand it.
• Use templates for common questions. Well-known questions can map to reviewed, parameterised queries, leaving free-form generation for the rest.
• Let the agent ask. An ambiguous question such as "best customers last quarter" should prompt a clarifying question or a clearly stated assumption.
• Show the working. Display the SQL or a plain description of it, the filters applied and the time range, so that people can spot a mistake.
• Handle empty results honestly. "No rows" may mean a bad filter, not an absence of data. Check for this before stating a conclusion.

For decisions with real consequences, have an analyst check the query. Where the agent's output feeds an action, not only a report, human approval belongs in the execution path.

Testing and monitoring

Build a set of questions with known answers. Compare the results a query returns with the expected results, not the SQL text, because different queries can be equally correct. Run a test database with fixed data so results stay stable, and re-run the set when you change the model, prompt or schema.

Write security tests as well. Include attempts to reach other tenants, write data, read system tables, run multiple statements and cause slow queries. Include injection text in stored data too, in case results are passed back to the model. A correctly configured system should refuse or contain them at the database level, even when validation is turned off for the test.

Log everything useful. Record the question, generated SQL, validation outcome, duration and row count, with appropriate care for sensitive values. Review rejected queries and user-flagged answers, and add them to your test set.

For help building LLM features with appropriate controls, see generative AI and LLM development.

Frequently asked questions

Is text-to-SQL safe to use on a production database?

Direct access to the primary database is hard to justify. A read-only, narrowly scoped role on a replica or analytics copy, with validation and limits, reduces the risk considerably, although it does not remove it.

Is a read-only role enough on its own?

No. It prevents writes but not reading too much, slow queries or wrong answers. Combine it with restricted views, row-level rules, query validation, timeouts and testing.

Can the system prompt stop harmful queries?

Not reliably. Instructions can be ignored or overridden, so treat them as helpful guidance only. Controls that must hold belong in database permissions and application code.

How do I test text-to-SQL accuracy?

Build questions with known answers on a stable test dataset and compare query results, not SQL text. Include ambiguous questions and review failures to find missing definitions or context.