Text-to-SQL: securing AI queries on business databases

Natural-language database queries without opening the database to the model? WZ-IT builds AI agents and assistants that read approved data through restricted roles, views and validated queries, operated on your own infrastructure. The AI process assessment reviews process, data sources, permissions and risks up front. Explore AI agents · Book a call

Text-to-SQL lets a language model turn a question such as "Which customers had more than €50,000 in revenue in the third quarter?" into an SQL query and run it against the database. This is an obvious fit for ERP, CRM and controlling data, because the answers are not in documents but in tables. The difficulty lies in two places: the model has to understand the schema correctly, and it must not do anything to the database that nobody approved. This article explains the architecture, the permissions in PostgreSQL, the role of MCP connectors and how to measure quality. As of September 2026.

Table of contents

• How text-to-SQL works
• Benchmarks and practice: Spider and BIRD
• The pipeline: context, generation, validation, execution
• Database permissions as the real boundary
• Views as a semantic layer
• Free SQL or predefined queries
• MCP database connectors
• Risks: prompt injection, data leakage, load
• Measuring quality
• Local model and operations

How text-to-SQL works

The language model receives three things: the question, a description of the available tables and columns, and instructions on the SQL dialect. From these it writes a query. The application executes it and returns the result, as a table or summarised in a sentence.

The difference from RAG is fundamental. RAG retrieves text passages and lets the model phrase an answer. In text-to-SQL, the database does the computing. Sums, averages and counts are exact, provided the query is right. That is exactly where the risk lies: a wrong query returns a wrong number that looks just as convincing as a correct one.

Benchmarks and practice: Spider and BIRD

Two benchmarks shape the discussion. Above all, their numbers show how strongly results depend on the difficulty of the databases.

Sources for the top scores: BIRD leaderboard; Spider figures from the Spider 2.0 paper, which compares o1-preview across all three benchmarks (73.0% on BIRD).

Two points follow for practice. First, accuracy drops considerably once schemas are large, messy and domain-heavy, which is exactly what grown ERP systems look like. Second, benchmark scores come from public datasets that models may have seen during training. On your own schema, your own test set decides, not the leaderboard.

The pipeline: context, generation, validation, execution

A robust text-to-SQL application consists of more than a prompt. A five-step flow has proven itself:

Context. A model does not need the whole schema, only the relevant tables with understandable descriptions. Column comments (COMMENT ON COLUMN) and two or three example queries per subject area improve results noticeably. With schemas of hundreds of tables, the selection itself becomes a retrieval task, similar to retrieval in RAG.

Validation. The generated query is parsed before execution, not searched for "DELETE" as text. SQLGlot (MIT licence) is a Python parser for more than 30 SQL dialects, including PostgreSQL. The syntax tree allows the rules to be checked properly: exactly one statement, SELECT only, only tables and views from the allowlist, a LIMIT added or capped. An EXPLAIN before execution also shows whether a query is likely to be expensive.

Output. The application displays the executed query alongside the result. Anyone using the number can then see which filters and joins are behind it. This is the most effective protection against plausible-looking wrong results.

Database permissions as the real boundary

A language model can generate any SQL statement. Prompt rules such as "only write SELECT" lower the probability but prevent nothing. What actually happens is decided by the privileges of the database role under which the query runs. That role is therefore the real security boundary; all other measures complement it.

An example for PostgreSQL:

-- Dedicated schema containing only approved views
CREATE SCHEMA ai;
CREATE ROLE ai_reader LOGIN PASSWORD '...';

GRANT USAGE ON SCHEMA ai TO ai_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA ai TO ai_reader;
-- Grant no privileges on base tables in other schemas

-- Convenience defaults, not a security boundary
ALTER ROLE ai_reader SET default_transaction_read_only = on;
ALTER ROLE ai_reader SET statement_timeout = '15s';

Four details matter:

Where load on the production database is a concern, queries run on a replica. Connections to a hot standby in PostgreSQL are strictly read-only; not even temporary tables can be written there (PostgreSQL, Hot Standby). For MySQL and MariaDB the same principle applies with dedicated users that receive SELECT only on approved views.

