2017年10月27日 星期五

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),表示小數部分無條件捨去。
  • 測試加密前後資料長度:
    1. 將"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
    2. 將"1234567890ABCDEF"資料,用"testkey"當作密鑰加密,前後的長度變化
      SELECT LENGTH("1234567890ABCDEF"); //16
      SELECT LENGTH(AES_ENCRYPT("1234567890ABCDEF","testkey")); //32 bytes
      
      加密後的長度為 16*(trunc(16/16)+1)=32 bytes
    3. 將"1234567890ABCDEFG"資料,用"testkey"當作密鑰加密,前後的長度變化
      SELECT LENGTH("1234567890ABCDEFG"); //17
      SELECT LENGTH(AES_ENCRYPT("1234567890ABCDEFG","testkey")); //32 bytes
      
      加密後的長度為 16*(trunc(17/16)+1)=32 bytes
    4. 將"一二三四五六七八九十一二三四五六"資料,用"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 相同的加解密結果。
    1. 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;
          }
      
      }
    2. 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 條件過濾加密前的資料時,便可派上用場。

參考:

沒有留言:

張貼留言