PHP 與 MySQL 資料庫整合實戰：從連線機制到安全防護完整指南

<?php
/**
 * PHP 與 MySQL 資料庫整合範例
 * 展示 PDO 與 MySQLi 的基本連線方式、預處理陳述式使用，以及安全的資料查詢操作
 */

// ============================================================================
// 範例一：PDO 資料庫連線與基本查詢
// ============================================================================

/**
 * 使用 PDO 建立資料庫連線
 * PDO 提供資料庫抽象層，支援多種資料庫系統
 */
class DatabasePDO {
    private $pdo;
    private $host = 'localhost';
    private $database = 'company_db';
    private $username = 'web_user';
    private $password = 'secure_password';
    private $charset = 'utf8mb4';
    
    /**
     * 建立 PDO 連線
     * 使用 try-catch 處理連線錯誤
     */
    public function connect() {
        try {
            // 建立 DSN (Data Source Name) 字串
            // charset=utf8mb4 確保完整的 Unicode 支援（包含表情符號）
            $dsn = "mysql:host={$this->host};dbname={$this->database};charset={$this->charset}";
            
            // PDO 連線選項設定
            $options = [
                // 設定錯誤模式為例外處理，方便捕捉錯誤
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                
                // 設定預設的 fetch 模式為關聯陣列
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                
                // 禁用模擬預處理陳述式，使用真正的預處理功能
                PDO::ATTR_EMULATE_PREPARES => false,
                
                // 設定持久連線（需謹慎使用，可能導致連線洩漏）
                // PDO::ATTR_PERSISTENT => true,
            ];
            
            // 建立 PDO 實例
            $this->pdo = new PDO($dsn, $this->username, $this->password, $options);
            
            return $this->pdo;
            
        } catch (PDOException $e) {
            // 記錄錯誤訊息到日誌檔案
            error_log("資料庫連線失敗：" . $e->getMessage());
            
            // 不要將詳細錯誤訊息顯示給使用者，避免洩漏系統資訊
            throw new Exception("資料庫連線失敗，請稍後再試");
        }
    }
    
    /**
     * 使用預處理陳述式進行安全查詢
     * 
     * @param int $userId 使用者 ID
     * @return array|false 查詢結果或 false
     */
    public function getUserById($userId) {
        try {
            // 準備 SQL 陳述式，使用 ? 作為參數佔位符
            // 這種方式稱為位置參數（Positional Parameters）
            $sql = "SELECT id, username, email, created_at 
                    FROM users 
                    WHERE id = ? AND status = ?";
            
            // 建立預處理陳述式
            $stmt = $this->pdo->prepare($sql);
            
            // 執行查詢並綁定參數
            // 參數按順序對應 SQL 中的 ? 佔位符
            $stmt->execute([$userId, 'active']);
            
            // 取得單筆記錄
            $user = $stmt->fetch();
            
            return $user;
            
        } catch (PDOException $e) {
            error_log("查詢錯誤：" . $e->getMessage());
            return false;
        }
    }
    
    /**
     * 使用命名參數的預處理陳述式
     * 命名參數提供更好的可讀性，特別是在複雜查詢中
     * 
     * @param string $email 電子郵件
     * @param string $username 使用者名稱
     * @return array|false 查詢結果
     */
    public function getUserByEmailOrUsername($email, $username) {
        try {
            // 使用命名參數（Named Parameters），以 :name 格式標記
            $sql = "SELECT id, username, email 
                    FROM users 
                    WHERE email = :email OR username = :username 
                    LIMIT 1";
            
            $stmt = $this->pdo->prepare($sql);
            
            // 綁定命名參數
            // 參數名稱必須與 SQL 中的佔位符一致（包含冒號）
            $stmt->execute([
                ':email' => $email,
                ':username' => $username
            ]);
            
            return $stmt->fetch();
            
        } catch (PDOException $e) {
            error_log("查詢錯誤：" . $e->getMessage());
            return false;
        }
    }
    
    /**
     * 新增使用者記錄
     * 展示 INSERT 操作與取得最後插入的 ID
     * 
     * @param array $userData 使用者資料
     * @return int|false 新增記錄的 ID 或 false
     */
    public function createUser($userData) {
        try {
            $sql = "INSERT INTO users (username, email, password_hash, created_at) 
                    VALUES (:username, :email, :password_hash, NOW())";
            
            $stmt = $this->pdo->prepare($sql);
            
            // 密碼應該使用 password_hash() 進行雜湊處理
            $passwordHash = password_hash($userData['password'], PASSWORD_BCRYPT);
            
            $result = $stmt->execute([
                ':username' => $userData['username'],
                ':email' => $userData['email'],
                ':password_hash' => $passwordHash
            ]);
            
            if ($result) {
                // 回傳最後插入的 ID
                return $this->pdo->lastInsertId();
            }
            
            return false;
            
        } catch (PDOException $e) {
            error_log("新增使用者失敗：" . $e->getMessage());
            return false;
        }
    }
    
