Claude
85a3359186
Co-Authored-By: Claude Sonnet 5.5 <[email protected]> Claude-Session: https://claude.ai/code/session_01JXVi2GMQ7bR5EmbUFdDj7N
2.8 KiB
text-to-sql
Companion code for Text-to-SQL with Spring AI, Done Safely: Read-Only Roles and Query Validation, part of the Spring AI series on ankurm.com.
A question goes to a model, the model writes SQL, and the SQL is checked, run as a restricted database role, capped and scored.
No language model was used, and no claim is made about any. ScriptedModel is a script: question in, SQL string out, some of it right and some wrong or hostile on purpose. It stands for "a model produced this string" so the code around the model can be tested. The database is real: PostgreSQL 16 on 127.0.0.1:5451, started by scripts/pg-up.sh with no Docker. Query results, error messages, timeouts and permission failures in output/ are PostgreSQL's own. The schema, data, attack queries and the eight evaluation questions are written for this module.
Versions
| Component | Version |
|---|---|
| Spring Boot | 4.1.1 (parent) |
| Spring AI | 2.0.1 (spring-ai-client-chat) |
| JSqlParser | 5.4 |
| PostgreSQL / JDBC driver | 16 (apt) / managed by Boot |
| Java | 25 (LTS) |
Quickstart
scripts/run-all.sh # starts PostgreSQL, runs the suite, regenerates output/01 .. 06
Needs to run where it can su postgres (root in the sandbox). Two consecutive runs produce byte-identical files.
What's here
| File | What it is |
|---|---|
schema.sql |
Five tables, deterministic data, the t2s_reader role (column-level grants, read-only by default, 2 s statement timeout) |
SchemaPrompt.java |
Builds the schema part of the prompt from information_schema and COMMENTs, through the reader role |
SqlGuard.java |
JSqlParser checks: one statement, SELECT only, listed tables and functions |
QueryRunner.java |
Runs a query and returns at most N rows, reporting truncation |
TextToSql.java |
The pipeline: model, fence stripping, guard, database |
Output files
| File | Written by |
|---|---|
01-schema-prompt.txt |
SchemaPromptTest: the prompt the model receives |
02-attack-matrix.txt |
AttackMatrixTest: sixteen queries against neither, the guard, the role, both |
03-guard-cases.txt |
GuardCasesTest: what the guard accepts and refuses |
04-limits.txt |
LimitsTest: row cap and statement timeout |
05-role.txt |
RoleTest: the two walls of the read-only role |
06-evaluation.txt |
EvaluationTest: string match versus execution match |