您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 5.0+ Prepared Statements (PREPARE/EXECUTE) 詳解：動態 SQL、防注入與存儲過程應用

編輯：MySQL 資料庫相關知識

前言：從字串拼接到參數化查詢，安全與效能雙贏

MySQL 5.0 引入 PREPARE、EXECUTE、DEALLOCATE PREPARE 三語句，實現服務端預編譯，支援參數綁定 (? 佔位符)，完美對應 MS SQL sp_executesql，同時從根源杜絕 SQL 注入。本文詳細解析語法、變數用法、限制條件、存儲過程整合與進階技巧。

一、核心三語句語法

-- 1. 預編譯：從字串或用戶變數建立語句物件
PREPARE stmt_name FROM preparable_stmt;

-- 2. 執行：綁定用戶變數 (USING @var)
EXECUTE stmt_name [USING @var_name [, @var_name] ...];

-- 3. 釋放：釋放記憶體與句柄
{DEALLOCATE | DROP} PREPARE stmt_name;

二、基礎範例：勾股定理計算

-- 直接字串
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
SET @a = 3; SET @b = 4;
EXECUTE stmt1 USING @a, @b;  -- 結果 5
DEALLOCATE PREPARE stmt1;

-- 從用戶變數取 SQL (動態構建)
SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
PREPARE stmt2 FROM @s;
SET @a = 6; SET @b = 8;
EXECUTE stmt2 USING @a, @b;  -- 結果 10
DEALLOCATE PREPARE stmt2;

三、關鍵規則與注意事項 (必讀)

規則	說明
stmt_name 不區分大小寫	內部統一小寫存儲
佔位符 ? 無需引號	即使對應字串類型，`VALUES (?)` 而非 `VALUES ('?')`
重複 PREPARE 同名 stmt	舊語句立即釋放，即使新語句語法錯誤也會導致舊語句消失
作用域 = 當前會話	斷開連線自動釋放，跨會話不可見
支援語句類型	CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, 大部分 SHOW
不支援	LOCK TABLES, UNLOCK TABLES, START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT 等
存儲過程支援	5.0.13+ 可在存儲過程中使用，函數 (FUNCTION) 仍不支援
LIMIT 參數化	5.0.7+ 支援 `LIMIT ?`、`LIMIT ?, ?`

四、進階應用：動態 WHERE 條件構建 (存儲過程範例)

CREATE PROCEDURE `search_users`(
  IN p_id INT UNSIGNED,
  IN p_name VARCHAR(50)
)
BEGIN
  DECLARE sql_cmd TEXT DEFAULT 'SELECT * FROM users WHERE 1=1';
  
  IF p_id IS NOT NULL THEN
    SET sql_cmd = CONCAT(sql_cmd, ' AND id = ?');
    PREPARE stmt FROM sql_cmd;
    SET @a = p_id;
    EXECUTE stmt USING @a;
    DEALLOCATE PREPARE stmt;
    LEAVE search_users;  -- 結束
  END IF;
  
  IF p_name IS NOT NULL THEN
    SET sql_cmd = CONCAT(sql_cmd, ' AND name LIKE ?');
    PREPARE stmt FROM sql_cmd;
    SET @a = CONCAT(p_name, '%');
    EXECUTE stmt USING @a;
    DEALLOCATE PREPARE stmt;
  END IF;
END;

-- 調用
CALL search_users(1, NULL);      -- 按 ID 精確查
CALL search_users(NULL, 'Zhang'); -- 按姓名前綴模糊查

五、防 SQL 注入原理

傳統拼接："SELECT * FROM t WHERE name = '" + input + "'" → 輸入 ' OR '1'='1 繞過。
預編譯：SQL 結構在 PREPARE 階段固化，? 僅作為資料傳入，永遠不會被解析為 SQL 片段，徹底隔離代碼與數據。

六、效能優勢：執行計畫複用

同一 PREPARE 語句重複 EXECUTE 時，MySQL 可複用解析樹、優化器輸出的執行計畫 (視版本/參數化程度)。
減少網絡傳輸：只需傳參數值，不重發 SQL 文本。
配合 COM_STMT_PREPARE/COM_STMT_EXECUTE 二進位協議 (Connector/NET、JDBC、PDO 等驅動內部自動使用)，效能更強。

七、客戶端驅動層面的 Prepared Statement

應用開發不應手寫 PREPARE SQL，而應使用驅動提供的參數化 API：

語言/驅動	API 範例
C# (MySqlConnector)	`cmd.Parameters.AddWithValue("@p", val); cmd.Prepare();`
Java (JDBC)	`PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, id);`
PHP (PDO)	`$stmt = $pdo->prepare($sql); $stmt->execute([$id]);`
Python (PyMySQL)	`cursor.execute(sql, (id,))` (自動預編譯)
Node.js (mysql2)	`await conn.execute(sql, [id])`

八、常見坑點與對策

佔位符不能用於標識符：SELECT ? FROM t、SELECT * FROM ?、ORDER BY ? 皆非法。動態表名/欄位名需應用層拼接 (嚴格白名單驗證)。
大量 PREPARE 未 DEALLOCATE：會話級記憶體洩漏，務必成對使用或連線池回收時清理。
預編譯後 Schema 變更：ALTER TABLE 導致準備語句失效，下次 EXECUTE 會自動重新準備 (5.6+)，但可能報錯需捕獲重試。
函數中不可用：僅存儲過程支援，UDF/存儲函數不可用。