Topic 12.5
Tables and Databases
In one line
Questions such as "how many rentals did each shop have?" are answered by a query, not by a passage. A model can write the SQL, but its output is untrusted: run it only through a read-only role, an allow-list of tables and a validator.
Think of it like this
Asking a librarian "how many books about bikes do you have?" A good librarian does not read you a page. She counts the cards. Some questions are answered by counting, adding or filtering rows, and a pile of text passages cannot do that reliably.
Words you'll meet
New words in this topic, in plain English. Come back here whenever one feels fuzzy.
- Text-to-SQL
- Having a model write a SQL query from a question.
- Aggregation
- Combining many rows into one number: count, sum, average, maximum.
- Read-only role
- A database user that is allowed to read but not to change anything.
- Allow-list
- A list of the only things permitted, such as tables. Anything not on it is refused.
- Validator
- Code that checks a generated query before it is run.
- View
- A saved query that behaves like a table. Useful to expose only safe columns.
- Statement timeout
- A limit after which the database stops a running query.
Step by step
01Step 1: a question text search cannot answer
"How many rentals did each shop have?" is a count over a table. No passage contains the answer. A query does, exactly.
02Step 2: the model writes a query
You give the model the question and the schema of the allowed tables. It returns a SQL string. In this lesson the strings are fixed examples, some good and some bad on purpose.
03Step 3: never trust the string
The string goes to a validator first. Only the validator's approved version, with a row limit, goes on. The example runs seven strings through it: a good one, one that already has a limit, a DELETE, a table not on the list, two statements joined by a semicolon, a comment, and a sub-select into a forbidden table.
04Step 4: read what was rejected and why
Each rejection names the rule that failed. That message is useful in logs and in a retry (ask the model to try again with the error), and it must never include any data.
05Step 5: the database is the real wall
Suppose a query passed the validator by mistake. The read-only role still cannot write. The statement timeout still stops a runaway query. Say out loud which layer would stop each bad query, and fix any that only one layer stops.
06Step 6: put the numbers into the answer
Give the model the result rows and the query text, ask it to state the number and say which query produced it. Do not ask it to do arithmetic on large result sets: let the database do it.
Try it yourself
- 1
Add a bad query
Add
"SELECT * FROM rentals WHERE note = 'update'"to the list. What happens, and is it a correct rejection? Discuss what a real parser would do. - 2
Allow a new table
Add a
pricestable toALLOWED_TABLES. Which of the earlier inputs change? What must also change in the database for this to be safe? - 3
Change the limit
Set
MAX_ROWSto 5. Which output lines change? What would you want to happen when a query would return more than the limit?
Code & diagrams
The queries are plain strings and nothing here talks to a database. A word-based check is a teaching version of Layer 2. In production a SQL parser and a read-only role do this job.
Expected output
SELECT shop, COUNT(*) FROM rentals GROUP BY shop
ok: SELECT shop, COUNT(*) FROM rentals GROUP BY shop LIMIT 100
SELECT name FROM shops LIMIT 5
ok: SELECT name FROM shops LIMIT 5
DELETE FROM rentals
rejected: only SELECT is allowed
SELECT * FROM users
rejected: table not allowed: users
SELECT id FROM rentals; DROP TABLE rentals
rejected: more than one statement
SELECT id FROM rentals -- and more
rejected: comments are not allowed
SELECT shop FROM shops WHERE id IN (SELECT id FROM secrets)
rejected: table not allowed: secretsBreak it on purpose
Errors are the best teachers. Make each change, read the error, guess what went wrong, then reveal the answer.
Break #1
Check only the first word
In the Python tab, delete the lines that check FORBIDDEN and the table allow-list (the two for loops after the SELECT test), keeping the rest. Run.
Myth vs fact
Myth
Telling the model "only write SELECT" is enough.
Fact
A prompt is a request. Only the database role and a validator are rules. Models make mistakes and can be steered by the text they read.
Myth
A word filter makes arbitrary SQL safe.
Fact
Word filters are crude and miss clever syntax. Use a parser as well and always keep the read-only role.
Myth
Embedding table rows is enough for questions about data.
Fact
It works for lookups and fails for counts, sums and trends. Those need a query.
Pro corner
Extra depth for experienced readers. New to this? Skip it for now and come back later.
- ▸
Log every generated query, its verdict and its row count. They are your audit trail and, later, a source of test cases for the validator.
- ▸
Per-user data: create views or use row-level security so the database itself filters rows by the caller. A prompt that says "only show this user's rows" is not an access rule (Topic 10.3).
- ▸
Return errors from the validator to the model once for a retry, with a retry limit. Never return database error text that mentions schema details the user should not see.
- ▸
Word matching misses forms a real parser sees: schema-qualified or quoted names (
public.users,"users"), functions that read files or sleep, and a cross join that multiplies two big tables. Each is stopped by the role (no privileges on other schemas or functions) and the statement timeout, not by the validator. Test the role itself: log in as it and try the bad queries.
Remember this
- 1
Why this exists. Embedding a table row as text works for lookups ("what does the monthly pass cost?") and fails for aggregation: counts, sums, averages, "the most", "since March". A model reading ten retrieved rows cannot add up ten thousand. The data already lives in a database that can do this exactly. The pattern is text-to-SQL: a model turns the question into a SQL query, the database runs it, and the rows (or a number) are given to the model to phrase the answer.
- 2
The model's SQL is untrusted input. The model reads text that may come from users and documents, and it can be steered or simply wrong. Treat what it writes the way you treat a form field typed by a stranger. Never run it with the application's normal database account. This topic is entirely about defence, and the examples only parse and judge a SQL string; they never run it.
- 3
Layer 1: a read-only role. Create a database user that can only
SELECT, and only from the tables and views you list. Then even a query that slips through every check cannot change or drop anything. This is the layer that must not fail, because it is enforced by the database and not by your code. Prefer views that expose only the safe columns, and set a statement timeout and a row limit on the role. - 4
Layer 2: an allow-list and a validator. Before running anything, check the string: exactly one statement, no comments, it starts with
SELECT, no forbidden words, and every table afterFROMorJOINis on the allow-list. The example rejects five of its seven test strings, accepts two, and appendsLIMIT 100when the query has none. A word-based check is crude: it can reject a harmless query (a column namedupdate) and it can be fooled by clever syntax. That is why it sits on top of the read-only role and never replaces it. A real SQL parser is better than word matching. - 5
Layer 3: describe only what is allowed. Give the model the schema of the allowed tables and nothing else, so it cannot ask about tables it has never heard of. Keep user-specific rules in the database role or in a mandatory filter (for example a view per tenant), not in the prompt: a prompt is a request, a database permission is a rule (Topic 10.3).
- 6
Mixing rows and passages. Many questions need both: "which shop had the most late returns, and what is the late fee?" The first half is a query, the second is a passage (D6). Route each part (Topic 7.6), run the query through the safe path, retrieve the passage, and give the model both, labelled as different kinds of source. Cite the query and the row count as the source of a number.
Explain it without notes
Why are aggregation questions a poor fit for embedded text?
Why is a model's SQL treated as untrusted, and what are the layers of defence?
What does your validator check and what can it miss?
How do you combine a SQL result with retrieved passages in one answer?
Practice
Write add_limit(sql, max_rows) that appends LIMIT <max_rows> when the statement has no LIMIT word (any letter case) and returns it unchanged otherwise. Print the result for two statements.
Write tables_in(sql) that returns the sorted list of table names that follow FROM or JOIN (any letter case). Print it for a query with a join and for a query with a sub-select.
Trade-offs
- ↔
Text-to-SQL: exact answers and a new attack surface.
- ↔
A strict allow-list: safe and limiting. A wide one: flexible and riskier.
- ↔
Views per tenant: strong isolation and more objects to maintain.
Done when you can
Done when you can say why aggregation needs a query and not retrieved text.
Done when you can list the layers that protect a database from model-written SQL.
Done when you can write a validator that checks statement count, start word, comments and tables.
Done when you can say what the validator cannot catch and what covers it.