PHP Prepared Statements

PHP Prepared Statements – Secure Database Queries 🔒

In PHP, **prepared statements** are a powerful way to execute database queries while preventing SQL injection attacks. Instead of inserting values directly into queries (which can be risky 😨), we use placeholders and bind values securely.

Prepared statements are essential for handling user input safely. Let’s learn how to use them with MySQLi and PDO.

🔹 Why Use Prepared Statements?

Using raw SQL queries can expose your database to **SQL injection**. Prepared statements help by:

  • Preventing SQL injection attacks 🛡️
  • Improving security by separating SQL logic from data
  • Boosting performance when executing the same query multiple times

1️⃣ Using Prepared Statements with MySQLi

📝 Example: Insert Data with MySQLi Prepared Statements

Here’s how to insert user data safely using MySQLi prepared statements.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "secure_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare the statement
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");

// Bind parameters (s = string, i = integer, d = double, b = blob)
$name = "Alice Wonderland";
$email = "alice@example.com";
$stmt->bind_param("ss", $name, $email);

// Execute the statement
if ($stmt->execute()) {
    echo "User added successfully!";
} else {
    echo "Error: " . $stmt->error;
}

// Close statement and connection
$stmt->close();
$conn->close();
?>

Try It Now

📝 Example: Retrieve Data with MySQLi Prepared Statements

Fetching user records securely using prepared statements.

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

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare statement
$stmt = $conn->prepare("SELECT id, name, email FROM users WHERE name = ?");
$name = "Alice Wonderland";
$stmt->bind_param("s", $name);

// Execute and fetch results
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
}

// Close statement and connection
$stmt->close();
$conn->close();
?>

Try It Now

2️⃣ Using Prepared Statements with PDO

📝 Example: Insert Data with PDO Prepared Statements

Using PDO (PHP Data Objects) for database interaction.

<?php
$dsn = "mysql:host=localhost;dbname=secure_db";
$username = "root";
$password = "";

try {
    // Create PDO instance
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Prepare statement
    $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");

    // Bind parameters
    $name = "Bob Builder";
    $email = "bob@example.com";
    $stmt->bindParam(":name", $name);
    $stmt->bindParam(":email", $email);

    // Execute statement
    $stmt->execute();
    echo "User added successfully!";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Close connection
$pdo = null;
?>

Try It Now

📝 Example: Retrieve Data with PDO Prepared Statements

Fetching user data securely with PDO.

<?php
$dsn = "mysql:host=localhost;dbname=secure_db";
$username = "root";
$password = "";

try {
    // Create PDO instance
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Prepare statement
    $stmt = $pdo->prepare("SELECT id, name, email FROM users WHERE name = :name");
    $name = "Bob Builder";
    $stmt->bindParam(":name", $name);
    
    // Execute and fetch results
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Close connection
$pdo = null;
?>

Try It Now

🎯 Summary: MySQLi vs. PDO Prepared Statements

Feature MySQLi PDO
Supports Multiple Databases ❌ No (MySQL only) ✅ Yes (MySQL, PostgreSQL, SQLite, etc.)
Object-Oriented ✅ Yes ✅ Yes
Named Placeholders ❌ No ✅ Yes
Recommended for New Projects 🤔 Maybe ✅ Yes

🚀 Next Steps

Try modifying the examples above and practice with different values. Prepared statements are an essential skill for secure PHP applications!