PHP MySQL 預處理語句深度解析：原理、防注入機制、綁定參數與雙介面實戰

預處理語句是資料庫操作的黃金標準，能徹底防禦 SQL 注入、提升重複執行效能、減少網路傳輸。本文詳細說明運作原理、MySQLi 與 PDO 兩套介面完整實作、參數綁定型別、交易控制、常見錯誤與最佳實務。

預處理語句運作三階段

1. 準備：傳送 SQL 模板 (含 ? 或 :name 佔位符) 給資料庫
2. 編譯/優化：資料庫解析、編譯、優化查詢計畫，儲存執行計畫
3. 執行：綁定實際參數值，資料庫執行預編譯計畫

同一預處理語句可重複執行多次，僅參數值不同，省去重複解析優化開銷。

核心優勢

MySQLi 實作完整範例

物件導向風格

<?php
$host = 'localhost'; $user = 'username'; $pass = 'password'; $db = 'myDB';

$mysqli = new mysqli($host, $user, $pass, $db);
if ($mysqli->connect_error) {
    die("連線失敗: " . $mysqli->connect_error);
}

// 1. 準備
$stmt = $mysqli->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
if (!$stmt) {
    die("準備失敗: " . $mysqli->error);
}

// 2. 綁定參數
// "sss" = 三個字串參數
// 型別對應: i=integer, d=double, s=string, b=blob
$stmt->bind_param("sss", $firstname, $lastname, $email);

// 3. 重複執行
$data = [
    ['John', 'Doe', 'john@example.com'],
    ['Mary', 'Moe', 'mary@example.com'],
    ['Julie', 'Dooley', 'julie@example.com']
];

foreach ($data as $row) {
    [$firstname, $lastname, $email] = $row;
    if (!$stmt->execute()) {
        echo "執行失敗: " . $stmt->error;
    }
}

echo "新記錄插入成功";
$stmt->close();
$mysqli->close();

程序式風格

$conn = mysqli_connect($host, $user, $pass, $db);
if (!$conn) die("連線失敗: " . mysqli_connect_error());

$stmt = mysqli_stmt_init($conn);
if (mysqli_stmt_prepare($stmt, "INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)")) {
    mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);

    $data = [ /* ... */ ];
    foreach ($data as $row) {
        [$firstname, $lastname, $email] = $row;
        mysqli_stmt_execute($stmt);
    }
}
mysqli_stmt_close($stmt);
mysqli_close($conn);

PDO 實作完整範例

<?php
$host = 'localhost'; $user = 'username'; $pass = 'password'; $db = 'myDBPDO';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // 使用真實預處理

    // 命名佔位符 (推薦，可讀性佳)
    $stmt = $pdo->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (:firstname, :lastname, :email)");

    // 綁定參數 (參考綁定，變數值改變即生效)
    $stmt->bindParam(':firstname', $firstname);
    $stmt->bindParam(':lastname', $lastname);
    $stmt->bindParam(':email', $email);

    $data = [
        ['John', 'Doe', 'john@example.com'],
        ['Mary', 'Moe', 'mary@example.com'],
        ['Julie', 'Dooley', 'julie@example.com']
    ];

    foreach ($data as $row) {
        [$firstname, $lastname, $email] = $row;
        $stmt->execute();
    }

    echo "新記錄插入成功";
} catch (PDOException $e) {
    echo "錯誤: " . $e->getMessage();
}
$pdo = null;

參數綁定型別對照表

PDO::PARAM_* 常數

$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->bindValue(':data', $blob, PDO::PARAM_LOB);
$stmt->bindValue(':bool', $flag, PDO::PARAM_BOOL);

進階技巧與最佳實務

1. 批次插入優化 (單次交易)

// MySQLi
$mysqli->begin_transaction();
$stmt = $mysqli->prepare("INSERT INTO logs (message) VALUES (?)");
$stmt->bind_param('s', $msg);
foreach ($messages as $msg) $stmt->execute();
$mysqli->commit();

// PDO
$pdo->beginTransaction();
$stmt = $pdo->prepare("INSERT INTO logs (message) VALUES (?)");
foreach ($messages as $msg) {
    $stmt->bindValue(1, $msg);
    $stmt->execute();
}
$pdo->commit();

2. 動態建構 WHERE 條件

function buildWhere(array $filters, array &$params): string {
    $clauses = [];
    foreach ($filters as $field => $value) {
        if (is_array($value)) {
            // IN 查詢
            $placeholders = implode(',', array_fill(0, count($value), '?'));
            $clauses[] = "`$field` IN ($placeholders)";
            $params = array_merge($params, $value);
        } else {
            $clauses[] = "`$field` = ?";
            $params[] = $value;
        }
    }
    return $clauses ? 'WHERE ' . implode(' AND ', $clauses) : '';
}

$filters = ['status' => 'active', 'category_id' => [1,2,3]];
$params = [];
$where = buildWhere($filters, $params);
// $where = "WHERE `status` = ? AND `category_id` IN (?,?,?)"
// $params = ['active', 1, 2, 3]

$stmt = $pdo->prepare("SELECT * FROM products $where");
$stmt->execute($params);

3. 獲取自動遞增 ID

// MySQLi
$stmt->execute();
$newId = $stmt->insert_id; // 或 $mysqli->insert_id

// PDO
$stmt->execute();
$newId = $pdo->lastInsertId();

4. 預處理語句與 LIKE 查詢

// 正確：萬用字元包含在參數值中
$search = "%$keyword%";
$stmt = $pdo->prepare("SELECT * FROM products WHERE name LIKE ?");
$stmt->execute([$search]);

// 錯誤：SQL 中拼接萬用字元 (無法使用索引、注入風險)
// $stmt = $pdo->prepare("SELECT * FROM products WHERE name LIKE '%?%'");

5. 限制預處理語句快取大小

// MySQL 伺服器端快取限制
// max_prepared_stmt_count (預設 16382)
// 超過會導致 "Prepared statement needs to be re-prepared"
// 解決：增大參數、減少同時開啟的 stmt、使用後立即 close

常見錯誤與陷阱

MySQLi vs PDO 預處理比較

總結

預處理語句是 PHP 連接 MySQL 的必要實務。新專案強烈建議使用 PDO + 真實預處理 + 命名佔位符 + 例外模式，獲得跨資料庫可移植性、型別安全、清晰錯誤處理。掌握參數綁定型別、動態條件建構、交易控制、批次優化等進階技巧，能構建安全、高效、可維護的資料存取層。

1. 上一篇文章： PHP MySQL SELECT 查詢完整教學
2. 下一篇文章： PHP MySQL 批次插入資料完全指南：多條 SQL、預處理語句與交易控制

PHP 入門

PHP 開發避坑指南：UTF-8 BOM (Byte Order Mark) 導致的輸出異常

在 Dreamweaver 以 UTF-8 儲存檔案時，若勾

PHP MongoDB 驅動整數溢位 Bug 與應對策略：棄用 group 改用 MapReduce

PHP MongoDB 驅動早期版本（pecl/mongo

PHP 表單處理基礎：$_GET、$_POST 超全域變數與資料接收流程

PHP 表單處理基礎 PHP 自動將 HTML 表單提交的資

PHP 過濾器完整指南：從基礎驗證到回調過濾

PHP 過濾器完整指南：從基礎驗證到回調過濾 PHP 過濾器