Complete Prepared Statements Implementation

<?php
// ========================================
// PDO PREPARED STATEMENTS
// ========================================

class SecurePDODatabase {
    private $pdo;
    private $allowedTables = ['users', 'products', 'orders'];
    private $allowedColumns = [];
    
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
        $this->initializeAllowedColumns();
    }
    
    /**
     * Initialize allowed columns for each table
     */
    private function initializeAllowedColumns() {
        $this->allowedColumns = [
            'users' => ['id', 'username', 'email', 'status', 'created_at'],
            'products' => ['id', 'name', 'price', 'category', 'stock'],
            'orders' => ['id', 'user_id', 'total', 'status', 'created_at']
        ];
    }
    
    /**
     * Secure SELECT with prepared statements
     */
    public function secureSelect($table, $conditions = [], $columns = ['*']) {
        // Validate table name (whitelist)
        if (!in_array($table, $this->allowedTables)) {
            throw new Exception("Invalid table name");
        }
        
        // Validate columns (whitelist)
        $safeColumns = $this->validateColumns($table, $columns);
        
        // Build query
        $sql = "SELECT " . implode(', ', $safeColumns) . " FROM $table";
        
        // Add WHERE conditions
        if (!empty($conditions)) {
            $whereClauses = [];
            $params = [];
            
            foreach ($conditions as $column => $value) {
                // Validate column name
                if (!in_array($column, $this->allowedColumns[$table])) {
                    throw new Exception("Invalid column: $column");
                }
                
                if (is_array($value)) {
                    // Handle IN clause
                    $placeholders = array_fill(0, count($value), '?');
                    $whereClauses[] = "$column IN (" . implode(', ', $placeholders) . ")";
                    $params = array_merge($params, $value);
                } elseif ($value === null) {
                    // Handle NULL
                    $whereClauses[] = "$column IS NULL";
                } else {
                    // Regular condition
                    $whereClauses[] = "$column = ?";
                    $params[] = $value;
                }
            }
            
            $sql .= " WHERE " . implode(' AND ', $whereClauses);
        }
        
        // Prepare and execute
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            
            return $stmt->fetchAll();
            
        } catch (PDOException $e) {
            $this->logSecurityEvent('Query failed', [
                'sql' => $sql,
                'error' => $e->getMessage()
            ]);
            throw new Exception("Database query failed");
        }
    }
    
    /**
     * Secure INSERT with type checking
     */
    public function secureInsert($table, array $data) {
        // Validate table
        if (!in_array($table, $this->allowedTables)) {
            throw new Exception("Invalid table name");
        }
        
        // Validate columns
        $columns = array_keys($data);
        foreach ($columns as $column) {
            if (!in_array($column, $this->allowedColumns[$table])) {
                throw new Exception("Invalid column: $column");
            }
        }
        
        // Build query
        $placeholders = array_fill(0, count($data), '?');
        
        $sql = "INSERT INTO $table (" . implode(', ', $columns) . ") 
                VALUES (" . implode(', ', $placeholders) . ")";
        
        try {
            $stmt = $this->pdo->prepare($sql);
            
            // Bind parameters with type detection
            $i = 1;
            foreach ($data as $value) {
                $type = $this->getPDOType($value);
                $stmt->bindValue($i++, $value, $type);
            }
            
            $stmt->execute();
            
            return [
                'success' => true,
                'id' => $this->pdo->lastInsertId()
            ];
            
        } catch (PDOException $e) {
            $this->logSecurityEvent('Insert failed', [
                'table' => $table,
                'error' => $e->getMessage()
            ]);
            throw new Exception("Insert operation failed");
        }
    }
    
    /**
     * Secure UPDATE with strict validation
     */
    public function secureUpdate($table, array $data, array $where) {
        // Validate table
        if (!in_array($table, $this->allowedTables)) {
            throw new Exception("Invalid table name");
        }
        
        // Validate all columns
        $allColumns = array_merge(array_keys($data), array_keys($where));
        foreach ($allColumns as $column) {
            if (!in_array($column, $this->allowedColumns[$table])) {
                throw new Exception("Invalid column: $column");
            }
        }
        
        // Build SET clause
        $setClauses = [];
        $params = [];
        
        foreach ($data as $column => $value) {
            $setClauses[] = "$column = ?";
            $params[] = $value;
        }
        
        // Build WHERE clause
        $whereClauses = [];
        foreach ($where as $column => $value) {
            $whereClauses[] = "$column = ?";
            $params[] = $value;
        }
        
        $sql = "UPDATE $table SET " . implode(', ', $setClauses) . 
               " WHERE " . implode(' AND ', $whereClauses);
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            
            return [
                'success' => true,
                'affected' => $stmt->rowCount()
            ];
            
        } catch (PDOException $e) {
            $this->logSecurityEvent('Update failed', [
                'table' => $table,
                'error' => $e->getMessage()
            ]);
            throw new Exception("Update operation failed");
        }
    }
    
    /**
     * Secure DELETE with confirmation
     */
    public function secureDelete($table, array $where, $requireConfirmation = true) {
        // Validate table
        if (!in_array($table, $this->allowedTables)) {
            throw new Exception("Invalid table name");
        }
        
        // Prevent accidental mass deletion
        if (empty($where) && $requireConfirmation) {
            throw new Exception("Cannot delete without WHERE clause");
        }
        
        // Validate columns
        foreach (array_keys($where) as $column) {
            if (!in_array($column, $this->allowedColumns[$table])) {
                throw new Exception("Invalid column: $column");
            }
        }
        
        // Build WHERE clause
        $whereClauses = [];
        $params = [];
        
        foreach ($where as $column => $value) {
            $whereClauses[] = "$column = ?";
            $params[] = $value;
        }
        
        $sql = "DELETE FROM $table WHERE " . implode(' AND ', $whereClauses);
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            
            $affected = $stmt->rowCount();
            
            // Log deletion for audit
            $this->logSecurityEvent('Data deleted', [
                'table' => $table,
                'conditions' => $where,
                'affected' => $affected
            ]);
            
            return [
                'success' => true,
                'deleted' => $affected
            ];
            
        } catch (PDOException $e) {
            $this->logSecurityEvent('Delete failed', [
                'table' => $table,
                'error' => $e->getMessage()
            ]);
            throw new Exception("Delete operation failed");
        }
    }
    
    /**
     * Execute stored procedure securely
     */
    public function callProcedure($procedure, array $params = []) {
        // Whitelist procedures
        $allowedProcedures = ['GetUserById', 'UpdateInventory', 'ProcessOrder'];
        
        if (!in_array($procedure, $allowedProcedures)) {
            throw new Exception("Invalid procedure");
        }
        
        $placeholders = array_fill(0, count($params), '?');
        $sql = "CALL $procedure(" . implode(', ', $placeholders) . ")";
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            
            return $stmt->fetchAll();
            
        } catch (PDOException $e) {
            $this->logSecurityEvent('Procedure failed', [
                'procedure' => $procedure,
                'error' => $e->getMessage()
            ]);
            throw new Exception("Procedure execution failed");
        }
    }
    
    /**
     * Validate columns against whitelist
     */
    private function validateColumns($table, $columns) {
        if ($columns === ['*']) {
            return $columns;
        }
        
        $validColumns = [];
        foreach ($columns as $column) {
            if (!in_array($column, $this->allowedColumns[$table])) {
                throw new Exception("Invalid column: $column");
            }
            $validColumns[] = $column;
        }
        
        return $validColumns;
    }
    
    /**
     * Get PDO parameter type
     */
    private function getPDOType($value) {
        if (is_null($value)) {
            return PDO::PARAM_NULL;
        } elseif (is_bool($value)) {
            return PDO::PARAM_BOOL;
        } elseif (is_int($value)) {
            return PDO::PARAM_INT;
        } else {
            return PDO::PARAM_STR;
        }
    }
    
    /**
     * Log security events
     */
    private function logSecurityEvent($event, $data) {
        $logEntry = [
            'timestamp' => date('Y-m-d H:i:s'),
            'event' => $event,
            'data' => $data,
            'ip' => $_SERVER['REMOTE_ADDR'] ?? 'CLI',
            'user' => $_SESSION['user_id'] ?? 'anonymous'
        ];
        
        error_log(json_encode($logEntry), 3, '/var/log/security.log');
    }
}

