Injection Attacks - SQLi, Command & Template Injection, Parameterized Queries

# SQL injection: string-built query vs bound parameters vs identifier allowlist.
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE users (id INTEGER, email TEXT, is_admin INTEGER);
INSERT INTO users VALUES (1,'ana@x.io',1),(2,'bo@x.io',0),(3,'cy@x.io',0);
""")
attack = "nobody@x.io' OR '1'='1"

# BAD: user text is spliced into SQL, so the quote ends the literal and OR runs.
bad = f"SELECT id FROM users WHERE email = '{attack}'"
print("concat rows      :", db.execute(bad).fetchall())

# GOOD: the driver sends SQL and value separately; the value is never parsed.
good = db.execute("SELECT id FROM users WHERE email = ?", (attack,)).fetchall()
print("parameterized    :", good)

# Identifiers (column names, ORDER BY, table names) CANNOT be bound.
# Map user choices onto a fixed allowlist instead of escaping them.
SORTABLE = {"id": "id", "email": "email"}
def list_users(sort: str) -> list:
    col = SORTABLE.get(sort)
    if col is None:
        raise ValueError(f"unsupported sort {sort!r}")
    return db.execute(f"SELECT id FROM users ORDER BY {col} DESC").fetchall()

print("allowlisted sort :", list_users("email"))
try:
    list_users("(CASE WHEN (SELECT is_admin FROM users WHERE id=1)=1 THEN id ELSE email END)")
except ValueError as e:
    print("blind-SQLi sort  :", "rejected ->", str(e)[:40], "...")

concat rows      : [(1,), (2,), (3,)]
parameterized    : []
allowlisted sort : [(3,), (2,), (1,)]
blind-SQLi sort  : rejected -> unsupported sort '(CASE WHEN (SELECT is_ ...

// Command injection: a shell string re-parses metacharacters; an argv array does not.
// No @types/node in the sandbox, so declare require; in a real project use
// `import { execFileSync, execSync } from "node:child_process"`.
declare function require(m: string): any;
const { execFileSync, execSync } = require("child_process");

const filename = "report.txt; echo INJECTED-COMMAND-RAN";

// BAD: the whole string goes to /bin/sh, so ';' starts a second command.
const viaShell = execSync(`echo processing ${filename}`, { encoding: "utf8" }) as string;
console.log("shell string :", JSON.stringify(viaShell.trim().split("\n")));

// GOOD: execFile passes argv directly to the program; ';' is just a character.
const viaArgv = execFileSync("echo", ["processing", filename], { encoding: "utf8" }) as string;
console.log("argv array   :", JSON.stringify(viaArgv.trim().split("\n")));

// Still validate: argv blocks the shell, but a value starting with '-' can be
// read as an option (argument injection). Use '--' or an allowlist pattern.
const safeName = /^[\w.-]{1,64}$/;
for (const f of ["report.txt", "--output=/etc/passwd", filename]) {
  const ok = safeName.test(f) && !f.startsWith("-");
  console.log(`validate ${JSON.stringify(f).padEnd(42)} -> ${ok ? "ok" : "reject"}`);
}

shell string : ["processing report.txt","INJECTED-COMMAND-RAN"]
argv array   : ["processing report.txt; echo INJECTED-COMMAND-RAN"]
validate "report.txt"                               -> ok
validate "--output=/etc/passwd"                     -> reject
validate "report.txt; echo INJECTED-COMMAND-RAN"    -> reject