<?php
// Legacy vulnerable PHP code snippet (illustrative, DO NOT USE IN PRODUCTION)
// This function demonstrates common SQL injection vulnerabilities using deprecated mysql_* functions

/**
 * Fetches user data based on a username directly from HTTP GET request.
 * CRITICAL: Highly vulnerable to SQL Injection.
 *
 * @param string $username The username to fetch.
 * @return array|false User data or false if not found/error.
 */
function getLegacyUserByUsername_Vulnerable($username) {
    // Simulate receiving data directly from a GET request without sanitation
    $unclean_username = $_GET['username'] ?? $username; // Example: ?username=admin' OR '1'='1

    // Establish a deprecated MySQL connection (simulated for demonstration)
    // In a real legacy system, this would be a global connection or passed around.
    $conn = @mysqli_connect('localhost', 'root', 'password', 'mydb'); // Using mysqli for sim, but conceptually mysql_*
    if (!$conn) {
        die('Connection failed: ' . mysqli_connect_error());
    }

    // Constructing the SQL query directly with unsanitized user input
    // This is the primary vector for SQL Injection.
    $sql = "SELECT id, username, email FROM users WHERE username = '" . $unclean_username . "'";

    echo "<p><strong>Executing Vulnerable Query:</strong> " . htmlspecialchars($sql) . "</p>"; // For demonstration

    // Execute the query using deprecated or direct methods
    $result = mysqli_query($conn, $sql); // In legacy, would be mysql_query($sql)

    if ($result) {
        $user_data = mysqli_fetch_assoc($result); // mysql_fetch_assoc() in truly legacy
        mysqli_free_result($result);
        mysqli_close($conn);
        return $user_data;
    } else {
        echo "<p><strong>Query Error:</strong> " . mysqli_error($conn) . "</p>";
        mysqli_close($conn);
        return false;
    }
}

// --- Example Usage of the Vulnerable Function (for testing purposes) ---
// To test SQL Injection, you might pass a URL like:
// http://yourdomain.com/yourscript.php?username=admin%27+OR+%271%27=%271
// This would return all users if 'admin' doesn't exist, or even if it does.
// Or: http://yourdomain.com/yourscript.php?username=admin%27;+DROP+TABLE+users;--

// We simulate a malicious input here:
// $_GET['username'] = "admin' OR '1'='1"; // Simulate a GET request for demonstration
// $user = getLegacyUserByUsername_Vulnerable(''); // Call with a dummy to trigger the simulated GET

// echo "<h3>Results from Vulnerable Function:</h3>";
// if ($user) {
//     echo "<pre>" . print_r($user, true) . "</pre>";
// } else {
//     echo "<p>No user found or an error occurred.</p>";
// }

// --- End of example ---

// Common vulnerable patterns to look for:
// 1. `mysql_connect()`, `mysql_query()`, `mysql_real_escape_string()` - these functions are deprecated and signify old code.
// 2. Direct concatenation of user input into SQL queries:
//    `"SELECT * FROM users WHERE username = '" . $username . "' AND password = '" . $password . "'"`
// 3. Lack of explicit input validation or sanitation on ALL user-supplied data (GET, POST, COOKIE, REQUEST, SERVER).
// 4. Use of `mysqli_real_escape_string()` without prepared statements, as it's a stop-gap, not a full solution.
?>

<?php
// PHP PDO Connection Example
// This demonstrates how to establish a secure PDO connection.

/**
 * Establishes a PDO database connection.
 * 
 * @return PDO The PDO connection object.
 * @throws PDOException If connection fails.
 */
function getPdoConnection() {
    // Database connection parameters
    $host = 'localhost';
    $db   = 'mydb';
    $user = 'root';
    $pass = 'password';
    $charset = 'utf8mb4';

    // DSN (Data Source Name) string for MySQL
    $dsn = "mysql:host=$host;dbname=$db;charset=$charset";

    // PDO options for secure and robust connection
    $options = [
        // Set default fetch mode to associative array for easier data handling
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        // Enable exceptions for error handling, crucial for debugging and robust applications
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        // Disable emulation of prepared statements. This forces native prepared statements, 
        // which are safer and often more performant.
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];

    try {
        // Create a new PDO instance
        $pdo = new PDO($dsn, $user, $pass, $options);
        echo "<p><strong>PDO Connection Established Successfully.</strong></p>"; // For demonstration
        return $pdo;
    } catch (PDOException $e) {
        // Catch any connection errors and throw a custom exception or log it.
        // In a production environment, avoid exposing detailed error messages to users.
        throw new PDOException("PDO Connection Error: " . $e->getMessage(), (int)$e->getCode());
    }
}

// --- Example Usage ---
// try {
//     $pdo = getPdoConnection();
//     // Now $pdo object can be used for secure database operations.
//     // For example, prepare and execute a simple query.
//     // $stmt = $pdo->query('SELECT * FROM users');
//     // $users = $stmt->fetchAll();
//     // echo "<pre>" . print_r($users, true) . "</pre>";
// } catch (PDOException $e) {
//     echo "<p style='color: red;'>" . $e->getMessage() . "</p>";
// }

