萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP MySQL 資料庫操作類:從 mysql_* 遷移到 PDO 的現代化重構

⚠️ 原始碼問題分析

原文提供的 Database 類別使用 mysql_* 函數,**已在 PHP 7.0 完全移除**。且存在:

  • SQL 注入風險(字串拼接)
  • 無預備語句支援
  • 錯誤處理不一致
  • 編碼問題(UTF8 vs utf8mb4)
  • 無交易支援

現代化 PDO 版 Database 類別

<?php
declare(strict_types=1);

class Database
{
    private PDO $pdo;
    private string $tablePrefix = '';

    public function __construct(array $config)
    {
        $dsn = "mysql:host={$config['host']};dbname={$config['name']};charset=utf8mb4";
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",
        ];
        $this->pdo = new PDO($dsn, $config['user'], $config['pass'], $options);
        
        if (!empty($config['prefix'])) {
            $this->tablePrefix = rtrim($config['prefix'], '_') . '_';
        }
    }

    public function getPdo(): PDO
    {
        return $this->pdo;
    }

    // === 核心查詢方法 ===

    public function query(string $sql, array $params = []): PDOStatement
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }

    public function fetchAll(string $sql, array $params = []): array
    {
        return $this->query($sql, $params)->fetchAll();
    }

    public function fetchOne(string $sql, array $params = []): array|false
    {
        return $this->query($sql, $params)->fetch();
    }

    public function fetchColumn(string $sql, array $params = [], int $column = 0): mixed
    {
        return $this->query($sql, $params)->fetchColumn($column);
    }

    // === CRUD 封裝 ===

    public function insert(string $table, array $data): int
    {
        $table = $this->prefix($table);
        $fields = implode(', ', array_keys($data));
        $placeholders = ':' . implode(', :', array_keys($data));
        $sql = "INSERT INTO `$table` ($fields) VALUES ($placeholders)";
        $this->query($sql, $data);
        return (int)$this->pdo->lastInsertId();
    }

    public function insertBatch(string $table, array $rows): int
    {
        if (empty($rows)) return 0;
        $table = $this->prefix($table);
        $fields = array_keys($rows[0]);
        $fieldList = implode(', ', array_map(fn($f) => "`$f`", $fields));
        $placeholders = '(' . implode(', ', array_map(fn($f) => ":$f", $fields)) . ')';
        $allPlaceholders = implode(', ', array_fill(0, count($rows), $placeholders));
        $sql = "INSERT INTO `$table` ($fieldList) VALUES $allPlaceholders";
        
        $flatParams = [];
        foreach ($rows as $i => $row) {
            foreach ($row as $k => $v) {
                $flatParams["{$k}_{$i}"] = $v;
            }
        }
        $this->query($sql, $flatParams);
        return $this->pdo->lastInsertId(); // 返回第一筆 ID
    }

    public function update(string $table, array $data, string $where, array $whereParams = []): int
    {
        $table = $this->prefix($table);
        $set = implode(', ', array_map(fn($k) => "`$k` = :$k", array_keys($data)));
        $sql = "UPDATE `$table` SET $set WHERE $where";
        $params = array_merge($data, $whereParams);
        $this->query($sql, $params);
        return $this->pdo->lastInsertId(); // 實際上應用 rowCount()
    }

    public function delete(string $table, string $where, array $params = []): int
    {
        $table = $this->prefix($table);
        $sql = "DELETE FROM `$table` WHERE $where";
        $this->query($sql, $params);
        return $this->pdo->lastInsertId(); // 同理
    }

    // === 交易支援 ===
    public function transaction(callable $callback): mixed
    {
        $this->pdo->beginTransaction();
        try {
            $result = $callback($this);
            $this->pdo->commit();
            return $result;
        } catch (Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }

    // === Schema 相關 ===
    public function getColumns(string $table): array
    {
        $table = $this->prefix($table);
        return $this->fetchAll("SHOW COLUMNS FROM `$table`");
    }

    public function getEnumValues(string $table, string $column): array
    {
        $cols = $this->getColumns($table);
        foreach ($cols as $col) {
            if ($col['Field'] === $column && preg_match("/^enum\((.+)\)$/", $col['Type'], $m)) {
                return array_map(fn($v) => trim($v, "'"), explode(',', $m[1]));
            }
        }
        return [];
    }

    private function prefix(string $table): string
    {
        return $this->tablePrefix . $table;
    }
}
?>

使用範例

<?php
require 'Database.php';

$config = [
    'host' => 'localhost',
    'name' => 'myapp',
    'user' => 'app_user',
    'pass' => 'secret',
    'prefix' => 'app_',
];

