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

Syntax

php

$stmt = mysqli_prepare($conn, "SELECT * FROM table_name WHERE column = ?");
mysqli_stmt_bind_param($stmt, "s", $value);   // s = string, i = integer
mysqli_stmt_execute($stmt);

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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$db->exec("INSERT INTO users (name) VALUES ('Alice')");
// Declare `$stmt`, set to `$db->prepare("SELECT * FROM users WHERE name = ?")`
$stmt = $db->prepare("SELECT * FROM users WHERE name = ?");
$stmt->bindValue(1, "Alice", SQLITE3_TEXT);
// Declare `$result`, set to `$stmt->execute()`
$result = $stmt->execute();
// Print a human-readable dump of `$result->fetchArray(SQLITE3_ASSOC)`
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";
?>

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