Prepared Statements and SQL Injection Prevention in PHP Practice Questions with Solutions

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.
  • s means 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:

  • s represents a string.
  • i represents 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.

NameEmailCourseMarks
Anjalianjali@gmail.comPHP88

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.
  • :email represents 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.

NameEmailCourseMarks
Mohitmohit@gmail.comPython92

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 students specifies the table.
  • WHERE email = :email identifies 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:

  1. Receives a student ID.
  2. Checks whether the ID is a valid positive integer.
  3. Uses a prepared statement to retrieve the student.
  4. 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_INT validates the integer format.
  • min_range ensures 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:

  1. Accepts a course name from the user.
  2. Validates that the course name is not empty.
  3. Uses a PDO prepared statement to search for matching students.
  4. Displays all matching students.
  5. 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.

FeatureMySQLiPDO
Prepare queryprepare()prepare()
Bind valuesbind_param() or execute() with appropriate statement methodsbindValue(), bindParam() or execute() array
Positional placeholders??
Named placeholdersNoYes
Execute queryexecute()execute()
Fetch one recordfetch_assoc() with resultfetch()
Fetch all recordsLoop through resultfetchAll()

Important MySQLi Data Type Identifiers

When using bind_param(), MySQLi requires a type identifier for each bound variable.

IdentifierMeaningExample
sStringStudent name
iIntegerStudent ID
dDoubleDecimal value
bBlobBinary data

Example:

$stmt->bind_param("sid", $name, $age, $salary);

Here:

  • s represents the name.
  • i represents the age.
  • d represents the salary.

The number and order of type identifiers must match the bound variables.

Best Practices for SQL Injection Prevention

  1. Use prepared statements for SQL queries containing values.
  2. Never directly join user input into SQL query strings.
  3. Validate input according to the expected data type and business rules.
  4. Use htmlspecialchars() when displaying untrusted text in HTML.
  5. Store passwords using password_hash() and verify them with password_verify().
  6. Give database accounts only the permissions they need.
  7. Keep database credentials outside publicly accessible files.
  8. Do not display raw SQL errors or database credentials to website visitors.
  9. Use HTTPS to protect information while it travels between the browser and server.
  10. 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() and password_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.

Scroll to Top