PHP Prepared Statements (Prevent SQL Injection with Examples)

Introduction

SQL injection is one of the most common security vulnerabilities in PHP applications. If not handled properly, attackers can access or modify your database.

In this tutorial, you’ll learn how to prevent SQL injection in PHP using prepared statements with simple and practical examples.

WHAT YOU’LL LEARN

• What SQL injection is
• Why it is dangerous
• How prepared statements work
• Secure coding examples
• Real-world use cases

What is SQL Injection?

SQL Injection happens when user input is directly added to an SQL query, allowing attackers to manipulate the query.

❌ Unsafe Example

$id = $_GET['id'];
$sql = "SELECT * FROM users WHERE id = $id";

If someone enters:

1 OR 1=1

The query becomes:

SELECT * FROM users WHERE id = 1 OR 1=1

This can expose all records.

Why SQL Injection is Dangerous?

• Data theft
• Data deletion
• Admin access
• Website hacking
• Server compromise

⚠️ Never trust user input

What Are Prepared Statements?

Prepared statements:

• Separate SQL logic from data
• Prevent user input from changing the query structure
• Automatically escape values

✅ This is the most secure way to run database queries.

How Prepared Statements Work

1. Prepare SQL query
2. Bind user values
3. Execute safely

Example: Select Data Using Prepared Statement

<?php
include "db.php";

$sql = "SELECT * FROM users WHERE id = ?";
$stmt = mysqli_prepare($conn, $sql);

mysqli_stmt_bind_param($stmt, "i", $id);

$id = 1;
mysqli_stmt_execute($stmt);

$result = mysqli_stmt_get_result($stmt);

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . "<br>";
}
?>

Insert Data Using Prepared Statement

<?php
include "db.php";

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

mysqli_stmt_bind_param($stmt, "ss", $name, $email);

$name  = "Divyesh";
$email = "div@example.com";

mysqli_stmt_execute($stmt);

echo "Data inserted securely";
?>

Update Data Using Prepared Statement

$sql = "UPDATE users SET name=?, email=? WHERE id=?";
$stmt = mysqli_prepare($conn, $sql);

mysqli_stmt_bind_param($stmt, "ssi", $name, $email, $id);

mysqli_stmt_execute($stmt);

Delete Data Using Prepared Statement

$sql = "DELETE FROM users WHERE id=?";
$stmt = mysqli_prepare($conn, $sql);

mysqli_stmt_bind_param($stmt, "i", $id);

mysqli_stmt_execute($stmt);

Bind Param Types Explained

Prepared Statements vs Normal Queries

💡 Real-Life Use Case: Secure Login System

In real-world applications like login systems, user input is directly used in database queries. Without prepared statements, attackers can bypass authentication using SQL injection.

Using prepared statements ensures that login queries are secure and user data remains protected.

❌ Unsafe Login Example

$username = $_POST['username'];
$password = $_POST['password'];
$sql = "SELECT * FROM users WHERE username='$username' AND password='$password'";

👉 Problem:
An attacker can enter:

' OR '1'='1

👉 This can bypass login authentication 😱

✅ Secure Login Using Prepared Statements

<?php
include "db.php";

$username = $_POST['username'];
$password = $_POST['password'];

$sql = "SELECT * FROM users WHERE username = ? AND password = ?";
$stmt = mysqli_prepare($conn, $sql);

mysqli_stmt_bind_param($stmt, "ss", $username, $password);

mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

if (mysqli_num_rows($result) > 0) {
    echo "Login successful";
} else {
    echo "Invalid credentials";
}
?>

🔐 Why This Is Secure

• User input does NOT change SQL structure
• Query is pre-defined using placeholders (?)
• Values are safely bound before execution

In real applications, passwords should be hashed using password_hash() and verified using password_verify().

🔐 When to Use Prepared Statements

Use prepared statements in the following situations:

• When handling form input (login, registration, contact forms)
• When inserting or updating data in a database
• When working with user-generated content
• When building APIs or dynamic applications

👉 In short, use prepared statements whenever user input is involved in SQL queries.

💡 Prepared Statements in WordPress

global $wpdb;$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM wp_users WHERE ID = %d",
        $user_id
    )
);

👉 Use case:

• Custom plugins
• Secure database queries

Additional Security Tips

• Always validate user input
• Use prepared statements for all queries
• Escape output using htmlspecialchars()
• Limit database permissions
• Disable error display in production
• Use HTTPS for secure data transfer

Common Mistakes

• Mixing prepared and normal queries
• Forgetting bind types
• Trusting hidden form fields
• Skipping validation

🧪 Practical Tasks (Secure Your Code)

Practice these tasks to strengthen your understanding of SQL injection prevention in PHP. Don’t just read—practice these examples to build secure PHP applications.

🧪 Task 1: Convert Unsafe Query to Safe Query

👉 Given this unsafe code:

$id = $_GET['id'];
$sql = "SELECT * FROM users WHERE id = $id";

✅ Task:
Convert it into a prepared statement using mysqli.

🧪 Task 2: Secure a Login Form

👉 Create a login query using prepared statements:

• Input: username & password
• Output: Check if user exists

💡 Hint:
Use WHERE username = ? AND password = ?

🧪 Task 3: Insert Data Securely

👉 Write a prepared statement to insert:

• name
• email

into a users table.

🧪 Task 4: Validate User Input

👉 Before executing query:

• Ensure ID is a number
• Trim extra spaces

💡 Hint:
Use intval() and trim()

🧪 Challenge (Advanced)

👉 Create a secure search feature:

• User enters keyword
• Fetch matching results using prepared statement

FAQs

Conclusion

• SQL injection is a serious security vulnerability that can compromise your entire database. Using prepared statements ensures that user input does not interfere with SQL queries, making your application secure.
• In this tutorial, you learned how to prevent SQL injection using prepared statements with practical examples.
• 👉 Always follow secure coding practices when working with databases.