Cheatsheet

-- ===== 1. The root cause =====
-- SQLi is concatenation of untrusted data into SQL. That is it.
-- 'WHERE id = ' + input            <- bug
-- 'WHERE id = ?'  bound to input   <- fix
-- Frameworks (Eloquent, Sequelize, SQLAlchemy, EF Core) generate
-- parameterised queries by default. The bug almost always lives in:
--   - raw / rawQuery / DB::statement / sequelize.query
--   - dynamic ORDER BY / table name (cannot be parameterised; whitelist)
--   - LIKE patterns built by string concat
--   - LIMIT / OFFSET when those come from untrusted input

-- ===== 2. The defence: parameterised everywhere =====
-- PHP PDO
$stmt = $pdo->prepare('SELECT id FROM users WHERE email = :email AND status = :status');
$stmt->execute(['email' => $email, 'status' => $status]);

-- Node (pg)
await client.query('SELECT id FROM users WHERE email = $1 AND status = $2', [email, status]);

-- Python (psycopg)
cur.execute('SELECT id FROM users WHERE email = %s AND status = %s', (email, status))

-- Java (PreparedStatement)
var ps = c.prepareStatement("SELECT id FROM users WHERE email = ? AND status = ?");
ps.setString(1, email); ps.setString(2, status);

-- C# (Dapper)
var rows = c.Query<User>("SELECT id FROM users WHERE email = @email AND status = @status",
                        new { email, status });

-- Go (database/sql)
rows, err := db.Query("SELECT id FROM users WHERE email = $1 AND status = $2", email, status)

-- ===== 3. Inputs that cannot be parameters (handle differently) =====
-- Table / column names, ORDER BY columns, ASC/DESC, LIMIT
-- Solution: WHITELIST. Map untrusted input through a fixed dictionary.
const SORTS = { newest: 'created_at DESC', price: 'price ASC' };
const order = SORTS[req.query.sort] ?? SORTS.newest;
db.query("SELECT * FROM products ORDER BY " + order);

-- ===== 4. LIKE patterns =====
-- DO escape the wildcards % and _ before substituting:
const safe = input.replace(/[%_]/g, c => '\\\\' + c);
await db.query('SELECT id FROM products WHERE name LIKE $1', ['%' + safe + '%']);

-- ===== 5. Stored procedures =====
-- DO NOT trust that 'we use sprocs' = 'we are safe'.
-- An sproc that builds SQL with concatenation is just as vulnerable.
-- Parameterise inside the sproc too; use USING / sp_executesql.

-- ===== 6. Secondary defences (in-depth) =====
-- - Least-privilege DB user: app role with only the rights it needs
-- - WAF / SQLi rules (ModSecurity OWASP CRS) in front of the app
-- - Detection: log queries with @@/-- /UNION SELECT / sleep() shapes
-- - Database-level user separation between reads and writes

-- ===== 7. Smell-tests in code review =====
-- 1. grep -RIn 'rawQuery\|sequelize.query\|DB::statement\|connection.query'
-- 2. Any string concatenation building SQL anywhere
-- 3. Any ORDER BY / LIMIT / table-name fed from request data
-- 4. Tests for SQLi in your test suite (a few canonical attacks per route)

-- Parameter binding (?, $1, :name) everywhere | Allow-list identifiers
-- Hide raw errors | Least-privilege DB user | Validate types before query