MariaDB/MySQL 加减密码专题

加密是一个永恒的话题，一般的做法是在程序中做好加密再存入数据库中，实际上数据库本身就带有了强大的加密功能，在开发时可以将加密减密部分丢给数据库处理，以减轻程序负担。

1. 加密减密函数
2. 表空间加密

1. 加密减密函数

1.1 不可逆哈希函数 MD5()、SHA1()、SHA2()

MD5() 函数计算一个给定字符串的 MD5 摘要，并将结果作为一个 32 位的由十六进制字符组成的字符串返回。
SHA()/SHA1() 函数计算并返回给定字符串的 SHA-1 160 位校验和。
SHA2() 函数计算并返回给定字符串的 SHA-2 摘要，包括： SHA-224, SHA-256, SHA-384, 和 SHA-512 算法。

MariaDB [(none)]> select MD5('1234'), SHA1('1234');
+----------------------------------+------------------------------------------+
| MD5('1234')                      | SHA1('1234')                             |
+----------------------------------+------------------------------------------+
| 81dc9bdb52d04dc20036dbd8313ed055 | 7110eda4d09e062aa5e4a390b0a572ac0d2c0220 |
+----------------------------------+------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> select SHA2('1234', 'SHA-256');
+------------------------------------------------------------------+
| SHA2('1234', 'SHA-256')                                          |
+------------------------------------------------------------------+
| 03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4 |
+------------------------------------------------------------------+
1 row in set, 2 warnings (0.000 sec)

1.2 可逆BASE64编码函数 TO_BASE64()、FROM_BASE64()

TO_BASE64() 函数将给定字符串以 base-64 形式编码，并返回编码后的字符串表示。
FROM_BASE64() 函数可以解码 TO_BASE64() 函数的结果。

不同于现有的 base-64 方案， MySQL TO_BASE64() 和 FROM_BASE64() 函数有些特殊的规则：
字母值 62 的编码是 '+'.
字母值 63 的编码是 '/'.
编码输出由 4 个可打印字符组成。输入数据的每 3 个字节使用 4 个字符进行编码。如果最后一组长度不够 4，则用 '=' 字符填充。
在编码输出的每 76 个字符之后添加一个换行符，以将长输出分成多行。
解码识别并忽略换行符、回车符、制表符和空格。

MariaDB [(none)]> select TO_BASE64('123456'), FROM_BASE64(TO_BASE64('123456'));
+---------------------+----------------------------------+
| TO_BASE64('123456') | FROM_BASE64(TO_BASE64('123456')) |
+---------------------+----------------------------------+
| MTIzNDU2            | 123456                           |
+---------------------+----------------------------------+
1 row in set (0.000 sec)

1.3 可逆字符串加密减密 ENCODE()、DECODE()

ENCODE()函数用于对纯文本字符串进行编码。 MySQL ENCODE()函数返回一个二进制字符串，其大小与纯文本字符串相同。如果传递的字符串为空字符串，则MySQL DECODE()函数将返回空字符串。
ENCODE(plain_string, password_string);
plain_string –它用于指定要编码的纯文本。
password_string –用于指定密码字符串以对纯文本字符串进行编码。

DECODE()函数用于解码编码的字符串并返回原始字符串。如果编码的字符串为空字符串，则MySQL DECODE()函数将返回空字符串。
DECODE(encoded_string, password_string);
encoded_string –它用于指定要解码的编码字符串。
password_string –用于指定密码字符串以对纯文本字符串进行编码。

MariaDB [(none)]> select TO_BASE64(ENCODE('123456', 'longlongkey')), DECODE(ENCODE('123456', 'longlongkey'), 'longlongkey');
+--------------------------------------------+--------------------------------------------------------+
| TO_BASE64(ENCODE('123456', 'longlongkey')) | DECODE(ENCODE('123456', 'longlongkey'), 'longlongkey') |
+--------------------------------------------+--------------------------------------------------------+
| ZuJdugqY                                   | 123456                                                 |
+--------------------------------------------+--------------------------------------------------------+
1 row in set (0.000 sec)

