Encryption and Compression
Encryption
AES_ENCRYPT(str, key [, init_vector]) encrypts str with key
using the AES algorithm; AES_DECRYPT(str, key [,
init_vector]) reverses it. DES_ENCRYPT(str, key [,
init_vector]) encrypts str with key using the Triple-DES
algorithm; DES_DECRYPT(str, key [, init_vector]) reverses it.
Hashing
SHA1(str) (synonym: SHA(str)) calculates an SHA-1
160-bit checksum, returned as a string of 40 hex digits.
SHA2(str, hash_length) calculates a hash from the SHA-2 family
(SHA-224, SHA-256, SHA-384, SHA-512); hash_length must be 224, 256,
384, 512, or 0 (a synonym for 256). MD5(str) calculates an
MD5 128-bit checksum, returned as a string of 32 hex digits.
| SELECT SHA1('hello'), SHA2('hello', 256), MD5('hello'); |
SELECT SHA1('hello'), SHA2('hello', 256), MD5('hello');
SELECT SHA1('hello'), SHA2('hello', 256), MD5('hello');
| SHA1('hello') | SHA2('hello', 256) | MD5('hello') |
|---|
| aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d | 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824 | 5d41402abc4b2a76b9719d911017c592 |
Compression
COMPRESS(str) returns a compressed version of str as a binary
string. UNCOMPRESS(str) returns the original string from a
value produced by COMPRESS(). UNCOMPRESSED_LENGTH(str) returns
the length str will have once uncompressed, without actually
uncompressing it.
SELECT UNCOMPRESS(COMPRESS('some text to compress'));
SELECT UNCOMPRESSED_LENGTH(COMPRESS('some text to compress')); |
'some text to compress'; 21
SET @c = COMPRESS('some text to compress');
SELECT UNCOMPRESS(@c);
SELECT UNCOMPRESSED_LENGTH(@c);
SET @c = COMPRESS('some text to compress');
SELECT UNCOMPRESS(@c);
SELECT UNCOMPRESSED_LENGTH(@c);
SELECT UNCOMPRESS(@c):
| UNCOMPRESS(@c) |
|---|
| some text to compress |
SELECT UNCOMPRESSED_LENGTH(@c):
| UNCOMPRESSED_LENGTH(@c) |
|---|
| 21 |
Random Bytes
RANDOM_BYTES(len) returns a binary string of len
cryptographically random bytes, suitable for use as a key or salt.
SET @key = 'my_secret_key';
SET @enc = AES_ENCRYPT('sensitive data', @key);
SELECT AES_DECRYPT(@enc, @key) AS decrypted;
SET @key = 'my_secret_key';
SET @enc = AES_ENCRYPT('sensitive data', @key);
SELECT AES_DECRYPT(@enc, @key) AS decrypted;
-- VALIDATE_PASSWORD_STRENGTH() depends on the server's configured
-- validate_password policy, and RANDOM_BYTES() is non-deterministic,
-- so neither has a fixed result to show here.
SELECT VALIDATE_PASSWORD_STRENGTH('P@ssw0rd!') AS strength;
SELECT HEX(RANDOM_BYTES(16)) AS random_token;
-- VALIDATE_PASSWORD_STRENGTH() depends on the server's configured
-- validate_password policy, and RANDOM_BYTES() is non-deterministic,
-- so neither has a fixed result to show here.
SELECT VALIDATE_PASSWORD_STRENGTH('P@ssw0rd!') AS strength;
SELECT HEX(RANDOM_BYTES(16)) AS random_token;