Skip to content

SQL-Injection-Prevention.md

Latest commit

 

History

History
97 lines (67 loc) · 4.38 KB

SQL-Injection-Prevention.md

File metadata and controls

97 lines (67 loc) · 4.38 KB
Raw

SQL Injection Prevention

SQL injection (SQLi) lets an attacker alter the meaning of a database query by smuggling SQL syntax through untrusted input; the fix is to stop mixing data with code.

How SQL Injection Works

When input is concatenated into a query string, the database parser cannot tell where the developer's SQL ends and the attacker's data begins:

// VULNERABLE
$user = $_POST['username'];
$pass = $_POST['password'];
$sql  = "SELECT * FROM users WHERE username = '$user' AND password = '$pass'";
$result = $conn->query($sql);

An attacker submits username = admin' -- :

SELECT * FROM users WHERE username = 'admin' -- ' AND password = ''

The -- comments out the password check and the attacker logs in as admin. Payloads like ' OR '1'='1, ' UNION SELECT ..., and stacked ; DROP TABLE follow the same principle.

Impact

Class Consequence
Authentication bypass Log in without valid credentials
Data exfiltration Dump users, hashes, PII via UNION SELECT
Data tampering / destruction UPDATE / DELETE / DROP
Blind / time-based Extract data one bit at a time via boolean or SLEEP() responses
RCE / lateral movement INTO OUTFILE, stacked queries, DB-to-OS pivots

Primary Defense: Parameterized Queries

Prepared statements send the query template and the data on separate channels, so bound values are always literals — never syntax. This is the single most effective control.

// FIXED — data can never become code
$stmt = $pdo->prepare('SELECT id FROM users WHERE username = :u AND password_hash = :h');
$stmt->execute([':u' => $user, ':h' => $hash]);
$row = $stmt->fetch();

See Prepared-Statements for full PDO and MySQLi patterns.

Defense in Depth

Parameterization is necessary but pair it with layered controls:

  • Least-privilege DB user — the app account should only SELECT/INSERT/UPDATE the tables it needs; never DROP, FILE, or GRANT. This caps the blast radius if an injection still slips through.
  • Input validation (allow-list) — reject values that don't match expected type/format (numeric IDs, enum values) before they reach the query.
  • Whitelist dynamic identifiers — table/column names and ORDER BY directions can't be bound, so map them through a fixed allow-list.
  • Escaping as a last resort — only when parameterization is genuinely impossible, use context-correct escaping (PDO::quote() / mysqli_real_escape_string()), and never for identifiers.
  • ORM / query builder — Doctrine, Eloquent, etc. parameterize automatically, but raw/DB::raw() escapes still reintroduce risk.
// Example least-privilege grant for the app's runtime user
CREATE USER 'app'@'10.0.%' IDENTIFIED BY 'strong-secret';
GRANT SELECT, INSERT, UPDATE ON app.* TO 'app'@'10.0.%';
-- No DROP, DELETE, FILE, or GRANT privileges

Escaping vs Parameterization

Escaping is error-prone: it depends on correct charset (set_charset('utf8mb4')), applies only inside quoted string literals, and fails entirely for numeric contexts and identifiers. Prefer parameterization; treat escaping as legacy-only.

Summary

  • Parameterize every query — prepared statements are the single most effective control because bound values can never become SQL syntax.
  • Never build queries by concatenating untrusted input. If a query looks like "... '$var' ...", it is a bug.
  • Identifiers can't be bound — validate table/column names and ORDER BY directions against a fixed allow-list.
  • Layer defenses: least-privilege DB account, allow-list input validation, and escaping only as a genuine last resort.

Related