1.4 可逆AES加密减密 AES_ENCRYPT()、AES_DECRYPT()

AES_ENCRYPT函数用于使用高级加密标准(AES)算法对字符串进行加密。 MySQL AES_ENCRYPT函数使用128位 key 长度对数据进行编码，但是可以将其扩展为256位 key 长度。它加密一个字符串并返回一个二进制字符串。
AES_ENCRYPT函数返回的值是二进制字符串，如果参数为NULL，则返回NULL。 AES_ENCRYPT函数接受两个参数，它们是加密的字符串和用于加密该字符串的 key 字符串。

使用方法：
AES_ENCRYPT(str, key_str)
str -用於指定純字符串。
key_str -它用於指定用於加密str的String。

AES_DECRYPT函数在解密加密的字符串后返回原始字符串。它使用AES(高级加密标准)算法执行解密。 AES_DECRYPT函数返回解密的字符串，如果检测到无效数据，则返回NULL。
AES_DECRYPT函数返回的值是使用AES_ENCRYPT函数加密的原始纯文本字符串。 AES_DECRYPT函数接受两个参数，它们是加密的字符串和用于解密加密的字符串的字符串。

使用方法：
AES_DECRYPT(encrypted_string, key_string)
encrypted_string -它用于指定加密的字符串。
key_string -它用于指定用于解密encrypted_string的字符串。

MariaDB [(none)]> select TO_BASE64(AES_ENCRYPT('123456', 'longlongkey')), AES_DECRYPT(AES_ENCRYPT('123456', 'longlongkey'), 'longlongkey');
+-------------------------------------------------+------------------------------------------------------------------+
| TO_BASE64(AES_ENCRYPT('123456', 'longlongkey')) | AES_DECRYPT(AES_ENCRYPT('123456', 'longlongkey'), 'longlongkey') |
+-------------------------------------------------+------------------------------------------------------------------+
| 9kht2Y23owte27XvL5nrHw==                        | 123456                                                           |
+-------------------------------------------------+------------------------------------------------------------------+
1 row in set (0.000 sec)

推荐使用这对加解密函数。aes_encrypt()和aes_decrypt()使用官方的aes（高级加密标准）算法（以前称为“rijndael”）实现数据的加密和解密。
加密后的二进制串长度可以通过下面公式计算：16 * (trunc(string_length / 16) + 1)

1.5 可逆DES加密减密 DES_ENCRYPT()、DES_DECRYPT()

DES_ENCRYPT函数用于使用DES(数据加密标准)算法对字符串进行加密。 MySQL DES_ENCRYPT函数使用 key 来加密字符串。
DES_ENCRYPT函数返回的值是加密的字符串或NULL。 DES_ENCRYPT函数接受三个参数，即纯文本字符串和 key 字符串以及用于对该字符串进行加密的 key 编号。

使用方法:
DES_ENCRYPT(plaintext_string, [key_number | key_string]);
plaintext_string -它用于指定要加密的字符串。
key_number -它用于从DES key 文件中指定一个介于0到9之间的数字。
key_string -它用于指定用于加密plain-text字符串的字符串。

DES_DECRYPT函数用于使用DES(数据加密标准)算法解密加密的字符串。 MySQL DES_DECRYPT函数使用 key 来解密字符串。
DES_DECRYPT函数返回的值是解密的字符串或NULL。 DES_DECRYPT函数接受两个参数，即加密字符串和用于解密该字符串的 key 字符串。
使用方法:
DES_DECRYPT(encrypted_string, key_string);
encrypted_string -它用于指定要解密的加密字符串。
key_string -它用于指定用于解密加密字符串的 key。

MariaDB [(none)]> select TO_BASE64(DES_ENCRYPT('123456', 'longlongkey')), DES_DECRYPT(DES_ENCRYPT('123456', 'longlongkey'), 'longlongkey');
+-------------------------------------------------+------------------------------------------------------------------+
| TO_BASE64(DES_ENCRYPT('123456', 'longlongkey')) | DES_DECRYPT(DES_ENCRYPT('123456', 'longlongkey'), 'longlongkey') |
+-------------------------------------------------+------------------------------------------------------------------+
| /xxlGjs1E7yP                                    | 123456                                                           |
+-------------------------------------------------+------------------------------------------------------------------+
1 row in set (0.000 sec)

