PHP Prepared Statements

Prepared statements are a way to send a SQL query to a database in two separate steps: first you send the query’s structure with placeholders standing in for values, then you send the actual values for the database driver to bind safely. This separation between “what to run” and “what data to use” is the single most effective defense against SQL injection, and it is the standard way to talk to a database in modern PHP.

Overview: What Prepared Statements Are and Why They Matter

To see why this matters, imagine building a query by gluing a variable straight into a SQL string: "SELECT * FROM students WHERE email = '$email'". If $email comes from user input and contains something like ' OR '1'='1, the meaning of the query changes completely – the database can no longer tell the difference between “data” and “code”. A prepared statement removes this ambiguity entirely: the SQL parser sees only placeholders where the values go, compiles the query structure once, and then treats every bound value as pure data, no matter what characters it contains.

PHP exposes prepared statements through two extensions: mysqli and PDO (PHP Data Objects). This lesson uses PDO, because it works with multiple database drivers through one consistent API and is the recommended choice for new code. Everything you learn here – named placeholders, positional placeholders, binding, transactions – applies the same way whether you’re talking to MySQL, PostgreSQL, or SQLite through PDO.

Native Prepares vs. Emulated Prepares

MySQL’s client/server protocol has native support for prepared statements. When PDO prepares a statement in “native” mode, it sends a COM_STMT_PREPARE packet containing the SQL text with placeholders. MySQL’s parser compiles that text into an execution plan and returns a statement ID, doing this parsing and planning work only once. When you call execute(), PDO sends a COM_STMT_EXECUTE packet containing just the statement ID and the binary-encoded parameter values. MySQL substitutes those values into the already-compiled plan and runs it. Because the plan was fixed before any value arrived, a value can never be reinterpreted as SQL syntax.

By default, PDO’s MySQL driver actually uses emulated prepares (PDO::ATTR_EMULATE_PREPARES is true) rather than this native protocol. In emulated mode, PDO itself parses the placeholder positions, and when you call execute(), it quotes and escapes each value according to its type and the connection’s character set, then splices the finished, fully-escaped SQL string and sends it as one ordinary query. This is still safe against injection – the escaping is done correctly by the driver, not by ad-hoc string concatenation – but MySQL sees a plain query each time, and type coercion is looser than with native prepares. You can request true native prepares by setting PDO::ATTR_EMULATE_PREPARES => false when creating the connection.

Syntax

The general shape of a prepared statement with PDO is: prepare the query template with placeholders, bind or pass the values, then execute.

<?php
$pdo = new PDO($dsn, $username, $password);

$stmt = $pdo->prepare('SELECT * FROM table_name WHERE column = :placeholder');
$stmt->bindValue(':placeholder', $value, PDO::PARAM_STR);
$stmt->execute();

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

prepare(string $query) – sends or parses the SQL template and returns a PDOStatement object. Placeholders are written as ? (positional) or :name (named).
bindValue($param, $value, $type) – binds a value immediately, at the point you call it. Use this for literal or already-final values.
bindParam($param, &$variable, $type) – binds a variable by reference; PHP reads its value only when execute() runs, which matters if the variable changes in a loop.
execute(?array $params = null) – runs the statement. You can also skip bindValue()/bindParam() entirely and pass an array of values straight into execute().
fetch() / fetchAll() – retrieve one row or all rows from a result set, in the fetch mode you choose (e.g. PDO::FETCH_ASSOC).

Style	Placeholder	Binding by name?	Typical use
Positional	`?`	No, order matters	Short queries with few parameters
Named	`:name`	Yes, order-independent	Longer queries, or when passing an associative array

You cannot mix positional and named placeholders in the same query – pick one style per statement.

Examples

Example 1: Inserting a Row with Named Placeholders