    /**
     * 交易處理範例
     * 確保多個資料庫操作的原子性（ACID 特性）
     * 
     * @param int $fromUserId 轉出帳戶 ID
     * @param int $toUserId 轉入帳戶 ID
     * @param float $amount 轉帳金額
     * @return bool 交易是否成功
     */
    public function transferFunds($fromUserId, $toUserId, $amount) {
        try {
            // 開始交易
            $this->pdo->beginTransaction();
            
            // 從轉出帳戶扣款
            $sql1 = "UPDATE accounts 
                     SET balance = balance - :amount 
                     WHERE user_id = :user_id AND balance >= :amount";
            
            $stmt1 = $this->pdo->prepare($sql1);
            $stmt1->execute([
                ':amount' => $amount,
                ':user_id' => $fromUserId
            ]);
            
            // 檢查是否有記錄被更新（餘額是否足夠）
            if ($stmt1->rowCount() === 0) {
                throw new Exception("餘額不足或帳戶不存在");
            }
            
            // 向轉入帳戶加款
            $sql2 = "UPDATE accounts 
                     SET balance = balance + :amount 
                     WHERE user_id = :user_id";
            
            $stmt2 = $this->pdo->prepare($sql2);
            $stmt2->execute([
                ':amount' => $amount,
                ':user_id' => $toUserId
            ]);
            
            // 記錄交易歷史
            $sql3 = "INSERT INTO transactions 
                     (from_user_id, to_user_id, amount, created_at) 
                     VALUES (:from_user, :to_user, :amount, NOW())";
            
            $stmt3 = $this->pdo->prepare($sql3);
            $stmt3->execute([
                ':from_user' => $fromUserId,
                ':to_user' => $toUserId,
                ':amount' => $amount
            ]);
            
            // 提交交易
            $this->pdo->commit();
            
            return true;
            
        } catch (Exception $e) {
            // 發生錯誤時回滾所有變更
            $this->pdo->rollBack();
            
            error_log("交易失敗：" . $e->getMessage());
            return false;
        }
    }
}

// ============================================================================
// 範例二：MySQLi 物件導向方式的資料庫操作
// ============================================================================

/**
 * 使用 MySQLi 物件導向方式建立資料庫連線
 * MySQLi 專注於 MySQL 資料庫的最佳化支援
 */
class DatabaseMySQLi {
    private $mysqli;
    private $host = 'localhost';
    private $username = 'web_user';
    private $password = 'secure_password';
    private $database = 'company_db';
    
    /**
     * 建立 MySQLi 連線
     */
    public function connect() {
        // 啟用錯誤報告
        mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
        
        try {
            // 建立 MySQLi 物件
            $this->mysqli = new mysqli(
                $this->host,
                $this->username,
                $this->password,
                $this->database
            );
            
            // 設定字元集為 utf8mb4（完整 Unicode 支援）
            $this->mysqli->set_charset('utf8mb4');
            
            return $this->mysqli;
            
        } catch (mysqli_sql_exception $e) {
            error_log("MySQLi 連線失敗：" . $e->getMessage());
            throw new Exception("資料庫連線失敗");
        }
    }
    
    /**
     * 使用 MySQLi 預處理陳述式進行查詢
     * 
     * @param string $email 電子郵件
     * @return array|null 查詢結果
     */
    public function getUserByEmail($email) {
        try {
            // 準備 SQL 陳述式，使用 ? 作為參數佔位符
            $sql = "SELECT id, username, email, created_at 
                    FROM users 
                    WHERE email = ?";
            
            // 建立預處理陳述式
            $stmt = $this->mysqli->prepare($sql);
            
            // 綁定參數
            // 第一個參數 "s" 表示參數類型為字串（string）
            // 其他類型：i=integer, d=double, b=blob
            $stmt->bind_param("s", $email);
            
            // 執行查詢
            $stmt->execute();
            
            // 取得結果
            $result = $stmt->get_result();
            
            // 取得一筆記錄作為關聯陣列
            $user = $result->fetch_assoc();
            
            // 關閉陳述式
            $stmt->close();
            
            return $user;
            
        } catch (mysqli_sql_exception $e) {
            error_log("查詢錯誤：" . $e->getMessage());
            return null;
        }
    }
    
