XYZ的筆記本: MySQL 使用 AES 加解密資料、PHP 產生相容 MySQL 的加解密效果

MySQL 使用 AES_ENCRYPT()、AES_DECRYPT() 兩個函式，可將資料進行 AES 的加解、解密。
所以若資料庫中，有某些欄位是隱私資料，想加密後再儲存到資料表中。只要 INSERT 到資料表時用 AES_ENCRYPT() 將資料先加密；SELECT 時用 AES_DECRYPT() 將資料解密還原即可。

AES_ENCRYPT() 加密語法為 AES_ENCRYPT(str,key_str[,init_vector])
str：原始字串
key_str：自行設定的密鑰
init_vector：此參數 MySQL 5.6.17 之後才有。且多了 block_encryption_mode 環境變數，可設定不同演算法。測試環境為 MariaDB 5.5，演算法是 ECB，沒用到此參數，後面都以 AES-128-ECB 演算法加解密測試。
AES_DECRYPT() 解密語法為 AES_DECRYPT(crypt_str,key_str[,init_vector])
crypt_str：加密後的二進位資料
key_str：自行設定的密鑰
init_vector：ECB 演算法沒用到此參數
解密失敗會回傳 null，也可能回傳非 null 的垃圾資料。
「If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid.」
密鑰長度：AES-128-ECB 密鑰長度為 128bits (As of MySQL 5.6.17, key lengths of 196 or 256 bits can be used)，但使用 AES_ENCRYPT()、AES_DECRYPT() 時，如果輸入太短或太長的密鑰，MySQL 會自動處理成 128bits
原始字串 str、加密後資料 crypt_str 可以是任意長度。
而 AES-128-ECB 演算法(128bits)，原資料須為 16bytes 的倍數，所以原始字串 str 加密前，AES_ENCRYPT() 會自動將長度填充為AES加密演算法須要的區塊倍數長度(16byte的倍數)，解密時 AES_DECRYPT() 再將填充的字元移除。
至於用來填充的字元，則是用原始字串 str 須要再補多少長度才會是的16byte倍數的char值(所補長度數字取char得到的字元、ASCII對應的字元)，且若原字串長度剛好為 16bytes 倍數時，也會再填充一個完整的 16bytes 區塊。如此反解後，只須由最後一個字元，即可知反解後的字串最後面多少長度是填充的，才能將填充後的字串去除，得到原始字串 str。
將加密完的密文，儲存到資料表，所以須知道 AES_ENCRYPT() 回傳的資料型態、資料長度。
資料型態：加密後的資料為二進位資料。(若 str、key_str 有任一個為 null，AES_ENCRYPT 將回傳 null)
資料長度：資料加密後的長度計算方式「16 * (trunc(string_length / 16) + 1)」，其中 trunc() 是虛擬程式碼(pseudo code)，表示小數部分無條件捨去。

測試加密前後資料長度：

將"ABC"資料，用"testkey"當作密鑰加密，前後的長度變化

SELECT LENGTH("ABC"); //3 bytes
SELECT LENGTH(AES_ENCRYPT("ABC","testkey")); //16 bytes
SELECT LENGTH(AES_ENCRYPT("ABC","testkey123456790")); //16 bytes

加密後的長度為 16*(trunc(3/16)+1)=16 bytes

將"1234567890ABCDEF"資料，用"testkey"當作密鑰加密，前後的長度變化
```
SELECT LENGTH("1234567890ABCDEF"); //16
SELECT LENGTH(AES_ENCRYPT("1234567890ABCDEF","testkey")); //32 bytes
```
加密後的長度為 16*(trunc(16/16)+1)=32 bytes
將"1234567890ABCDEFG"資料，用"testkey"當作密鑰加密，前後的長度變化
```
SELECT LENGTH("1234567890ABCDEFG"); //17
SELECT LENGTH(AES_ENCRYPT("1234567890ABCDEFG","testkey")); //32 bytes
```
加密後的長度為 16*(trunc(17/16)+1)=32 bytes

將"一二三四五六七八九十一二三四五六"資料，用"testkey"當作密鑰加密，前後的長度變化

SELECT LENGTH("一二三四五六七八九十一二三四五六"); //48
SELECT LENGTH(AES_ENCRYPT("一二三四五六七八九十一二三四五六","testkey")); //64
SELECT c, LENGTH(c ), CHAR_LENGTH(c), LENGTH(AES_ENCRYPT(c, "testkey")) FROM zz;


mysql> SELECT c, LENGTH(c), CHAR_LENGTH(c), LENGTH(AES_ENCRYPT(c, "testkey")) FROM test;

