Rebasely Beta: data may be purged

justintsus-experiment Public

JavaScript · 114 lines · 6.2 KiB · at 8e57616

// 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"), []);  });});