Hashing, UUIDs, Server Info

Hashing, Encryption, Compression, UUIDs, and Server Information

The list has shrunk. PASSWORD() went in MySQL 8.0.11 524 , and ENCRYPT(), ENCODE(), DECODE(), DES_ENCRYPT() and DES_DECRYPT() in 8.0.3. MD5() and SHA1() (with its alias SHA()) were deprecated in 9.4.0 and moved out of the server in 9.6.0, so 9.7 LTS has only SHA2() built in; an administrator can restore the old two with INSTALL COMPONENT 'file://component_classic_hashing'. AES_ENCRYPT() defaults to aes-128-ecb; set block_encryption_mode to CBC and pass a random IV.

SHA-2, AES-256-CBC, compression, and what the server says about the sessionSQL

SELECT MD5('ana@example.com');
SET block_encryption_mode = 'aes-256-cbc';
SET @key = UNHEX(SHA2('passphrase kept outside the database', 256)), @iv = RANDOM_BYTES(16);
SET @ct = AES_ENCRYPT('4111 1111 1111 1111', @key, @iv);
SELECT LEFT(SHA2('ana@example.com', 256), 16) AS sha256_start, LENGTH(@ct) AS ct_bytes,
       CAST(AES_DECRYPT(@ct, @key, @iv) AS CHAR) AS plain,
       AES_DECRYPT(@ct, UNHEX(SHA2('wrong', 256)), @iv) AS wrong_key,
       LENGTH(COMPRESS(REPEAT('shop ', 1000))) AS packed_5000;
INSERT INTO customers (email, name, country) VALUES ('ivy@example.com', 'Ivy Tan', 'SG');
SELECT LAST_INSERT_ID() AS new_id, VERSION() AS version, DATABASE() AS db,
       CURRENT_USER() AS me, UUID() AS uuid;

Output

ERROR 1305 (42000): FUNCTION shop.MD5 does not exist
+------------------+----------+---------------------+-----------+-------------+
| sha256_start     | ct_bytes | plain               | wrong_key | packed_5000 |
+------------------+----------+---------------------+-----------+-------------+
| 8e43ca37701228e7 |       32 | 4111 1111 1111 1111 | NULL      |          42 |
+------------------+----------+---------------------+-----------+-------------+
+--------+---------+---------+----------------+--------------------------------------+
| new_id | version | db      | me             | uuid                                 |
+--------+---------+---------+----------------+--------------------------------------+
|      9 | 9.7.2   | shop    | root@localhost | 3181c3fc-b724-11f1-98d6-00155d5f9c3f |
+--------+---------+---------+----------------+--------------------------------------+

A wrong key usually gives NULL, but not always. SQL-side encryption sends key and plaintext to the server, where logs and SHOW PROCESSLIST can expose them: encrypt in PHP with Sodium (Encryption), and hash passwords with password_hash() (Password Hashing). UUID() is version 1, time-based; store it as BINARY(16) via UUID_TO_BIN(u, 1), whose swap flag puts the time bits first so keys insert in index order. LAST_INSERT_ID() is per connection, so concurrent inserts never see each other's ids.

MENU

Hashing, UUIDs, Server Info