Time-based

// SCENARIO — a search endpoint, defensive perspective

// ─── VULNERABLE — DO NOT SHIP ──────────────────────────────────

// Node + raw MySQL string concat
app.get('/products', async (req, res) => {
    const q = req.query.q || '';
    const rows = await conn.query(`SELECT id, name FROM products WHERE name LIKE '%${q}%'`);
    res.json(rows);
    // ❌ A scanner can append: ' AND IF(SUBSTRING(@@version,1,1)='8', SLEEP(5), 0)-- -
    //    If the response takes ~5s, the version starts with 8.
    //    Repeat character-by-character to extract entire tables.
});

// ─── FIX 1 — Parameterized queries ─────────────────────────────
app.get('/products', async (req, res) => {
    const q = req.query.q || '';
    const rows = await conn.execute(
        'SELECT id, name FROM products WHERE name LIKE ?',
        [`%${q}%`],
    );
    res.json(rows);
    // ✓ The driver sends the query and bound value separately to the DB.
    //   The DB never parses user input as SQL syntax.
});

// PostgreSQL (pg)
await pg.query('SELECT id, name FROM products WHERE name ILIKE $1', [`%${q}%`]);

// SQLite (better-sqlite3)
db.prepare('SELECT id, name FROM products WHERE name LIKE ?').all(`%${q}%`);

// Python (psycopg)
cur.execute('SELECT id, name FROM products WHERE name ILIKE %s', (f'%{q}%',))

// PHP (PDO)
$stmt = $pdo->prepare('SELECT id, name FROM products WHERE name LIKE ?');
$stmt->execute(["%{$q}%"]);

// Laravel query builder / Eloquent
Product::where('name', 'like', "%{$q}%")->get();

// ─── FIX 2 — Identifiers (table/column names) ──────────────────
// Parameter binding works for VALUES, not identifiers.
// If users pick the sort column, use an allowlist:

const SORT_COLS = new Set(['name', 'price', 'created_at']);
const sort = SORT_COLS.has(req.query.sort) ? req.query.sort : 'created_at';
await conn.execute(`SELECT * FROM products ORDER BY ${sort} LIMIT 50`);
// ✓ \\${sort} only ever comes from the allowlist.

// ─── FIX 3 — Least-privilege DB account ────────────────────────
// The app's DB user should not have:
//   • DROP, ALTER, TRUNCATE on production tables
//   • Access to system tables it doesn't read
//   • Permission to write to its own auth tables (use a separate signup role)
// A SQL injection on a low-priv account is far less catastrophic.

// ─── FIX 4 — Statement timeouts ────────────────────────────────
// Time-based exfil relies on long-running SLEEP. Cap query duration.

// Postgres
await pg.query(`SET statement_timeout = '2s'`);          // per session
// or per-database: ALTER DATABASE app SET statement_timeout = '2s';

// MySQL
await conn.execute('SET SESSION MAX_EXECUTION_TIME=2000');

// Express request timeout — kill stuck requests too
app.use((req, res, next) => {
    req.setTimeout(5000, () => res.status(504).end());
    next();
});

// ─── FIX 5 — WAF / detection signals ───────────────────────────
// Watch for these patterns in access logs (high false-positive rate, use carefully):
//   • SLEEP(, BENCHMARK(, pg_sleep(, WAITFOR DELAY
//   • Response times > 3s on endpoints that normally return in < 200 ms
//   • Same client IP making many slow requests with varying ?q= values
//   • Single-quote or comment markers (--, /*) in fields that don't need them

const SUSPICIOUS = /\b(sleep|benchmark|pg_sleep|waitfor)\s*\(/i;
app.use((req, res, next) => {
    const blob = JSON.stringify({ q: req.query, b: req.body });
    if (SUSPICIOUS.test(blob)) {
        log.warn({ ip: req.ip, blob }, 'possible sqli probe');
        // Don't block silently — return a normal 400 so probing data is noisy
    }
    next();
});

// ─── FIX 6 — ORM-only repository layer ─────────────────────────
// If a repository module is the ONLY place that touches the DB, raw concat
// can't sneak in from a feature module by accident.

class ProductRepository {
    async search(q) {
        return Product.query()
            .where('name', 'ilike', `%${q}%`)
            .orderBy('created_at', 'desc')
            .limit(50);
    }
}

// ─── REGRESSION TESTS ──────────────────────────────────────────
import request from 'supertest';

test('search endpoint rejects sleep-style probes safely', async () => {
    const probes = [
        "' OR SLEEP(2)-- -",
        "%' AND pg_sleep(2)-- -",
        "1; WAITFOR DELAY '0:0:2'-- ",
    ];
    for (const p of probes) {
        const start = Date.now();
        const res = await request(app).get('/products').query({ q: p });
        const ms  = Date.now() - start;
        expect(res.status).toBeLessThan(500);
        expect(ms).toBeLessThan(1000);             // no time-based signal
    }
});

// ─── CHECKLIST ─────────────────────────────────────────────────
// 1. ALL queries use bind parameters; lint for string concat in SQL
// 2. Identifiers come from allowlists, never user input
// 3. DB account has read-only / write-narrow privileges per service
// 4. statement_timeout set globally (1-5s for OLTP)
// 5. Request timeout on the web layer
// 6. Logging + alerting on slow responses to normally-fast endpoints

// Attacker triggers SLEEP() to detect injectability via response time.
// Defence is unchanged — parameterise + limit query-time at the gateway.