Security Implementation

<?php
// ========================================
// INPUT VALIDATION AND SANITIZATION
// ========================================

class DatabaseSecurity {
    private $pdo;
    
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    
    /**
     * Safe user authentication
     */
    public function authenticateUser($email, $password) {
        // Validate email
        if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            return false;
        }
        
        // Prepare statement
        $stmt = $this->pdo->prepare(
            "SELECT id, username, password_hash 
             FROM users 
             WHERE email = :email 
             AND status = 'active'
             LIMIT 1"
        );
        
        $stmt->execute([':email' => $email]);
        $user = $stmt->fetch();
        
        // Verify password
        if ($user && password_verify($password, $user['password_hash'])) {
            // Update last login
            $this->updateLastLogin($user['id']);
            
            // Return user data (exclude password)
            unset($user['password_hash']);
            return $user;
        }
        
        // Log failed attempt
        $this->logFailedLogin($email);
        return false;
    }
    
    /**
     * Prevent SQL injection in dynamic queries
     */
    public function safeSearch($searchTerm, $category = null) {
        // Base query
        $sql = "SELECT * FROM products WHERE 1=1";
        $params = [];
        
        // Add search condition
        if (!empty($searchTerm)) {
            // Sanitize for LIKE query
            $searchTerm = str_replace(['%', '_'], ['\%', '\_'], $searchTerm);
            $sql .= " AND (name LIKE :search OR description LIKE :search)";
            $params[':search'] = "%{$searchTerm}%";
        }
        
        // Add category filter
        if ($category !== null) {
            // Validate category is integer
            if (!is_numeric($category)) {
                throw new InvalidArgumentException("Invalid category");
            }
            $sql .= " AND category_id = :category";
            $params[':category'] = (int)$category;
        }
        
        // Execute safely
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        
        return $stmt->fetchAll();
    }
    
    /**
     * Safe dynamic ORDER BY
     */
    public function getUsers($orderBy = 'id', $direction = 'ASC') {
        // Whitelist allowed columns
        $allowedColumns = ['id', 'username', 'email', 'created_at'];
        $allowedDirections = ['ASC', 'DESC'];
        
        // Validate input
        if (!in_array($orderBy, $allowedColumns)) {
            $orderBy = 'id';
        }
        
        if (!in_array(strtoupper($direction), $allowedDirections)) {
            $direction = 'ASC';
        }
        
        // Safe to use now (not from user input directly)
        $sql = "SELECT * FROM users ORDER BY {$orderBy} {$direction}";
        
        return $this->pdo->query($sql)->fetchAll();
    }
    
    /**
     * Prevent mass assignment
     */
    public function updateUser($userId, array $data) {
        // Whitelist allowed fields
        $allowedFields = ['username', 'email', 'phone'];
        
        // Filter data
        $filtered = array_intersect_key(
            $data,
            array_flip($allowedFields)
        );
        
        // Build UPDATE query
        $fields = [];
        foreach ($filtered as $key => $value) {
            $fields[] = "{$key} = :{$key}";
        }
        
        if (empty($fields)) {
            return false;
        }
        
        $sql = "UPDATE users SET " . implode(', ', $fields) . 
               " WHERE id = :id";
        
        $filtered['id'] = $userId;
        
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($filtered);
    }
    
    /**
     * Rate limiting
     */
    private function checkRateLimit($identifier) {
        $stmt = $this->pdo->prepare(
            "SELECT COUNT(*) FROM login_attempts 
             WHERE ip_address = :ip 
             AND attempted_at > DATE_SUB(NOW(), INTERVAL 15 MINUTE)"
        );
        
        $stmt->execute([':ip' => $identifier]);
        $attempts = $stmt->fetchColumn();
        
        if ($attempts > 5) {
            throw new Exception("Too many attempts. Please try later.");
        }
    }
    
    /**
     * Log security events
     */
    private function logSecurityEvent($event, $details) {
        $stmt = $this->pdo->prepare(
            "INSERT INTO security_log (event_type, details, ip_address, created_at)
             VALUES (:event, :details, :ip, NOW())"
        );
        
        $stmt->execute([
            ':event' => $event,
            ':details' => json_encode($details),
            ':ip' => $_SERVER['REMOTE_ADDR'] ?? 'unknown'
        ]);
    }
}

// ========================================
// CONNECTION SECURITY
// ========================================

// Store credentials outside web root
// config/database.php (above public_html)
return [
    'host' => getenv('DB_HOST'),
    'dbname' => getenv('DB_NAME'),
    'username' => getenv('DB_USER'),
    'password' => getenv('DB_PASS'),
    'options' => [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::MYSQL_ATTR_SSL_CA => '/path/to/ca-cert.pem',
    ]
];

// ========================================
// ERROR HANDLING
// ========================================

class DatabaseConnection {
    private static $instance = null;
    private $pdo;
    
    private function __construct() {
        try {
            $config = require '../config/database.php';
            
            $dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset=utf8mb4";
            
            $this->pdo = new PDO(
                $dsn,
                $config['username'],
                $config['password'],
                $config['options']
            );
            
        } catch (PDOException $e) {
            // Log error, don't expose details
            error_log("Database connection failed: " . $e->getMessage());
            
            // Show user-friendly error
            if (PHP_SAPI === 'cli') {
                die("Database connection error\n");
            } else {
                http_response_code(500);
                include '../templates/error-500.php';
                exit;
            }
        }
    }
    
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance->pdo;
    }
    
    // Prevent cloning
    private function __clone() {}
    
    // Prevent unserialization
    public function __wakeup() {
        throw new Exception("Cannot unserialize singleton");
    }
}