    /**
     * 使用 MySQLi 進行多參數查詢
     * 展示不同資料型態的參數綁定
     * 
     * @param int $minAge 最小年齡
     * @param int $maxAge 最大年齡
     * @param string $city 城市
     * @return array 查詢結果陣列
     */
    public function getUsersByAgeAndCity($minAge, $maxAge, $city) {
        try {
            $sql = "SELECT id, username, email, age, city 
                    FROM users 
                    WHERE age BETWEEN ? AND ? AND city = ? 
                    ORDER BY age ASC";
            
            $stmt = $this->mysqli->prepare($sql);
            
            // 綁定多個參數
            // "iis" 表示：integer, integer, string
            $stmt->bind_param("iis", $minAge, $maxAge, $city);
            
            $stmt->execute();
            
            $result = $stmt->get_result();
            
            // 取得所有記錄
            $users = $result->fetch_all(MYSQLI_ASSOC);
            
            $stmt->close();
            
            return $users;
            
        } catch (mysqli_sql_exception $e) {
            error_log("查詢錯誤：" . $e->getMessage());
            return [];
        }
    }
    
    /**
     * MySQLi 交易處理範例
     * 
     * @param array $orderData 訂單資料
     * @return bool 交易是否成功
     */
    public function createOrder($orderData) {
        try {
            // 開始交易
            $this->mysqli->begin_transaction();
            
            // 插入訂單主檔
            $sql1 = "INSERT INTO orders (user_id, total_amount, created_at) 
                     VALUES (?, ?, NOW())";
            
            $stmt1 = $this->mysqli->prepare($sql1);
            $stmt1->bind_param("id", $orderData['user_id'], $orderData['total_amount']);
            $stmt1->execute();
            
            // 取得訂單 ID
            $orderId = $this->mysqli->insert_id;
            
            // 插入訂單明細
            $sql2 = "INSERT INTO order_items (order_id, product_id, quantity, price) 
                     VALUES (?, ?, ?, ?)";
            
            $stmt2 = $this->mysqli->prepare($sql2);
            
            foreach ($orderData['items'] as $item) {
                $stmt2->bind_param(
                    "iiid",
                    $orderId,
                    $item['product_id'],
                    $item['quantity'],
                    $item['price']
                );
                $stmt2->execute();
            }
            
            // 更新庫存
            $sql3 = "UPDATE products 
                     SET stock = stock - ? 
                     WHERE id = ? AND stock >= ?";
            
            $stmt3 = $this->mysqli->prepare($sql3);
            
            foreach ($orderData['items'] as $item) {
                $stmt3->bind_param(
                    "iii",
                    $item['quantity'],
                    $item['product_id'],
                    $item['quantity']
                );
                $stmt3->execute();
                
                // 檢查是否成功更新（庫存是否足夠）
                if ($stmt3->affected_rows === 0) {
                    throw new Exception("庫存不足：產品 ID {$item['product_id']}");
                }
            }
            
            // 提交交易
            $this->mysqli->commit();
            
            return true;
            
        } catch (Exception $e) {
            // 回滾交易
            $this->mysqli->rollback();
            
            error_log("訂單建立失敗：" . $e->getMessage());
            return false;
        }
    }
}

// ============================================================================
// 範例三：防範 SQL 注入攻擊的實務案例
// ============================================================================

/**
 * 展示不安全的查詢方式（絕對不要這樣做！）
 * 這段程式碼僅供教學目的，說明 SQL 注入的危險性
 */
class UnsafeDatabaseExample {
    private $pdo;
    
    /**
     * 不安全的查詢方式 - 容易受到 SQL 注入攻擊
     * 
     * 假設攻擊者輸入：admin' OR '1'='1
     * 最終 SQL 變成：SELECT * FROM users WHERE username = 'admin' OR '1'='1'
     * 這會回傳所有使用者記錄！
     */
    public function unsafeLogin($username, $password) {
        // ❌ 危險！直接將使用者輸入拼接到 SQL 字串中
        $sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
        
        // 執行查詢（這會讓攻擊者有機會注入惡意 SQL）
        $result = $this->pdo->query($sql);
        
        return $result->fetch();
    }
    
    /**
     * 安全的查詢方式 - 使用預處理陳述式
     */
    public function safeLogin($username, $password) {
        // ✅ 安全！使用預處理陳述式與參數綁定
        $sql = "SELECT id, username, email FROM users WHERE username = ? LIMIT 1";
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$username]);
        
        $user = $stmt->fetch();
        
        // 使用 password_verify() 驗證密碼雜湊
        if ($user && password_verify($password, $user['password_hash'])) {
            return $user;
        }
        
        return false;
    }
}

// ============================================================================
// 使用範例
// ============================================================================

// PDO 範例
$db = new DatabasePDO();
$db->connect();

// 安全地查詢使用者
$user = $db->getUserById(123);

if ($user) {
    echo "使用者名稱：" . htmlspecialchars($user['username']) . "\n";
    echo "電子郵件：" . htmlspecialchars($user['email']) . "\n";
}

// MySQLi 範例
$mysqli = new DatabaseMySQLi();
$mysqli->connect();

// 查詢特定城市的使用者
$users = $mysqli->getUsersByAgeAndCity(25, 35, '台北市');

foreach ($users as $user) {
    echo "姓名：" . htmlspecialchars($user['username']) . "\n";
}