Why model SQL must only read
Running model SQL safely means it can read and never change anything. A model could write DELETE or DROP by mistake, or because a user's question tricked it.
Module 4 teaches changing data on purpose. This lesson puts three locks in front of a model's query: a guard, limits and a read-only connection.
By the end of this lesson
Note
I can run a model's query so that it can only read, a little at a time.
Key words
| Word | What it means | Example |
|---|---|---|
| Guard | code that refuses a query before it runs | |
| Statement | one complete SQL command | SELECT title FROM books |
| Row limit | the most rows a query may return | |
| Timeout | the longest a query may run | |
| Read only | able to read rows but never change them |
The cells students fill in are left blank in this preview.
Allow one SELECT and nothing else
The guard tidies the query and drops one semicolon at the end. If another semicolon is left, there is a second statement, so it refuses.
The first word must be SELECT or WITH. Any write word, such as DELETE, DROP or UPDATE, is refused wherever it appears. So is a comment, which can hide text.
Worked example: what the guard says
| Query | Verdict | Reason |
|---|---|---|
| SELECT title FROM books WHERE year > 2020 | Allowed | One SELECT |
| SELECT title FROM books; DROP TABLE loans | Refused | More than one statement |
| DELETE FROM loans WHERE returned = 1 | Refused | Not a SELECT |
| SELECT title FROM books -- note | Refused | A comment |
The cells students fill in are left blank in this preview.
A row limit and a time limit
A correct query can still return a million rows or run for ever. Wrap it as SELECT * FROM (the query) LIMIT 5, so no more than five rows come back.
Run it in a separate process with a timeout. When time runs out the process is stopped, and the page answers instead of hanging.
Read-only access is the last lock
A guard checks words, and words can slip past. So open the database read only, or log in as a user who may only SELECT.
Never run model SQL on live data with write access. Use a read-only copy, so the worst a bad query can do is be slow.
A common mistake
Warning
Many people think a guard that checks the words is enough. A guard has gaps, because SQL has many ways to say one thing. Keep the read-only connection as well, so a missed query still cannot write.
Write the model helper, part 1
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const base = process.env.AI_URL || \"http://ai:4000\";' 'function count(data, name) {' ' return data.usage && typeof data.usage[name] === \"number\" ? data.usage[name] : null;' '}' '// A reply may wrap the query in a fenced block: keep what is inside it.' 'function sqlOf(text) {' ' const parts = text.split(String.fromCharCode(96).repeat(3));' ' const inner = (parts.length > 2 ? parts[1] : text).trim();' ' return inner.toLowerCase().startsWith(\"sql\") ? inner.slice(3).trim() : inner;' '}' > model.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the model helper, part 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'async function ask(messages, sample, max) {' ' try {' ' const res = await fetch(base + \"/v1/chat/completions\", {' ' method: \"POST\",' ' headers: { \"content-type\": \"application/json\", authorization: \"Bearer lab\" },' ' body: JSON.stringify({ model: \"lab\", messages: messages, max_tokens: max || 100 }),' ' signal: AbortSignal.timeout(30000),' ' });' ' if (res.ok === false) throw new Error(\"status \" + res.status);' ' const data = await res.json();' ' const text = data.choices[0].message.content;' ' return { text, inTokens: count(data, \"prompt_tokens\"), outTokens: count(data, \"completion_tokens\"), recorded: false };' ' } catch (error) {' ' console.log(\"Recorded sample: no model answered (\" + error.message + \").\");' ' return { text: sample, inTokens: null, outTokens: null, recorded: true };' ' }' '}' 'module.exports = { ask, sqlOf };' >> model.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the small library database, part 1
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const { DatabaseSync } = require(\"node:sqlite\");' '// An invented library: six books, four members, nine loans.' 'const BOOKS = [[1, \"The Salt Road\", 2019], [2, \"Glass Harbour\", 2021], [3, \"A Map of Rain\", 2015],' ' [4, \"Quiet Engines\", 2023], [5, \"Seven Kettles\", 2018], [6, \"North of Noon\", 2022]];' 'const MEMBERS = [[1, \"Asha\"], [2, \"Ben\"], [3, \"Chen\"], [4, \"Dara\"]];' '// id, book_id, member_id, month, returned (1 back, 0 still out)' 'const LOANS = [[1, 1, 1, 3, 1], [2, 2, 1, 3, 1], [3, 3, 2, 3, 0], [4, 1, 3, 3, 1], [5, 1, 1, 4, 0],' ' [6, 4, 2, 4, 0], [7, 2, 3, 4, 1], [8, 5, 2, 5, 0], [9, 2, 4, 5, 0]];' > library.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the small library database, part 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'function put(db, table, rows) {' ' const marks = rows[0].map(function () { return \"?\"; }).join(\", \");' ' const insert = db.prepare(\"INSERT INTO \" + table + \" VALUES (\" + marks + \")\");' ' rows.forEach(function (row) { insert.run(...row); });' '}' 'function open(file) {' ' const db = new DatabaseSync(file || \":memory:\");' ' db.exec(\"CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, year INTEGER)\");' ' db.exec(\"CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT)\");' ' db.exec(\"CREATE TABLE loans (id INTEGER PRIMARY KEY, book_id INTEGER REFERENCES books(id), \"' ' + \"member_id INTEGER REFERENCES members(id), month INTEGER, returned INTEGER)\");' ' put(db, \"books\", BOOKS);' ' put(db, \"members\", MEMBERS);' ' put(db, \"loans\", LOANS);' ' return db;' '}' 'module.exports = { open };' >> library.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the read-only runner
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const { DatabaseSync } = require(\"node:sqlite\");' '// Opened read only: even a query the guard missed cannot change a row.' 'const db = new DatabaseSync(\"library.db\", { readOnly: true });' 'try {' ' const rows = db.prepare(process.argv[2]).all().map(function (r) { return Object.values(r); });' ' console.log(\"rows \" + rows.length + \": \" + JSON.stringify(rows));' '} catch (error) { console.log(\"error: \" + error.message); }' > runone.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the guard script, part 1
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const { ask, sqlOf } = require(\"./model\");' 'const { open } = require(\"./library\");' 'const fs = require(\"node:fs\");' 'const { spawnSync } = require(\"node:child_process\");' 'fs.rmSync(\"library.db\", { force: true });' 'open(\"library.db\").close();' 'const BANNED = [\"INSERT\", \"UPDATE\", \"DELETE\", \"DROP\", \"ALTER\", \"CREATE\", \"ATTACH\", \"PRAGMA\", \"REPLACE\"];' 'const LIMIT = 5;' > guard.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the guard script, part 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'function guard(sql) {' ' let text = sql.trim();' ' if (text.endsWith(\";\")) text = text.slice(0, -1).trim();' ' if (text.indexOf(\";\") >= 0) return { ok: false, why: \"more than one statement\" };' ' if (text.indexOf(\"--\") >= 0 || text.indexOf(\"/*\") >= 0) return { ok: false, why: \"a comment\" };' ' const words = text.toUpperCase().split(/[^A-Z_]+/).filter(function (w) { return w.length > 0; });' ' if ([\"SELECT\", \"WITH\"].indexOf(words[0]) < 0) return { ok: false, why: \"not a SELECT\" };' ' const bad = words.filter(function (w) { return BANNED.indexOf(w) >= 0; });' ' if (bad.length) return { ok: false, why: \"forbidden word \" + bad[0] };' ' return { ok: true, sql: \"SELECT * FROM (\" + text + \") LIMIT \" + LIMIT };' '}' >> guard.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the guard script, part 3
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'function runOne(sql) {' ' const out = spawnSync(process.execPath, [\"--experimental-sqlite\", \"--no-warnings\", \"runone.js\", sql],' ' { encoding: \"utf8\", timeout: 2000 });' ' return out.error ? \"stopped: over 2 seconds\" : out.stdout.trim();' '}' 'function run(sql) {' ' const g = guard(sql);' ' return g.ok ? runOne(g.sql) : \"refused: \" + g.why;' '}' 'const N = \"WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n\";' >> guard.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the guard script, part 4
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'async function main() {' ' const tries = [\"SELECT title FROM books WHERE year > 2020\", \"SELECT title FROM books; DROP TABLE loans\",' ' \"DELETE FROM loans WHERE returned = 1\", N + \") SELECT COUNT(*) FROM n\",' ' N + \" WHERE i < 1000) SELECT i FROM n\"];' ' tries.forEach(function (sql, i) { console.log((i + 1) + \" \" + run(sql)); });' ' console.log(\"6 no guard, read-only file: \" + runOne(\"DELETE FROM loans\"));' ' const q = \"books(id, title, year)\nQuestion: Which books were published after 2020?\";' ' const sample = \"SELECT title FROM books WHERE year > 2020;\";' ' const reply = await ask([{ role: \"system\", content: \"Answer with one SQLite SELECT query and nothing else.\" },' ' { role: \"user\", content: q }], sample, 120);' ' const sql = sqlOf(reply.text);' ' console.log(\"model wrote: \" + sql);' ' console.log(\" through the guard: \" + run(sql));' '}' 'main();' >> guard.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Put six queries through the locks
docker exec -w /home/node/project agent node --experimental-sqlite --no-warnings guard.jsShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
What the Run prints
| Line | What it shows |
|---|---|
| 1 rows 3: [["Glass Harbour"],["Quiet Engines"],["North of Noon"]] | An allowed query |
| 2 refused: more than one statement | A hidden DROP |
| 3 refused: not a SELECT | A write |
| 4 stopped: over 2 seconds | A query that never ends |
| 5 rows 5: [[1],[2],[3],[4],[5]] | 1,000 rows cut to five |
| 6 no guard, read-only file: error: attempt to write a readonly database | The last lock holds |
The cells students fill in are left blank in this preview.