// --- End of example ---
?>

<?php
// PHP PDO Prepared Statement Example
// This demonstrates how to refactor a vulnerable query into a secure, parameterized PDO query.

/**
 * Fetches user data based on a username using PDO prepared statements.
 * This function is secure against SQL Injection.
 *
 * @param PDO $pdo The PDO connection object.
 * @param string $username The username to fetch.
 * @return array|false User data or false if not found.
 */
function getSecureUserByUsername(PDO $pdo, $username) {
    // 1. Prepare the SQL query with placeholders. 
    //    Placeholders can be named (:username) or positional (?).
    //    NEVER concatenate variables directly into $sql after $pdo->prepare() has been called.
    $sql = "SELECT id, username, email FROM users WHERE username = :username";

    try {
        // 2. Prepare the statement. This sends the query structure to the database for parsing.
        $stmt = $pdo->prepare($sql);

        // 3. Bind the parameter. This associates a PHP variable with a placeholder.
        //    PDO handles proper escaping and ensures the value is treated as data, not code.
        $stmt->bindParam(':username', $username, PDO::PARAM_STR); // Specify data type for robustness

        // 4. Execute the statement.
        $stmt->execute();

        // 5. Fetch the result.
        $user_data = $stmt->fetch(); // Fetch single row for one user

        echo "<p><strong>Secure Query Executed. User ID:</strong> " . ($user_data ? $user_data['id'] : 'N/A') . "</p>"; // For demonstration

        return $user_data;
    } catch (PDOException $e) {
        // Log the error and handle it gracefully.
        // In production, avoid exposing detailed error messages to users.
        error_log("Database Error in getSecureUserByUsername: " . $e->getMessage());
        return false;
    }
}

/**
 * Inserts new user data into the database using PDO prepared statements.
 * This function is secure against SQL Injection.
 *
 * @param PDO $pdo The PDO connection object.
 * @param string $username The username to insert.
 * @param string $email The email to insert.
 * @return bool True on success, false on failure.
 */
function insertSecureUser(PDO $pdo, $username, $email) {
    $sql = "INSERT INTO users (username, email) VALUES (:username, :email)";

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->bindParam(':username', $username, PDO::PARAM_STR);
        $stmt->bindParam(':email', $email, PDO::PARAM_STR);
        $stmt->execute();
        echo "<p><strong>Secure Insert Executed. Rows affected:</strong> " . $stmt->rowCount() . "</p>";
        return true;
    } catch (PDOException $e) {
        error_log("Database Error in insertSecureUser: " . $e->getMessage());
        return false;
    }
}

// --- Example Usage ---
// try {
//     $pdo = getPdoConnection(); // Assume getPdoConnection() is defined and works

//     // Simulate user input for existing user
//     $testUsername1 = 'admin';
//     $user1 = getSecureUserByUsername($pdo, $testUsername1);
//     if ($user1) {
//         echo "<h3>Found User:</h3><pre>" . print_r($user1, true) . "</pre>";
//     } else {
//         echo "<p>User '{$testUsername1}' not found or error.</p>";
//     }

//     // Simulate malicious input (will be treated as data, not code)
//     $maliciousUsername = "admin' OR '1'='1"; // This will search for a user with EXACTLY this string as username
//     $userMalicious = getSecureUserByUsername($pdo, $maliciousUsername);
//     if ($userMalicious) {
//         echo "<h3>Found Malicious User (NOT INJECTED!):</h3><pre>" . print_r($userMalicious, true) . "</pre>";
//     } else {
//         echo "<p>User '{$maliciousUsername}' not found (as expected, no injection).</p>";
//     }

//     // Insert a new user securely
//     $newUsername = 'johndoe';
//     $newEmail = 'john.doe@example.com';
//     if (insertSecureUser($pdo, $newUsername, $newEmail)) {
//         echo "<p>New user '{$newUsername}' inserted successfully.</p>";
//     }

// } catch (PDOException $e) {
//     echo "<p style='color: red;'>" . $e->getMessage() . "</p>";
// }
?>

<?php
// Example of a simple Data Access Layer (DAL) using PDO.
// This demonstrates centralizing database operations for consistency and security.

/**
 * Class UserRepository
 * Handles all database operations related to 'users'.
 */
class UserRepository {
    private PDO $pdo;

