Introduction
When PHP applications accept information from users, that information may become part of a database query. If it is handled incorrectly, attackers can manipulate SQL queries and access or damage database information. In this chapter, you will learn how prepared statements work, how to bind values and how to protect PHP applications against SQL injection using MySQLi and PDO. Prepared Statements and SQL Injection Prevention in PHP Practice Questions with Solutions to help you undretsand the concepts.
Database Setup
Before starting the practice questions, create a database named school_db and a table named students.
SQL Table Structure
CREATE DATABASE school_db;
USE school_db;
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
course VARCHAR(100),
marks INT
);
Insert Sample Records
INSERT INTO students (name, email, course, marks)
VALUES
('Rahul', 'rahul@gmail.com', 'PHP', 85),
('Priya', 'priya@gmail.com', 'Python', 90),
('Amit', 'amit@gmail.com', 'Java', 75),
('Neha', 'neha@gmail.com', 'PHP', 95),
('Karan', 'karan@gmail.com', 'Python', 80);
The following questions use this database and table.
Understanding Prepared Statements
A prepared statement is a way to execute an SQL query by keeping the query structure separate from the values supplied to it.
For example, suppose you want to find a student using their email address.
A query written by directly joining user input into SQL might look like this:
$email = $_POST["email"];
$sql = "SELECT * FROM students WHERE email = '$email'";
This approach is unsafe because user input is inserted directly into the SQL query.
Instead, use a prepared statement:
$sql = "SELECT * FROM students WHERE email = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $email);
$stmt->execute();
Here, ? is a placeholder. The actual email address is supplied separately through bind_param().
The database treats the supplied value as data rather than as part of the SQL command.
What Is SQL Injection?
SQL injection is an attack in which someone supplies specially crafted input to manipulate an application’s SQL query.
For example, an attacker might enter:
' OR '1'='1
If an application directly joins this input into a SQL condition, the resulting query may behave differently from what the developer intended.
Prepared statements help prevent this type of attack by separating SQL instructions from user-supplied values.
Question 1: Create a Prepared Statement Using MySQLi
Problem Statement
Create a PHP program that connects to the school_db database and prepares a SELECT query to find a student by email address.
Use the email rahul@gmail.com as the search value.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$sql = "SELECT * FROM students WHERE email = ?";
$stmt = $conn->prepare($sql);
$email = "rahul@gmail.com";
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
echo "Student Name: " . htmlspecialchars($row["name"]);
echo "<br>Course: " . htmlspecialchars($row["course"]);
echo "<br>Marks: " . $row["marks"];
} else {
echo "Student not found.";
}
$stmt->close();
$conn->close();
?>
Sample Output
Student Name: Rahul
Course: PHP
Marks: 85
Explanation
prepare()prepares the SQL query.?represents a placeholder for the email address.bind_param("s", $email)attaches the email value to the placeholder.smeans the supplied value is a string.execute()runs the prepared query.get_result()retrieves the query result.fetch_assoc()retrieves the matching student record.
The email address is supplied separately from the SQL query, making the operation safer than directly joining user input into SQL.
Concepts Covered
- MySQLi prepared statements
- SQL placeholders
prepare()bind_param()execute()fetch_assoc()
Question 2: Use Multiple Placeholders in a MySQLi Prepared Statement
Problem Statement
A school wants to find students based on their course and minimum marks.
Create a PHP program that retrieves students who meet these conditions:
- Course: PHP
- Minimum Marks: 85
Use two placeholders in the prepared statement.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$sql = "SELECT * FROM students
WHERE course = ? AND marks >= ?";
$stmt = $conn->prepare($sql);
$course = "PHP";
$minimumMarks = 85;
$stmt->bind_param("si", $course, $minimumMarks);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "Name: " . htmlspecialchars($row["name"]) . "<br>";
echo "Course: " . htmlspecialchars($row["course"]) . "<br>";
echo "Marks: " . $row["marks"] . "<br><br>";
}
} else {
echo "No matching students found.";
}
$stmt->close();
$conn->close();
?>
Sample Output
Name: Rahul
Course: PHP
Marks: 85
Name: Neha
Course: PHP
Marks: 95
Explanation
This query contains two placeholders:
- The first
?represents the course. - The second
?represents the minimum marks.
The bind_param("si", ...) method specifies the data types:
srepresents a string.irepresents an integer.
The AND condition ensures that both conditions must be satisfied.
The query returns students enrolled in PHP who have at least 85 marks.
Concepts Covered
- Multiple placeholders
- MySQLi prepared statements
- Binding multiple values
- String and integer parameters
- Multiple SQL conditions
Question 3: Insert a Student Record Using MySQLi Prepared Statements
Problem Statement
A school wants to register a new student.
Create a PHP program that inserts the following information into the students table using a prepared statement.
| Name | Course | Marks | |
|---|---|---|---|
| Anjali | anjali@gmail.com | PHP | 88 |
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$sql = "INSERT INTO students (name, email, course, marks)
VALUES (?, ?, ?, ?)";
$stmt = $conn->prepare($sql);
$name = "Anjali";
$email = "anjali@gmail.com";
$course = "PHP";
$marks = 88;
$stmt->bind_param("sssi", $name, $email, $course, $marks);
if ($stmt->execute()) {
echo "Student inserted successfully!";
} else {
echo "Unable to insert student.";
}
$stmt->close();
$conn->close();
?>
Sample Output
Student inserted successfully!
Explanation
- The INSERT query contains four placeholders.
prepare()prepares the SQL statement.bind_param("sssi", ...)attaches four PHP variables to the placeholders.- The first three values are strings, and the last value is an integer.
execute()inserts the student record.
Prepared statements protect the query structure from being changed by the supplied values.
Concepts Covered
- Secure INSERT operation
- Prepared statements
- Multiple placeholders
bind_param()- SQL injection prevention
Question 4: Retrieve a Student Using PDO Prepared Statements
Problem Statement
A school administrator wants to search for a student using an email address.
Create a PHP program using PDO that retrieves the student whose email address is priya@gmail.com.
PHP Solution
<?php
try {
$conn = new PDO(
"mysql:host=localhost;dbname=school_db;charset=utf8mb4",
"root",
""
);
$conn->setAttribute(
PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION
);
$sql = "SELECT * FROM students WHERE email = :email";
$stmt = $conn->prepare($sql);
$email = "priya@gmail.com";
$stmt->execute([
":email" => $email
]);
$student = $stmt->fetch(PDO::FETCH_ASSOC);
if ($student) {
echo "Name: " . htmlspecialchars($student["name"]) . "<br>";
echo "Email: " . htmlspecialchars($student["email"]) . "<br>";
echo "Course: " . htmlspecialchars($student["course"]) . "<br>";
echo "Marks: " . $student["marks"];
} else {
echo "Student not found.";
}
} catch (PDOException $e) {
echo "Database operation failed.";
}
?>
Sample Output
Name: Priya
Email: priya@gmail.com
Course: Python
Marks: 90
Explanation
PDO supports named placeholders such as :email.
prepare()prepares the SQL query.:emailrepresents the email value.execute()supplies the email through an associative array.fetch(PDO::FETCH_ASSOC)retrieves one matching record.htmlspecialchars()safely displays text in HTML.
Unlike MySQLi, PDO does not require bind_param() to pass values through an array to execute().
Concepts Covered
- PDO prepared statements
- Named placeholders
execute()fetch()- Associative arrays
Question 5: Insert a Record Using PDO Prepared Statements
Problem Statement
A training institute wants to register a new student in its database.
Insert the following record using PDO prepared statements.
| Name | Course | Marks | |
|---|---|---|---|
| Mohit | mohit@gmail.com | Python | 92 |
PHP Solution
<?php
try {
$conn = new PDO(
"mysql:host=localhost;dbname=school_db;charset=utf8mb4",
"root",
""
);
$conn->setAttribute(
PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION
);
$sql = "INSERT INTO students (name, email, course, marks)
VALUES (:name, :email, :course, :marks)";
$stmt = $conn->prepare($sql);
$stmt->execute([
":name" => "Mohit",
":email" => "mohit@gmail.com",
":course" => "Python",
":marks" => 92
]);
echo "Student record inserted successfully!";
} catch (PDOException $e) {
echo "Unable to insert student record.";
}
?>
Sample Output
Student record inserted successfully!
Explanation
This example uses four named placeholders:
:name:email:course:marks
The execute() method receives an associative array containing the values for these placeholders.
PDO keeps the supplied values separate from the SQL query structure. This helps prevent SQL injection.
Concepts Covered
- PDO INSERT query
- Named placeholders
- Prepared statements
- Associative arrays
- Secure database insertion
Question 6: Update Student Marks Using Prepared Statements
Problem Statement
A teacher wants to update the marks of a student.
Update the marks of student ID 3 from 75 to 85 using a MySQLi prepared statement.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$sql = "UPDATE students
SET marks = ?
WHERE id = ?";
$stmt = $conn->prepare($sql);
$newMarks = 85;
$studentId = 3;
$stmt->bind_param("ii", $newMarks, $studentId);
if ($stmt->execute()) {
if ($stmt->affected_rows > 0) {
echo "Student marks updated successfully!";
} else {
echo "No record was changed.";
}
} else {
echo "Unable to update student marks.";
}
$stmt->close();
$conn->close();
?>
Sample Output
Student marks updated successfully!
Explanation
The UPDATE query contains two placeholders.
- The first placeholder represents the new marks.
- The second placeholder represents the student ID.
bind_param("ii", ...) specifies that both values are integers.
The WHERE condition ensures that only the selected student’s marks are updated.
If the new marks are already stored in the database, the statement may execute successfully but report zero changed rows.
Concepts Covered
- UPDATE query
- Prepared statements
- Multiple integer parameters
- WHERE condition
affected_rows
Question 7: Delete a Student Record Using PDO Prepared Statements
Problem Statement
A school wants to remove a student from the database using their email address.
Create a PHP program using PDO that deletes the student whose email is karan@gmail.com.
PHP Solution
<?php
try {
$conn = new PDO(
"mysql:host=localhost;dbname=school_db;charset=utf8mb4",
"root",
""
);
$conn->setAttribute(
PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION
);
$sql = "DELETE FROM students WHERE email = :email";
$stmt = $conn->prepare($sql);
$email = "karan@gmail.com";
$stmt->execute([
":email" => $email
]);
if ($stmt->rowCount() > 0) {
echo "Student record deleted successfully!";
} else {
echo "Student record not found.";
}
} catch (PDOException $e) {
echo "Unable to delete student record.";
}
?>
Sample Output
Student record deleted successfully!
Explanation
DELETE FROM studentsspecifies the table.WHERE email = :emailidentifies the record to delete.prepare()prepares the query.execute()supplies the email value and executes the query.rowCount()reports how many rows were affected by the DELETE operation.
The email address is passed separately from the SQL query, so it cannot change the query structure.
Concepts Covered
- PDO DELETE query
- Named placeholders
- Prepared statements
execute()rowCount()
Question 8: Understand SQL Injection Through an Unsafe Login Query
Problem Statement
A website has a login system that checks a user’s email and password in a database.
The following code directly joins user input into an SQL query.
Study this example and understand why it is unsafe.
Unsafe PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
$email = $_POST["email"];
$password = $_POST["password"];
$sql = "SELECT * FROM users
WHERE email = '$email'
AND password = '$password'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "Login successful!";
} else {
echo "Invalid login details.";
}
$conn->close();
?>
Why Is This Code Unsafe?
The problem is that user input is directly inserted into the SQL query.
An attacker could enter specially crafted text into the email or password field. This may change the meaning of the SQL condition.
For example, an input such as:
' OR '1'='1
can manipulate a query if the input is joined directly into SQL.
When the query is constructed unsafely, the application may return records that should not have matched.
Safe PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$email = $_POST["email"] ?? "";
$password = $_POST["password"] ?? "";
$sql = "SELECT * FROM users WHERE email = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();
if ($user && password_verify($password, $user["password"])) {
echo "Login successful!";
} else {
echo "Invalid login details.";
}
$stmt->close();
$conn->close();
?>
Sample Output
For valid credentials:
Login successful!
For invalid credentials:
Invalid login details.
Explanation
The safe version changes the login process.
- The email is passed through a prepared statement.
- The query searches for a matching email.
password_verify()checks the submitted password against the stored password hash.- The application does not compare a plain-text password directly with a database value.
Important: The users table used in this example must contain a password column storing hashes created with PHP’s password_hash() function. Never store plain-text passwords. A real login system should also use HTTPS, session security, rate limiting and appropriate account protections.
Concepts Covered
- SQL injection
- Unsafe SQL queries
- Prepared statements
password_verify()- Password hashing
- Secure login design
Question 9: Validate User Input Before Executing a Prepared Statement
Problem Statement
A school administrator wants to search for a student using their ID.
Create a PHP program that:
- Receives a student ID.
- Checks whether the ID is a valid positive integer.
- Uses a prepared statement to retrieve the student.
- Displays the student details if the record exists.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$studentId = $_POST["student_id"] ?? "";
$studentId = filter_var(
$studentId,
FILTER_VALIDATE_INT,
["options" => ["min_range" => 1]]
);
if ($studentId === false || $studentId === null) {
echo "Please enter a valid student ID.";
} else {
$sql = "SELECT * FROM students WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $studentId);
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
echo "Name: " . htmlspecialchars($row["name"]) . "<br>";
echo "Course: " . htmlspecialchars($row["course"]) . "<br>";
echo "Marks: " . $row["marks"];
} else {
echo "Student not found.";
}
$stmt->close();
}
$conn->close();
?>
Sample Output
For student ID 3:
Name: Amit
Course: Java
Marks: 75
For an invalid ID such as abc:
Please enter a valid student ID.
Explanation
This program uses two layers of protection.
Input validation:
filter_var()checks whether the supplied value is a valid integer.FILTER_VALIDATE_INTvalidates the integer format.min_rangeensures that the ID is at least 1.
Database protection:
prepare()prepares the SQL query.bind_param("i", $studentId)binds the ID as an integer.execute()runs the query.
Input validation and prepared statements have different purposes. Validation checks whether the input is acceptable for the application, while prepared statements protect the SQL query structure.
Concepts Covered
- Input validation
filter_var()FILTER_VALIDATE_INT- Prepared statements
- SQL injection prevention
- Safe database searching
Question 10: Build a Secure Student Search Program Using PDO
Problem Statement
A school wants a student search feature.
Create a PHP program that:
- Accepts a course name from the user.
- Validates that the course name is not empty.
- Uses a PDO prepared statement to search for matching students.
- Displays all matching students.
- Shows a message if no records are found.
PHP Solution
<?php
try {
$conn = new PDO(
"mysql:host=localhost;dbname=school_db;charset=utf8mb4",
"root",
""
);
$conn->setAttribute(
PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION
);
$course = trim($_POST["course"] ?? "");
if ($course === "") {
echo "Please enter a course name.";
} else {
$sql = "SELECT * FROM students WHERE course = :course";
$stmt = $conn->prepare($sql);
$stmt->execute([
":course" => $course
]);
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (count($students) > 0) {
echo "Students in " .
htmlspecialchars($course) . " Course<br><br>";
foreach ($students as $student) {
echo "Name: " .
htmlspecialchars($student["name"]) . "<br>";
echo "Email: " .
htmlspecialchars($student["email"]) . "<br>";
echo "Marks: " . $student["marks"] . "<br><br>";
}
} else {
echo "No students found for this course.";
}
}
} catch (PDOException $e) {
echo "Database operation failed.";
}
?>
Sample Output
If the user submits PHP:
Students in PHP Course
Name: Rahul
Email: rahul@gmail.com
Marks: 85
Name: Neha
Email: neha@gmail.com
Marks: 95
If the user submits an empty value:
Please enter a course name.
Explanation
This program combines input validation, prepared statements and safe output.
trim()removes unnecessary spaces from the beginning and end of the course name.- The empty string check prevents an empty search.
prepare()prepares the SELECT query.execute()supplies the course name separately.fetchAll(PDO::FETCH_ASSOC)retrieves all matching records.count()checks whether matching records exist.htmlspecialchars()safely displays text in HTML.
Even if someone enters SQL-related characters in the course field, the prepared statement treats the supplied value as data rather than as SQL instructions.
Concepts Covered
- PDO prepared statements
- Input validation
trim()- Named placeholders
fetchAll()- SQL injection prevention
- Safe HTML output
MySQLi vs PDO: Prepared Statements
Both MySQLi and PDO support prepared statements, but their syntax is slightly different.
| Feature | MySQLi | PDO |
|---|---|---|
| Prepare query | prepare() | prepare() |
| Bind values | bind_param() or execute() with appropriate statement methods | bindValue(), bindParam() or execute() array |
| Positional placeholders | ? | ? |
| Named placeholders | No | Yes |
| Execute query | execute() | execute() |
| Fetch one record | fetch_assoc() with result | fetch() |
| Fetch all records | Loop through result | fetchAll() |
Important MySQLi Data Type Identifiers
When using bind_param(), MySQLi requires a type identifier for each bound variable.
| Identifier | Meaning | Example |
|---|---|---|
s | String | Student name |
i | Integer | Student ID |
d | Double | Decimal value |
b | Blob | Binary data |
Example:
$stmt->bind_param("sid", $name, $age, $salary);
Here:
srepresents the name.irepresents the age.drepresents the salary.
The number and order of type identifiers must match the bound variables.
Best Practices for SQL Injection Prevention
- Use prepared statements for SQL queries containing values.
- Never directly join user input into SQL query strings.
- Validate input according to the expected data type and business rules.
- Use
htmlspecialchars()when displaying untrusted text in HTML. - Store passwords using
password_hash()and verify them withpassword_verify(). - Give database accounts only the permissions they need.
- Keep database credentials outside publicly accessible files.
- Do not display raw SQL errors or database credentials to website visitors.
- Use HTTPS to protect information while it travels between the browser and server.
- Remember that table names, column names and SQL keywords generally cannot be supplied as ordinary prepared-statement values. Use a fixed allowlist when dynamic identifiers are required.
Key Takeaways
- Prepared statements separate SQL query structure from supplied values.
- SQL injection occurs when an attacker manipulates an application’s SQL query through unsafe input handling.
- MySQLi and PDO both support prepared statements.
- MySQLi uses
bind_param()to bind values with data type identifiers. - PDO supports positional and named placeholders.
- Input validation checks whether information is acceptable for an application.
- Prepared statements help protect SQL queries from injection attacks.
password_hash()andpassword_verify()should be used for password storage and verification.htmlspecialchars()helps protect HTML output from unsafe characters.- Secure PHP applications should combine prepared statements, input validation, access control and safe output handling.
Frequently Asked Questions (FAQs)
1. What is a prepared statement in PHP?
A prepared statement is an SQL query prepared separately from the values supplied to it. It allows PHP to pass values through placeholders instead of joining them directly into the SQL query.
2. What is SQL injection?
SQL injection is a security attack in which an attacker supplies specially crafted input to manipulate an application’s SQL query. It can potentially expose, modify or delete database information.
3. How do prepared statements prevent SQL injection?
Prepared statements separate SQL instructions from supplied values. The database treats the bound values as data rather than allowing them to change the structure of the query.
4. What is the difference between prepare() and execute()?
prepare() prepares the SQL statement for execution. execute() runs that statement with the supplied parameter values.
5. What is the difference between bind_param() and bindValue()?
MySQLi uses bind_param() to bind variables with specified data types. PDO uses bindValue() to bind a specific value to a placeholder.
6. Can we use named placeholders in MySQLi?
No. MySQLi prepared statements use question mark placeholders (?). PDO supports both question mark placeholders and named placeholders.
7. Are prepared statements enough to secure a PHP application?
No. Prepared statements are an important protection against SQL injection, but applications also need input validation, safe output handling, secure password storage, proper permissions and other security controls.
8. What is the difference between input validation and prepared statements?
Input validation checks whether supplied information follows the application’s rules. Prepared statements protect SQL query structure from being manipulated through supplied values. Both should be used together.
9. Can prepared statements protect dynamic table names?
Prepared-statement placeholders are intended for values, not table or column names. When dynamic identifiers are required, select them from a predefined allowlist rather than accepting arbitrary user input.
10. Should we use MySQLi or PDO for secure database operations?
Both are suitable. MySQLi and PDO support prepared statements and can be used to build secure PHP database applications.
Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.
