SQL injection — how it works, prepared statements, PDO
Concept
SQL injection is the #1 web application vulnerability (OWASP Top 10). It occurs when user input is concatenated directly into SQL queries, allowing attackers to manipulate the query structure.
Classic example: "SELECT * FROM users WHERE email = '$email'" — if $email is ' OR '1'='1, the resulting query is SELECT * FROM users WHERE email = '' OR '1'='1' — returns all users.
Prepared statements: The fix. The query structure and data are sent to the database separately. The database treats the user data as data only — it can never become SQL syntax.
PDO prepared statements: Use ? or :named placeholders, then bindValue() or execute(array $params). The database driver handles proper escaping and type handling.
Never use:
mysql_real_escape_string()— deprecated and error-prone.- String interpolation or concatenation into SQL:
"WHERE id = $id". addslashes()— insufficient and encoding-dependent.
Laravel Eloquent: Automatically uses PDO prepared statements for all queries. Raw queries with DB::select("SELECT * FROM users WHERE id = $id") bypass this — always use DB::select("SELECT * FROM users WHERE id = ?", [$id]).
Second-order SQL injection: Stored data that was safely inserted can be re-used unsafely later. Always use parameterized queries when reading back data too.
Code Example
<?php
declare(strict_types=1);
// VULNERABLE — never do this
$email = $_POST['email']; // attacker sends: ' OR '1'='1' --
$query = "SELECT * FROM users WHERE email = '$email'"; // SQL INJECTION!
// SAFE — PDO prepared statement with positional placeholder
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$email]);
$user = $stmt->fetch(\PDO::FETCH_ASSOC);
// SAFE — PDO prepared statement with named placeholder
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email AND active = :active");
$stmt->execute([':email' => $email, ':active' => 1]);
// SAFE — bindValue with explicit type
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ? AND role = ?");
$stmt->bindValue(1, $userId, \PDO::PARAM_INT);
$stmt->bindValue(2, 'admin', \PDO::PARAM_STR);
$stmt->execute();
// SAFE — Laravel Eloquent (uses prepared statements automatically)
$user = User::where('email', $email)->first();
// SAFE — Laravel raw query with bindings
$users = \DB::select("SELECT * FROM users WHERE role = ? AND active = ?", ['admin', 1]);
// VULNERABLE — raw query without bindings
$users = \DB::select("SELECT * FROM users WHERE role = '$role'"); // DON'T!
// SAFE — IN clause with bindings (requires placeholder per value)
$ids = [1, 2, 3];
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("SELECT * FROM users WHERE id IN ($placeholders)");
$stmt->execute($ids);
// Laravel handles IN clause automatically
$users = User::whereIn('id', [1, 2, 3])->get();
// Column names CANNOT be parameterized — use an allowlist
$sortColumn = in_array($request->sort, ['name', 'email', 'created_at'])
? $request->sort
: 'created_at';
$users = User::orderBy($sortColumn, 'asc')->get();