SQL Injection Prevention in PHP: Prepared Statements, PDO & Real Fixes

<?php
// ============================================================
// WARNING: THIS IS THE VULNERABLE PATTERN — DO NOT USE IN PRODUCTION
// It exists here so you can see exactly what you must avoid.
// ============================================================

$host     = 'localhost';
$database = 'forge_app';
$username = 'forge_user';
$password = 'secret';

// Old-school mysqli connection (no prepared statements yet)
$connection = mysqli_connect($host, $username, $password, $database);

if (!$connection) {
    die('Database connection failed: ' . mysqli_connect_error());
}

// Imagine these come from a login form POST — user-controlled input
$emailFromForm    = $_POST['email']    ?? '';
$passwordFromForm = $_POST['password'] ?? '';

// THE VULNERABILITY: raw user input is concatenated directly into SQL
// An attacker can set $emailFromForm = "' OR '1'='1" to bypass the password check
$unsafeQuery = "SELECT id, email, role
                FROM users
                WHERE email    = '$emailFromForm'
                AND   password = '$passwordFromForm'";

// With the injected input, this actually executes:
// SELECT id, email, role FROM users
// WHERE email = '' OR '1'='1'
// AND password = 'anything'
// '1'='1' is always TRUE, so every row matches — instant bypass.

$result = mysqli_query($connection, $unsafeQuery);

if ($result && mysqli_num_rows($result) > 0) {
    $user = mysqli_fetch_assoc($result);
    // Attacker is now authenticated as this user — often the first admin
    echo "Logged in as: " . $user['email'] . " (role: " . $user['role'] . ")";
} else {
    echo "Invalid credentials.";
}

mysqli_close($connection);
?>

<?php
// ============================================================
// SECURE VERSION — PDO with prepared statements
// This is production-grade code. Use this pattern.
// ============================================================

$host     = 'localhost';
$dbName   = 'forge_app';
$dbUser   = 'forge_user';
$dbPass   = 'secret';

// PDO DSN (Data Source Name) — tells PDO which driver and database to use
$dsn = "mysql:host=$host;dbname=$dbName;charset=utf8mb4";

// Connection options that every production app should set
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,  // Throw exceptions on errors
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,        // Return rows as associative arrays
    PDO::ATTR_EMULATE_PREPARES   => false,                   // Use REAL prepared statements, not emulated ones
];

try {
    $pdo = new PDO($dsn, $dbUser, $dbPass, $options);
} catch (PDOException $connectionError) {
    // Log the real error internally — never expose DB details to the user
    error_log('DB connection failed: ' . $connectionError->getMessage());
    die('Service temporarily unavailable. Please try again later.');
}

// Collect user input — in real apps, you'd also validate format here
$emailInput    = $_POST['email']    ?? '';
$passwordInput = $_POST['password'] ?? '';

// --- STEP 1: Define the SQL template with named placeholders ---
// MySQL receives and compiles THIS string. No user data is in it yet.
$sqlTemplate = "SELECT id, email, password_hash, role
                FROM   users
                WHERE  email = :email
                LIMIT  1";

// --- STEP 2: Prepare the statement ---
$statement = $pdo->prepare($sqlTemplate);

// --- STEP 3: Execute with the actual data values ---
// These values are sent separately — MySQL treats them as data, NEVER as SQL.
// Even if $emailInput = "' OR '1'='1", it's just a string being compared to the email column.
$statement->execute([':email' => $emailInput]);

// --- STEP 4: Fetch the result ---
$matchedUser = $statement->fetch();

if ($matchedUser && password_verify($passwordInput, $matchedUser['password_hash'])) {
    // password_verify() safely compares the plain input against the stored bcrypt hash
    echo "Welcome back, " . htmlspecialchars($matchedUser['email']) . "!";
    echo " Your role is: " . htmlspecialchars($matchedUser['role']);

    // In a real app: session_start(); $_SESSION['user_id'] = $matchedUser['id'];
} else {
    // Deliberately vague — don't tell attackers whether the email or password was wrong
    echo "Invalid email or password.";
}
?>

<?php
// ============================================================
// SECURE MySQLi — prepared statements with bind_param()
// Common in WordPress ecosystems and legacy PHP applications
// ============================================================

$host     = 'localhost';
$dbUser   = 'forge_user';
$dbPass   = 'secret';
$dbName   = 'forge_app';

// Object-oriented MySQLi style (preferred over procedural)
$mysqli = new mysqli($host, $dbUser, $dbPass, $dbName);

if ($mysqli->connect_errno) {
    error_log('MySQLi connect error: ' . $mysqli->connect_error);
    die('Service temporarily unavailable.');
}

