Claude
85a3359186
Co-Authored-By: Claude Sonnet 5.5 <[email protected]> Claude-Session: https://claude.ai/code/session_01JXVi2GMQ7bR5EmbUFdDj7N
47 lines
2.8 KiB
Markdown
47 lines
2.8 KiB
Markdown
# text-to-sql
|
|
|
|
Companion code for [Text-to-SQL with Spring AI, Done Safely: Read-Only Roles and Query Validation](https://ankurm.com/text-to-sql-spring-ai-read-only-roles-query-validation/), part of the [Spring AI series](../README.md) 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
|
|
|
|
```bash
|
|
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`](sql/schema.sql) | Five tables, deterministic data, the `t2s_reader` role (column-level grants, read-only by default, 2 s statement timeout) |
|
|
| [`SchemaPrompt.java`](src/main/java/com/ankurm/texttosql/SchemaPrompt.java) | Builds the schema part of the prompt from `information_schema` and COMMENTs, through the reader role |
|
|
| [`SqlGuard.java`](src/main/java/com/ankurm/texttosql/SqlGuard.java) | JSqlParser checks: one statement, SELECT only, listed tables and functions |
|
|
| [`QueryRunner.java`](src/main/java/com/ankurm/texttosql/QueryRunner.java) | Runs a query and returns at most N rows, reporting truncation |
|
|
| [`TextToSql.java`](src/main/java/com/ankurm/texttosql/TextToSql.java) | The pipeline: model, fence stripping, guard, database |
|
|
|
|
## Output files
|
|
|
|
| File | Written by |
|
|
|---|---|
|
|
| [`01-schema-prompt.txt`](output/01-schema-prompt.txt) | `SchemaPromptTest`: the prompt the model receives |
|
|
| [`02-attack-matrix.txt`](output/02-attack-matrix.txt) | `AttackMatrixTest`: sixteen queries against neither, the guard, the role, both |
|
|
| [`03-guard-cases.txt`](output/03-guard-cases.txt) | `GuardCasesTest`: what the guard accepts and refuses |
|
|
| [`04-limits.txt`](output/04-limits.txt) | `LimitsTest`: row cap and statement timeout |
|
|
| [`05-role.txt`](output/05-role.txt) | `RoleTest`: the two walls of the read-only role |
|
|
| [`06-evaluation.txt`](output/06-evaluation.txt) | `EvaluationTest`: string match versus execution match |
|