<?php
$pdo = new PDO(
    'mysql:host=localhost;dbname=school;charset=utf8mb4',
    'root',
    'secret',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

$sql = "INSERT INTO students (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
$stmt = $pdo->prepare($sql);

$stmt->execute([
    ':first_name' => 'Ada',
    ':last_name'  => 'Lovelace',
    ':email'      => 'ada@example.com',
]);

echo "New student ID: " . $pdo->lastInsertId() . "\n";
echo "Rows affected: " . $stmt->rowCount() . "\n";

Output:

New student ID: 1
Rows affected: 1

The query template uses named placeholders (:first_name, :last_name, :email), and execute() receives an associative array mapping each placeholder to its value. Because PDO::ATTR_ERRMODE is set to throw exceptions, any constraint violation, like a duplicate email on a unique column, would raise a PDOException rather than failing silently. lastInsertId() and rowCount() then report what actually happened on the server.

Example 2: Filtering Results with Positional Placeholders

<?php
$pdo = new PDO(
    'mysql:host=localhost;dbname=school;charset=utf8mb4',
    'root',
    'secret',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]
);

$sql = "SELECT first_name, last_name, email FROM students WHERE id > ? AND last_name = ?";
$stmt = $pdo->prepare($sql);

$minId = 0;
$lastName = 'Lovelace';

$stmt->bindValue(1, $minId, PDO::PARAM_INT);
$stmt->bindValue(2, $lastName, PDO::PARAM_STR);
$stmt->execute();

foreach ($stmt as $row) {
    echo "{$row['first_name']} {$row['last_name']} <{$row['email']}>\n";
}

Output:

Ada Lovelace <ada@example.com>

This query uses two ? placeholders, bound in order with bindValue() and explicit types: PDO::PARAM_INT for the numeric ID and PDO::PARAM_STR for the name. Because PDOStatement is iterable, the foreach loop pulls each matching row directly as an associative array, thanks to PDO::ATTR_DEFAULT_FETCH_MODE, without a separate fetch() call.

Example 3: Reusing One Prepared Statement in a Transaction