// ========================================
// MYSQLI PREPARED STATEMENTS
// ========================================

class SecureMySQLiDatabase {
    private $mysqli;
    private $typeMap = [];
    
    public function __construct(mysqli $mysqli) {
        $this->mysqli = $mysqli;
    }
    
    /**
     * Secure query with automatic type detection
     */
    public function secureQuery($sql, array $params = []) {
        // Check for dangerous patterns
        if ($this->containsDangerousSQL($sql)) {
            throw new Exception("Potentially dangerous SQL detected");
        }
        
        $stmt = $this->mysqli->prepare($sql);
        
        if (!$stmt) {
            throw new Exception("Prepare failed: " . $this->mysqli->error);
        }
        
        if (!empty($params)) {
            // Build type string
            $types = $this->buildTypeString($params);
            
            // Bind parameters
            $stmt->bind_param($types, ...$params);
        }
        
        if (!$stmt->execute()) {
            throw new Exception("Execute failed: " . $stmt->error);
        }
        
        // Get result if SELECT query
        if (stripos($sql, 'SELECT') === 0) {
            $result = $stmt->get_result();
            $data = [];
            
            while ($row = $result->fetch_assoc()) {
                $data[] = $row;
            }
            
            $stmt->close();
            return $data;
        }
        
        // Return affected rows for other queries
        $affected = $stmt->affected_rows;
        $stmt->close();
        
        return ['affected' => $affected];
    }
    
