PHP 讀取資料表到二維陣列：從 mysql_* 到 PDO 的安全遷移指南

原始函數分析

function list_hit($fields, $where, $order, $direction, $limit, $conn) {
    if ($fields == "") {$fields = "hit_id,banner_id,hit_ip,hit_time";}
    if ($order == "") {$direction = "";}
    $sql = "select $fields from hit $where $order $direction $limit";
    $res = mysql_query($sql, $conn);
    $i = 0;
    $list_hit[$i]["rows"] = mysql_num_rows($res);
    $i++;
    while ($rs = mysql_fetch_array($res)) {
        $list_hit[$i]["hit_id"] = $rs["hit_id"];
        $list_hit[$i]["banner_id"] = $rs["banner_id"];
        $list_hit[$i]["hit_ip"] = $rs["hit_ip"];
        $list_hit[$i]["hit_time"] = $rs["hit_time"];
        $i++;
    }
    return $list_hit;
}

關鍵風險

1. SQL 注入：$where, $order, $direction, $limit 直接拼接，若來自使用者輸入極度危險。
2. API 淘汰：mysql_* 於 PHP 7.0 移除，需遷移至 PDO 或 MySQLi。
3. 型別不一致：回傳陣列首項為 ["rows" => count]，其餘為資料列，結構不統一。
4. 資源洩漏：未釋放 mysql_free_result()。
5. 硬編碼欄位：僅支援 hit 表特定欄位，缺乏通用性。

現代化通用 Repository 實作 (PDO)

1. 查詢建構器 (防注入核心)

<?php
declare(strict_types=1);

class SqlBuilder {
    public static function buildSelect(
        string $table,
        array $columns = ['*'],
        array $where = [],
        array $orderBy = [],
        ?int $limit = null,
        ?int $offset = null
    ): array { // 回傳 [sql, params]
        $sql = "SELECT " . implode(', ', array_map('self::quoteIdent', $columns)) . " FROM " . self::quoteIdent($table);
        $params = [];

        if ($where) {
            $conditions = [];
            foreach ($where as $col => $val) {
                if (is_array($val)) { // IN 查詢
                    $placeholders = implode(',', array_fill(0, count($val), '?'));
                    $conditions[] = self::quoteIdent($col) . " IN ($placeholders)";
                    $params = array_merge($params, $val);
                } elseif ($val === null) {
                    $conditions[] = self::quoteIdent($col) . " IS NULL";
                } else {
                    $conditions[] = self::quoteIdent($col) . " = ?";
                    $params[] = $val;
                }
            }
            $sql .= " WHERE " . implode(" AND ", $conditions);
        }

        if ($orderBy) {
            $orders = [];
            foreach ($orderBy as $col => $dir) {
                $dir = strtoupper($dir) === 'DESC' ? 'DESC' : 'ASC';
                $orders[] = self::quoteIdent($col) . " $dir";
            }
            $sql .= " ORDER BY " . implode(", ", $orders);
        }

        if ($limit !== null) {
            $sql .= " LIMIT ?";
            $params[] = $limit;
            if ($offset !== null) {
                $sql .= " OFFSET ?";
                $params[] = $offset;
            }
        }

        return [$sql, $params];
    }

    private static function quoteIdent(string $ident): string {
        // 簡易識別符引用，實際建議用 PDO::quote 或資料庫特定函數
        return '`' . str_replace('`', '``', $ident) . '`';
    }
}

2. 通用 Repository 基類

<?php
abstract class BaseRepository {
    protected PDO $pdo;
    protected string $table;
    protected string $primaryKey = 'id';

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    }

    public function findAll(array $criteria = [], array $orderBy = [], ?int $limit = null, ?int $offset = null): array {
        [$sql, $params] = SqlBuilder::buildSelect($this->table, ['*'], $criteria, $orderBy, $limit, $offset);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function count(array $criteria = []): int {
        [$sql, $params] = SqlBuilder::buildSelect($this->table, ['COUNT(*) as cnt'], $criteria);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    public function findById(mixed $id): ?array {
        $stmt = $this->pdo->prepare("SELECT * FROM " . SqlBuilder::quoteIdent($this->table) . " WHERE " . SqlBuilder::quoteIdent($this->primaryKey) . " = ?");
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }
}

3. 具體 HitRepository

<?php
class HitRepository extends BaseRepository {
    protected string $table = 'hit';
    protected string $primaryKey = 'hit_id';

    public function findByBannerId(int $bannerId, int $limit = 100, int $offset = 0): array {
        return $this->findAll(['banner_id' => $bannerId], ['hit_time' => 'DESC'], $limit, $offset);
    }

    public function getStatsByDate(string $date): array {
        $sql = "SELECT COUNT(*) as total, COUNT(DISTINCT hit_ip) as unique_ips FROM hit WHERE DATE(hit_time) = ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$date]);
        return $stmt->fetch() ?: ['total' => 0, 'unique_ips' => 0];
    }
}

4. 使用範例

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass');
$hitRepo = new HitRepository($pdo);

// 取得第 1 頁，每頁 15 筆，按時間倒序
$hits = $hitRepo->findAll([], ['hit_time' => 'DESC'], 15, 0);
$total = $hitRepo->count();

foreach ($hits as $hit) {
    echo "ID: {$hit['hit_id']}, IP: {$hit['hit_ip']}, Time: {$hit['hit_time']}\n";
}

// 類型安全、可測試、防注入
// $hitRepo->findAll(["hit_ip = '1' OR 1=1" => null]) // 不會注入，因為值會被參數化

遷移對照表

進階建議

• 引入 **Doctrine DBAL** 或 **Laravel Illuminate\Database** 獲得完整 Query Builder、Schema Builder、Migration。
• 使用 **Read Replica** 分流查詢：$pdoRead / $pdoWrite。
• 大量資料匯出改用 **Cursor / Unbuffered Query**：PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false。
• 關鍵查詢加入 **Explain Analyze** 監控索引使用。

結語

mysql_* 時代已終結。擁抱 PDO + Prepared Statements + Repository Pattern，不僅修補安全漏洞，更奠定可測試、可維護、可擴展的資料存取架構基石。