MySQL 5.0 預處理語句：PREPARE/EXECUTE 實現動態 SQL 與防注入

新語法支援

MySQL 5.0 引入預處理語句，類似 MS SQL 的 sp_executesql，可執行動態 SQL 並有效防止 SQL 注入。

核心語法

PREPARE stmt_name FROM preparable_stmt;
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
{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;

範例三：在 LIMIT 子句中使用（5.0.7+）

SET @a = 1;
PREPARE stmt FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE stmt USING @a;

SET @skip = 1; SET @numrows = 5;
PREPARE stmt FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE stmt USING @skip, @numrows;

重要注意事項

• stmt_name 不分大小寫
• 佔位符 ? 代表字串時不需加引號
• 同名 PREPARE 會立即釋放舊語句（即使新語句有錯）
• 作用域：當前客戶端連線會話
• 若會話結束未顯式 DEALLOCATE，伺服器會自動釋放
• 支援語句：CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE 及大部分 SHOW
• 5.0.13+ 可用於預存程序，但不支援在函數中使用

預存程序中應用範例

CREATE PROCEDURE p1(IN id INT UNSIGNED, IN name VARCHAR(11))
BEGIN
    SET @SqlCmd = 'SELECT * FROM tA';
    IF id IS NOT NULL THEN
        SET @SqlCmd = CONCAT(@SqlCmd, ' WHERE id=?');
        PREPARE stmt FROM @SqlCmd;
        SET @a = id;
        EXECUTE stmt USING @a;
    ELSEIF name IS NOT NULL THEN
        SET @SqlCmd = CONCAT(@SqlCmd, ' WHERE name LIKE ?');
        PREPARE stmt FROM @SqlCmd;
        SET @a = CONCAT(name, '%');
        EXECUTE stmt USING @a;
    END IF;
END;

預處理語句是構建安全、高效動態 SQL 的關鍵工具，建議在應用開發中廣泛採用。