    /**
     * Build type string for mysqli
     */
    private function buildTypeString(array $params) {
        $types = '';
        
        foreach ($params as $param) {
            if (is_int($param)) {
                $types .= 'i';
            } elseif (is_float($param)) {
                $types .= 'd';
            } elseif (is_string($param)) {
                $types .= 's';
            } else {
                $types .= 'b'; // blob
            }
        }
        
        return $types;
    }
    
    /**
     * Check for dangerous SQL patterns
     */
    private function containsDangerousSQL($sql) {
        $dangerous = [
            '/DROP\s+TABLE/i',
            '/DROP\s+DATABASE/i',
            '/TRUNCATE/i',
            '/DELETE\s+FROM\s+\w+\s*$/i', // DELETE without WHERE
            '/UPDATE\s+\w+\s+SET\s+[^W]+$/i', // UPDATE without WHERE
            '/;.*--;?/i', // Multiple statements
            '/UNION.*SELECT/i', // UNION without proper context
            '/INTO\s+OUTFILE/i', // File operations
            '/LOAD_FILE/i'
        ];
        
        foreach ($dangerous as $pattern) {
            if (preg_match($pattern, $sql)) {
                return true;
            }
        }
        
        return false;
    }
}

// ========================================
// INPUT VALIDATION & SANITIZATION
// ========================================

class InputValidator {
    
    /**
     * Validate and sanitize input
     */
    public static function validate($input, $type, $options = []) {
        switch ($type) {
            case 'int':
                return self::validateInt($input, $options);
                
            case 'float':
                return self::validateFloat($input, $options);
                
            case 'email':
                return self::validateEmail($input);
                
            case 'url':
                return self::validateUrl($input);
                
            case 'alphanumeric':
                return self::validateAlphanumeric($input, $options);
                
            case 'date':
                return self::validateDate($input, $options);
                
            case 'phone':
                return self::validatePhone($input);
                
            case 'uuid':
                return self::validateUuid($input);
                
            default:
                return self::validateString($input, $options);
        }
    }
    
    /**
     * Validate integer
     */
    private static function validateInt($input, $options = []) {
        $filtered = filter_var($input, FILTER_VALIDATE_INT);
        
        if ($filtered === false) {
            throw new ValidationException("Invalid integer");
        }
        
        // Check range
        if (isset($options['min']) && $filtered < $options['min']) {
            throw new ValidationException("Value below minimum");
        }
        
        if (isset($options['max']) && $filtered > $options['max']) {
            throw new ValidationException("Value above maximum");
        }
        
        return $filtered;
    }
    
    /**
     * Validate float
     */
    private static function validateFloat($input, $options = []) {
        $filtered = filter_var($input, FILTER_VALIDATE_FLOAT);
        
        if ($filtered === false) {
            throw new ValidationException("Invalid float");
        }
        
        return $filtered;
    }
    
    /**
     * Validate email
     */
    private static function validateEmail($input) {
        $filtered = filter_var($input, FILTER_VALIDATE_EMAIL);
        
        if ($filtered === false) {
            throw new ValidationException("Invalid email address");
        }
        
        // Additional checks
        if (!checkdnsrr(substr(strrchr($filtered, "@"), 1), 'MX')) {
            throw new ValidationException("Email domain does not exist");
        }
        
        return $filtered;
    }
    
