PHP: Advanced PDO and Security

<?php
$stmt = $pdo->prepare("INSERT INTO users (username, age) VALUES (:name, :age)");

// bindValue: the value is determined at binding time (pass by value)
$name = 'John';
$stmt->bindValue(':name', $name, PDO::PARAM_STR);

// bindParam: binds a variable reference—the value is read at execute() time
$age = 25;
$stmt->bindParam(':age', $age, PDO::PARAM_INT);

// Key difference:
$name = 'Jane';  // bindValue already bound "John"—changing $name has no effect
$age = 30;        // bindParam references $age—execute() will use 30

$stmt->execute();  // INSERT: name='John', age=30
?>

<?php
// bindParam shines in loops
$stmt = $pdo->prepare("INSERT INTO logs (message) VALUES (:msg)");
$stmt->bindParam(':msg', $msg);

foreach (['Service started', 'User logged in', 'Request processed', 'Service stopped'] as $msg) {
    $stmt->execute();  // Each execute() picks up the current value of $msg
}
?>

<?php
// Create a test table
// CREATE TABLE accounts (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), balance DECIMAL(10,2));

try {
    $pdo->beginTransaction();
    
    // John's account: subtract 200
    $stmt = $pdo->prepare("UPDATE accounts SET balance = balance - :amount WHERE name = :name");
    $stmt->execute(['amount' => 200, 'name' => 'John']);
    
    // Jane's account: add 200
    $stmt->execute(['amount' => 200, 'name' => 'Jane']);
    
    $pdo->commit();
    echo "Transfer successful! John → Jane: 200";
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Transfer failed, rolled back: " . $e->getMessage();
}
?>

<?php
// Imagine a login check (❌ Dangerous pattern—never use)
$username = $_POST['username'] ?? '';
$password = $_POST['password'] ?? '';

// ❌ Fatal mistake: directly concatenating SQL
$sql = "SELECT * FROM users WHERE username = '{$username}' AND password = '{$password}'";
echo "Executing SQL: {$sql}<br>";

// An attacker types this into the username field:
// ' OR '1'='1' --
// The resulting SQL becomes:
//   SELECT * FROM users WHERE username = '' OR '1'='1' --' AND password = ''
//   1=1 is always true! -- comments out the rest! All users are returned!

// An even more terrifying attack:
// '; DROP TABLE users; --
// The resulting SQL:
//   SELECT * FROM users WHERE username = ''; DROP TABLE users; --' AND password = ''
//   The entire table is deleted!
?>

<?php
// ✅ Safe approach—use prepared statements
$sql = "SELECT * FROM users WHERE username = :username AND password = :password";
$stmt = $pdo->prepare($sql);
$stmt->execute([
    'username' => $_POST['username'] ?? '',
    'password' => $_POST['password'] ?? '',
]);
$user = $stmt->fetch();

if ($user) {
    echo "Login successful!";
} else {
    echo "Invalid username or password";
}
// The attacker's input ' OR '1'='1' -- is treated as a plain string to match, not executed
?>

<?php
// Registration: hash the password
$password = "mySecret123";
$hash = password_hash($password, PASSWORD_DEFAULT);
echo $hash;  
// $2y$10$WzjCuq4qY8yXqUqMqHqRdOq... 
// Each hash result is different (because of a random salt)

// Store in the database
$stmt = $pdo->prepare("INSERT INTO users (username, password) VALUES (:u, :p)");
$stmt->execute(['u' => 'John', 'p' => $hash]);

// Login: verify the password
$inputPassword = "mySecret123";
$stmt = $pdo->prepare("SELECT password FROM users WHERE username = :u");
$stmt->execute(['u' => 'John']);
$user = $stmt->fetch();

if ($user && password_verify($inputPassword, $user['password'])) {
    echo "✅ Password correct, login successful!";
} else {
    echo "❌ Incorrect password";
}
// Even if someone gets the database, they only see hash values—they can't log in directly
?>

<?php
// Advanced: increase the cost factor
$hash = password_hash($password, PASSWORD_BCRYPT, ['cost' => 12]);
// Higher cost is more secure but slower (10-12 is recommended)

// Check if a hash needs rehashing (when algorithms are upgraded)
if (password_needs_rehash($hash, PASSWORD_DEFAULT, ['cost' => 12])) {
    $newHash = password_hash($password, PASSWORD_DEFAULT, ['cost' => 12]);
    // Update the hash in the database
}
?>

<?php
class Auth {
    private PDO $pdo;
    
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    
    /**
     * User registration
     */
    public function register(string $username, string $email, string $password): array {
        // Check if the username already exists
        $stmt = $this->pdo->prepare("SELECT COUNT(*) FROM users WHERE username = :u");
        $stmt->execute(['u' => $username]);
        if ($stmt->fetchColumn() > 0) {
            return ['success' => false, 'message' => 'Username already taken'];
        }
        
        // Insert the new user
        $hash = password_hash($password, PASSWORD_DEFAULT, ['cost' => 12]);
        $stmt = $this->pdo->prepare(
            "INSERT INTO users (username, email, password) VALUES (:u, :e, :p)"
        );
        $stmt->execute(['u' => $username, 'e' => $email, 'p' => $hash]);
        
        return ['success' => true, 'id' => $this->pdo->lastInsertId()];
    }
    
    /**
     * User login
     */
    public function login(string $username, string $password): array {
        $stmt = $this->pdo->prepare("SELECT * FROM users WHERE username = :u");
        $stmt->execute(['u' => $username]);
        $user = $stmt->fetch();
        
        if (!$user || !password_verify($password, $user['password'])) {
            return ['success' => false, 'message' => 'Invalid username or password'];
        }
        
        // Check if the hash needs updating
        if (password_needs_rehash($user['password'], PASSWORD_DEFAULT, ['cost' => 12])) {
            $newHash = password_hash($password, PASSWORD_DEFAULT, ['cost' => 12]);
            $stmt = $this->pdo->prepare("UPDATE users SET password = :p WHERE id = :id");
            $stmt->execute(['p' => $newHash, 'id' => $user['id']]);
        }
        
        unset($user['password']);  // Don't store the password hash in the session
        return ['success' => true, 'user' => $user];
    }
}

// Usage
$auth = new Auth($pdo);

// Register
print_r($auth->register('TestUser', 'test@example.com', 'StrongPass123'));

// Login
print_r($auth->login('TestUser', 'StrongPass123'));
?>