PHP MySQL Prepared Statements: A Comprehensive Guide

Prepared statements are the single most important technique for writing secure PHP code that talks to a database. They separate the SQL command from the data it operates on, which stops SQL injection cold and also speeds up queries you run repeatedly. This guide explains what prepared statements are, why they matter, and how to use them with both the MySQLi and PDO extensions.

This page covers:

• What a prepared statement is and why the "compile once, run many" model exists
• Writing prepared INSERT and SELECT queries with MySQLi
• The same patterns with PDO (named placeholders)
• Common gotchas: error reporting, binding the wrong type, and reusing statements

What are Prepared Statements?

A prepared statement is a SQL query sent to the database in two stages:

1. Prepare — you send the SQL with ? (or :name) placeholders instead of real values. The database parses, compiles, and optimizes this template once.
2. Execute — you send the actual values separately. The database slots them into the already-compiled plan and runs it.

Because the values travel on a different channel from the SQL text, the database never confuses data with commands. A value like ' OR '1'='1 is treated as a literal string to search for, not as SQL to run — which is exactly why injection attacks fail against prepared statements.

Why Use Prepared Statements?

• Security. User input can never alter the structure of your query. This is the recommended defense against SQL injection and the reason you should never build queries by concatenating variables into a string.
• Performance. The query is parsed and compiled once. If you execute it many times (for example inserting 1,000 rows in a loop), the database reuses the same plan instead of re-parsing every time.
• Cleaner code. Placeholders mean no manual escaping with mysqli_real_escape_string() and no quote juggling. You bind a variable and you are done.

Rule of thumb: the moment any part of a query comes from user input — a form field, a URL parameter, a cookie — use a prepared statement.

The Steps

Every prepared statement follows the same lifecycle:

1. Connect to the database.
2. Prepare the SQL with placeholders.
3. Bind your variables to the placeholders.
4. Execute the statement.
5. Fetch results (for SELECT queries).
6. Close the statement.

Prepared INSERT with MySQLi

MySQLi uses positional ? placeholders. You bind them with mysqli_stmt_bind_param(), where the first argument is a type string: s for string, i for integer, d for double/float, b for blob.

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // throw on errors

$conn = mysqli_connect("localhost", "username", "password", "database");

$stmt = mysqli_prepare($conn, "INSERT INTO users (name, email) VALUES (?, ?)");

// "ss" => both placeholders are strings, in order
mysqli_stmt_bind_param($stmt, "ss", $name, $email);

$name  = "John";
$email = "[email protected]";
mysqli_stmt_execute($stmt);   // inserts John

$name  = "Jane";
$email = "[email protected]";
mysqli_stmt_execute($stmt);   // reuses the same compiled statement, inserts Jane

mysqli_stmt_close($stmt);
mysqli_close($conn);

bind_param binds by reference, so you can change $name/$email and call execute() again without re-binding — the new values are picked up automatically. That is the "compile once, run many" advantage in action.

Prepared SELECT with MySQLi

For a SELECT, you execute the statement and then read rows out. The cleanest way is mysqli_stmt_get_result(), which gives you a normal result set you can loop over:

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$conn = mysqli_connect("localhost", "username", "password", "database");

$stmt = mysqli_prepare($conn, "SELECT id, name FROM users WHERE email = ?");
mysqli_stmt_bind_param($stmt, "s", $email);

$email = "[email protected]";
mysqli_stmt_execute($stmt);

$result = mysqli_stmt_get_result($stmt);
while ($row = mysqli_fetch_assoc($result)) {
    echo $row["id"] . ": " . $row["name"] . "\n";
}

mysqli_stmt_close($stmt);
mysqli_close($conn);

Prepared Statements with PDO

PDO is the other common database extension and many developers prefer it because it works across different database systems and supports named placeholders (:email), which are easier to read.

<?php

$pdo = new PDO(
    "mysql:host=localhost;dbname=database;charset=utf8mb4",
    "username",
    "password",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

// INSERT with named placeholders
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->execute([":name" => "John", ":email" => "[email protected]"]);

// SELECT and fetch
$stmt = $pdo->prepare("SELECT id, name FROM users WHERE email = :email");
$stmt->execute([":email" => "[email protected]"]);

foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    echo $row["id"] . ": " . $row["name"] . "\n";
}

Notice you do not declare types with PDO — you pass an associative array of values directly to execute(). Setting PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION makes PDO throw on failure so problems are never silently ignored.

Common Mistakes

• Forgetting error reporting. By default MySQLi can fail quietly. Call mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) (or use PDO exceptions) so a bad query throws instead of returning false.
• Binding the wrong type count. The type string in bind_param must have exactly one character per ?. "ss" for two placeholders, "si" for a string then an integer.
• Placing a placeholder where SQL doesn't allow one. You can bind values, not identifiers. WHERE id = ? works; ORDER BY ? or SELECT * FROM ? does not — table and column names must be hard-coded or whitelisted.
• Concatenating "just this one" value. There is no safe exception. If it came from a user, bind it.

Conclusion

Prepared statements split a query into a compiled SQL template plus the values that fill it in. That separation is what makes them both safe (injection-proof) and fast (parsed once, executed many times). Use MySQLi's ? placeholders or PDO's named :value placeholders, but always bind user input rather than building SQL by hand.

Continue with related chapters: Connect to MySQL, Insert Data, Select Data, and the mysqli_prepare() reference.

Practice