Data and security all 2 written

example.mjs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26import { DatabaseSync } from "node:sqlite";
import { existsSync } from "node:fs";

const db = new DatabaseSync("shop.db");     // the file is created if it does not exist

db.exec(`
  CREATE TABLE products (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    price INTEGER NOT NULL           -- in pence: never store money as a float
  )
`);

const insert = db.prepare("INSERT INTO products (name, price) VALUES (?, ?)");
console.log(insert.run("Tea", 350));
console.log(insert.run("Coffee", 420));
insert.run("Cake", 275);

const rows = db.prepare("SELECT * FROM products ORDER BY price").all();
console.log(rows);

db.close();
console.log("shop.db exists:", existsSync("shop.db"));

check("three rows, cheapest first", rows.map((r) => r.name), ["Cake", "Tea", "Coffee"]);
check("each row is a plain record of the columns", { ...rows[0] }, { id: 3, name: "Cake", price: 275 });

example.mjs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");     // a database in memory, gone at exit
db.exec(`
  CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price INTEGER NOT NULL, category TEXT);
  INSERT INTO products (name, price, category) VALUES
    ('Tea', 350, 'drinks'), ('Coffee', 420, 'drinks'), ('Cake', 275, 'food'), ('Soup', 590, 'food');
`);

// get(): the first row, or undefined.
const byId = db.prepare("SELECT name, price FROM products WHERE id = ?");
console.log("id 2:", byId.get(2));
console.log("id 99:", byId.get(99));

// Named parameters, for readability.
const cheaper = db.prepare("SELECT name FROM products WHERE category = :category AND price < :max");
console.log("drinks under 400:", cheaper.all({ category: "drinks", max: 400 }).map((r) => r.name));

// SQL does the adding up.
const totals = db.prepare(`
  SELECT category, count(*) AS items, avg(price) AS average
  FROM products GROUP BY category ORDER BY category
`).all();
console.log(totals);

check("a missing row is undefined, not an error", byId.get(99), undefined);
check("named parameters matched", cheaper.all({ category: "drinks", max: 400 }).map((r) => r.name), ["Tea"]);
check("the database did the arithmetic", totals.map((t) => [t.category, t.items, t.average]),
      [["drinks", 2, 385], ["food", 2, 432.5]]);

example.mjs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");
db.exec(`
  CREATE TABLE users  (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE);
  CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id));
`);
db.prepare("INSERT INTO users (email) VALUES (?)").run("ada@example.com");

const refused = [];
function attempt(label, sql, ...values) {
  try {
    db.prepare(sql).run(...values);
    console.log(`${label}: accepted`);
  } catch (err) {
    refused.push(err.errcode);
    console.log(`${label}: ${err.code} ${err.errcode}, "${err.message}"`);
  }
}

attempt("same email twice ", "INSERT INTO users (email) VALUES (?)", "ada@example.com");
attempt("no email at all  ", "INSERT INTO users (email) VALUES (?)", null);
attempt("order for user 99", "INSERT INTO orders (user_id) VALUES (?)", 99);
attempt("order for user 1 ", "INSERT INTO orders (user_id) VALUES (?)", 1);

console.log("foreign keys switched on:", db.prepare("PRAGMA foreign_keys").get().foreign_keys === 1);

check("UNIQUE, NOT NULL and the foreign key each refused a bad row", refused, [2067, 1299, 787]);

example.mjs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");
db.exec(`
  CREATE TABLE loose  (age INTEGER);
  CREATE TABLE strict (age INTEGER) STRICT;
`);
const outcomes = {};
function attempt(label, work) {
  try {
    const result = work();
    outcomes[label] = "ok";
    console.log(`${label.padEnd(34)} ok${result === undefined ? "" : ", " + JSON.stringify(result)}`);
  } catch (err) {
    outcomes[label] = err.name;
    console.log(`${label.padEnd(34)} ${err.name}: ${err.message}`);
  }
}

attempt("text into a loose INTEGER column", () => {
  db.prepare("INSERT INTO loose (age) VALUES (?)").run("not a number");
  return { ...db.prepare("SELECT age, typeof(age) AS stored_as FROM loose").get() };
});
attempt("text into a STRICT INTEGER column", () => db.prepare("INSERT INTO strict (age) VALUES (?)").run("not a number"));
attempt("a boolean", () => db.prepare("INSERT INTO strict (age) VALUES (?)").run(true));
attempt("undefined", () => db.prepare("INSERT INTO strict (age) VALUES (?)").run(undefined));
attempt("an integer past 2 ** 53", () => db.prepare("SELECT 9007199254740993 AS big").get());

const big = db.prepare("SELECT 9007199254740993 AS big");
big.setReadBigInts(true);
console.log("the same, read as a BigInt:", big.get().big);