<?php
$pdo = new PDO(
    'mysql:host=localhost;dbname=school;charset=utf8mb4',
    'root',
    'secret',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

$students = [
    ['Grace', 'Hopper', 'grace@example.com'],
    ['Alan', 'Turing', 'alan@example.com'],
    ['Katherine', 'Johnson', 'katherine@example.com'],
];

$stmt = $pdo->prepare(
    'INSERT INTO students (first_name, last_name, email) VALUES (?, ?, ?)'
);

$pdo->beginTransaction();

try {
    foreach ($students as [$first, $last, $email]) {
        $stmt->execute([$first, $last, $email]);
    }
    $pdo->commit();
    echo 'Inserted ' . count($students) . " students.\n";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo 'Batch insert failed: ' . $e->getMessage() . "\n";
}

Output:

Inserted 3 students.

The statement is prepared exactly once, outside the loop, and then execute() is called three times with different value arrays. This is both faster, because there is no repeated parsing, and safer than building three separate query strings. Wrapping the loop in beginTransaction() and commit() means that if any single insert fails, rollBack() undoes all of them, leaving the table exactly as it was before the batch started.

Under the Hood: Step by Step

Here is exactly what happens when Example 1 above runs, from connection to result:

new PDO(...) opens a connection to MySQL (TCP or socket) and authenticates using the given credentials.
$pdo->prepare($sql) sends the query template. With native prepares, MySQL parses and validates the SQL, checking that the students table and its columns exist, and compiles an execution plan, returning a statement handle. With emulated prepares, PDO itself scans the string for :first_name, :last_name, and :email and remembers their positions; no round trip happens yet.
$stmt->execute([...]) supplies the actual values. PDO either sends them as typed, binary-encoded parameters alongside the statement ID (native mode) or quotes and escapes each one and substitutes it into the SQL text before sending a single finished query (emulated mode).
MySQL executes the statement and, because this is an INSERT, returns the number of affected rows rather than a result set.
$pdo->lastInsertId() asks the server for the auto-increment value generated by that connection’s most recent insert.
The two echo calls print the ID and the row count that PHP received back from the driver.

Common Mistakes

Mistake 1: Trying to Bind a Column or Table Name

Placeholders only ever represent values, never SQL identifiers like table or column names. The following looks reasonable but is wrong: PDO will bind :column as the string 'last_name', so the query becomes ORDER BY 'last_name', which MySQL treats as sorting by a constant string literal, not the column. It won’t sort by last name at all, and it won’t raise an obvious error either – the query still runs, just not the way you intended.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=school;charset=utf8mb4', 'root', 'secret');

$column = 'last_name';
$stmt = $pdo->prepare('SELECT * FROM students ORDER BY :column');
$stmt->execute([':column' => $column]);

To sort or filter by a dynamic identifier, validate it against an allow-list of known-safe values in PHP, then interpolate the validated string directly – never pass it through a placeholder:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=school;charset=utf8mb4', 'root', 'secret');

$allowedColumns = ['first_name', 'last_name', 'email'];
$column = 'last_name';

if (!in_array($column, $allowedColumns, true)) {
    throw new InvalidArgumentException('Invalid sort column');
}

$stmt = $pdo->prepare("SELECT * FROM students ORDER BY {$column}");
$stmt->execute();

Mistake 2: Using `prepare()` but Still Concatenating the Value

Calling prepare() doesn’t protect you if you still glue the variable into the SQL string yourself – the placeholder mechanism is never actually used, so you get zero protection from SQL injection despite the misleading appearance of “using prepared statements”:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=school;charset=utf8mb4', 'root', 'secret');

$email = $_GET['email'];
$stmt = $pdo->prepare("SELECT * FROM students WHERE email = '$email'");
$stmt->execute();

The fix is to put an actual placeholder in the SQL and pass the value through execute(), so PDO, not string interpolation, handles the value:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=school;charset=utf8mb4', 'root', 'secret');

$email = $_GET['email'];
$stmt = $pdo->prepare('SELECT * FROM students WHERE email = ?');
$stmt->execute([$email]);

Best Practices

Always use a prepared statement for any query that includes variable data – never build SQL with string concatenation or interpolation of raw input.
Set PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION on every connection so database errors surface as catchable exceptions instead of silent failures.
Never put placeholders where an identifier, such as a table name, column name, or ORDER BY direction, belongs – validate those against an allow-list in PHP instead.
Prefer bindValue() over bindParam() unless you specifically need late binding by reference, for example a variable that changes across loop iterations.
Pass explicit PDO::PARAM_* types for non-string data such as integers and booleans instead of relying on emulation to guess the type.
Reuse a single prepared PDOStatement across a loop for batch operations rather than calling prepare() again on every iteration.
Wrap multi-statement writes in a transaction with beginTransaction(), commit(), and rollBack() so a failure partway through doesn’t leave inconsistent data.
Set the connection charset to utf8mb4 in the DSN to avoid legacy multi-byte encoding tricks that predate modern escaping.
Consider PDO::ATTR_EMULATE_PREPARES => false when you want the database itself to enforce real parameter types and catch certain SQL errors earlier.

Practice Exercises

Write a PDO script that inserts a new product (name, price, sku) into a products table using named placeholders, then prints the new row’s auto-increment ID with lastInsertId().
Write a script that searches a users table by a partial email match supplied through $_GET['q'], using a prepared statement with a LIKE clause. Hint: build the %...% wildcard string in PHP before binding it – do not put % characters inside the SQL string itself.
Refactor a loop that currently calls $pdo->prepare() and execute() inside every iteration to insert 100 rows, so that it prepares the statement once outside the loop, executes it 100 times, and wraps the whole batch in a single transaction.

Summary

Prepared statements separate a query’s structure from its data, so user-supplied values can never change the meaning of the SQL.
PDO supports both server-side (native) prepares and client-side (emulated) prepares; both are safe, but native prepares give stricter type handling.
Supply values with bindValue(), bindParam(), or by passing an array straight into execute().
Placeholders work only for values – identifiers like table and column names must be validated against an allow-list instead.
Reusing one prepared statement across a loop makes batch inserts and updates significantly faster.
Combine prepared statements with exception-based error handling and transactions for robust, production-ready database code.

PHP Prepared Statements

Overview: What Prepared Statements Are and Why They Matter

Native Prepares vs. Emulated Prepares

Syntax

Examples

Example 1: Inserting a Row with Named Placeholders

Example 2: Filtering Results with Positional Placeholders

Example 3: Reusing One Prepared Statement in a Transaction

Under the Hood: Step by Step

Common Mistakes

Mistake 1: Trying to Bind a Column or Table Name

Mistake 2: Using prepare() but Still Concatenating the Value

Best Practices

Practice Exercises

Summary

Table of Contents

Mistake 2: Using `prepare()` but Still Concatenating the Value