SQL injection — injecting SQL via user input; why prepared statements fix it
Beginner5 min read·eng-19-002
interviewsecuritysql
Concept
SQL injection — an attack where an attacker injects malicious SQL code into an application's SQL queries by supplying crafted input. If the application builds queries by concatenating user input, the attacker's input becomes part of the SQL statement.
How it works:
sql
-- Application builds: SELECT * FROM users WHERE email = '$input'
-- Attacker inputs: ' OR '1'='1
-- Result: SELECT * FROM users WHERE email = '' OR '1'='1'
-- '1'='1' is always true → returns ALL usersClassic payloads:
' OR '1'='1— bypass login (always true).'; DROP TABLE users; --— drop table (Bobby Tables).' UNION SELECT username, password FROM users --— exfiltrate data.' AND 1=0 UNION SELECT 1, @@version --— fingerprint database.
Prevention:
- Prepared statements / parameterized queries: User input is NEVER part of the SQL string — it's a separate parameter.
- ORM: Eloquent uses prepared statements by default.
- Input validation: Reject unexpected characters, but this is a second line of defense.
- Least privilege: Database user has only SELECT/INSERT/UPDATE, not DROP TABLE.
Laravel's protection: Eloquent and the Query Builder use PDO parameterized queries. Raw expressions (DB::select("SELECT * FROM users WHERE id = $id")) break this protection.
Blind SQL injection: When the application doesn't return query results but you can infer data from response behavior (time delays, error vs no-error).
Code Example
php
<?php
// VULNERABLE — concatenating user input into SQL
$email = $_GET['email']; // user input
// Direct DB query (vulnerable)
$users = DB::select("SELECT * FROM users WHERE email = '{$email}'");
// Input: ' OR '1'='1 → returns all users
// Input: '; DROP TABLE users; -- → drops users table
// Eloquent (vulnerable form):
$users = User::whereRaw("email = '{$email}'")->get();
// Same vulnerability — raw SQL, not parameterized
// SECURE — parameterized queries
// Method 1: PDO prepared statements
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?');
$stmt->execute([$email]);
$users = $stmt->fetchAll();
// Input ' OR 1=1 is treated as LITERAL STRING, not SQL code
// Method 2: Laravel Query Builder (always parameterized)
$users = DB::table('users')->where('email', $email)->get();
// Builds: SELECT * FROM users WHERE email = ? with $email as binding
// Method 3: Eloquent (always parameterized)
$user = User::where('email', $email)->first();
// Method 4: whereRaw with bindings (when raw is needed)
$users = User::whereRaw('email = ? AND active = ?', [$email, 1])->get();
// Bindings are safe
// COLUMN NAME INJECTION (different from value injection)
// Whitelist approach — you CANNOT parameterize column names
$allowedSorts = ['name', 'email', 'created_at'];
$sortColumn = in_array($request->sort, $allowedSorts) ? $request->sort : 'name';
$users = User::orderBy($sortColumn, 'asc')->get();
// DO NOT do: User::orderBy($request->sort) — column names aren't parameterizable!
// DB::raw is safe only for structure, not user values
$users = User::orderByRaw('RAND()')->get(); // safe — no user input
$users = User::orderByRaw($request->sort . ' ASC')->get(); // UNSAFE — user input!