Prepared Statements: Lock Down Your PHP Database

PHP

"PHP is a server-side programming language that is flexible, easy to learn, and still the backbone of many websites, even though it is often thought to be outdated"

Hey, PHP warrior!  So, you’ve been playing around with MySQL queries like:

$sql = "SELECT * FROM users WHERE username = '" . $_GET['username'] . "'";
$result = $conn->query($sql);

Uh-oh...  That’s a SQL injection time bomb!  Someone can exploit it and delete your database faster than you can say "Oops!" 

But don’t worry! Prepared Statements are here to save the day!  They prevent SQL injection, improve performance, and make your code more secure. Let’s dive in!

Why Use Prepared Statements?

Here’s why you need to switch to prepared statements right now:

Prevents SQL Injection – Faster Execution – Reusable queries mean better performance! More Readable & Maintainable Code – Clean, structured, and easier to debug!

How Prepared Statements Work

A prepared statement separates SQL logic from user input, making it immune to injection attacks. Instead of directly inserting user input into SQL queries, we use placeholders (?) and bind values to them safely.

Here’s how you do it:

Step 1: Prepare the SQL Statement

Instead of:

$sql = "SELECT * FROM users WHERE username = '$username'";

Do this:

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

Step 2: Bind Parameters

Before executing, we bind values securely:

$stmt->bind_param("s", $username);

Where:

• "s" = String (other types: i for int, d for double, b for blob)
• $username = The actual value

Step 3: Execute & Fetch Data

$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo "User: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
}

Boom! Safe & efficient!

Inserting Data Safely (Real-World Example: User Registration)

Imagine a registration form where users sign up with a username and email. Instead of inserting data directly into SQL (which is dangerous), we do:

$stmt = $conn->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);
$stmt->execute();

User added securely!

Updating Data Securely (Example: Updating User Email)

Let’s say a user wants to update their email. Here’s the safe way to do it:

$stmt = $conn->prepare("UPDATE users SET email=? WHERE username=?");
$stmt->bind_param("ss", $new_email, $username);
$stmt->execute();

No more risky updates!

Deleting Data Safely (Example: Deleting a User Account)

$stmt = $conn->prepare("DELETE FROM users WHERE username=?");
$stmt->bind_param("s", $username);
$stmt->execute();

Bye-bye, user (safely)!

Using Prepared Statements for Login Authentication

A common security issue is unsafe login queries. Let’s secure our login system:

$stmt = $conn->prepare("SELECT id, password FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();
if (password_verify($password, $user['password'])) {
    echo "Login successful!";
} else {
    echo "Invalid credentials!";
}

This keeps hackers OUT!

Bonus: Using PDO for Prepared Statements

If you prefer PDO (PHP Data Objects) instead of MySQLi, here’s how you do it:

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
$stmt->execute([$username]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

PDO is even more flexible!

 You’ve now mastered prepared statements and can: Prevent SQL Injection, Write faster, optimized queries, Keep your database hacker-proof.