    /**
     * Validate URL
     */
    private static function validateUrl($input) {
        $filtered = filter_var($input, FILTER_VALIDATE_URL);
        
        if ($filtered === false) {
            throw new ValidationException("Invalid URL");
        }
        
        // Check allowed protocols
        $allowedProtocols = ['http', 'https'];
        $protocol = parse_url($filtered, PHP_URL_SCHEME);
        
        if (!in_array($protocol, $allowedProtocols)) {
            throw new ValidationException("Invalid URL protocol");
        }
        
        return $filtered;
    }
    
    /**
     * Validate alphanumeric
     */
    private static function validateAlphanumeric($input, $options = []) {
        $allowSpaces = $options['allow_spaces'] ?? false;
        $pattern = $allowSpaces ? '/^[a-zA-Z0-9\s]+$/' : '/^[a-zA-Z0-9]+$/';
        
        if (!preg_match($pattern, $input)) {
            throw new ValidationException("Invalid alphanumeric value");
        }
        
        return $input;
    }
    
    /**
     * Validate date
     */
    private static function validateDate($input, $options = []) {
        $format = $options['format'] ?? 'Y-m-d';
        $date = DateTime::createFromFormat($format, $input);
        
        if (!$date || $date->format($format) !== $input) {
            throw new ValidationException("Invalid date format");
        }
        
        return $input;
    }
    
    /**
     * Validate phone number
     */
    private static function validatePhone($input) {
        // Remove non-numeric characters
        $phone = preg_replace('/[^0-9]/', '', $input);
        
        // Check length (adjust for your country)
        if (strlen($phone) < 10 || strlen($phone) > 15) {
            throw new ValidationException("Invalid phone number");
        }
        
        return $phone;
    }
    
    /**
     * Validate UUID
     */
    private static function validateUuid($input) {
        $pattern = '/^[0-9a-f]{8}-[0-9a-f]{4}-4[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i';
        
        if (!preg_match($pattern, $input)) {
            throw new ValidationException("Invalid UUID");
        }
        
        return $input;
    }
    
    /**
     * Validate string with sanitization
     */
    private static function validateString($input, $options = []) {
        $minLength = $options['min_length'] ?? 0;
        $maxLength = $options['max_length'] ?? PHP_INT_MAX;
        $allowHtml = $options['allow_html'] ?? false;
        
        // Remove null bytes
        $input = str_replace(chr(0), '', $input);
        
        // Trim whitespace
        $input = trim($input);
        
        // Check length
        if (strlen($input) < $minLength) {
            throw new ValidationException("String too short");
        }
        
        if (strlen($input) > $maxLength) {
            throw new ValidationException("String too long");
        }
        
        // Sanitize HTML
        if (!$allowHtml) {
            $input = htmlspecialchars($input, ENT_QUOTES, 'UTF-8');
        } else {
            // Allow only safe HTML tags
            $allowedTags = '

';
            $input = strip_tags($input, $allowedTags);
        }
        
        return $input;
    }
}

class ValidationException extends Exception {}

// ========================================
// USAGE EXAMPLES
// ========================================

// PDO Example
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
$db = new SecurePDODatabase($pdo);

// Secure SELECT
try {
    $users = $db->secureSelect('users', [
        'status' => 'active',
        'role' => ['admin', 'moderator']
    ], ['id', 'username', 'email']);
    
} catch (Exception $e) {
    echo "Query failed: " . $e->getMessage();
}

// Secure INSERT with validation
try {
    $email = InputValidator::validate($_POST['email'], 'email');
    $age = InputValidator::validate($_POST['age'], 'int', ['min' => 18, 'max' => 120]);
    
    $result = $db->secureInsert('users', [
        'email' => $email,
        'age' => $age,
        'created_at' => date('Y-m-d H:i:s')
    ]);
    
    echo "User created with ID: " . $result['id'];
    
} catch (ValidationException $e) {
    echo "Validation error: " . $e->getMessage();
} catch (Exception $e) {
    echo "Database error: " . $e->getMessage();
}

// MySQLi Example
$mysqli = new mysqli('localhost', 'user', 'pass', 'test');
$db = new SecureMySQLiDatabase($mysqli);

// Secure query
$result = $db->secureQuery(
    "SELECT * FROM products WHERE price > ? AND category = ?",
    [29.99, 'electronics']
);

print_r($result);