justintsus-experiment Public
// SQL safety: every value bound, nothing from the input or ctx in the SQL text, injection attempts// inert, placeholders and params in step (BRIEF.md, "SQL contract").import assert from "node:assert/strict";import { describe, test } from "node:test";import { api, CTX, fixture, numbers, parsed, sqlOf } from "./helpers.mjs";const db = fixture();const tableCount = () => db.prepare("SELECT (SELECT COUNT(*) FROM issues) + (SELECT COUNT(*) FROM pull_index) AS n").get().n;const BEFORE = tableCount();// [query, target, markers that must never appear in where or orderBy, expected rows]const ALL_ISSUES = [7, 5, 4, 3, 2, 1, 8, 6];const ALL_PULLS = [6, 2, 1, 4, 3, 5];const PAYLOADS = [ ["zqx'); DROP TABLE issues; --", "issues", ["zqx", "DROP"], []], ['"zqx\' OR \'1\'=\'1"', "issues", ["zqx", "'1'"], []], ['"zqx\\" OR 1=1 --"', "issues", ["zqx", "1=1"], []], ['-"zqx\\" OR 1=1 --"', "issues", ["zqx", "1=1"], ALL_ISSUES], ["zqx%_\\", "issues", ["zqx"], []], ['"zqx) OR (1=1) OR ("', "issues", ["zqx", "1=1"], []], ["zqx' UNION SELECT number FROM pull_index --", "pulls", ["zqx", "UNION"], []], ["author:zqxbot", "issues", ["zqxbot"], []], ["-author:zqxbot", "pulls", ["zqxbot"], ALL_PULLS], ["review-requested:zqxbot", "pulls", ["zqxbot"], []], ["label:zqx-label", "issues", ["zqx"], []], ["-label:zqx-label", "issues", ["zqx"], ALL_ISSUES], ["#123456789", "issues", ["123456789"], []], ["zqx\u0000; DELETE FROM issues", "issues", ["zqx", "DELETE"], []], ["zqxüñï🐛", "issues", ["zqx", "🐛"], []],];function inert([q, target, markers, want]) { const s = sqlOf(parsed(q, target)); for (const m of markers) { assert.ok(!s.where.includes(m), `${q}: where contains ${JSON.stringify(m)}: ${s.where}`); assert.ok(!s.orderBy.includes(m), `${q}: orderBy contains ${JSON.stringify(m)}`); } const marker = markers[0]; assert.ok(s.params.some((p) => (typeof p === "string" ? p.includes(marker.slice(0, 3)) : String(p).includes(marker))), `${q}: the value travels as a param`); // It runs, matches what the spec says, and leaves the tables alone. assert.deepEqual(numbers(db, q, target), want, q); assert.equal(tableCount(), BEFORE);}describe("sql: injection attempts are bound, never interpolated", () => { test("free text and phrases carrying SQL", () => { for (const p of PAYLOADS.filter(([q, t]) => t === "issues" && !/^-?(author|label|review-requested):|^#/.test(q))) inert(p); }); test("qualifier values and numbers carrying markers", () => { for (const p of PAYLOADS.filter(([q]) => /^-?(author|label|review-requested):|^#/.test(q))) inert(p); }); test("pull request queries carrying SQL", () => { for (const p of PAYLOADS.filter(([, t]) => t === "pulls")) inert(p); });});describe("sql: shape", () => { test("no date or number from the input appears in the SQL text", () => { for (const [q, marks] of [["created:2026-10-01", ["2026"]], ["created:>2026-10-01 updated:<7d", ["2026"]], ["created:2026-09-30..2026-10-01", ["2026"]], ["#4242", ["4242"]], ["updated:>=9999h", ["9999"]]]) { const s = sqlOf(parsed(q)); for (const m of marks) assert.ok(!s.where.includes(m), `${q}: where contains ${m}: ${s.where}`); for (const p of s.params) if (typeof p === "number" && Math.abs(p) >= 10_000) assert.ok(!s.where.includes(String(p)), `${q}: ${p} is in the SQL text`); assert.ok(s.params.some((p) => typeof p === "number" && Math.abs(p) >= 1000), `${q}: the number or time is a param`); } }); test("ctx values are bound too, even when they contain quotes", () => { const ctx = { repoId: "r'1\" --", orgId: "o'); DROP TABLE issues; --", viewerId: "v'1", now: CTX.now }; const evil = fixture({ repoId: ctx.repoId, orgId: ctx.orgId }); for (const target of ["issues", "pulls"]) { const s = sqlOf(parsed("author:@me review-requested:@me".split(" ")[target === "issues" ? 0 : 1], target), ctx); for (const v of [ctx.repoId, ctx.orgId, ctx.viewerId, "r'1", "DROP"]) assert.ok(!s.where.includes(v), `${target}: where contains ${v}`); assert.ok(s.params.includes(ctx.repoId) && s.params.includes(ctx.orgId), "repoId and orgId are params"); } assert.deepEqual(numbers(evil, "", "issues", ctx), [7, 5, 4, 3, 2, 1, 8, 6]); assert.deepEqual(numbers(evil, "", "pulls", ctx), [6, 2, 1, 4, 3, 5]); }); test("where keeps its meaning when the caller ANDs more conditions after it", () => { // The helper always appends AND number > 0; a top-level OR without parentheses would leak rows. for (const q of ["author:@me", "-author:@me", "crash", "-is:closed", "label:bug -label:wontfix"]) numbers(db, q, "issues"); for (const q of ["author:@me", "-author:@me", "review-requested:@me", "-is:draft"]) numbers(db, q, "pulls"); const s = sqlOf(parsed("author:@me", "pulls")); const extra = db.prepare(`SELECT number FROM pull_index WHERE ${s.where} AND number = 1 ORDER BY ${s.orderBy}`).all(...s.params).map((r) => r.number); assert.deepEqual(extra, [1]); }); test("every query of a mixed corpus has as many placeholders as params, and a fixed orderBy", () => { const corpus = [ "", "is:open", "is:closed label:bug -label:wontfix no:label", 'label:"needs review" author:@me -author:fixer', "#3 crash -\"x y\" 100%", "created:>2026-10-01 created:<=2026-10-05 updated:<7d updated:>=1w created:2026-10-01..* created:*..2026-10-02", "sort:comments-asc", "sort:created-asc", "sort:updated-desc", "sort:updated-asc", ]; for (const q of corpus) sqlOf(parsed(q)); for (const q of ["is:merged is:draft -is:open review-requested:@me -review-requested:fixer author:@me", "sort:created-asc #2 x", ""]) sqlOf(parsed(q, "pulls")); }); test("toSql does not throw for odd but valid queries", () => { for (const q of ["-", '"\\\\"', "a\u0000b", "🐛", "%", "_", "\\", "'", "''", "?", "?1", "$1", ":x", "@x"]) { const s = api.toSql(parsed(q), CTX); assert.equal(typeof s.where, "string", q); numbers(db, q); } }); test("a ? or :name in the text is a value, not a placeholder", () => { const s = sqlOf(parsed("? ?1 :name @p $1")); assert.ok(!s.where.includes(":name") && !s.where.includes("@p") && !s.where.includes("$1")); assert.deepEqual(numbers(db, "? ?1 :name @p $1"), []); });});