萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL AES 加解密函數實戰:AES_ENCRYPT/AES_DECRYPT 使用詳解與坑點避雷

基礎用法(MySQL 5.5+,默認 AES-128-ECB)

-- 加密
SET @key = 'my_secret_key';
SET @encrypted = AES_ENCRYPT('敏感數據', @key);
-- 存儲 @encrypted 到 BLOB/VARBINARY 列

-- 解密
SELECT AES_DECRYPT(@encrypted, @key);  -- 返回 '敏感數據'

進階模式(MySQL 5.6+ 支持指定模式與 IV)

-- 設置加密模式:AES-256-CBC
SET block_encryption_mode = 'aes-256-cbc';

-- 派生密鑰與初始化向量
SET @key_str = SHA2('My secret passphrase', 512);
SET @init_vector = RANDOM_BYTES(16);

-- 加密
SET @crypt_str = AES_ENCRYPT('text', @key_str, @init_vector);

-- 解密(需相同 key + iv)
SELECT AES_DECRYPT(@crypt_str, @key_str, @init_vector);

關鍵坑點與最佳實踐

1. 存儲類型必須用二進制

錯誤:用 VARCHAR/TEXT 存儲加密結果 → 字符集轉換、尾部空格截斷導致數據損壞

正確:VARBINARY(n) 或 BLOB

CREATE TABLE t_secret (
    id INT PRIMARY KEY AUTO_INCREMENT,
    secret_data VARBINARY(256)  -- 或 BLOB
);

2. 二進制數據插入方式

  • 預處理語句(推薦):驅動層直接傳輸二進制流,無需轉碼
  • 十六進制字符串:INSERT ... VALUES (UNHEX('...'))
  • Base64:應用層編碼/解碼,存 VARCHAR 但增加 33% 體積

3. 密鑰管理原則

  • **不要硬編碼** 在 SQL 或代碼中
  • 使用密鑰管理系統(HashiCorp Vault、AWS KMS、阿里雲 KMS)
  • 應用層派生密鑰(PBKDF2/Argon2)+ 隨機 IV,每次加密唯一
  • 定期輪換密鑰,配合數據重加密策略

4. 版本兼容性注意

MySQL 版本默認模式支持模式備註
5.5 / 5.6AES-128-ECB僅 ECBECB 無 IV,相同明文產生相同密文,安全性較弱
5.7+AES-128-ECB支持 CBC/CFB/OFB 等需顯式 SET block_encryption_mode
8.0+AES-128-ECB全模式 + RANDOM_BYTES()推薦生產環境

完整應用示例(PHP PDO)

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 設置加密模式(需 5.7+)
$pdo->exec("SET block_encryption_mode = 'aes-256-cbc'");

// 派生密鑰與 IV
$passphrase = 'My secret passphrase';
$key = hash('sha512', $passphrase, true);  // 64 bytes
$iv = random_bytes(16);

// 加密插入
$plain = '身份證號: 110101199001011234';
$stmt = $pdo->prepare('INSERT INTO t_secret (secret_data) VALUES (AES_ENCRYPT(?, ?, ?))');
$stmt->bindValue(1, $plain, PDO::PARAM_STR);
$stmt->bindValue(2, $key, PDO::PARAM_LOB);
$stmt->bindValue(3, $iv, PDO::PARAM_LOB);
$stmt->execute();

// 解密查詢
$stmt = $pdo->prepare('SELECT AES_DECRYPT(secret_data, ?, ?) AS plain FROM t_secret WHERE id = ?');
$stmt->bindValue(1, $key, PDO::PARAM_LOB);
$stmt->bindValue(2, $iv, PDO::PARAM_LOB);
$stmt->bindValue(3, 1, PDO::PARAM_INT);
$stmt->execute();
echo $stmt->fetchColumn();  // 身份證號: 110101199001011234

性能與安全建議

  • 大字段加密考慮應用層加密,減少 DB CPU 壓力
  • 索引加密列**無意義**——密文無序,篩選需解密後比較
  • 審計日志記錄「誰、何時、訪問了什麼」,而非記錄明文
  • 合規場景結合透明加密(TDE)或字段級加密插件
copyright © 萬盛學電腦網 all rights reserved