MySQL 雙向加密解密：ENCODE/DECODE 與 AES_ENCRYPT/AES_DECRYPT 實戰

雙向加密場景

需在資料庫儲存敏感資料（密碼、身分證、銀行卡），應用層需還原明文。雙向加密：加密需金鑰，解密需同金鑰。MySQL 內建兩組函數。

方案一：ENCODE / DECODE（簡單、較弱）

-- 插入加密資料
INSERT INTO `users` (`username`, `password`) VALUES ('joe', ENCODE('guessme', 'abracadabra'));

-- 查詢解密
SELECT DECODE(`password`, 'abracadabra') FROM `users` WHERE `username` = 'joe';
-- 返回 'guessme'

特點：對稱加密、金鑰即密碼、結果為二進位字串、安全性較低（演算法未公開、無鹽、無 IV）。

方案二：AES_ENCRYPT / AES_DECRYPT（推薦、標準）

-- 插入（AES-128-ECB，金鑰自動截取/補齊 16 位元組）
INSERT INTO `users` (`username`, `password`) VALUES ('joe', AES_ENCRYPT('guessme', 'abracadabra'));

-- 解密
SELECT AES_DECRYPT(`password`, 'abracadabra') FROM `users` WHERE `username` = 'joe';

特點：AES 標準演算法、支援 128/192/256 位元金鑰（MySQL 5.6.17+ block_encryption_mode）、輸出二進位、相對安全。

PHP 整合範例

prepare("INSERT INTO `users` (`username`, `password`) VALUES (?, AES_ENCRYPT(?, ?))");
$stmt->execute(['joe', 'guessme', $key]);

// 驗證登入（資料庫端解密比對）
$stmt = $pdo->prepare("SELECT COUNT(*) FROM `users` WHERE `username` = ? AND AES_DECRYPT(`password`, ?) = ?");
$stmt->execute(['joe', $key, 'guessme']);
$valid = $stmt->fetchColumn();
echo $valid ? '登入成功' : '帳號或密碼錯誤';

金鑰管理最佳實踐

1. 絕不硬編碼：使用 Vault、AWS KMS、Azure Key Vault、HashiCorp Vault、或環境變數注入。
2. 金鑰輪換：定期更換金鑰，舊資料重新加密（離線批次處理）。
3. 分級加密：高敏感（身分證、銀行卡）用 AES-256-CBC + 隨機 IV，低敏感用 AES-128-ECB。
4. 應用層加密優先：資料進 DB 前已加密，DB 僅存密文，降低 DB 洩露風險。

進階：AES_CBC + IV（MySQL 5.6.17+）

-- 設定模式
SET block_encryption_mode = 'aes-256-cbc';

-- 加密：需 32 位元組金鑰 + 16 位元組 IV
SET @key = SHA2('master_key', 256);  -- 32 bytes
SET @iv = RANDOM_BYTES(16);          -- 16 bytes
INSERT INTO `secure_data` (`data`, `iv`) VALUES (AES_ENCRYPT('secret', @key, @iv), @iv);

-- 解密
SELECT AES_DECRYPT(`data`, @key, `iv`) FROM `secure_data`;

結語

MySQL 內建加密函數適合簡單場景、遷移專案、輔助加密。核心系統建議應用層加密（libsodium、OpenSSL）+ 金鑰管理系統，資料庫僅存密文。無論哪層加密，金鑰生命週期管理才是安全的核心。