// Set charset explicitly — always do this to avoid encoding-based injection vectors
$mysqli->set_charset('utf8mb4');

// Imagine this is a search feature — user searches for members by username
$searchTerm = $_GET['username'] ?? '';
$minAge     = (int) ($_GET['min_age'] ?? 18); // Cast to int immediately — never trust input

// SQL template — two placeholders: one string, one integer
$sqlTemplate = "SELECT id, username, email, age
                FROM   members
                WHERE  username LIKE ?
                AND    age      >= ?
                ORDER  BY username ASC
                LIMIT  50";

// Prepare the statement
$statement = $mysqli->prepare($sqlTemplate);

if (!$statement) {
    error_log('Prepare failed: ' . $mysqli->error);
    die('Search unavailable.');
}

// Wrap the search term in % wildcards for a LIKE search
// This is safe — the wildcards are added BEFORE binding, so they're part of the data value
$likePattern = '%' . $searchTerm . '%';

// bind_param(types, ...variables)
// 'si' = first param is string (s), second is integer (i)
// Variables are bound by REFERENCE — they're evaluated at execute() time
$statement->bind_param('si', $likePattern, $minAge);

// Execute — data travels to MySQL separately from the SQL template
$statement->execute();

// get_result() requires mysqlnd driver (standard on PHP 7+)
$results = $statement->get_result();

if ($results->num_rows === 0) {
    echo "No members found matching '" . htmlspecialchars($searchTerm) . "'.";
} else {
    echo "Found " . $results->num_rows . " member(s):\n\n";

    while ($member = $results->fetch_assoc()) {
        // htmlspecialchars() prevents XSS when displaying data back in HTML
        echo "- " . htmlspecialchars($member['username'])
             . " (" . htmlspecialchars($member['email']) . ")"
             . " Age: " . (int)$member['age'] . "\n";
    }
}

// Always close the statement and connection
$statement->close();
$mysqli->close();
?>

<?php
// ============================================================
// HARDENED UPDATE EXAMPLE — Input validation + prepared statement
// + output encoding + principle of least privilege in action
// ============================================================

// --- Input Validation Layer ---
// Validate BEFORE touching the database. Fail fast with clear errors.

$userId      = filter_input(INPUT_POST, 'user_id', FILTER_VALIDATE_INT);
$newUsername = trim(filter_input(INPUT_POST, 'username', FILTER_SANITIZE_SPECIAL_CHARS) ?? '');
$newEmail    = filter_input(INPUT_POST, 'email', FILTER_VALIDATE_EMAIL);
$newAge      = filter_input(INPUT_POST, 'age', FILTER_VALIDATE_INT, [
    'options' => ['min_range' => 13, 'max_range' => 120]
]);

$validationErrors = [];

if ($userId === false || $userId === null) {
    $validationErrors[] = 'Invalid user ID.';
}
if (strlen($newUsername) < 3 || strlen($newUsername) > 30) {
    $validationErrors[] = 'Username must be 3-30 characters.';
}
if ($newEmail === false) {
    $validationErrors[] = 'Invalid email address format.';
}
if ($newAge === false) {
    $validationErrors[] = 'Age must be between 13 and 120.';
}

if (!empty($validationErrors)) {
    // Return errors before any database work happens
    http_response_code(422);
    foreach ($validationErrors as $error) {
        // htmlspecialchars() here because we're outputting to HTML
        echo htmlspecialchars($error) . "\n";
    }
    exit;
}

// --- Database Layer — PDO prepared statement ---
$dsn = 'mysql:host=localhost;dbname=forge_app;charset=utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, 'forge_app_writer', 'writerpass', $options);
    // Note: forge_app_writer only has SELECT, INSERT, UPDATE, DELETE
    // It CANNOT DROP tables, CREATE users, or GRANT permissions

    $updateStatement = $pdo->prepare(
        "UPDATE users
         SET    username = :username,
                email    = :email,
                age      = :age,
                updated_at = NOW()
         WHERE  id = :userId"
    );

    $updateStatement->execute([
        ':username' => $newUsername,
        ':email'    => $newEmail,
        ':age'      => $newAge,
        ':userId'   => $userId,
    ]);

    $rowsAffected = $updateStatement->rowCount();

    if ($rowsAffected === 1) {
        // Output encoding before rendering to HTML — prevents stored XSS
        echo "Profile updated for: " . htmlspecialchars($newUsername);
    } else {
        echo "No profile found with that ID, or no changes were made.";
    }

} catch (PDOException $dbError) {
    // Log full error internally — show nothing useful to the attacker
    error_log('Profile update error: ' . $dbError->getMessage());
    http_response_code(500);
    echo 'An error occurred. Please try again.';
}
?>