check("an ordinary table stored text in an INTEGER column", outcomes["text into a loose INTEGER column"], "ok");
check("a STRICT one refused it", outcomes["text into a STRICT INTEGER column"], "Error");
check("booleans and undefined cannot be stored", [outcomes["a boolean"], outcomes["undefined"]], ["TypeError", "TypeError"]);
check("a number too big for JavaScript is an error, not a wrong answer", outcomes["an integer past 2 ** 53"], "RangeError");
check("BigInt reads it exactly", big.get().big, 9007199254740993n);

example.mjs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");
db.exec(`
  CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0)) STRICT;
  INSERT INTO accounts VALUES ('ada', 100), ('alan', 50);
`);

function transaction(db, work) {
  db.exec("BEGIN");
  try {
    const result = work();
    db.exec("COMMIT");
    return result;
  } catch (err) {
    db.exec("ROLLBACK");            // undo everything since BEGIN
    throw err;
  }
}

const change = db.prepare("UPDATE accounts SET balance = balance + ? WHERE name = ?");
function transfer(from, to, amount) {
  transaction(db, () => {
    change.run(amount, to);         // pay in first...
    change.run(-amount, from);      // ...then take out, which the CHECK may refuse
  });
}
const balances = () => db.prepare("SELECT name, balance FROM accounts ORDER BY name").all()
  .map((row) => `${row.name} ${row.balance}`).join(", ");

transfer("ada", "alan", 30);
console.log("after sending 30: ", balances());

let refusal;
try {
  transfer("ada", "alan", 500);
} catch (err) {
  refusal = err.message;
  console.log("sending 500 failed:", refusal);
}
console.log("after the failure:", balances());

check("the second transfer was refused by the CHECK", refusal, "CHECK constraint failed: balance >= 0");
check("and changed nothing: alan was not paid the 500", balances(), "ada 70, alan 80");

