Prepared Statements

Prepared Statements & SQL Injection Prevention (prepare(), execute())

Prepared statements separate SQL code from dynamic user parameter data, rendering SQL Injection (SQLi) attacks mathematically impossible.

How Prepared Statements Prevent SQL Injection

flowchart TD
    A["1. $pdo->prepare('SELECT * FROM users WHERE email = :email')"] --> B["Database Compiles SQL Structure & Query Plan"]
    B --> C["2. $stmt->execute(['email' => $userSubmittedInput])"]
    C --> D["Database Binds Input Strictly as Literal Data Value"]
    D --> E["Malicious SQL Commands in Input (e.g. ' OR '1'='1) Cannot Alter Query Structure!"]

Parameter Binding Strategies

1. Positional Parameters (?): $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?"); $stmt->execute([$id]);
2. Named Parameters (:key): $stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id"); $stmt->execute(['id' => $id]);

Practical Code Example

<?php
declare(strict_types=1);

$userSearchInput = "[email protected]' OR '1'='1"; // Malicious SQL Injection attempt!

try {
    $pdo = new PDO("mysql:host=127.0.0.1;dbname=ecommerce_db;charset=utf8mb4", 'root', 'secret_password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);

    // Secure Prepared Statement (SQL Structure compiled safely)
    $sql = "SELECT id, username, email FROM users WHERE email = :email AND status = :status";
    $stmt = $pdo->prepare($sql);

    // Execute with parameters
    $stmt->execute([
        'email' => $userSearchInput,
        'status' => 'active'
    ]);

    $user = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "Found Record Count: " . ($user ? "1" : "0 (SQL Injection Neutralized Safely!)") . "
";

} catch (PDOException $e) {
    die("Query Error: " . $e->getMessage());
}

Best Practices

• NEVER Concatenate User Input into SQL Strings: Never write $pdo->query("SELECT * FROM users WHERE email = '$email'").
• Prefer Named Parameters (:email) Over Positional (?): Named parameters eliminate ordering bugs when queries contain many parameters.
• Set PDO::ATTR_EMULATE_PREPARES => false: Forces native database engine prepared statement compilation.

Self-Check Challenge

Why are prepared statements immune to SQL Injection attacks? (Because SQL syntax compilation is completed before user parameter data is bound as a raw literal value!)