Prepared Statements

PHP Prepared Statements: Complete SQL Injection Defense

A PHP prepared statement separates a SQL query's structure from its user-supplied values by sending a parameterized template to MySQL first, then transmitting bound values separately. Even if an attacker types ' OR 1=1 -- into a login or search form, MySQL treats the input strictly as harmless literal text—never as executable SQL.

Named vs Positional Placeholders

PDO supports both positional ? placeholders and :named placeholders, though you cannot mix both styles in the same query.

<?php
declare(strict_types=1);

function findActiveUsersByRole(PDO $pdo, string $role, int $limit): array
{
    $stmt = $pdo->prepare(
        'SELECT id, full_name, email
         FROM users
         WHERE role = :role AND status = :status
         ORDER BY created_at DESC
         LIMIT :limit'
    );

    // Explicit PDO::PARAM_INT is safe even if emulation is enabled
    $stmt->bindValue(':role', $role, PDO::PARAM_STR);
    $stmt->bindValue(':status', 1, PDO::PARAM_INT);
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->execute();

    return $stmt->fetchAll();
}

Using bindValue() with PDO::PARAM_INT is especially important for LIMIT and OFFSET clauses, because passing integers inside $stmt->execute([':limit' => 10]) treats every array value as a string by default when ATTR_EMULATE_PREPARES is on.

What Prepared Statements Cannot Bind

Placeholders only represent scalar values—never table names, column names, ASC/DESC sort directions, or SQL keywords. For dynamic sorting (?sort=price&dir=asc), map user inputs through an explicit PHP match or in_array() allowlist before interpolating the column name.

Common Mistakes to Avoid

• Disabling ATTR_EMULATE_PREPARES => false omission: Always set PDO::ATTR_EMULATE_PREPARES => false on your PDO connection so MySQL compiles real server-side prepared statements.
• Using LIKE :term with % outside the value: Put wildcards inside the bound PHP string ($stmt->execute([':term' => '%' . $search . '%'])), never inside SQL like LIKE '%:term%'.
• Reusing the same :name twice in one query with emulation off: Native MySQL PDO forbids repeating :email twice in one SQL string; use :email_a and :email_b instead.

Frequently Asked Questions

Are prepared statements slower than raw queries?

No. For repeated queries, prepared statements are actually faster because MySQL parses and optimizes the query plan once and reuses it across executions.

What is the difference between bindValue() and bindParam()?

bindValue() binds the variable's value immediately at the time of the call, whereas bindParam() binds a PHP variable by reference and reads its value when execute() runs. Prefer bindValue() or passing an array to execute().

How do I bind a dynamic IN (...) list of IDs safely?

Generate one ? placeholder per ID (implode(',', array_fill(0, count($ids), '?'))) and pass array_values($ids) to $stmt->execute().