Preventing SQL Injection with Prepared Statements (PHP PDO)

<?php

// --- Configuration (replace with your actual database credentials) ---
$host = 'localhost';
$db   = 'your_database';
$user = 'your_user';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false, // Crucial for security
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// --- Example: Securely inserting data ---
function insertUser($pdo, $username, $email) {
    $sql = "INSERT INTO users (username, email) VALUES (:username, :email)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':username' => $username, ':email' => $email]);
    echo "User '$username' inserted securely.
";
}

// --- Example: Securely selecting data ---
function findUserByEmail($pdo, $email) {
    $sql = "SELECT id, username, email FROM users WHERE email = :email";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':email' => $email]);
    return $stmt->fetch();
}

// --- Usage Example ---
// Simulate user input (which could be malicious if not handled properly)
$inputUsername = "John Doe";
$inputEmail = "[email protected]' OR '1'='1"; // Malicious input example

// Call the secure functions
insertUser($pdo, $inputUsername, $inputEmail);

$foundUser = findUserByEmail($pdo, "[email protected]' OR '1'='1"); // Still attempts to find a user
if ($foundUser) {
    echo "Found user: " . $foundUser['username'] . " with email " . $foundUser['email'] . "
";
} else {
    echo "User not found (as expected for malicious input due to parameterization).
";
}

// A correct email search
$correctUser = findUserByEmail($pdo, "[email protected]");
if ($correctUser) {
    echo "Found user (correct search): " . $correctUser['username'] . " with email " . $correctUser['email'] . "
";
} else {
    echo "User not found.
";
}

?>