If users should only see their own data, for example a sales representative only their region, that restriction also belongs in the database: through RLS policies or separate views per user group. RAG with permissions describes the same rule for documents: permissions apply before the model, not in its prompt.

Views as a semantic layer

Raw tables are hard to read for people and even harder for a model. Columns such as stat_cd, flag_cncl or dt_3 do not explain themselves, and many tables contain legacy data, test records or intermediate states. A semantic layer translates this into the terms people use when asking questions.

In its simplest form, this is a set of views in a dedicated schema:

Larger installations use dedicated metrics layers in BI tools for this. Views are enough to start: they can be versioned and tested, and they also limit which data is reachable at all.

Free SQL or predefined queries

Not every application needs free SQL. Often the set of meaningful questions is manageable, and then a collection of parameterised queries is safer and more accurate.

With parameterised queries, the model only passes values such as a customer number or a date range. The query itself is fixed and executed with placeholders, which rules out SQL injection through the model's text. For agents that use several systems, this is the usual pattern anyway: one tool per task with clearly limited privileges (AI agents: permissions and approvals).

MCP database connectors

The Model Context Protocol connects assistants to tools, including databases. The available servers differ considerably in their protection mechanisms, as of September 2026:

The reference server illustrates the core problem: it ran queries in a read-only transaction but accepted several statements in a single call. A COMMIT; ended the transaction, and everything after it ran unprotected. According to Datadog, the package was still downloaded around 21,000 times a week via npm when the analysis was published in August 2025. The lesson applies to every connector: protections in the application layer complement database privileges; they do not replace them.

Risks: prompt injection, data leakage, load

The risks map onto the categories of the OWASP Top 10 for LLM Applications 2025:

The Supabase MCP case from July 2025 showed what prompt injection through database content looks like in practice: a crafted support ticket told the assistant to read the table of integration tokens and write the contents into the ticket. The assistant ran with the service_role, which bypasses row-level security. Simon Willison calls the combination of access to private data, exposure to untrusted content and a way to send data out the "lethal trifecta". Remove one of the three ingredients and this attack fails. More in Protection against prompt injection.

A related pattern affects tools that generate code from results. In the Vanna library, prompt injection via the visualisation function led to execution of arbitrary Python code (CVE-2024-5565, CVSS 8.1). Charts should therefore be produced from fixed templates, not from model-written code.

Measuring quality

A query that runs without errors is not automatically correct. The most common errors are errors of meaning: cancellations counted, a join that duplicates rows, the wrong date column filtered. Measurement is therefore based on the result.

The test set is built with the domain experts who know the data: typical questions, one reviewed reference query each, plus questions the schema deliberately cannot answer. Every change to the model, prompt or views is measured against this set. Question, generated query, runtime and user feedback are logged, for example with Langfuse. The approach mirrors the one for retrieval systems in Measuring RAG quality.

Local model and operations

Text-to-SQL does not require a model from a cloud provider. With business databases in particular, there are good reasons to keep schema descriptions, questions and results inside your own network. The choice of model depends on complexity: a manageable schema with clean views works with a mid-sized model, while large schemas with many joins benefit from larger models and longer context. Which LLM to self-host? gives an overview of models suitable for self-hosting, and GPU and VRAM sizing for LLMs works out the memory requirements.

A clear separation has proven itself in operations:

The text-to-SQL service holds the database credentials, not the model. The model only sees schema context and, where the answer requires it, the result.

What this means for your project

Text-to-SQL pays off when many recurring questions are asked of the same data and the answers currently come from exports or report requests. The order determines success: first a dedicated role and views with the approved data, then a test set with reference queries, and only then the model and prompt. Where the questions are manageable, parameterised queries are more accurate than free SQL.

WZ-IT implements such applications as AI agents on the open stack, with PostgreSQL or MySQL as the data source, restricted roles and tracing. Models run locally on the AI Cube or on WZ-IT managed GPU servers with an NVIDIA RTX PRO 4000 Blackwell (24 GB) or RTX PRO 6000 Blackwell Max-Q (96 GB). Support, consulting and implementation by WZ-IT.

Related topics: Protection against prompt injection, MCP in the enterprise, AI agents & automation and the AI agent frameworks comparison.