2017年12月17日 星期日

MySQL 時間函數 NOW() 和 SYSDATE() 的差異

NOW():返回該句 SQL(或stored function、trigger ) 開始執行的時間。
SYSDATE() :返回執時到此函式的時間。

所以,
一句 SQL 有多個 NOW(),返回值一定相同;
一句 SQL 有多個 SYSDATE(),返回值不一定相同,會受執行花費時間影響。


NOW()不管在何處,返回時間都一樣。
mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+



SYSDATE() 返回執行到該函式的時間,所不一定相同。
mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+



參考:MySQL :: MySQL 5.7 Reference Manual :: 12.7 Date and Time Functions
「NOW() returns a constant time that indicates the time at which the statement began to execute.
(Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.
This differs from the behavior for SYSDATE(), which returns the exact time at which it executes.」


PHP、MySQL,IP 轉整數存資料庫

MySQL
INT:0 ~ 4294967295
mysql> SELECT INET_ATON("0.0.0.0"); #0
mysql> SELECT INET_ATON("255.255.255.255"); #4294967295
mysql> SELECT INET_NTOA("0"); #0.0.0.0
mysql> SELECT INET_NTOA("4294967295"); #255.255.255.255
註:
INET_ATON = Address to number
INET_NTOA = Number to Address



PHP(32 bit)
INT:-2147483648 ~ 2147483647
var_dump(ip2long("0.0.0.0")); #int(0)
var_dump(ip2long("127.255.255.255")); #int(2147483647)
var_dump(ip2long("255.255.255.255")); #int(-1)
var_dump(ip2long("128.0.0.0")); #int(-2147483648)
var_dump(long2ip(0)); #string(7) "0.0.0.0"
var_dump(long2ip(2147483647)); #string(15) "127.255.255.255"
var_dump(long2ip(-1)); #string(15) "255.255.255.255"
var_dump(long2ip(-2147483648)); #string(9) "128.0.0.0"
因為在32位元系統,PHP int 型態有分正負號(signed),
範圍為 -2147483648 ~ 2147483647(http://php.net/manual/en/function.intval.php)

可用 sprintf(%u) 轉換成不帶正負號的 (unsigned) 的10進制字串,
可轉成:"0" ~ "4294967295"
var_dump(sprintf("%u", ip2long("0.0.0.0"))); #string(1) "0"
var_dump(sprintf("%u", ip2long("127.255.255.255"))); #string(10) "2147483647"
var_dump(sprintf("%u", ip2long("255.255.255.255"))); #string(10) "4294967295"
var_dump(sprintf("%u", ip2long("128.0.0.0"))); #string(10) "2147483648"
var_dump(long2ip(0)); #string(7) "0.0.0.0"
var_dump(long2ip(2147483647)); #string(15) "127.255.255.255"
var_dump(long2ip(4294967295)); #string(15) "255.255.255.255"
var_dump(long2ip(2147483648)); #string(9) "128.0.0.0"



PHP(64 bit)
INT:0 ~ 4294967295
var_dump(ip2long("0.0.0.0")); #int(0)
var_dump(ip2long("127.255.255.255")); #int(2147483647)
var_dump(ip2long("255.255.255.255")); #int(4294967295)
var_dump(ip2long("128.0.0.0")); #int(2147483648)
var_dump(long2ip(0)); #string(7) "0.0.0.0"
var_dump(long2ip(2147483647)); #string(15) "127.255.255.255"
var_dump(long2ip(4294967295)); #string(15) "255.255.255.255"
var_dump(long2ip(2147483648)); #string(9) "128.0.0.0"

在 64 位元的 PHP,int 最大值可到 9223372036854775807,
有無用 sprintf(%u) 不影響轉出的數字範圍
var_dump(sprintf("%u", ip2long("0.0.0.0"))); #string(1) "0"
var_dump(sprintf("%u", ip2long("127.255.255.255"))); #string(10) "2147483647"
var_dump(sprintf("%u", ip2long("255.255.255.255"))); #string(10) "4294967295"
var_dump(sprintf("%u", ip2long("128.0.0.0"))); #string(10) "2147483648"
var_dump(long2ip(0)); #string(7) "0.0.0.0"
var_dump(long2ip(2147483647)); #string(15) "127.255.255.255"
var_dump(long2ip(4294967295)); #string(15) "255.255.255.255"
var_dump(long2ip(2147483648)); #string(9) "128.0.0.0"

[其他測試]
64 位元的 PHP 將 32 位元的負數結果轉回 IP,似乎也可正常執行(環境:PHP 7.0.12)
var_dump(long2ip(-1)); #string(15) "255.255.255.255"
var_dump(long2ip(-2)); #string(15) "255.255.255.254"
var_dump(long2ip(-3)); #string(15) "255.255.255.253"
var_dump(long2ip(-2147483647)); #string(9) "128.0.0.1"
var_dump(long2ip(-2147483648)); #string(9) "128.0.0.0"


結論:
  1. IP 轉數字存進 MySQL,MySQL 欄位建議使用 INT UNSIGNED,只須 4 bytes,儲存範圍剛好 0~4294967295
  2. 可用 MySQL INET_ATON() 將IP處理成數字存入。
  3. 或先用 PHP long2ip() 處理成數字(要相容 32bit PHP,則long2ip的值須再用 sprintf(%u) 處理)

參考:

Linux、Windows 設定自動關機時間

[Linux]
設定 100 分鐘後自動關機
shutdown -h +100

當天20:15關機
shutdown -h 20:15

清除自動關機設定
shutdown -c

查看自動關機設定
=>Active: active (running) 表示有正在倒數計時的關機設定
=>Active: inactive (dead) 表示無執行中的設定
systemctl status systemd-shutdownd

不真的關機,僅發出關機通知訊息
shutdown -k 23:55


參考:
systemd: How to check scheduled time of a delayed shutdown? - Unix & Linux Stack Exchange 
shutdown - What commands show pending/scheduled tasks in Terminal? - Ask Ubuntu



[Windows]
設定300秒後關機
shutdown -s -t 300

清出自動關機設定
shutdown -a


參考:
Windows 7 使用指令讓它預約關機! @ 回憶......! :: 痞客邦 PIXNET ::


Windows cmd 使用 UTF8

Windows cmd 命令視窗,預設使用Big5(cp950),可使用 chcp 指令改為 UTF8。


查目前使用的 Code Page
> chcp 65001
使用中的字碼頁: 950


改成使用 UTF8 (UTF8的 Code Page 編號為 65001)
> chcp 65001


參考:
代碼頁 - 維基百科,自由的百科全書

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 條件過濾加密前的資料時,便可派上用場。

參考: