深度解析：MySQL加密解密

MySQL 數據庫內部有多種 加密函數 解密函數

被加密的字段的類型需要是 VARBINARY、BLOB 類型 存儲

AES_ENCRYPT(str,key) : 返回用密鑰key對字符串str利用高級加密標準算法加密后的結果，調用AES_ENCRYPT的結果是一個二進制字符串

AES_DECRYPT(str,key) : 返回用密鑰key對字符串str利用高級加密標準算法解密后的結果

mysql> CREATE TABLE userdata(username VARCHAR(20),pwssword VARCHAR(50),encryptedpassword VARBINARY(100));

mysql> INSERT INTO userdata(username,pwssword,encryptedpassword) VALUES('smith','abdABC123',AES_ENCRYPT('abdABC123','key'));

mysql> SELECT * FROM userdata;

+----------+-----------+-------------------+

| username | pwssword? | encryptedpassword |

+----------+-----------+-------------------+

| smith? ? | abdABC123 | .|

+----------+-----------+-------------------+

1 row in set (0.01 sec)

mysql> SELECT username,pwssword,AES_DECRYPT(encryptedpassword,'key') FROM userdata;

+----------+-----------+--------------------------------------+

| username | pwssword? | AES_DECRYPT(encryptedpassword,'key') |

+----------+-----------+--------------------------------------+

| smith? ? | abdABC123 | abdABC123? ? ? ? ? ? ? ? ? ? ? ? ? ? |

+----------+-----------+--------------------------------------+

1 row in set (0.00 sec)

MD5(str) : MD5方式對 str 加密

mysql> SELECT MD5('abcABC123');

+----------------------------------+

| MD5('abcABC123')? ? ? ? ? ? ? ? ?|

+----------------------------------+

| 480aeb42d7b1e3937fe8db12a1ffe6d8 |

+----------------------------------+

1 row in set (0.00 sec)

SHA(str) : SHA方式對 str 加密

mysql> SELECT SHA('abcABC123');

+------------------------------------------+

| SHA('abcABC123')? ? ? ? ? ? ? ? ? ? ? ? ?|

+------------------------------------------+

| 0a6807c0856b137fb44ce239587e4f34e011b005 |

+------------------------------------------+

1 row in set (0.00 sec)

SHA1(str) : SHA1方式對 str 加密

mysql> SELECT SHA1('abcABC123');

+------------------------------------------+

| SHA1('abcABC123')? ? ? ? ? ? ? ? ? ? ? ? |

+------------------------------------------+

| 0a6807c0856b137fb44ce239587e4f34e011b005 |

+------------------------------------------+

1 row in set (0.00 sec)

ENCODE(str,key) : 使用字符串 key 作為秘鑰來加密字符串str，加密結果是二進制數

DECODE(str,key) : 使用字符串 key 作為秘鑰來解密字符串str

mysql> INSERT INTO userdata(username,pwssword,encryptedpassword) VALUES('KT','abdABC123',ENCODE('abdABC123','key'));

mysql> SELECT * FROM userdata WHERE username='KT';

+----------+-----------+-------------------+

| username | pwssword? | encryptedpassword |

+----------+-----------+-------------------+

| KT? ? ? ?| abdABC123 | 1? ? ? ?|

+----------+-----------+-------------------+

1 row in set (0.00 sec)

mysql> SELECT username,pwssword,DECODE(encryptedpassword,'key') FROM userdata WHERE username='KT';

+----------+-----------+---------------------------------+

| username | pwssword? | DECODE(encryptedpassword,'key') |

+----------+-----------+---------------------------------+

| KT? ? ? ?| abdABC123 | abdABC123? ? ? ? ? ? ? ? ? ? ? ?|

+----------+-----------+---------------------------------+

1 row in set, 1 warning (0.00 sec)

ENCRYPT(str,salt) : 使用UNIXcrypt()函數，用關鍵詞salt(一個可以惟一確定口令的字符串，就像鑰匙一樣)加密字符串str

mysql> SELECT ENCRYPT('abdABC123','key');

+----------------------------+

| ENCRYPT('abdABC123','key') |

+----------------------------+

| kezazmcIo.aCw? ? ? ? ? ? ? |

+----------------------------+

1 row in set, 1 warning (0.00 sec)

PASSWORD(str) : 返回字符串str的加密版本，這個加密過程是不可逆轉的，和UNIX密碼加密過程使用不同的算法

mysql> INSERT INTO userdata(username,pwssword,encryptedpassword) VALUES('Tom','abdABC123',PASSWORD('abdABC123'));

mysql> SELECT * FROM userdata WHERE username='Tom';

+----------+-----------+-------------------------------------------+

| username | pwssword? | encryptedpassword? ? ? ? ? ? ? ? ? ? ? ? ?|

+----------+-----------+-------------------------------------------+

| Tom? ? ? | abdABC123 | *6970C4ACB558CFFCAD8DE8DA17CDD40CFC023FDA |

+----------+-----------+-------------------------------------------+

1 row in set (0.00 sec)

mysql> SELECT PASSWORD('abcde');

+-------------------------------------------+

| PASSWORD('abcde')? ? ? ? ? ? ? ? ? ? ? ? ?|

+-------------------------------------------+

| *8DC54F2E15823C98AEA063E339A5D4C53D1A471A |

+-------------------------------------------+

1 row in set, 1 warning (0.00 sec)

---------------------?