Prepared statement — a pre-compiled SQL template with bound parameters
Concept
Prepared statement — a pre-compiled SQL query template where user-supplied values are bound AFTER compilation, as parameters. The SQL structure and the data are transmitted and processed separately.
The problem they solve: SQL injection. If you concatenate user input into SQL: "SELECT * FROM users WHERE email = '{$_POST['email']}'", a user who sends ' OR 1=1 -- as their email gets: SELECT * FROM users WHERE email = '' OR 1=1 --' — returning all users. The injection attacks the SQL structure.
How prepared statements prevent injection: The SQL template is sent to the database and compiled first. Then the parameters are sent separately — as DATA, not as SQL. The database treats parameter values as literal strings, not as SQL syntax. ' OR 1=1 -- is just a string value, not SQL.
Two-step execution:
- Prepare: Send SQL template with placeholders.
SELECT * FROM users WHERE email = ? - Execute: Send the parameter values separately.
['alice@example.com']
Placeholders:
?(positional):WHERE email = ? AND active = ?→ bind by order.:name(named):WHERE email = :email AND active = :active→ bind by name.
Beyond security — performance: Prepared statements can be cached by the database. If you execute the same query 100 times with different params, the database compiles it once and reuses the execution plan.
In PHP/Laravel: PDO uses prepared statements via prepare() + execute(). Laravel's Query Builder and Eloquent use prepared statements automatically. Raw queries via DB::select(), DB::insert() etc. use bindings.
Code Example
<?php
// ❌ VULNERABLE — string concatenation (NEVER DO THIS)
$email = $_POST['email']; // attacker sends: ' OR 1=1 --
$sql = "SELECT * FROM users WHERE email = '{$email}'";
// Executed: SELECT * FROM users WHERE email = '' OR 1=1 --'
// Returns ALL users!
// ✅ PREPARED STATEMENT — PDO directly
$pdo = new \PDO('mysql:host=localhost;dbname=myapp', 'user', 'pass');
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ? AND active = ?');
$stmt->execute([$_POST['email'], 1]);
$user = $stmt->fetch(\PDO::FETCH_ASSOC);
// Database compiles SQL, then binds email as data — injection impossible
// Named placeholders
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND role = :role');
$stmt->bindParam(':email', $email, \PDO::PARAM_STR);
$stmt->bindParam(':role', $role, \PDO::PARAM_STR);
$stmt->execute();
// ✅ LARAVEL — prepared statements automatically
// DB::select with bindings
$users = \DB::select('SELECT * FROM users WHERE email = ? AND active = ?', [$email, 1]);
// The ? values are bound as prepared statement parameters
// Named bindings
$users = \DB::select(
'SELECT * FROM users WHERE email = :email AND role = :role',
['email' => $email, 'role' => $role]
);
// Eloquent — always uses prepared statements
$user = User::where('email', $email)->first();
// Generates: SELECT * FROM users WHERE email = ? [bound: $email]
// ✅ The Query Builder binds ALL values automatically
User::where('email', $email)
->where('active', true)
->where('role', 'admin')
->get();
// Every value in where() is a bound parameter — injection-proof
// WHEN YOU MUST USE RAW SQL — use bindings!
$orders = Order::whereRaw('total > ? AND status = ?', [100, 'paid'])->get();
Order::whereRaw('YEAR(created_at) = :year', ['year' => 2024])->get();
// ❌ STILL VULNERABLE even with Query Builder — columnRaw isn't safe
Order::orderByRaw($request->input('sort')); // if $sort is user input — INJECTION POSSIBLE
// ✅ SAFE: whitelist allowed columns
$allowedSorts = ['created_at', 'total', 'status'];
$sort = in_array($request->sort, $allowedSorts) ? $request->sort : 'created_at';
Order::orderBy($sort)->get();