Preventing SQL Injection with Prepared Statements in PHP: The Ultimate Guide 🚀

SQL injection is one of the most dangerous web security vulnerabilities. Attackers can use it to steal, modify, or delete data from your database 😱. But don’t worry! You can completely prevent SQL injection using prepared statements in PHP.

In this step-by-step guide, you’ll learn:
✅ What SQL injection is and how it works
✅ How to prevent it using prepared statements (PDO & MySQLi)
✅ Real-world examples and a mini-project

Let’s secure your PHP applications! 🔐🚀

---

🎯 What Is SQL Injection?

SQL Injection (SQLi) is a hacking technique where attackers inject malicious SQL code into input fields to manipulate the database.

❌ Example of a Vulnerable Query

<?php
$conn = new mysqli("localhost", "root", "", "zeroexp_dev");
$username = $_GET['username']; // Unsafe user input
$password = $_GET['password']; // Unsafe user input

$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = $conn->query($query);
?>

If a hacker enters:

username: ' OR 1=1 --  
password: (anything)

The query becomes:

SELECT * FROM users WHERE username = '' OR 1=1 --' AND password = ''

🚨 What happens?

• 1=1 always evaluates to true.
• The attacker logs in as ANY user without knowing the password!

---

1️⃣ Preventing SQL Injection Using PDO (Recommended)

The best way to prevent SQL injection is by using prepared statements with PDO (PHP Data Objects).

Safe Login Query (Using PDO)

<?php
$pdo = new PDO("mysql:host=localhost;dbname=zeroexp_dev;charset=utf8", "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

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

// Use prepared statement
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->execute([$username, $password]);

if ($stmt->rowCount() > 0) {
    echo "Login successful!";
} else {
    echo "Invalid credentials!";
}
?>

🔥 Why is this secure?
✅ Uses ? placeholders (not raw input)
✅ Prevents SQL injection automatically
✅ More efficient than manual escaping

---

2️⃣ Preventing SQL Injection Using MySQLi (Alternative)

If you're using MySQLi, you can still use prepared statements.

Safe Login Query (Using MySQLi)

<?php
$conn = new mysqli("localhost", "root", "", "zeroexp_dev");

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

// Use prepared statement
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();

$result = $stmt->get_result();

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

🔥 Why is this secure?
✅ Uses ? placeholders
✅ bind_param() prevents SQL injection
✅ More secure than using raw queries

---

3️⃣ Avoiding Common SQL Injection Mistakes

❌ Never Concatenate User Input in Queries

$query = "SELECT * FROM users WHERE username = '$username'"; // ❌ Bad practice!

✅ Always Use Prepared Statements Instead

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");

❌ Never Trust GET/POST Data Without Validation

$username = $_GET['username']; // ❌ Unsafe

✅ Sanitize User Input First

$username = filter_input(INPUT_POST, "username", FILTER_SANITIZE_STRING);

---

4️⃣ Secure Insert, Update, and Delete Queries

1️⃣ Safe Insert Query

<?php
$stmt = $pdo->prepare("INSERT INTO users (username, email, password) VALUES (?, ?, ?)");
$stmt->execute(["zerodev", "contact@zeroexp.dev", "securepassword"]);
?>

2️⃣ Safe Update Query

<?php
$stmt = $pdo->prepare("UPDATE users SET email = ? WHERE username = ?");
$stmt->execute(["newemail@zeroexp.dev", "zerodev"]);
?>

3️⃣ Safe Delete Query

<?php
$stmt = $pdo->prepare("DELETE FROM users WHERE username = ?");
$stmt->execute(["zerodev"]);
?>

🔥 Why use prepared statements?
✅ No SQL injection risk
✅ Works with all queries (SELECT, INSERT, UPDATE, DELETE)
✅ More secure and efficient

---

🎯 Mini Project: Secure Login System

Let’s build a real-world login system that:
✅ Uses prepared statements
✅ Prevents SQL injection
✅ Secures user authentication

1️⃣ login.php (User Login Form)

<form action="login_process.php" method="post">
    Username: <input type="text" name="username"><br>
    Password: <input type="password" name="password"><br>
    <input type="submit" value="Login">
</form>

2️⃣ login_process.php (Handles Login Securely)

<?php
session_start();
require "db.php"; // Database connection

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $username = trim($_POST["username"] ?? '');
    $password = trim($_POST["password"] ?? '');

    if (empty($username) || empty($password)) {
        die("Both fields are required!");
    }

    $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
    $stmt->execute([$username, $password]);
    $user = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($user) {
        $_SESSION["username"] = $username;
        header("Location: dashboard.php");
    } else {
        echo "Invalid login!";
    }
}
?>

3️⃣ dashboard.php (User Dashboard)

<?php
session_start();
if (!isset($_SESSION["username"])) {
    echo "Access denied! <a href='login.php'>Login here</a>";
    exit;
}

echo "Welcome, " . $_SESSION["username"] . "! <a href='logout.php'>Logout</a>";
?>

4️⃣ logout.php (Log Out the User)

<?php
session_start();
session_unset();
session_destroy();
header("Location: login.php");
?>

🔥 What’s Happening?

• login.php provides a secure login form.
• login_process.php validates credentials using prepared statements.
• dashboard.php only allows logged-in users.
• logout.php destroys the session, logging the user out.

🚀 You now have a fully secure login system!

---

🚀 Final Thoughts

Now you know how to protect your PHP applications from SQL injection!
✅ Use PDO or MySQLi prepared statements
✅ Never trust user input
✅ Secure all CRUD operations

👉 Next: Build a Simple Login System with PHP and MySQL

Happy coding! 🎉🚀