Prepared statements and parameter binding — preventing SQL injection

Intermediate5 min read·fw-06-003

sqlsecurity

Concept

Prepared statements and parameter binding are the primary defense against SQL injection. A prepared statement sends the SQL structure to the database first, then sends parameter values separately. The database treats parameters as data, never as SQL syntax.

How SQL injection works: Without prepared statements, string interpolation mixes data into SQL: "WHERE email = '$email'". If $email = "' OR '1'='1", the resulting SQL is WHERE email = '' OR '1'='1' — always true. The attacker can dump the entire table.

Prepared statement flow:

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?') — parse SQL structure.
$stmt->execute(['alice@example.com']) — send values separately. The DB treats them as literal data.
The DB never interprets parameter values as SQL.

Positional (?) vs named (:name) placeholders:

?: execute(['value']) — order matters.
:name: execute([':email' => 'alice@example.com']) — name matches :email in SQL. More readable.
Can't mix positional and named in one query.

bindValue() vs bindParam():

bindValue(): Binds a literal value now.
bindParam(): Binds a variable by reference — value is read at execute() time. Useful in loops.

Type specification: $stmt->bindValue(':age', 25, PDO::PARAM_INT). Ensures the value is treated as an integer, not a string. The PARAM_* constants: PARAM_INT, PARAM_STR, PARAM_BOOL, PARAM_NULL.

PDO::ATTR_EMULATE_PREPARES = false: Use native prepared statements (not emulated). Emulated prepared statements don't actually send parameters separately — they do client-side string escaping, which is not as safe.

Code Example

php

<?php
// Positional placeholders
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ? AND active = ?');
$stmt->execute(['alice@example.com', 1]);
$user = $stmt->fetch();

// Named placeholders — more readable for complex queries
$stmt = $pdo->prepare('
    SELECT * FROM users
    WHERE email    = :email
    AND   active   = :active
    AND   role     = :role
');
$stmt->execute([':email' => 'alice@example.com', ':active' => 1, ':role' => 'admin']);
// OR without leading colon (PDO accepts both):
$stmt->execute(['email' => 'alice@example.com', 'active' => 1, 'role' => 'admin']);

// bindValue with type enforcement
$stmt = $pdo->prepare('SELECT * FROM posts WHERE user_id = :userId AND published = :published');
$stmt->bindValue(':userId',    42,   PDO::PARAM_INT);
$stmt->bindValue(':published', true, PDO::PARAM_BOOL);
$stmt->execute();
$posts = $stmt->fetchAll();

// INSERT with named params
$stmt = $pdo->prepare('INSERT INTO users (name, email, password) VALUES (:name, :email, :password)');
$stmt->execute([
    ':name'     => 'Alice Smith',
    ':email'    => 'alice@example.com',
    ':password' => password_hash('secret', PASSWORD_ARGON2ID),
]);
$newId = $pdo->lastInsertId();

// NEVER do string interpolation:
// $pdo->query("SELECT * FROM users WHERE email = '$email'"); // SQL INJECTION RISK

// NEVER use htmlspecialchars or addslashes for SQL safety:
// They're for HTML/JS escaping, not SQL — use prepared statements only

// Dynamic WHERE clause building (safe pattern)
$conditions = [];
$bindings   = [];
if ($email !== null) { $conditions[] = 'email = :email'; $bindings['email'] = $email; }
if ($role  !== null) { $conditions[] = 'role = :role';   $bindings['role']  = $role; }
$where = $conditions ? 'WHERE ' . implode(' AND ', $conditions) : '';
$stmt  = $pdo->prepare("SELECT * FROM users {$where}");
$stmt->execute($bindings);

PreviousConnection class — wrapping PDO with a fluent APIDatabase Connection & PDO Wrappers NextTransaction handling in the connection classDatabase Connection & PDO Wrappers