PHP MySQL Prepared Statements

Building a SQL query by directly gluing user input into a string is the single most common way web applications get hacked -- a technique called SQL injection. Prepared statements fix this at the root: the SQL structure is sent to the database first, separately from the actual values, so user input can never be misinterpreted as SQL code.

In this page:

Why Prepared Statements Exist
Preparing a Statement with Placeholders
Binding Values with bind_param
Executing and Retrieving Results
Reusing a Prepared Statement

Why Prepared Statements Exist

Directly building SQL by concatenating a variable into the query string, like "SELECT * FROM users WHERE name = '$name'", lets an attacker supply a value like '; DROP TABLE users; -- that changes the query's actual meaning. Prepared statements prevent this by sending the SQL template and the values as two entirely separate things.

Note: Treat prepared statements as the default way to run any query involving a variable value, not an optional extra step reserved for "risky" inputs.

Warning: A single un-prepared query built from concatenated user input anywhere in an application is enough to create a serious SQL injection vulnerability.

Example: Why Prepared Statements Exist

php

<?php
$name = "Robert'); DROP TABLE users; --";
// Never do: "SELECT * FROM users WHERE name = '$name'"
echo "The SQL template and the value are sent separately instead";
?>

Preparing a Statement with Placeholders

mysqli_prepare($conn, $sql) compiles a SQL statement containing one or more ? placeholders in place of the actual values, returning a statement object you will attach real values to and then execute -- the placeholders mark exactly where values belong without specifying what they are yet.

Note: Use one ? placeholder per value, in the exact order those values will later be bound, matching the query's intended structure.

Warning: A ? placeholder can only stand in for a value (like a WHERE clause comparison) -- it cannot be used for a table name, column name, or other structural part of the SQL.

Example: Preparing a Statement with Placeholders

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$stmt = $db->prepare("INSERT INTO users (name) VALUES (?)"); // mysqli_prepare($conn, $sql)
echo "Statement compiled with a placeholder";
?>

Binding Values with bind_param

mysqli_stmt_bind_param($stmt, $types, ...$values) attaches actual values to a prepared statement's placeholders, in order -- the $types string tells MySQL each value's kind: "i" for integer, "d" for double/float, "s" for string, and "b" for binary blob data.

Note: Match the type string exactly to each value's real type, in the same left-to-right order as the placeholders in your SQL.

Warning: Passing an "i" type for a value that is not actually a clean integer (like a string with letters in it) can produce unexpected results or an error, depending on the value.

Example: Binding Values with bind_param

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER, name TEXT)");
$stmt = $db->prepare("INSERT INTO users (id, name) VALUES (?, ?)");
$stmt->bindValue(1, 1, SQLITE3_INTEGER); // like "i" in mysqli's bind_param
$stmt->bindValue(2, "Alice", SQLITE3_TEXT); // like "s"
$stmt->execute();
echo "Values bound by type";
?>

Executing and Retrieving Results

mysqli_stmt_execute($stmt) runs the prepared statement with its bound values, and mysqli_stmt_get_result($stmt) converts the statement's result into a regular result set you can loop over with the same fetch functions used for a plain query, like mysqli_fetch_assoc().

Note: Call mysqli_stmt_get_result() right after execute() to get a familiar result set object you can process exactly like a normal query result.

Warning: mysqli_stmt_get_result() requires the mysqlnd driver, which is the default in most modern PHP installations, but worth confirming on unusual hosting setups.

Example: Executing and Retrieving Results

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$db->exec("INSERT INTO users (name) VALUES ('Alice')");
$stmt = $db->prepare("SELECT * FROM users WHERE name = ?");
$stmt->bindValue(1, "Alice", SQLITE3_TEXT);
$result = $stmt->execute();
print_r($result->fetchArray(SQLITE3_ASSOC));
?>

Reusing a Prepared Statement

A single prepared statement can be executed multiple times with different bound values, without re-preparing it each time -- just call bind_param (or rebind) and execute again for each new set of values, which is both safer and faster than preparing a fresh statement per call.

Note: Prepare a statement once outside a loop when you know you will run it many times with different values, rather than re-preparing it on every iteration.

Warning: Reusing a prepared statement across genuinely different SQL structures (not just different values) is not possible -- a new structure needs a new prepare() call.

Example: Reusing a Prepared Statement

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$stmt = $db->prepare("INSERT INTO users (name) VALUES (?)");
foreach (["Alice", "Bob", "Carol"] as $name) {
    $stmt->bindValue(1, $name, SQLITE3_TEXT);
    $stmt->execute(); // same statement, new values each time
}
echo "3 rows inserted with one prepared statement";
?>

Common Mistakes

Skipping prepared statements for a query because the input 'seems safe', when any value originating from a user, even indirectly, should be treated as untrusted.
Forgetting to specify the correct type string (s, i, d, b) when calling bind_param, causing values to be interpreted incorrectly.
Mixing manually escaped, concatenated SQL with prepared statement placeholders in the same query, defeating the safety prepared statements are meant to provide.

Chapter Summary

mysqli_prepare($conn, $sql) compiles a SQL statement with ? placeholders where values will go, before any actual data is attached.
mysqli_stmt_bind_param($stmt, $types, ...$values) attaches real values to the placeholders, with a type string indicating each value's kind.
The database engine keeps the SQL structure and the bound values strictly separate, making SQL injection through bound values structurally impossible.

Browser Support

Prepared statements have been supported by mysqli since PHP 5, and are considered the standard, safe way to run parameterized queries.

🔒

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP MySQL Insert Multiple Next Lesson → PHP MySQL Select Data