Preventing SQL Injection with PHP PDO Prepared Statements

<?php

/**
 * Establishes a PDO database connection.
 * @return PDO
 */
function getDbConnection() {
    $host = 'localhost';
    $db   = 'your_database_name';
    $user = 'your_db_username';
    $pass = 'your_db_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 real prepared statements
    ];

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

/**
 * Safely retrieves user data using prepared statements.
 * @param string $username The username to search for.
 * @return array|false User data if found, false otherwise.
 */
function getUserData(string $username) {
    $pdo = getDbConnection();
    $stmt = $pdo->prepare("SELECT id, username, email FROM users WHERE username = :username");

    // Bind parameters to prevent SQL injection
    $stmt->bindParam(':username', $username, PDO::PARAM_STR);

    $stmt->execute();
    return $stmt->fetch();
}

/**
 * Safely inserts new user data.
 * @param string $username
 * @param string $email
 * @param string $passwordHash (Assume password is already hashed)
 * @return bool True on success, false on failure.
 */
function insertUser(string $username, string $email, string $passwordHash) {
    $pdo = getDbConnection();
    $stmt = $pdo->prepare("INSERT INTO users (username, email, password_hash) VALUES (:username, :email, :password_hash)");

    $stmt->bindParam(':username', $username, PDO::PARAM_STR);
    $stmt->bindParam(':email', $email, PDO::PARAM_STR);
    $stmt->bindParam(':password_hash', $passwordHash, PDO::PARAM_STR);

    return $stmt->execute();
}


// --- Example Usage ---
try {
    // Assume you have user input from a form or URL parameter
    $searchUsername = $_GET['user'] ?? 'john_doe'; // DO NOT use raw $_GET in production directly for DB ops without validation

    echo "Attempting to fetch data for username: " . htmlspecialchars($searchUsername) . "
";
    $userData = getUserData($searchUsername);

    if ($userData) {
        echo "User found: ID=" . $userData['id'] . ", Username=" . $userData['username'] . ", Email=" . $userData['email'] . "
";
    } else {
        echo "User '" . htmlspecialchars($searchUsername) . "' not found.
";
    }

    // Example of inserting a new user (password_hash would be from bcrypt, etc.)
    // $newUser = "jane_doe";
    // $newEmail = "[email protected]";
    // $newPasswordHash = password_hash("securePassword123!", PASSWORD_BCRYPT); // Example, use a real hashing mechanism
    // if (insertUser($newUser, $newEmail, $newPasswordHash)) {
    //     echo "User '{$newUser}' inserted successfully.
";
    // } else {
    //     echo "Failed to insert user '{$newUser}'.
";
    // }

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