+-------------------------------+------------+----------------+-----------------------------------+
| c                             | LENGTH(c ) | CHAR_LENGTH(c) | LENGTH(AES_ENCRYPT(c, "testkey")) |
+-------------------------------+------------+----------------+-----------------------------------+
| 一二三四五六七八九十一二三四五六 |         48 |             16 |                                64 |
+-------------------------------+------------+----------------+-----------------------------------+

可發現UTF8中，常用的中文，一個字是3 bytes，所以16個中文字，是16*3=48 bytes
加密後的長度為 16*(trunc((16*3)/16)+1)=64 bytes
所以假設原本 varchar(16)，要改用 varbinary 儲存加密後的結果，至少須設為 varbinary(64)，若只設 varbinary(63) 或 varbinary(16)、varbinary(32)，長度都不夠，加密資料無法全部儲存，將無法正確反解。

加解密寫入資料表測試：
(PHP、MySQL，用的 PHPMyAdmin 版本，常用手動改回不用16進位顯示2進位，所以直接用PHP測試)

//$db PDO物件
$stmt = $db->query("CREATE TABLE IF NOT EXISTS `zz` (`id` int(11) NOT NULL, `c` varchar(255) NOT NULL, `c_aes` varbinary(2) DEFAULT '', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
$stmt = $db->query("ALTER TABLE zz CHANGE c_aes c_aes VARBINARY(64) NULL DEFAULT ''"); //可修改不同 VARBINARY 長度測試
$stmt = $db->query("INSERT INTO zz (id, c, c_aes) VALUES (1, '一二三四五六七八九十一二三四五六', '') ON DUPLICATE KEY UPDATE c=VALUES(c)");　//可修改不同加密內容'一二三四五六七八九十一二三四五六'測試
$stmt = $db->query("UPDATE zz SET c_aes = AES_ENCRYPT( c, 'testkey')");
$stmt = $db->query("SELECT c, c_aes, LENGTH(c), CHAR_LENGTH(c), LENGTH(AES_ENCRYPT(c, 'testkey')) , AES_DECRYPT(AES_ENCRYPT(c,'testkey'),'testkey'), LENGTH(c_aes), AES_DECRYPT(c_aes,'testkey'), LENGTH(AES_DECRYPT(c_aes, 'testkey')) FROM zz");
var_dump($stmt->fetch(\PDO::FETCH_ASSOC));
array(9) {
  ["c"]=> //加密前字串
  string(48) "一二三四五六七八九十一二三四五六"
  ["c_aes"]=> //加密後二進位資料
  string(64) "? �Lmy; M� i� 3�zf@��r����p�'ƒ ا� ��x � � �/���r�%O� *e�:�y"
  ["LENGTH(c)"]=> //加密前byte長度
  string(2) "48"
  ["CHAR_LENGTH(c)"]=> //加密前字數
  string(2) "16"
  ["LENGTH(AES_ENCRYPT(c, 'testkey'))"]=> //加密後byte長度
  string(2) "64"
  ["AES_DECRYPT(AES_ENCRYPT(c,'testkey'),'testkey')"]=> //解密後字串
  string(48) "一二三四五六七八九十一二三四五六"
  ["LENGTH(c_aes)"]=> //加密後儲存到資料表的的二進位資料byte長度(VARBINARY太短時，可觀察到被截斷)
  string(2) "64"
  ["AES_DECRYPT(c_aes,'testkey')"]=>　//將資料表儲存的加密二進位資料解密後的字串(若儲存後已被截斷，會無法正常解密)
  string(48) "一二三四五六七八九十一二三四五六"
  ["LENGTH(AES_DECRYPT(c_aes, 'testkey'))"]=> //資料表儲存的加密二進位資料解密後的字串byte長度
  string(2) "48"
}

PHP 可以用 openssl_*、mcrypt_* 兩種方法進行 AES 加解密，但 mcrypt PHP 7.1 之後已不建議使用。以下是分別使用 openssl、mcrypt 產生跟 MySQL 相同的加解密結果。

openssl，主要須處理密鑰 key 超過 16 bytes 的部分，MySQL 會對過長部分進行 XOR 運算，PHP 測試結果似乎是將過長部分截斷。

/**
 * 模擬 MySQL AES_ENCRYPT()、AES_ENCRYPT()
 */
class AesMySQL {

    /**
     * 原始的密鑰字串
     * @var string 
     */
    private $key_str;

    /**
     * 處理後符合規則的密鑰字串
     * @var string 
     */
    private $key;

    /**
     * 將原始的密鑰字串，處理成 MySQL AES_ENCRYPT() 使用的密鑰格式
     * @param string $key_str 原始的密鑰字串
     * @return string
     */
    private function getAesKey($key_str) {
        if (isset($this->key_str) && $key_str === $this->key_str) {
            //此原始的密鑰字串已處理過
        } else {
            //PHP：測試超過16bytes的部分似乎會截斷。
            //MySQL：超過16bytes，依序每16bytes分成一組，每一組同位置的位元組進行XOR運算，最終處理成只有16bytes
            //若原始長度小於16bytes，PHP、MySQL都是在後面用 chr(0) 補齊，chr(0)即"\0"
            $key_len = 16; //處理成16bytes
            $key_str_len = strlen($key_str);
            if ($key_str_len <= $key_len) {
                $pad = $key_len - $key_str_len;
                $key = $key_str . str_repeat("\0", $pad); //"\0" 可用 chr(0) 替代
            } else {
                $key = substr($key_str, 0, $key_len);
                for ($i = $key_len; $i < $key_str_len; $i++) {
                    $pos = $i % $key_len;
                    $key[$pos] = $key[$pos] ^ $key_str[$i];
                }
            }
            $this->key_str = $key_str;
            $this->key = $key;
        }

        return $this->key;
    }

    /**
     * 模擬 MySQL AES_ENCRYPT() 加密結果 (使用openssl_encrypt)
     * @param string $str
     * @param string $key_str 原始的密鑰字串
     * @return binary|null
     */
    public function aesEncrypt($str, $key_str) {
        if (null === $str || null === $key_str) {
            return null;
        }
        //openssl_get_cipher_methods()可取得可用的演算法列表
        $cipher = "AES-128-ECB"; //MySQL使用 128bit ECB 演算法
        $key = $this->getAESKey($key_str); //密鑰用MySQL的規則再處理過(測試原本PHP太長超過16bytes的部分會截斷)
        $options = OPENSSL_RAW_DATA; //OPENSSL_RAW_DATA、OPENSSL_ZERO_PADDING
        //OPENSSL_RAW_DATA 會自動使用 PKCS#7 格式填充，所以加解密不須自己處理填充問題
        //OPENSSL_ZERO_PADDING 須自己處理填充(加密前自行加上填充、解密後自行去除填充)，且回傳格式為 Base64
        //http://php.net/manual/en/function.openssl-encrypt.php#117208
        $ciphertext_raw = openssl_encrypt($str, $cipher, $key, $options); //ECB沒使用iv
        return $ciphertext_raw;
    }

    /**
     * 模擬 MySQL AES_DECRYPT() 解密結果 (使用openssl_encrypt)
     * @param binary $crypt_str
     * @param string $key_str 原始的密鑰字串
     * @return string|null
     */
    public function aesDecrypt($crypt_str, $key_str) {
        if (null === $crypt_str || null === $key_str) {
            return null;
        }
        $cipher = "AES-128-ECB"; //MySQL使用 128bit ECB 演算法
        $options = OPENSSL_RAW_DATA;
        $key = $this->getAESKey($key_str);
        $original_plaintext = openssl_decrypt($crypt_str, $cipher, $key, $options);
        return $original_plaintext;
    }

}

mcrypt，須處理密鑰長度過短、過長，以及加密內容的填充

/**
 * 模擬 MySQL AES_ENCRYPT()、AES_ENCRYPT()
 */
class AesMySQL_Old {

    /**
     * 原始的密鑰字串
     * @var string 
     */
    private $key_str;

    /**
     * 處理後符合規則的密鑰字串
     * @var string 
     */
    private $key;

    /**
     * 將原始的密鑰字串，處理成 MySQL AES_ENCRYPT() 使用的密鑰格式
     * @param string $key_str 原始的密鑰字串
     * @return string
     */
    private function getAesKey($key_str) {
        if (isset($this->key_str) && $key_str === $this->key_str) {
            //此原始的密鑰字串已處理過
        } else {
            //PHP：只接受剛好 16、24、32 bytes 長度的字串。
            //MySQL：接受任何長度的字串，
            //       長度小於16bytes，MySQL在後面用 chr(0) 補齊，
            //       若超過16bytes，依序每16bytes分成一組，每一組同位置的位元組進行XOR運算，處理成只有16bytes
            $key_len = 16; //處理成16bytes
            $key_str_len = strlen($key_str);
            if ($key_str_len <= $key_len) {
                $pad = $key_len - $key_str_len;
                $key = $key_str . str_repeat("\0", $pad); //"\0" 可用 chr(0) 替代
            } else {
                $key = substr($key_str, 0, $key_len);
                for ($i = $key_len; $i < $key_str_len; $i++) {
                    $pos = $i % $key_len;
                    $key[$pos] = $key[$pos] ^ $key_str[$i];
                }
            }
            $this->key_str = $key_str;
            $this->key = $key;
        }

        return $this->key;
    }

    /**
     * 模擬 MySQL AES_ENCRYPT() 加密結果 (使用mcrypt_encrypt，PHP7.1以上已不建議使用)
     * @param string $str
     * @param string $key_str 原始的密鑰字串
     * @return binary|null
     */
    public function aesEncrypt($str, $key_str) {
        if (null === $str || null === $key_str) {
            return null;
        }
        $cipher = MCRYPT_RIJNDAEL_128;
        $key = $this->getAESKey($key_str);
        //使用 mcrypt_encrypt 須自行先將填充做好，避免預設自行填充"\0"
        //(If the size of the data is not n * blocksize, the data will be padded with '\0'.)
        $blocksize = 16; //須為16bytes的倍數
        $text = $this->pkcs5Pad($str, $blocksize); //使用PKCS#5填充
        $mode = MCRYPT_MODE_ECB;
        $encrypted_val = mcrypt_encrypt($cipher, $key, $text, $mode); //ECB沒使用iv
        return $encrypted_val;
    }

    /**
     * 模擬 MySQL AES_DECRYPT() 解密結果 (使用mcrypt_encrypt，PHP7.1以上已不建議使用)
     * @param binary $crypt_str
     * @param string $key_str 原始的密鑰字串
     * @return string|null
     */
    public function aesDecrypt($crypt_str, $key_str) {
        if (null === $crypt_str || null === $key_str) {
            return null;
        }
        $cipher = MCRYPT_RIJNDAEL_128;
        $key = $this->getAESKey($key_str);
        $mode = MCRYPT_MODE_ECB;
        $original_plaintext = mcrypt_decrypt($cipher, $key, $crypt_str, $mode);
        $original_plaintext = $this->pkcs5Unpad($original_plaintext); //去除PKCS#5填充的字元
        return $original_plaintext;
    }

    /**
     * 填充不足字節數(PKCS#5)
     * 1.將填充長度取chr()當填充值
     * 2.剛好滿$blocksize倍數，則再填充一組$blocksize大小
     * @param string $text
     * @param int $blocksize
     * @return string
     */
    private function pkcs5Pad($text, $blocksize) {
        $pad = $blocksize - (strlen($text) % $blocksize);
        return $text . str_repeat(chr($pad), $pad);
    }

    /**
     * 去除 pkcs5Pad() 的填充值(PKCS#5)
     * @param string $text
     * @return string|false
     */
    private function pkcs5Unpad($text) {
        $pad = ord($text[strlen($text) - 1]);
        if ($pad > strlen($text)) {
            return false;
        }
        if (strspn($text, chr($pad), strlen($text) - $pad) != $pad) {
            return false;
        }
        return substr($text, 0, -1 * $pad);
    }

}

使用 PHP 加解密，可減輕 MySQL 負擔、不用在 MySQL Server 執行包含密鑰的 SQL 指令，當然其實加解密方式可以不用做成跟 MySQL 相容。
但兩者相容的處理方式，若有一天需要直接使用 SQL 指令的 WHERE 條件過濾加密前的資料時，便可派上用場。

參考：

MySQL :: MySQL 5.6 Reference Manual :: 12.13 Encryption and Compression Functions
MySQL :: Re: AES_ENCRYPT() in php
How to replicate MySQL's aes-256-cbc in PHP - Stack Overflow
Understanding PHP AES Encryption
Replicating MySQL AES Encryption Methods With PHP – Smashing Magazine
進階加密標準 - 維基百科，自由的百科全書
區塊加密法工作模式 - 維基百科，自由的百科全書
第二十四個夏天後: 使用 Openssl / C++ Crypto++ (Cryptopp) 進行 AES-128 / AES-256 Encryption @ Ubutnu 14.04
寫程式是良心事業: Python M2Crypto - AES 的 Encrypt 與 Decrypt

填充 (密碼學) - 維基百科，自由的百科全書
Using openssl_en/decrypt() in PHP instead of...
encryption - What is the difference between PKCS#5 padding and PKCS#7 padding - Cryptography Stack Exchange

PHP: openssl_encrypt - Manual($options 說明)
关于PKCS5Padding与PKCS7Padding的区别 - 在路上... - 博客园
对称加密算法的PKCS5和PKCS7填充 | Zhiwei Li

key - looking for simple PHP multi way encryption method - Stack Overflow
PHP: Large Objects (LOBs) - Manual

XYZ的筆記本

網頁

2017年10月27日星期五

MySQL 使用 AES 加解密資料、PHP 產生相容 MySQL 的加解密效果

沒有留言:

張貼留言

網頁

2017年10月27日 星期五

MySQL 使用 AES 加解密資料、PHP 產生相容 MySQL 的加解密效果

沒有留言:

張貼留言

2017年10月27日星期五