Prevent SQL Injection with Prepared Statements (PDO)

<?php

// --- Configuration (replace with your actual database details) ---
$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,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Database connection successful.

";

    // --- Example 1: SELECT with a single parameter ---
    $user_id = 5;
    $stmt = $pdo->prepare("SELECT username, email FROM users WHERE id = :id");
    $stmt->execute(['id' => $user_id]);
    $user = $stmt->fetch();

    if ($user) {
        echo "User Found (ID: " . $user_id . "):
";
        echo "  Username: " . $user['username'] . "
";
        echo "  Email: " . $user['email'] . "

";
    } else {
        echo "User with ID " . $user_id . " not found.

";
    }

    // --- Example 2: INSERT with multiple parameters ---
    $new_username = "malicious_user"; // Imagine this comes from user input
    $new_email    = "[email protected]";
    $new_password = password_hash("securepwd123", PASSWORD_BCRYPT);

    $stmt = $pdo->prepare("INSERT INTO users (username, email, password) VALUES (?, ?, ?)");
    // Bind parameters by order
    $stmt->execute([$new_username, $new_email, $new_password]);
    echo "User '" . $new_username . "' inserted with ID: " . $pdo->lastInsertId() . "

";

    // --- Example 3: UPDATE with named parameters ---
    $update_user_id = 1; // User to update
    $new_email_address = "[email protected]";

    $stmt = $pdo->prepare("UPDATE users SET email = :email WHERE id = :id");
    $stmt->execute([':email' => $new_email_address, ':id' => $update_user_id]);
    echo "User ID " . $update_user_id . " email updated to " . $new_email_address . ". Rows affected: " . $stmt->rowCount() . "
";

} catch (PDOException $e) {
    exit("Database error: " . $e->getMessage());
}

// --- Note: Always ensure database credentials are NOT hardcoded in production and use environment variables. ---