1.6 不可逆字符串加密 ENCRYPT()

此函数用于使用UNIX crypt()对字符串进行加密。
语法为：ENCRYPT( string [, salt ] )
string要使用UNIX crypt()进行加密的纯文本字符串。
salt盐值。可选的。加密过程中使用的至少2个字符长的字符串。如果未提供盐，则ENCRYPT函数将使用随机值。

说明：
如果salt的长度小于2个字符，则ENCRYPT函数将返回NULL。
如果字符串为NULL ，则ENCRYPT函数将返回NULL。
如果您的系统上没有UNIX crypt()，则ENCRYPT函数将返回NULL。 (所以WINDOWS版本没法进行加密)

MariaDB [(none)]> select ENCRYPT('123456'), ENCRYPT('123456', '123'), ENCRYPT(null);
+-------------------+--------------------------+---------------+
| ENCRYPT('123456') | ENCRYPT('123456', '123') | ENCRYPT(null) |
+-------------------+--------------------------+---------------+
| GM10tk006M.KQ     | 12tir.zIbWQ3c            | NULL          |
+-------------------+--------------------------+---------------+
1 row in set (0.000 sec)

1.6 不可逆密码加密函数 OLD_PASSWORD()、PASSWORD

OLD_PASSWORD：函数将明文密码字符串加密散列密码，这是MySQL 4.1之前使用的技术。要使用更新的加密技术，请使用PASSWORD函数。
PASSWORD： MySQL中的身份验证系统使用PASSWORD函数将明文密码字符串变成散列密码。
如果字符串为NULL ，则PASSWORD函数将返回NULL。
注意：在MySQL中的身份验证系统中，PASSWORD函数用来存储密码。不要在自己的应用程序中使用PASSWORD函数，而应使用MD5或SHA1函数。

MariaDB [(none)]> select OLD_PASSWORD('longlongpass'), PASSWORD('longlongpass');
+------------------------------+-------------------------------------------+
| OLD_PASSWORD('longlongpass') | PASSWORD('longlongpass')                  |
+------------------------------+-------------------------------------------+
| 3082fbb5443042fc             | *36F8A7FF6DD88185406A7DDE6A3D5C332B6B25B8 |
+------------------------------+-------------------------------------------+
1 row in set (0.000 sec)

1.7 可逆字符串压缩函数 COMPRESS()、UNCOMPRESS()、UNCOMPRESSED_LENGTH()

COMPRESS() 函数压缩一个字符串并返回压缩后的二进制字符串。
UNCOMPRESS() 函数解压缩 COMPRESS() 函数压缩后的字符串。
UNCOMPRESSED_LENGTH() 函数返回一个压缩的字符串的原始字符串的长度。

MariaDB [(none)]> select TO_BASE64(COMPRESS('longlongtext')), UNCOMPRESS(COMPRESS('longlongtext')), UNCOMPRESSED_LENGTH(COMPRESS('longlongtext')) as LENGTH;
+-------------------------------------+--------------------------------------+--------+
| TO_BASE64(COMPRESS('longlongtext')) | UNCOMPRESS(COMPRESS('longlongtext')) | LENGTH |
+-------------------------------------+--------------------------------------+--------+
| DAAAAHicy8nPS88B4pLUihIAIS8FJg==    | longlongtext                         |     12 |
+-------------------------------------+--------------------------------------+--------+
1 row in set (0.000 sec)

虽然字符串压缩和加密无关，但用在字符串加密上有神奇的效果！

MariaDB [(none)]> select PASSWORD(COMPRESS(ENCRYPT(MD5('longlongpassword'))));
+------------------------------------------------------+
| PASSWORD(COMPRESS(ENCRYPT(MD5('longlongpassword')))) |
+------------------------------------------------------+
| *8C51F6BC538504AB1D3E5531068897161FF89813            |
+------------------------------------------------------+
1 row in set (0.000 sec)