Versions, and an honest limit. Spring Boot 4.1.1, Spring AI 2.0.1, JSqlParser 5.4, PostgreSQL 16 and Java 25. The code is thetext-to-sqlmodule of asmhatre/spring-ai, and every console block below is quoted from a file under itsoutput/directory. I did not use a language model, and nothing here measures how well one writes SQL. The “model” is a script that returns a prepared SQL string for each question, some correct, some wrong and some hostile on purpose, so the code around it can be tested. The database, though, is real: results, error messages, timeouts and permission failures are PostgreSQL 16’s. The schema, the data, the attack queries and the eight evaluation questions are examples I wrote.
Treat the model as an untrusted author of SQL
Three things can go wrong with SQL a model writes. It can be wrong: it joins the wrong way or picks the wrong column, and the number it returns is a plausible lie. It can be careless: it writes a query that reads a table or a column nobody meant to expose, or one that runs for ten minutes. And it can be steered: text in the question, or in data it reads, talks it into writing something harmful (the guardrails article covers that route). Telling it “only write SELECT” in the prompt helps with the first two and does nothing for the third, so the design assumes the model will sometimes ignore you.TextToSql.java:
public Answer answer(String question, Connection db) {
String raw = client.prompt().system(system).user(question).call().content();
String sql = extractSql(raw);
SqlGuard.Verdict verdict = guard.check(sql);
if (!verdict.ok()) {
return new Answer(sql, verdict.reason(), null, null);
}
try {
return new Answer(sql, null, null, QueryRunner.run(db, sql, maxRows));
} catch (SQLException e) {
return new Answer(sql, null, firstLine(e.getMessage()), null);
}
}extractSql first, because models often wrap SQL in a markdown fence even when told not to. Then the guard, then the database. A database error comes back as an Answer with the first line of PostgreSQL’s message, not as an exception, so a caller can show it or feed it back to the model.
Going deeper: why not just trust the prompt?
A prompt is a request, and a model follows it most of the time, not every time. Nothing in the pipeline above depends on the model behaving; each checkpoint assumes it did not. That is the same design rule as the guardrails article: put enforcement where the model cannot argue with it, and use the model’s good behaviour only for quality.
TextToSql.java— the pipeline, including the system prompt that asks for one SELECT.- Structured output in Spring AI 2.0 — an alternative where the model returns a typed object instead of a free-text SQL string.
Show the model only what it may use
A model writes better SQL when it knows the schema, and it cannot misuse a table it was never shown.SchemaPrompt.java builds the schema part of the prompt from the database itself: table and column names from information_schema, and the COMMENT text you attach to tables and columns, which is where you tell it what status can contain.
public static String describe(Connection c, List<String> tables) throws SQLException {
StringBuilder sb = new StringBuilder();
for (String table : tables) {
String tableComment = scalar(c, "SELECT obj_description(?::regclass, 'pg_class')", table);
sb.append("TABLE ").append(table);
if (tableComment != null) {
sb.append(" -- ").append(tableComment);
}
sb.append('\n');
try (PreparedStatement ps = c.prepareStatement(
"SELECT column_name, data_type, col_description(?::regclass, ordinal_position) "
+ "FROM information_schema.columns WHERE table_schema = 'public' AND table_name = ? "
+ "ORDER BY ordinal_position")) {
ps.setString(1, table);
ps.setString(2, table);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
sb.append(" ").append(rs.getString(1)).append(' ').append(rs.getString(2));
if (rs.getString(3) != null) {
sb.append(" -- ").append(rs.getString(3));
}
sb.append('\n');
}
}
}
}
return sb.toString();
}information_schema.columns lists only columns the current user has some privilege on, so the prompt shows exactly what the role can read. Here is the prompt the model receives (01-schema-prompt.txt, from SchemaPromptTest.java):
| You translate a question into ONE PostgreSQL SELECT statement.
| Use only the tables and columns below. Return only the SQL, no explanation, no markdown.
| Never write INSERT, UPDATE, DELETE, DDL or more than one statement.
|
| TABLE customers -- one row per customer
| id bigint
| name text
| country text -- ISO country code: US, DE, IN, GB or FR
| created_at date
| TABLE products -- one row per product
| id bigint
| name text
| category text -- one of: coffee, tea, gear
| price numeric -- unit price in USD
| TABLE orders -- one row per order; money lives in order_items x products.price
| id bigint
| customer_id bigint
| status text -- one of: paid, shipped, refunded, cancelled
| ordered_at date
| TABLE order_items -- one row per product on an order
| order_id bigint
| product_id bigint
| quantity integerapi_keys table, which holds secrets, is not mentioned at all. And customers has no email column in the prompt, because the role was granted only the other four columns. The last line of the transcript says so: the test checks that neither word appears. The comments came through (“one of: paid, shipped, refunded, cancelled”), which saves the model from guessing at status values and writing 'PAID'.
Going deeper: schema size, and describing meaning
Four small tables fit in a prompt without thought. A real warehouse with hundreds of tables does not, and a prompt with every table makes the model slower, more expensive and more likely to join the wrong thing. The usual answers are to expose a curated set of views rather than raw tables, or to retrieve only the tables relevant to the question first (the same retrieval idea as in the RAG article). I did not build either; the module passes an explicit table list. Comments are the cheapest place to put business meaning, such as “unit price in USD” on
products.price, because they live next to the column and travel with it.
SchemaPrompt.java— how the prompt is assembled.schema.sql— the tables, data andCOMMENTstatements.
Wall one: a database role that cannot do harm
The strongest protection is not in Java. It is a database login that is simply not able to do the dangerous thing, so even a perfect attack on your code lands on a refusal from the server. This is the role fromschema.sql:
-- The role the assistant connects as.
CREATE ROLE t2s_reader LOGIN PASSWORD 'reader' NOSUPERUSER NOCREATEDB NOCREATEROLE;
GRANT CONNECT ON DATABASE t2s TO t2s_reader;
GRANT USAGE ON SCHEMA public TO t2s_reader;
-- customers.email is personal data: the assistant gets column-level access without it
GRANT SELECT (id, name, country, created_at) ON customers TO t2s_reader;
GRANT SELECT ON products, orders, order_items TO t2s_reader;
ALTER ROLE t2s_reader SET default_transaction_read_only = on;
ALTER ROLE t2s_reader SET statement_timeout = '2s';public schema, and SELECT from three tables fully and from customers only in four named columns (email is left out). It is read-only by default and cancels any statement that runs longer than two seconds. It cannot create objects, because it owns nothing. I then probed it from a plain JDBC connection to see what it really does (05-role.txt, from RoleTest.java):
SHOW statement_timeout ok: 2s
SHOW default_transaction_read_only ok: on
SELECT count(*) FROM orders ok: 1000
INSERT INTO orders ... (wall 1: read-only default) ERROR: cannot execute INSERT in a read-only transaction
SELECT set_config('default_transaction_read_only','off',false) ok: off
SHOW default_transaction_read_only ok: off
INSERT INTO orders ... (wall 2: no INSERT privilege) ERROR: permission denied for table orders
SELECT count(*) FROM api_keys ERROR: permission denied for table api_keys
SELECT email FROM customers ERROR: permission denied for table customers
SELECT id, name FROM customers LIMIT 1 ok: 1INSERT fails with “read-only transaction”. Then the role runs set_config to switch default_transaction_read_only off, and the setting really does change. The second INSERT fails anyway, with a different message: “permission denied for table orders”. So there are two independent walls. The read-only default is a session setting the session itself can undo; the missing INSERT privilege cannot be undone from inside. If you rely on only the first, a query that flips the setting turns your read-only role into whatever its privileges allow.
Give the role privileges, not just a setting. GrantSELECTon the specific tables (and columns) the assistant needs and nothing else, and treatdefault_transaction_read_onlyandstatement_timeoutas extra guards on top. Create the role for this purpose only; never point a text-to-SQL feature at an application login that happens to have write rights.
Going deeper: what a role does not stop, and other databases
A read-only role limits what the model can change and see, but everything it may read, it may read freely, including across rows. If different users must see different rows (each customer only their own orders), table grants are the wrong tool; PostgreSQL has row-level security for that, which I did not test here. The role also does not stop reads of things PUBLIC can read, such as the list of database users (see the matrix below). Other databases have the same ideas under other names (a read-only user, column privileges, a query time limit); the exact commands differ, and I only ran PostgreSQL.
- PostgreSQL 16: GRANT, including column-level privileges.
- PostgreSQL 16: client connection defaults —
default_transaction_read_only,statement_timeout.
The seat belt: parse the SQL before it runs
The role is the wall, but you would rather not send obvious nonsense to the database at all, and a refusal in Java comes with a clear reason you can show or log. The tempting way is to search the string for words likeDROP and DELETE. It fails both ways: it blocks a legitimate query that has the word in a string or comment, and it misses anything spelled in a way you did not think of. The sturdier way is to parse the SQL into a tree and ask questions of the tree.
SqlGuard.java uses the JSqlParser library. First it checks the shape of the statement:
Statement st = parsed.get(0);
if (!(st instanceof Select select)) {
return Verdict.no("only SELECT is allowed, found " + st.getClass().getSimpleName());
}
Set<String> cteNames = new HashSet<>();
if (select.getWithItemsList() != null) {
for (WithItem<?> w : select.getWithItemsList()) {
if (!(w.getParenthesedStatement() instanceof ParenthesedSelect)) {
return Verdict.no("WITH item is not a SELECT (data-modifying CTE)");
}
cteNames.add(w.getAlias().getName().toLowerCase(Locale.ROOT));
}
}
if (select instanceof PlainSelect ps) {
if (ps.getIntoTables() != null && !ps.getIntoTables().isEmpty()) {
return Verdict.no("SELECT INTO creates a table");
}
if (ps.getForMode() != null) {
return Verdict.no("row locking clause (" + ps.getForMode() + ")");
}
}
Set<String> usedFunctions = new HashSet<>();SELECT; a WITH query is allowed only if every named subquery is itself a SELECT, which stops WITH d AS (DELETE ... RETURNING *). And SELECT INTO and row-locking clauses are refused. Then, still in SqlGuard.java, it checks what the statement touches:
Set<String> usedFunctions = new HashSet<>();
TablesNamesFinder<Void> finder = new TablesNamesFinder<>() {
@Override
public <S> Void visit(Function function, S context) {
usedFunctions.add(function.getName().toLowerCase(Locale.ROOT));
return super.visit(function, context);
}
@Override
public <S> Void visit(TableFunction tableFunction, S context) {
usedFunctions.add(tableFunction.getFunction().getName().toLowerCase(Locale.ROOT));
return super.visit(tableFunction, context);
}
};
Set<String> used;
try {
used = finder.getTables(st);
} catch (RuntimeException e) {
return Verdict.no("could not analyse the statement: " + e.getClass().getSimpleName());
}
for (String t : used) {
String name = t.toLowerCase(Locale.ROOT).replace("\"", "");
if (name.startsWith("public.")) {
name = name.substring("public.".length());
}
if (!tables.contains(name) && !cteNames.contains(name)) {
return Verdict.no("table " + t + " is not allowed");
}
}
for (String f : usedFunctions) {
if (!functions.contains(f)) {
return Verdict.no("function " + f + " is not allowed");
}
}
return Verdict.yes();03-guard-cases.txt, from GuardCasesTest.java):
ACCEPT SELECT count(*) FROM orders WHERE status = 'paid'
ACCEPT SELECT c.country, count(*) FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.country ORDE...
ACCEPT WITH big AS (SELECT order_id FROM order_items GROUP BY order_id HAVING sum(quantity) > 5) SELECT count(*)...
ACCEPT SELECT * FROM orders WHERE id IN (SELECT order_id FROM order_items WHERE quantity > 2)
ACCEPT SELECT 1 /* ; DROP TABLE orders */
ACCEPT SELECT name FROM public.customers
REFUSE DELETE FROM orders (only SELECT is allowed, found Delete)
REFUSE SELECT * FROM orders; DROP TABLE orders (more than one statement (2))
REFUSE SELECT * FROM api_keys (table api_keys is not allowed)
REFUSE SELECT usename FROM pg_user (table pg_user is not allowed)
REFUSE SELECT pg_sleep(3) (function pg_sleep is not allowed)
REFUSE SELECT pg_read_file('/etc/passwd') (function pg_read_file is not allowed)
REFUSE WITH d AS (DELETE FROM orders RETURNING *) SELECT count(*) FROM d (WITH item is not a SELECT (data-modifying CTE))
REFUSE SELECT * FROM orders FOR UPDATE (row locking clause (UPDATE))
REFUSE SELECT * INTO newtab FROM orders (SELECT INTO creates a table)
REFUSE SELECT count(*) FROM generate_series(1, 2000000000) (function generate_series is not allowed)
REFUSE SELECT set_config('default_transaction_read_only', 'off', false) (function set_config is not allowed)
REFUSE SELECT * FROM orders o, "api_keys" k (table "api_keys" is not allowed)
REFUSE SELECT date_part('month', ordered_at) FROM orders (function date_part is not allowed)
REFUSE SELECT now() (function now is not allowed); DROP TABLE orders inside a comment, which a keyword search would have blocked. The next twelve are refused, each with its own reason. The last two are the price of an allow-list: date_part and now() are harmless, but they were not on my list, so a legitimate question that needs them is refused until you add them. That is a deliberate trade, and the fix is a longer list, never a looser rule.
Going deeper: what the parser cannot promise
A parser answers “what does this statement say”, not “is it safe”. It does not know your columns (so it cannot tell that
email is private), and it does not know cost (so a join of four allowed tables passes). JSqlParser also has to understand PostgreSQL’s dialect; syntax it does not support is refused, which is safe but can refuse good queries, and I did not test how much real-world PostgreSQL it rejects. The version I used is 5.4, current when I wrote this.
SqlGuard.java— the guard, with the table and function allow-lists.- JSqlParser — the library.
- OWASP: SQL injection — the classic version of the problem.
Which layer stops what
To see the layers working I wrote sixteen queries, thirteen harmful and three legitimate, the kind of thing a careless or steered model might produce, and ran each four ways on the real database: with neither protection (a superuser connection), with the guard only, with the role only, and with both. Everything ran inside a transaction that was rolled back.HARM means the harmful query ran to completion; guard means the parser refused it; db means PostgreSQL refused or cancelled it (02-attack-matrix.txt, from AttackMatrixTest.java):
query neither guard only role only both
DELETE FROM order_items HARM guard db guard
second statement drops a table HARM guard db guard
table the assistant must not see HARM guard db guard
email column of an allowed table HARM HARM db db
system catalog: list database roles HARM guard HARM guard
pg_sleep(3) HARM guard db guard
read a server file HARM guard db guard
data-modifying CTE HARM guard db guard
row locks on every order HARM guard db guard
SELECT INTO makes a table HARM guard db guard
2 billion generated rows HARM guard db guard
turn read-only off for the session HARM guard HARM guard
cartesian product, allowed names only HARM HARM db db
(legit) join and group ok ok ok ok
(legit) harmless comment ok ok ok ok
(legit) date_part, not on the list ok guard ok guard
harmful outcomes out of 13 harmful queries: neither 13, guard only 2, role only 2, both 0email column (it checks table names, not columns) and the four-way join of allowed tables, which would run until the server fell over. The role alone lets through reading the list of database users, which any login may read, and the set_config call that switches the read-only default off (harmless on its own, as the previous section showed, but not something you want a model to be able to run). Together they leave nothing: every row of the last column is guard or db for a harmful query.
Going deeper: what the database actually said
For every harmful query that reached the database as the read-only role and was refused, here are PostgreSQL’s own words, from the second half of the same file. The listing is part of
02-attack-matrix.txt, which also holds the matrix above. Note that two of them (pg_sleep and the cartesian product) were stopped by the 2-second statement timeout, not by a permission, so a time limit is part of the role, not an optional extra.
what the database said to the read-only role:
DELETE FROM order_items ERROR: cannot execute DELETE in a read-only transaction
second statement drops a table ERROR: cannot execute DROP TABLE in a read-only transaction
table the assistant must not see ERROR: permission denied for table api_keys
email column of an allowed table ERROR: permission denied for table customers
pg_sleep(3) ERROR: canceling statement due to statement timeout
read a server file ERROR: permission denied for function pg_read_file
data-modifying CTE ERROR: cannot execute SELECT in a read-only transaction
row locks on every order ERROR: cannot execute SELECT FOR UPDATE in a read-only transaction
SELECT INTO makes a table ERROR: cannot execute SELECT INTO in a read-only transaction
2 billion generated rows ERROR: canceling statement due to statement timeout
cartesian product, allowed names only ERROR: canceling statement due to statement timeoutCap what comes back
A question like “show me all orders” is legitimate and still a bad idea to answer in full: the rows go to the model or the user, cost tokens or bandwidth, and may hold more than anyone meant to hand over.QueryRunner.java asks the server for at most N rows and says whether it stopped early:
public static Rows run(Connection c, String sql, int maxRows) throws SQLException {
c.setAutoCommit(false); // lets the JDBC driver ask the server for only maxRows + 1 rows
try (Statement st = c.createStatement()) {
st.setMaxRows(maxRows + 1);
boolean hasResult = st.execute(sql);
while (!hasResult && st.getUpdateCount() != -1) {
hasResult = st.getMoreResults();
}
if (!hasResult) {
return new Rows(List.of(), List.of(), false);
}
try (ResultSet rs = st.getResultSet()) {
ResultSetMetaData md = rs.getMetaData();
List<String> cols = new ArrayList<>();
for (int i = 1; i <= md.getColumnCount(); i++) {
cols.add(md.getColumnLabel(i));
}
List<List<String>> out = new ArrayList<>();
boolean truncated = false;
while (rs.next()) {
if (out.size() == maxRows) {
truncated = true;
break;
}
List<String> row = new ArrayList<>();
for (int i = 1; i <= cols.size(); i++) {
row.add(rs.getString(i));
}
out.add(row);
}
return new Rows(cols, out, truncated);
}
} finally {
c.rollback();
}
}04-limits.txt, from LimitsTest.java):
1000-row table, cap 100: rows returned 100, truncated true
exactly 100 matching rows, cap 100: rows returned 100, truncated false
7 matching rows, cap 100: rows returned 7, truncated false
a billion-row join, cap 100: rows returned 100, truncated true (the server stopped producing rows)
2 billion generated rows, cap 100: ERROR: canceling statement due to statement timeout (the row cap did not help here)
count(*) over 2 billion rows: ERROR: canceling statement due to statement timeout
stopped within 5 seconds of starting: true (role setting statement_timeout = 2s)generate_series(1, 2000000000) was not helped by it, and only the two-second timeout ended that query. So the two controls do different jobs. The row cap protects what you send onward; the timeout protects the database.
Going deeper: where to put the limits
I set the timeout on the role, so it applies to every connection that role makes, whatever the Java code does. A client-side query timeout (JDBC’s
setQueryTimeout) is a second control, but it works by cancelling from the client and depends on the client behaving. Row limits can also be put into the SQL itself by wrapping the model’s query in an outer LIMIT; the driver-level cap used here avoids rewriting the model’s SQL. If you use a connection pool in front of PostgreSQL, check that per-role settings still apply to the pooled sessions. I did not test a pooler.
QueryRunner.java— the cap and the truncation flag.LimitsTest.java— the boundary and timeout tests.
Score answers by their results, not their text
A safe query can still be the wrong query, and that is the bigger risk in practice: nobody is harmed, a wrong number just goes into a report. To measure it you need questions with known answers. The first instinct is to compare the model’s SQL with a reference SQL as text. That punishes every correct query written differently, which is most of them. The better check is to run both and compare the results, which is called execution accuracy.EvaluationTest.java has eight questions, each with a reference query and the SQL my script returns for it. The script is something I wrote, so I decided in advance which answers are right: four are equivalent to the reference but written differently, two are wrong in ways models are wrong, one uses a table that does not exist, and one is right for a reason worth a note. Here is how they scored (06-evaluation.txt):
question string execution what happened
How many customers are in Germany? no match same result
What is the total revenue of paid orders? no match same result
Which 3 products sold the most units? no match same result
Which customers have never ordered? no match same result
How many orders are there per status? no no DIFFERENT result: got [paid, 750], expected [paid, 250]
What is the average order value? no no DIFFERENT result: got [15.5000000000000000], expected [78.0150000000000000]
What is the revenue by country? no no refused by the guard: table sales is not allowed
How many orders were placed in March 2025? no match same result
string match: 0/8 execution match: 5/8 refused before running: 1 ran and returned a wrong answer: 2sales table was refused by the guard before it could run, which is the best way to be wrong. The other two ran and returned a number: counting orders per status after joining to line items gave 750 where the answer is 250 (each order was counted once per item), and “average order value” became the average product price, 15.5 against 78.015. Both look entirely plausible. Those two are the ones that matter, and only comparing results shows them.
Execution match has its own blind spot. Two different queries can return the same rows on your test data by coincidence. The March 2025 question passes with a different date test because all the orders happen to fall in 2025; on data spanning several years it would be wrong. Build the reference data so wrong answers produce visibly different results, and re-run the evaluation when the data or the model changes.
Going deeper: building a question set
Collect real questions people ask, write the reference SQL once by hand, and keep the set in version control as a regression test. Include the wrong-but-plausible traps in your own schema (joins that multiply rows, averages of averages, time zones) because those are what a real model gets wrong. Compare results as sets unless the question asks for an order, and normalise numbers so
12.50 and 12.5 match, as the test does. Running a real model many times against the set gives you a pass rate; the evaluation article shows how to gate a build on one without being fooled by a noisy judge.
EvaluationTest.java— the eight questions, the three scorers and the normalisation.ScriptedModel.java— the script that stands in for the model.
Should you even do this?
Yes for internal analytics questions on a curated slice of data; be careful anywhere else. The safest shape is a read-only role on a replica or a set of views built for this purpose, a short allow-list, a row cap and a timeout, with the generated SQL shown next to every answer so a person can check it. The remaining danger is not destruction but quiet wrongness, and a read-only role does nothing for that: measure it with a question set, and do not use unverified answers for decisions that matter. If the people asking must only see their own rows, you need row-level security or per-user connections; this article does not cover that. What I did not test: any real language model (how often it writes the wrong query, or is steered into a harmful one), a large schema, row-level security, views, a connection pooler, concurrency, other databases, and multi-turn follow-up questions. The model was a script; the database and its refusals were real.
Further reading
- The code for this article: the
text-to-sqlmodule of asmhatre/spring-ai, with all the transcripts underoutput/and its README. - Prompt injection defence in Spring AI — the same layering argument for tools and answers.
- Tool calling in Spring AI 2.0 — exposing “run a query” to a model as a tool.
- Structured output in Spring AI 2.0
- Testing LLM apps in Java — gating a build on a pass rate.
- Production RAG with Spring AI and choosing a vector store — retrieving the relevant tables for a large schema.
- Observability for Spring AI — counting refusals and timeouts as metrics.
- PostgreSQL 16: GRANT, client connection defaults, JSqlParser and OWASP: SQL injection
No Comments yet!