example.mjs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("events.db");
db.exec("CREATE TABLE events (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
const insert = db.prepare("INSERT INTO events (name) VALUES (?)");

let started = performance.now();
for (let i = 0; i < 1000; i++) insert.run(`event ${i}`);
const separately = performance.now() - started;

started = performance.now();
db.exec("BEGIN");
for (let i = 0; i < 1000; i++) insert.run(`event ${i}`);
db.exec("COMMIT");
const together = performance.now() - started;

console.log(`1,000 inserts, each its own transaction: ${separately.toFixed(0)} ms`);
console.log(`1,000 inserts in one transaction:        ${together.toFixed(1)} ms`);
console.log(`about ${Math.round(separately / together)} times faster`);

check("both wrote every row", db.prepare("SELECT count(*) AS n FROM events").get().n, 2000);
check("one transaction was at least ten times faster", separately / together > 10, true);

example.mjs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");
db.exec(`
  CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, item TEXT NOT NULL, card TEXT NOT NULL);
  INSERT INTO orders (customer, item, card) VALUES
    ('ada', 'tea', '**** 4242'), ('alan', 'coffee', '**** 1881'), ('grace', 'cake', '**** 0007');
`);

// Unsafe: the customer's text is pasted into the SQL.
function ordersUnsafe(customer) {
  return db.prepare(`SELECT customer, item, card FROM orders WHERE customer = '${customer}'`).all();
}
// Safe: the SQL is fixed, and the value travels separately.
function orders(customer) {
  return db.prepare("SELECT customer, item, card FROM orders WHERE customer = ?").all(customer);
}
const show = (rows) => rows.map((r) => `${r.customer}: ${r.item}, ${r.card}`);

const attack = "nobody' OR '1'='1";
console.log("the SQL the unsafe version builds:");
console.log(`  WHERE customer = '${attack}'`);
console.log("unsafe, for ada:     ", show(ordersUnsafe("ada")));
console.log("unsafe, for the attacker:", show(ordersUnsafe(attack)));

let broke;
try { ordersUnsafe("o'brien"); } catch (err) { broke = err.message; }
console.log("unsafe, for o'brien: ", broke);

console.log("safe, for the attacker:  ", show(orders(attack)));
console.log("safe, for o'brien:   ", show(orders("o'brien")));

check("the attacker got every customer's orders", ordersUnsafe(attack).length, 3);
check("an apostrophe in a real name broke the unsafe query", typeof broke, "string");
check("with a placeholder the attack is just a name nobody has", orders(attack), []);

public/index.html

1<h1>Welcome</h1>

.env

1DB_PASSWORD=pretend-password-123

example.mjs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46import { createServer } from "node:http";
import { once } from "node:events";
import { readFile } from "node:fs/promises";
import { resolve, sep } from "node:path";

const ROOT = resolve("public");

// Turn a URL path into a file inside ROOT, or refuse.
function fileFor(urlPath) {
  let decoded;
  try {
    decoded = decodeURIComponent(urlPath);
  } catch {
    return { status: 400 };                       // "%zz" is not valid encoding
  }
  const file = resolve(ROOT, "." + decoded);      // resolve does all the ".." walking
  if (!file.startsWith(ROOT + sep)) return { status: 403 };
  return { file };
}

const server = createServer(async (req, res) => {
  const { file, status } = fileFor(new URL(req.url, "http://localhost").pathname);
  if (!file) {
    res.writeHead(status);
    return res.end();
  }
  try {
    res.end(await readFile(file));
  } catch {
    res.writeHead(404);
    res.end();
  }
});
server.listen(0, "127.0.0.1");
await once(server, "listening");
const base = `http://127.0.0.1:${server.address().port}`;

const statuses = [];
for (const path of ["/index.html", "/..%2f.env", "/%2e%2e%2f.env", "/css/..%2f..%2f.env", "/%zz"]) {
  const res = await fetch(base + path);
  statuses.push(res.status);
  console.log(`${path.padEnd(20)} ${res.status}  ${(await res.text()).trim()}`);
}
server.close();

check("the page is served, and every escape is refused", statuses, [200, 403, 403, 403, 400]);

example.mjs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47// A JSON body, as an attacker would send it.
const body = JSON.parse('{ "theme": "light", "__proto__": { "isAdmin": true } }');
console.log("the parsed body's keys:", Object.keys(body));

// 1. Copied onto one object.
const todo = Object.assign({ id: 1 }, body);
console.log("todo.isAdmin:", todo.isAdmin);

// 2. Merged, with a hand-written deep merge.
function merge(target, source) {
  for (const key of Object.keys(source)) {
    if (typeof source[key] === "object" && source[key] !== null) {
      target[key] ??= {};
      merge(target[key], source[key]);
    } else {
      target[key] = source[key];
    }
  }
  return target;
}
merge({ theme: "dark" }, body);

const someoneElse = { name: "Grace" };        // created afterwards, never touched
console.log("someoneElse.isAdmin:", someoneElse.isAdmin);
console.log("[].isAdmin:", [].isAdmin);

check("Object.assign replaced that object's prototype", Object.getPrototypeOf(todo).isAdmin, true);
check("the merge made every object in the process an admin", someoneElse.isAdmin, true);

// The fix: never walk into keys that reach the prototype.
delete Object.prototype.isAdmin;
const FORBIDDEN = new Set(["__proto__", "constructor", "prototype"]);
function safeMerge(target, source) {
  for (const key of Object.keys(source)) {
    if (FORBIDDEN.has(key)) continue;
    if (typeof source[key] === "object" && source[key] !== null) {
      target[key] ??= {};
      safeMerge(target[key], source[key]);
    } else {
      target[key] = source[key];
    }
  }
  return target;
}
console.log("after safeMerge:", safeMerge({ theme: "dark" }, body), ({}).isAdmin);

check("safeMerge copied the theme and nothing else", ({}).isAdmin, undefined);

example.mjs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37import { scrypt, randomBytes, timingSafeEqual, createHash } from "node:crypto";
import { promisify } from "node:util";

const scryptAsync = promisify(scrypt);

async function hashPassword(password) {
  const salt = randomBytes(16);                     // different for every user
  const key = await scryptAsync(password, salt, 64);
  return `scrypt:${salt.toString("base64")}:${key.toString("base64")}`;
}

async function verifyPassword(password, stored) {
  const [, salt, key] = stored.split(":");
  const attempt = await scryptAsync(password, Buffer.from(salt, "base64"), 64);
  return timingSafeEqual(attempt, Buffer.from(key, "base64"));
}

let started = performance.now();
const stored = await hashPassword("correct horse battery staple");
const scryptMs = performance.now() - started;
console.log("stored:", stored);
console.log(`one scrypt hash took ${scryptMs.toFixed(0)} ms`);

const again = await hashPassword("correct horse battery staple");
const right = await verifyPassword("correct horse battery staple", stored);
const wrong = await verifyPassword("Correct horse battery staple", stored);
console.log("same password, same hash?", again === stored);
console.log("right password:", right, " wrong one:", wrong);

started = performance.now();
for (let i = 0; i < 1000; i++) createHash("sha256").update(`guess ${i}`).digest();
const shaMs = (performance.now() - started) / 1000;
console.log(`one SHA-256 hash took ${shaMs.toFixed(4)} ms`);

check("the salt makes the same password store differently", again === stored, false);
check("verify accepts the right password and rejects a near miss", [right, wrong], [true, false]);
check("scrypt is over a thousand times slower than SHA-256, on purpose", scryptMs / shaMs > 1000, true);