Prepared Statements
A prepared statement is a SQL query sent to the database in two separate steps: first the query's structure, with placeholders standing in for any values, and then the actual values, sent afterward and bound to those placeholders. That separation is what makes prepared statements the standard defense against SQL injection, and it's the single most important habit covered in this entire database section. This page focuses specifically on how and why they work, using both PDO and mysqli syntax.
Why separating structure from data closes the injection hole
When you build a query by concatenating a variable into a SQL string, the database has no way to tell "code" apart from "data" — it just sees one string and parses all of it as SQL. If that variable contains SQL syntax, the database happily executes it. A prepared statement avoids this entirely by sending the query template to the database first, letting the database compile a query plan for it, and only then sending the bound values — which are treated strictly as data no matter what characters they contain. A value can never rewrite the query's structure because the structure was already fixed before the value ever arrived.
This code is intentionally shown as an example of what NOT to do:
$email = $_POST['email']; $sql = "SELECT id, name FROM users WHERE email = '" . $email . "'"; $result = $pdo->query($sql);
If a visitor submits ' OR '1'='1 as the email, the query the database actually receives becomes:
SELECT id, name FROM users WHERE email = '' OR '1'='1'
That condition is true for every row, so the query returns the entire users table instead of one account. With a slightly different payload, the same technique can modify or delete data, not just read it.
Here's the same lookup rewritten as a prepared statement in both PDO and mysqli. In both versions, the submitted email is sent to the database as a bound parameter, never pasted into the SQL text itself.
Safe: PDO with a bound parameter
<?php
$email = $_POST['email'];
$statement = $pdo->prepare('SELECT id, name FROM users WHERE email = ?');
$statement->execute([$email]);
$user = $statement->fetch(PDO::FETCH_ASSOC);Safe: mysqli with a bound parameter
<?php
$email = $_POST['email'];
$stmt = $mysqli->prepare('SELECT id, name FROM users WHERE email = ?');
$stmt->bind_param('s', $email);
$stmt->execute();
$user = $stmt->get_result()->fetch_assoc();Even if $email literally contains the string ' OR '1'='1, the database treats the entire value as one piece of data to compare against the email column — it will simply fail to match any real row, rather than altering the query.
Named placeholders vs positional placeholders
PDO supports two placeholder styles. Positional placeholders are
plain ? marks, filled in order by position in the array passed to
->execute(). Named placeholders use a :label syntax and are
filled by matching keys in an associative array, which reads more
clearly once a query has more than two or three parameters. mysqli
only supports positional ? placeholders.
Positional placeholders
<?php
$statement = $pdo->prepare(
'INSERT INTO products (name, price, category) VALUES (?, ?, ?)'
);
$statement->execute(['Desk Lamp', 24.50, 'home-office']);Named placeholders
<?php
$statement = $pdo->prepare(
'INSERT INTO products (name, price, category)
VALUES (:name, :price, :category)'
);
$statement->execute([
':name' => 'Desk Lamp',
':price' => 24.50,
':category' => 'home-office',
]);With named placeholders, the order values are passed in no longer matters, and it's immediately obvious from the query text which value fills which column — a real readability win once a query has five or six parameters.
bindValue() vs bindParam()
PDO also offers ->bindValue() and ->bindParam() as an
alternative to passing an array straight to ->execute(). Both bind
a placeholder to a value, but they capture that value differently:
bindValue($placeholder, $value)binds the current value of$valueimmediately, at the momentbindValue()is called.bindParam($placeholder, $variable)binds the variable itself by reference — PDO reads its value later, at the moment->execute()runs, so changing the variable after binding but before executing changes what gets sent.
bindValue() — value captured immediately
<?php
$statement = $pdo->prepare('SELECT * FROM products WHERE category = :category');
$statement->bindValue(':category', 'home-office');
$statement->execute();bindParam() — variable read at execute() time, useful inside a loop
<?php
$statement = $pdo->prepare('INSERT INTO log_entries (message) VALUES (:message)');
$statement->bindParam(':message', $message);
foreach ($messages as $message) {
$statement->execute(); // reads the current value of $message each time
}Prepared statements protect data, not structure
One limitation worth knowing: placeholders can only stand in for values — things that appear on the right-hand side of a comparison, like a string or a number. They cannot be used for table names, column names, or SQL keywords like ASC/DESC, because those are part of the query's structure, not data being compared. If a column name needs to come from user input (for example, a "sort by" dropdown), it has to be validated against a fixed allow-list of known-safe column names instead of being bound as a parameter.
Allow-listing a dynamic column name that can't be a bound parameter
<?php
$allowedColumns = ['name', 'price', 'created_at'];
$sortBy = $_GET['sort'] ?? 'name';
if (!in_array($sortBy, $allowedColumns, true)) {
$sortBy = 'name'; // fall back to a safe default
}
// $sortBy is now guaranteed to be one of the allowed column names,
// so it's safe to interpolate directly into the SQL here
$statement = $pdo->query("SELECT * FROM products ORDER BY {$sortBy} ASC");(rows returned, sorted by the validated column)