Second-order

// SCENARIO — a multi-step user flow. We focus on defensive engineering.

// ─── THE TRAP — safe insert, vulnerable later use ──────────────

// Step 1 — signup stores the username with a parameterised query (SAFE)
app.post('/signup', async (req, res) => {
    const { username, password } = req.body;
    const hash = await bcrypt.hash(password, 12);
    await db.execute(
        'INSERT INTO users (username, password_hash) VALUES (?, ?)',
        [username, hash],
    );
    res.sendStatus(201);
});

// Step 2 — later code reads the username back and CONCATENATES it (UNSAFE)
app.get('/me/posts', requireLogin, async (req, res) => {
    const me = await db.queryOne(
        'SELECT username FROM users WHERE id = ?', [req.user.id],
    );
    // ❌ String concat — the stored value is interpreted as SQL syntax
    const rows = await db.query(
        `SELECT id, title FROM posts WHERE author = '${me.username}' ORDER BY created_at DESC`,
    );
    res.json(rows);
});

// The attacker registered with a username crafted to look like SQL.
// The insert is safe. The second query treats the stored value as code.
// First-order tooling that watches user-tainted strings often misses this — by
// the time the value is used, it's coming from the DB, which looks 'trusted'.

// ─── FIX 1 — Parameterise EVERY query, including reads of stored data ──────

app.get('/me/posts', requireLogin, async (req, res) => {
    const rows = await db.query(
        `SELECT p.id, p.title
         FROM posts p
         JOIN users u ON u.username = p.author
         WHERE u.id = ?
         ORDER BY p.created_at DESC`,
        [req.user.id],
    );
    res.json(rows);
});

// Better still — don't denormalise. Store user_id on posts, not username.

// ─── FIX 2 — Validate stored values at the boundary ────────────

import { z } from 'zod';

const UsernameSchema = z.string()
    .min(3).max(32)
    .regex(/^[a-z0-9_]+$/i, 'Use letters, digits, and underscores only');

app.post('/signup', async (req, res) => {
    const parse = UsernameSchema.safeParse(req.body.username);
    if (!parse.success) return res.status(400).json({ error: parse.error.issues });
    /* ... continue with parsed value ... */
});

// Charsets like /^[a-z0-9_]+$/i can't contain quotes, semicolons, or comment markers,
// so even a future bug that concatenates them is far less dangerous.

// ─── FIX 3 — Treat stored data with the same suspicion as user input ──────

// Code review heuristic — any string that becomes SQL must be a bind parameter,
// regardless of where it came from. Don't trust 'comes from the DB' or 'comes
// from config'. Sources of tainted data:
//   • user input
//   • DB columns populated from user input
//   • config files in version-controlled repos (still concatenation == bug)
//   • response bodies from external services
//   • environment variables on shared infra

// ─── FIX 4 — Centralise the data layer ─────────────────────────

// One repository per aggregate. Raw SQL lives only there.
class PostRepository {
    constructor(db) { this.db = db; }

    listByAuthorId(userId) {
        return this.db.query(
            `SELECT id, title FROM posts WHERE author_id = ? ORDER BY created_at DESC`,
            [userId],
        );
    }

    search(authorId, term, limit = 50) {
        return this.db.query(
            `SELECT id, title FROM posts
             WHERE author_id = ? AND title ILIKE ?
             ORDER BY created_at DESC LIMIT ?`,
            [authorId, `%${term}%`, limit],
        );
    }
}

// Feature code that doesn't touch SQL directly can't introduce a new injection by accident.

// ─── FIX 5 — Least-privilege database account ──────────────────

// The app's DB user can only:
//   • SELECT on its own tables
//   • INSERT/UPDATE on its own tables
//   • CALL a specific set of stored procs
// It cannot:
//   • DROP, TRUNCATE, GRANT
//   • Read pg_shadow / mysql.user / other system tables
// A successful second-order injection against this account can't extract password hashes.

// ─── FIX 6 — Static and runtime checks ─────────────────────────

// Static — lint for SQL inside template literals
// .eslintrc.cjs
rules: {
    // example custom rule: forbid template literals that start with SQL keywords
    'no-restricted-syntax': ['error', {
        selector: "TemplateLiteral[quasis.0.value.raw=/SELECT |INSERT |UPDATE |DELETE /i]",
        message: 'Use parameterised queries, not template literals.',
    }],
}

// Runtime — log + alert on dangerous shape
function safeQuery(sql, params) {
    if (/'[^']*'/.test(sql)) {
        log.warn({ sql }, 'literal quoted strings inside SQL — likely concatenation');
    }
    return db.query(sql, params);
}

// ─── REGRESSION TESTS ──────────────────────────────────────────

import request from 'supertest';

test('stored values do NOT influence later queries unsafely', async () => {
    const probes = [
        "alice'-- ",
        "alice' OR 1=1-- ",
        "alice'; DROP TABLE posts;-- ",
    ];
    for (const u of probes) {
        const r1 = await request(app).post('/signup').send({ username: u, password: 'x' });
        // Validation should reject these BEFORE storage in a hardened app.
        // If they slip through validation, the subsequent reads should still be safe.
        expect([400, 201]).toContain(r1.status);
        if (r1.status === 201) {
            const r2 = await request(app)
                .get('/me/posts')
                .set('Cookie', `session=${sessionFor(u)}`);
            expect(r2.status).toBeLessThan(500);
            const posts = await db.query('SELECT id FROM posts LIMIT 1');
            expect(posts.length).toBeGreaterThanOrEqual(0);  // table not dropped
        }
    }
});

// ─── CHECKLIST ─────────────────────────────────────────────────
// 1. Parameterise on writes AND reads — no exceptions
// 2. Validate stored fields at the boundary (Zod, etc.)
// 3. Repository layer is the only code that touches SQL strings
// 4. Lint blocks SQL in template literals with interpolation
// 5. Low-privilege DB account
// 6. Audit log + alerts for unusual stored values appearing in SQL warnings
// 7. Regression tests cover storage → retrieval round-trips

// Input is stored safely now, then later used unsafely in another query.
// Audit ALL query sites, not just "the obvious" places.