$db = new Database($config);

// 查詢
$users = $db->fetchAll('SELECT * FROM users WHERE status = ? AND created_at > ?', ['active', '2024-01-01']);
$user = $db->fetchOne('SELECT * FROM users WHERE id = ?', [123]);

// 新增
$userId = $db->insert('users', [
    'username' => 'john',
    'email' => 'john@example.com',
    'password' => password_hash('secret', PASSWORD_DEFAULT),
    'created_at' => date('Y-m-d H:i:s'),
]);

// 批次新增
$db->insertBatch('logs', [
    ['user_id' => 1, 'action' => 'login', 'ip' => '1.2.3.4'],
    ['user_id' => 2, 'action' => 'logout', 'ip' => '5.6.7.8'],
]);

// 更新
$db->update('users', ['last_login' => date('Y-m-d H:i:s')], 'id = ?', [123]);

// 刪除
$db->delete('sessions', 'expires_at < ?', [date('Y-m-d H:i:s', strtotime('-30 days'))]);

// 交易
$db->transaction(function ($db) {
    $orderId = $db->insert('orders', ['user_id' => 1, 'total' => 100, 'status' => 'pending']);
    $db->insert('order_items', ['order_id' => $orderId, 'product_id' => 5, 'qty' => 2, 'price' => 50]);
    $db->update('products', ['stock' => 8], 'id = ?', [5]);
});
?>

進階:查詢建構器模式(可選擇引入)

<?php
// 簡易 Query Builder
class QueryBuilder
{
    private Database $db;
    private string $table;
    private array $wheres = [];
    private array $params = [];
    private array $orders = [];
    private ?int $limit = null;
    private ?int $offset = null;

    public function __construct(Database $db, string $table)
    {
        $this->db = $db;
        $this->table = $table;
    }

    public function where(string $column, string $operator, mixed $value): self
    {
        $this->wheres[] = "`$column` $operator ?";
        $this->params[] = $value;
        return $this;
    }

    public function whereIn(string $column, array $values): self
    {
        $placeholders = implode(', ', array_fill(0, count($values), '?'));
        $this->wheres[] = "`$column` IN ($placeholders)";
        $this->params = array_merge($this->params, $values);
        return $this;
    }

    public function orderBy(string $column, string $direction = 'ASC'): self
    {
        $this->orders[] = "`$column` $direction";
        return $this;
    }

    public function limit(int $limit, int $offset = 0): self
    {
        $this->limit = $limit;
        $this->offset = $offset;
        return $this;
    }

    public function get(): array
    {
        $sql = "SELECT * FROM `{$this->table}`";
        if ($this->wheres) $sql .= ' WHERE ' . implode(' AND ', $this->wheres);
        if ($this->orders) $sql .= ' ORDER BY ' . implode(', ', $this->orders);
        if ($this->limit) $sql .= " LIMIT {$this->offset}, {$this->limit}";
        return $this->db->fetchAll($sql, $this->params);
    }

    public function first(): array|false
    {
        $this->limit = 1;
        $results = $this->get();
        return $results[0] ?? false;
    }

    public function count(): int
    {
        $sql = "SELECT COUNT(*) FROM `{$this->table}`";
        if ($this->wheres) $sql .= ' WHERE ' . implode(' AND ', $this->wheres);
        return (int)$this->db->fetchColumn($sql, $this->params);
    }
}

// 使用
$users = (new QueryBuilder($db, 'users'))
    ->where('status', '=', 'active')
    ->whereIn('role', ['admin', 'editor'])
    ->orderBy('created_at', 'DESC')
    ->limit(20)
    ->get();
?>

遷移檢查清單

  • [ ] 所有 mysql_* 替換為 PDO
  • [ ] 所有 SQL 字串拼接改為預備語句 + 參數綁定
  • [ ] 字元集改為 utf8mb4
  • [ ] 錯誤處理統一為 try-catch + PDO::ERRMODE_EXCEPTION
  • [ ] 新增交易支援
  • [ ] 移除 getLastID() 直接用 lastInsertId()
  • [ ] getPossibleValues() 改用 getEnumValues()
  • [ ] 單元測試覆蓋核心 CRUD

現代化資料庫層不僅修正安全漏洞,更能獲得型別提示、靜態分析、單元測試友善等工程化紅利。建議直接採用成熟 ORM(Eloquent、Doctrine、Cycle ORM)或輕量 Query Builder,避免重複造輪子。

copyright © 萬盛學電腦網 all rights reserved