MySQL SHA2 Function: Hashing, MD5 and UUID

MySQL has functions for hashes, encryption and random identifiers. Knowing which to use and which to avoid is a security skill.

Hashes: a fingerprint of data

A hash turns any input into a fixed-length “fingerprint”. The same input always gives the same hash, a small change gives a very different one, and you cannot get the original back.

query.sql

SQL

SELECT SHA2('hello', 256)  AS sha256,
       SHA2('hello', 512) = SHA2('hello', 512) AS same_input_same_hash,
       SHA2('hellp', 256) = SHA2('hello', 256) AS one_letter_changed,
       CHAR_LENGTH(SHA2('anything', 256)) AS hex_length;

Output

OUTPUT

+------------------------------------------------------------------+----------------------+--------------------+------------+
| sha256                                                           | same_input_same_hash | one_letter_changed | hex_length |
+------------------------------------------------------------------+----------------------+--------------------+------------+
| 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824 |                    1 |                  0 |         64 |
+------------------------------------------------------------------+----------------------+--------------------+------------+
1 row in set

SHA2(text, 256) returns 64 hexadecimal characters. Good uses:

A checksum to check a file or a record was not changed.
A change detector: a hash of the important columns of a row, to see whether a row changed.
A deterministic key to deduplicate large values.

Store the raw 32 bytes as BINARY(32) to halve the size (BINARY Types): UNHEX(SHA2(...)).

MD5 and SHA1

query.sql

SQL

SELECT MD5('hello') AS md5_value, SHA1('hello') AS sha1_value;

Output

OUTPUT

+----------------------------------+------------------------------------------+
| md5_value                        | sha1_value                               |
+----------------------------------+------------------------------------------+
| 5d41402abc4b2a76b9719d911017c592 | aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d |
+----------------------------------+------------------------------------------+
1 row in set

(The output above is from MySQL 8.4. On the MySQL 9.7.1 server used to test this course, MD5(), SHA1() and SHA() do not exist and give error 1305, FUNCTION ... does not exist, while SHA2() works on every version. If you depend on them, check the release notes of the version you run.)

MD5 and SHA1 are broken for security: people can make two different inputs with the same hash. They are fine for non-security uses such as cache keys and quick change detection, but use SHA2 for new work.

Never hash passwords in SQL

query.sql

SQL

SELECT SHA2('password123', 256) AS do_not_store_this_as_a_password;

Output

OUTPUT

+------------------------------------------------------------------+
| do_not_store_this_as_a_password                                  |
+------------------------------------------------------------------+
| ef92b778bafe771e89245b89ecbc08a44a4e166c06659911881f383d4473e94f |
+------------------------------------------------------------------+
1 row in set

A plain SHA2 of a password is not safe. The hash is fast to compute, so attackers can test billions of guesses per second, and the same password always gives the same hash (so rainbow tables work). Passwords need a slow, salted password-hashing algorithm: bcrypt, Argon2 or scrypt. Your application should do it (in PHP: password_hash() and password_verify()), and the database stores the resulting text. MySQL’s own old PASSWORD() function was removed in MySQL 8.0.

query.sql

SQL

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL
);
INSERT INTO users (email, password_hash) VALUES ('asha@example.com', '$2y$10$abcdefghijklmnopqrstuuABCDEFGHIJKLMNOPQRSTUVWXYZ01234');
SELECT email, LEFT(password_hash, 7) AS hash_prefix, CHAR_LENGTH(password_hash) AS hash_length FROM users;

Output

OUTPUT

+------------------+-------------+-------------+
| email            | hash_prefix | hash_length |
+------------------+-------------+-------------+
| asha@example.com | $2y$10$     |          60 |
+------------------+-------------+-------------+
1 row in set

(The stored value here is a made-up string that only has the shape of a bcrypt hash.) A VARCHAR(255) leaves room for future algorithms with longer hashes.

Random identifiers: UUID

query.sql

SQL

SELECT CHAR_LENGTH(UUID()) AS uuid_length, UUID() <> UUID() AS each_call_differs;

Output

OUTPUT

+-------------+-------------------+
| uuid_length | each_call_differs |
+-------------+-------------------+
|          36 |                 1 |
+-------------+-------------------+
1 row in set

UUID() generates a version 1 UUID. See UUIDs as Keys for how to store them.

Encryption: AES_ENCRYPT

AES_ENCRYPT(data, key) encrypts, and AES_DECRYPT reverses it. The result is binary data.

query.sql

SQL

1
2
3

SET @key = SHA2('a-long-secret-from-your-secret-store', 256);
SET @secret = AES_ENCRYPT('card ending 4242', @key);
SELECT CHAR_LENGTH(@secret) > 0 AS encrypted, CAST(AES_DECRYPT(@secret, @key) AS CHAR) AS decrypted;

Output

OUTPUT

+-----------+------------------+
| encrypted | decrypted        |
+-----------+------------------+
|         1 | card ending 4242 |
+-----------+------------------+
1 row in set

Reasons to be careful:

The key travels inside the SQL statement, so it can appear in the general log, the slow query log, performance_schema and the process list. Anyone who can read those can read your key.
The default mode aes-128-ecb leaks patterns (the same input gives the same output). Set block_encryption_mode to a mode such as aes-256-cbc and supply an initialisation vector.
Key management (where the key lives, rotation) is a bigger problem than the function call.

For most applications, it is better to encrypt in the application with a well-reviewed library and keep the key in a secrets manager, or to use InnoDB tablespace encryption (data at rest) with a keyring. Encrypting sensitive columns inside SQL is the last choice.

Summary

Need	Use	Avoid
Checksum, change detection	`SHA2(x, 256)`	`MD5`/`SHA1` for security
Password storage	`password_hash()` in the application (bcrypt or Argon2)	Any SQL hash, `PASSWORD()`
Unique identifier	`UUID()` + `UUID_TO_BIN()`, or application UUIDs
Encrypt a column	Application-side encryption, or tablespace encryption	`AES_ENCRYPT` with a key in the SQL text
Random tokens	A secure generator in the application	`RAND()`

dangerous

Treat SQL text as visible

Logs, monitoring tools and error messages can show SQL statements. Never put passwords, keys or secrets in a statement that is logged.