How It Works

-- SCOPE: defensive learning on the bundled lab app. Authorised testing only.
-- Do not probe systems you do not own or have written permission to test.

-- ===== The mechanism =====
-- SQL injection happens when user input is concatenated into a SQL string and
-- the database PARSES the input as SQL syntax rather than treating it as data.
-- The classes (OWASP / MITRE):
--   1. In-band: results returned in the same response (classic, union-based, error-based)
--   2. Inferential / Blind: no direct output; learn via timing or boolean responses
--   3. Out-of-band: response comes back via DNS / HTTP from the DB host (NXDOMAIN tricks)

-- ===== The vulnerability shape (DO NOT do this) =====
-- App pseudo-code (illustrative; the lab demonstrates it safely):
--   const sql = "SELECT * FROM users WHERE email = '" + email + "' AND password_hash = '" + ph + "'";
--   db.query(sql);
-- The DB sees ONE string; it cannot tell which characters came from the user.

-- ===== The fix: parameterised queries =====
-- The CORRECT shape — the DB receives template + values separately and treats values as data:
--   const r = await db.query(
--     'SELECT id, name FROM users WHERE email = $1 AND password_hash = $2',
--     [email, ph],
--   );
-- Same in every driver:
--   pg (node):     db.query(sql, params)
--   mysql2:        conn.execute(sql, params)
--   Python sqlite3: cur.execute(sql, params)
--   Python psycopg: cur.execute(sql, (email, ph))
--   PHP PDO:        $stmt->execute([':email' => $email])
--   Java JDBC:      PreparedStatement.setString(1, email)
--   .NET ADO.NET:   command.Parameters.AddWithValue("@email", email)

-- Even on the SAME wire protocol, the prepared statement path tells the DB which
-- tokens are SQL and which are user data. No amount of escaping replicates this safely.

-- ===== Why escaping by hand is fragile =====
-- - Character set mismatches (latin1 vs utf8) can sneak past escapers
-- - Some drivers' "escape" functions ignore types (numeric input as string)
-- - Multi-statement support varies; one escaper that protects single statements
--   does not protect against ; DROP TABLE ...
-- Just don't.

-- ===== Defence in depth =====
-- 1. Least privilege
--    Each app role has the MINIMUM grants needed.
--    The auth role doesn't need DELETE on orders. The reporting role doesn't need INSERT.
CREATE ROLE app_auth   LOGIN PASSWORD '...';
CREATE ROLE app_report LOGIN PASSWORD '...';
GRANT SELECT, INSERT, UPDATE ON users TO app_auth;
GRANT SELECT ON orders TO app_report;

-- 2. Stored procedures with explicit params (where appropriate)
CREATE OR REPLACE FUNCTION authenticate(p_email TEXT, p_hash TEXT)
RETURNS TABLE(id BIGINT, name TEXT) AS $$
  SELECT id, name FROM users WHERE email = p_email AND password_hash = p_hash;
$$ LANGUAGE SQL SECURITY DEFINER;

-- 3. Web Application Firewall (WAF) as a backstop
-- 4. Logging for shape (templates, error_class) not values (see sqli/logging)

-- ===== ORM caveats =====
-- ORMs default to parameters, but they all have ESCAPE HATCHES:
--   Prisma: $queryRaw -- parameterise with template strings (Prisma helps)
--   Sequelize: { raw: true } -- you must parameterise yourself
--   TypeORM: createQueryBuilder().where('email = :email', { email })
-- Any time you concatenate input into a string -> you opted OUT of the ORM's protection.

-- ===== Detection in code review =====
-- Search for:
--   String concatenation with SQL keywords (SELECT, INSERT, UPDATE, DELETE)
--   Template literals with backticks and ${...} inside SQL
--   $queryRaw / .raw() / db.exec(stringWith input)
--   Dynamic ORDER BY / column names from user input

-- ===== Dynamic identifiers (the tricky case) =====
-- Parameters bind VALUES, not identifiers. ORDER BY column cannot be a parameter.
-- Approach: validate against an allowlist.
const allowed = new Set(['name', 'created_at', 'total_cents']);
const sortCol = allowed.has(input) ? input : 'created_at';
const sql = \`SELECT id FROM orders ORDER BY ${sortCol} DESC\`;

-- ===== Patterns to internalise =====
-- - Parameterise EVERY query. No exceptions.
-- - Least privilege roles per service responsibility
-- - Allowlist for dynamic identifiers (sort/limit/group)
-- - Code review: ban string-concat SQL; lint for it
-- - Log shapes for detection (templates + error class), never values

-- ===== Pitfalls =====
-- - 'It's an internal tool' — internal tools get phished too
-- - 'We use an ORM' — yes, and people still call .raw()
-- - 'We escape input' — character set / encoding bypasses happen yearly
-- - LIKE patterns: user input with %_ wildcards leaks query plans
-- - JSON in TEXT columns -> JSON path tricks can re-introduce injection in some engines

// The bug:
//   query = "SELECT … WHERE email = '" + input + "'"
// With input = ' OR 1=1 -- the resulting SQL is:
//   SELECT … WHERE email = '' OR 1=1 --'
// → returns every row, bypassing auth.