Allow-list for Identifiers

// 1) The dangerous default — string concatenation
// BAD (Node, pg)
const sql = `SELECT * FROM orders ORDER BY ${req.query.sort} ${req.query.dir}`;
await db.query(sql);   // sort=DROP_TABLE_USERS-- destroys you

// 2) Allowlist — map untrusted input to a known-safe value
const SORT_COLUMNS = {
    id:        'id',
    created:   'created_at',
    total:     'total',
    customer:  'customer_id',
};
const DIRECTIONS = { asc: 'ASC', desc: 'DESC' };

const sort = SORT_COLUMNS[req.query.sort] ?? 'created_at';
const dir  = DIRECTIONS[req.query.dir]    ?? 'DESC';

const safeSql = `SELECT * FROM orders WHERE user_id = $1 ORDER BY ${sort} ${dir} LIMIT 50`;
await db.query(safeSql, [req.user.id]);

// 3) Dynamic table names — allowlist them too
const TABLES = new Set(['orders', 'invoices', 'shipments']);
if (!TABLES.has(req.params.entity)) return res.status(404).end();
const sql2 = `SELECT count(*) FROM ${req.params.entity}`;

// 4) Search filters — parameterise + allowlist the field
const FILTERS = {
    email:  ['email',  'ILIKE'],
    status: ['status', '='],
    region: ['region', '='],
};

const where = [];
const params = [];
for (const [key, value] of Object.entries(req.query.filter ?? {})) {
    const spec = FILTERS[key];
    if (!spec) continue;
    const [col, op] = spec;
    params.push(op === 'ILIKE' ? `%${value}%` : value);
    where.push(`${col} ${op} $${params.length}`);
}
const whereSql = where.length ? `WHERE ${where.join(' AND ')}` : '';
await db.query(`SELECT * FROM users ${whereSql} LIMIT 100`, params);

// 5) ORMs that handle this — Drizzle, Prisma, Knex
// Drizzle:
await db.select().from(users)
    .where(sql.raw`${columns.email} ILIKE ${'%${term}%'}`)   // identifier is safe (typed), value is parameter
    .orderBy(columns[sortKey]);

// Prisma — orderBy is a typed object, can't be a free-form string
await prisma.user.findMany({
    orderBy: { [SORT_COLUMNS[sortKey] ?? 'createdAt']: dir.toLowerCase() },
});

// 6) DON'T do these
// pg.escape(input) ... `WHERE x = ${input}`     // string escaping is fragile
// `WHERE x = '${input.replace("'","''")}'`     // misses many edge cases / drivers
// Stored procedure with dynamic SQL inside (sp_executesql in T-SQL) without parameters

// 7) Logging — never log raw queries with user input
// log.info(`Running: ${sql}`)  → goes into SIEM, becomes a confused-deputy

// You can't parameterise identifiers (column / table names / direction).
// Use an allow-list:
const sortable = { name: 'name', age: 'age' };
const col = sortable[req.query.sort] ?? 'name';
const dir = req.query.dir === 'desc' ? 'DESC' : 'ASC';
await db.query(`SELECT * FROM users ORDER BY ${col} ${dir}`);