Prepared statements allow PHP to execute SQL queries using parameters supplied separately from the SQL statement. They are an important defense against SQL injection and are commonly used with PDO when working with MySQL databases.
A prepared statement separates an SQL query from the values supplied by a user. The database driver handles those values as parameters instead of treating them as SQL syntax.
Example of a prepared statement:
<?php
require_once "db.php";
$sql = "SELECT id, name, email
FROM students
WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([
"id" => 1
]);
$student = $stmt->fetch();
if ($student) {
echo htmlspecialchars(
$student["name"],
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
);
} else {
echo "Student not found.";
}
?>
This example assumes your db.php file creates
a PDO connection and the students table
contains an id, name, and
email column.
Directly concatenating user input into an SQL query can allow SQL injection. Prepared statements help prevent user-supplied values from changing the intended query structure.
<?php
$id = $_GET["id"] ?? "";
$sql = "SELECT * FROM students WHERE id = " . $id;
?>This is unsafe because the value is inserted directly into the SQL text.
<?php
require_once "db.php";
$id = $_GET["id"] ?? "";
$stmt = $pdo->prepare(
"SELECT id, name FROM students WHERE id = :id"
);
$stmt->execute([
"id" => $id
]);
$student = $stmt->fetch();
?>Prepared statements protect the query structure, but input validation and authorization are still important. Validate that an ID has the expected format before using it.
Named parameters use labels such as
:name and :email.
They make queries easier to read.
<?php
require_once "db.php";
$name = "Rahul Kumar";
$email = "rahul@example.com";
$course = "PHP Programming";
$sql = "INSERT INTO students (name, email, course)
VALUES (:name, :email, :course)";
$stmt = $pdo->prepare($sql);
$stmt->execute([
"name" => $name,
"email" => $email,
"course" => $course
]);
echo "Student added successfully.";
?>Explanation:
:name represents the student name.:email represents the email address.:course represents the course name.execute() supplies the parameter values.The values are bound as data, not as SQL keywords or column names. Table names and column names generally cannot be supplied using ordinary value placeholders.
Positional parameters use question marks
(?) instead of named placeholders.
Values must be supplied in the correct order.
<?php
require_once "db.php";
$sql = "INSERT INTO students (name, email, course)
VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([
"Priya Singh",
"priya@example.com",
"Web Development"
]);
echo "Student added successfully.";
?>The first value is assigned to the first placeholder, the second to the second, and the third to the third.
Named parameters are often easier to maintain when a query contains many values. Both styles are useful.
Prepared statements are useful for searching database records using a form or a query parameter.
<?php
require_once "db.php";
$search = trim($_GET["name"] ?? "");
$sql = "SELECT id, name, email, course
FROM students
WHERE name LIKE :search
ORDER BY name";
$stmt = $pdo->prepare($sql);
$stmt->execute([
"search" => "%" . $search . "%"
]);
$students = $stmt->fetchAll();
foreach ($students as $student) {
echo htmlspecialchars(
$student["name"],
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
) . "<br>";
}
?>
If the search value is Rahul, the query
searches for names containing that text.
The percent sign (%) is a SQL LIKE wildcard.
If users must search for literal percent or underscore
characters, escape those wildcard characters separately.
You can use prepared statements with UPDATE queries to change existing records safely.
<?php
require_once "db.php";
$id = 1;
$course = "Full Stack Development";
$sql = "UPDATE students
SET course = :course
WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([
"course" => $course,
"id" => $id
]);
if ($stmt->rowCount() > 0) {
echo "A record was changed.";
} else {
echo "No changed row was reported.";
}
?>
The message from rowCount() must be
interpreted carefully: depending on the database driver,
an unchanged value or a nonexistent ID can result in
zero affected rows.
Always use an appropriate WHERE condition when you intend to update a specific record.
A prepared DELETE statement can remove a record using a parameterized ID.
<?php
require_once "db.php";
$id = 3;
$sql = "DELETE FROM students WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([
"id" => $id
]);
if ($stmt->rowCount() > 0) {
echo "Student deleted successfully.";
} else {
echo "No matching student was deleted.";
}
?>In this example, the record with ID 3 is deleted if it exists and the database reports an affected row.
In real applications, validate the requested ID, verify the user's authorization, and consider a confirmation step before deletion.
The bindValue() method binds a value to
a named or positional placeholder.
<?php
require_once "db.php";
$id = 1;
$stmt = $pdo->prepare(
"SELECT id, name, email
FROM students
WHERE id = :id"
);
$stmt->bindValue(":id", $id, PDO::PARAM_INT);
$stmt->execute();
$student = $stmt->fetch();
if ($student) {
echo htmlspecialchars(
$student["name"],
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
);
} else {
echo "Student not found.";
}
?>
PDO::PARAM_INT tells PDO to bind the value
as an integer parameter.
bindValue() binds the current value,
whereas bindParam() binds a variable by
reference and reads its value when the statement executes.
This example uses a form to search for a student by name.
Save it as search_student.php in the same
directory as db.php.
<?php
require_once "db.php";
$search = trim($_GET["name"] ?? "");
$students = [];
$message = "";
if ($search !== "") {
try {
$stmt = $pdo->prepare(
"SELECT id, name, email, course
FROM students
WHERE name LIKE :search
ORDER BY name"
);
$stmt->execute([
"search" => "%" . $search . "%"
]);
$students = $stmt->fetchAll();
if (!$students) {
$message = "No matching students found.";
}
} catch (PDOException $e) {
error_log($e->getMessage());
$message = "Unable to search students.";
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Search Student</title>
</head>
<body>
<h2>Search Student</h2>
<form method="get">
<label for="name">Student Name:</label>
<input
type="text"
id="name"
name="name"
value="<?= htmlspecialchars(
$search,
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
) ?>"
>
<button type="submit">Search</button>
</form>
<p>
<?= htmlspecialchars(
$message,
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
) ?>
</p>
<table border="1" cellpadding="8">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Course</th>
</tr>
<?php foreach ($students as $student): ?>
<tr>
<td><?= (int) $student["id"] ?></td>
<td><?= htmlspecialchars(
$student["name"],
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
) ?></td>
<td><?= htmlspecialchars(
$student["email"],
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
) ?></td>
<td><?= htmlspecialchars(
$student["course"],
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
) ?></td>
</tr>
<?php endforeach; ?>
</table>
</body>
</html>This example demonstrates how prepared statements, form input, and output escaping work together.
Prepared statements protect parameter values from being interpreted as SQL syntax. Output escaping separately helps prevent database content from being interpreted as HTML.
1. What is a major benefit of prepared statements?
2. Which method prepares an SQL statement in PDO?
3. Which symbol is used for a positional placeholder in PDO SQL?