    /**
     * Constructor for UserRepository.
     * @param PDO $pdo The PDO connection object.
     */
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }

    /**
     * Finds a user by their ID.
     * @param int $userId The ID of the user.
     * @return array|false User data or false if not found.
     */
    public function findById(int $userId): array|false {
        $sql = "SELECT id, username, email FROM users WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindParam(':id', $userId, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetch();
    }

    /**
     * Finds a user by their username.
     * @param string $username The username to search for.
     * @return array|false User data or false if not found.
     */
    public function findByUsername(string $username): array|false {
        $sql = "SELECT id, username, email FROM users WHERE username = :username";
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindParam(':username', $username, PDO::PARAM_STR);
        $stmt->execute();
        return $stmt->fetch();
    }

    /**
     * Creates a new user.
     * @param string $username The username.
     * @param string $email The email address.
     * @return int|false The ID of the newly created user or false on failure.
     */
    public function create(string $username, string $email): int|false {
        $sql = "INSERT INTO users (username, email) VALUES (:username, :email)";
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindParam(':username', $username, PDO::PARAM_STR);
        $stmt->bindParam(':email', $email, PDO::PARAM_STR);
        $stmt->execute();
        return $this->pdo->lastInsertId();
    }

    /**
     * Updates a user's email by ID.
     * @param int $userId The ID of the user to update.
     * @param string $newEmail The new email address.
     * @return bool True on success, false on failure.
     */
    public function updateEmail(int $userId, string $newEmail): bool {
        $sql = "UPDATE users SET email = :email WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindParam(':email', $newEmail, PDO::PARAM_STR);
        $stmt->bindParam(':id', $userId, PDO::PARAM_INT);
        $stmt->execute();
        return (bool)$stmt->rowCount();
    }
}

// --- Example Usage ---
// try {
//     $pdo = getPdoConnection(); // Assume getPdoConnection() is defined and works
//     $userRepo = new UserRepository($pdo);

//     // Find user by ID
//     $user = $userRepo->findById(1);
//     if ($user) {
//         echo "<h3>Found User (ID 1):</h3><pre>" . print_r($user, true) . "</pre>";
//     }

//     // Create a new user
//     $newUserId = $userRepo->create('testuser', 'test@example.com');
//     if ($newUserId) {
//         echo "<p>New user created with ID: {$newUserId}</p>";
//     }

//     // Update user email
//     if ($newUserId && $userRepo->updateEmail($newUserId, 'updated@example.com')) {
//         echo "<p>User ID {$newUserId} email updated.</p>";
//     }

// } catch (PDOException $e) {
//     echo "<p style='color: red;'>" . $e->getMessage() . "</p>";
// }
?>

<?php
// Input Validation Example
// This demonstrates robust input validation techniques alongside PDO.

/**
 * Validates and sanitizes a username.
 * @param string $username The input username.
 * @return string|false The sanitized username or false if invalid.
 */
function validateUsername(string $username): string|false {
    // Remove leading/trailing whitespace
    $username = trim($username);

    // Check if username is empty after trim
    if (empty($username)) {
        return false; // Username cannot be empty
    }

    // Restrict length
    if (strlen($username) < 3 || strlen($username) > 30) {
        return false; // Username length constraint
    }

    // Allow only alphanumeric characters, underscores, and hyphens
    if (!preg_match('/^[a-zA-Z0-9_-]+$/', $username)) {
        return false; // Invalid characters
    }

    // Additional checks: blacklisting reserved words, ensuring uniqueness (business logic)

    return $username;
}

/**
 * Validates and sanitizes an email address.
 * @param string $email The input email.
 * @return string|false The sanitized email or false if invalid.
 */
function validateEmail(string $email): string|false {
    // Remove leading/trailing whitespace
    $email = trim($email);

    // Use PHP's built-in filter_var for email validation and sanitation
    $sanitizedEmail = filter_var($email, FILTER_SANITIZE_EMAIL);

    // Validate the sanitized email format
    if (!filter_var($sanitizedEmail, FILTER_VALIDATE_EMAIL)) {
        return false; // Invalid email format
    }

    // Optionally, ensure the sanitized email matches original if filtering changed it significantly
    if ($sanitizedEmail !== $email && !empty($email)) {
        // This might indicate an attempt to inject HTML or unusual characters into the email.
        // Depending on strictness, you might return false here or log a warning.
        // For this example, we proceed with the sanitized email if it's valid.
        // return false; 
    }

    return $sanitizedEmail;
}

// --- Example Usage ---
// $rawUsername = "  user_name123  ";
// $validUsername = validateUsername($rawUsername);
// if ($validUsername) {
//     echo "<p>Valid Username: {$validUsername}</p>";
// } else {
//     echo "<p style='color: red;'>Invalid Username: {$rawUsername}</p>";
// }

// $rawEmail = " test@example.com ";
// $validEmail = validateEmail($rawEmail);
// if ($validEmail) {
//     echo "<p>Valid Email: {$validEmail}</p>";
// } else {
//     echo "<p style='color: red;'>Invalid Email: {$rawEmail}</p>";
// }

// $maliciousInput = "<script>alert('xss')</script>user";
// $invalidUsername = validateUsername($maliciousInput);
// if (!$invalidUsername) {
//     echo "<p style='color: green;'>Successfully rejected malicious username: " . htmlspecialchars($maliciousInput) . "</p>";
// }

// $maliciousEmail = "test@example.com<script>alert('xss')</script>";
// $invalidEmail = validateEmail($maliciousEmail);
// if (!$invalidEmail) {
//     echo "<p style='color: green;'>Successfully rejected malicious email: " . htmlspecialchars($maliciousEmail) . "</p>";
// } else {
//    echo "<p style='color: orange;'>Malicious email sanitized to: " . htmlspecialchars($invalidEmail) . " (consider stricter validation)</p>";
// }
?>