MySQL Bugs: #119796: Encrypted data changes when adding columns

Bug #119796	Encrypted data changes when adding columns
Submitted:	27 Jan 19:12	Modified:	29 Jan 4:32
Reporter:	Darryl Hannah	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	8.0.35	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Unrelated modifications to the database are causing AES_ENCRYPTed columns to change.

Data created like so:

```
SET @salt = RANDOM_BYTES(8), @passphrase = 'Bright stars shine at night';
INSERT INTO decrypterror (`security_token`, `password`, `encrypt_salt` )
VALUES 
(AES_ENCRYPT('test token', @passphrase, '', 'hkdf', @salt, ''), AES_ENCRYPT('test password', @passphrase, '', 'hkdf', @salt, ''), @salt),
(AES_ENCRYPT('test token 2 ', @passphrase, '', 'hkdf', @salt, ''), AES_ENCRYPT('test password 2', @passphrase, '', 'hkdf', @salt, ''), @salt);
```

And retrieved like so

```
SELECT  
	AES_DECRYPT(`password`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_password,
	AES_DECRYPT(`security_token`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_security_token 
FROM decrypterror;
```

Suddenly changed from returning data to returning NULL.

Possible ways to trigger it:

-- ALTER TABLE to add a DATETIME column (replicated in the "How to repeat section")
-- Server update / restart.  We noticed this when our RDS DB upgraded from 8.0.35 to 8.0.42, but didn't replicate this.

Further weirdness:

On our actual database which I can't share, adding a `SELECT *` to the query caused it to return data again:

```
SELECT *,  
	AES_DECRYPT(`password`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_password,
	AES_DECRYPT(`security_token`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_security_token 
FROM decrypterror;
```

I was not able to replicate that case.

I suspect it might have something to do with the length of the passphrase/key?

How to repeat:
CREATE TABLE `decrypterror` (
  `id` int NOT NULL AUTO_INCREMENT,
  `security_token` varbinary(128) NOT NULL,
  `password` varbinary(128) NOT NULL,
  `encrypt_salt` binary(8) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

SET SESSION block_encryption_mode = 'aes-128-ecb';

SET @salt = RANDOM_BYTES(8), @passphrase = 'Bright stars shine at night';
INSERT INTO decrypterror (`security_token`, `password`, `encrypt_salt` )
VALUES 
(AES_ENCRYPT('test token', @passphrase, '', 'hkdf', @salt, ''), AES_ENCRYPT('test password', @passphrase, '', 'hkdf', @salt, ''), @salt),
(AES_ENCRYPT('test token 2 ', @passphrase, '', 'hkdf', @salt, ''), AES_ENCRYPT('test password 2', @passphrase, '', 'hkdf', @salt, ''), @salt);

-- Decryption success
SET @passphrase = 'Bright stars shine at night';
SELECT  
	AES_DECRYPT(`password`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_password,
	AES_DECRYPT(`security_token`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_security_token 
FROM decrypterror;

ALTER TABLE `decrypterror`
ADD COLUMN `created_at` datetime NULL DEFAULT CURRENT_TIMESTAMP;

-- Returns null
SET @passphrase = 'Bright stars shine at night';
SELECT  
	AES_DECRYPT(`password`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_password,
	AES_DECRYPT(`security_token`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_security_token 
FROM decrypterror;

ALTER TABLE `decrypterror`
DROP COLUMN `created_at`;

-- Returns data again
SET @passphrase = 'Bright stars shine at night';
SELECT  
	AES_DECRYPT(`password`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_password,
	AES_DECRYPT(`security_token`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_security_token 
FROM decrypterror;

DDL problem?

I could repeat the issue on 8.0.35.

mysql> SELECT
    -> AES_DECRYPT(`password`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_password,
    -> AES_DECRYPT(`security_token`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_security_token
    -> FROM decrypterror;
+------------------+------------------------+
| decrypt_password | decrypt_security_token |
+------------------+------------------------+
| test password    | test token             |
| test password 2  | test token 2           |
+------------------+------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> ALTER TABLE `decrypterror`
    -> ADD COLUMN `created_at` datetime NULL DEFAULT CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> SET @passphrase = 'Bright stars shine at night';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    -> AES_DECRYPT(`password`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_password,
    -> AES_DECRYPT(`security_token`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_security_token
    -> FROM decrypterror;
+------------------+------------------------+
| decrypt_password | decrypt_security_token |
+------------------+------------------------+
| NULL             | NULL                   |
| NULL             | NULL                   |
+------------------+------------------------+
2 rows in set (0.01 sec)

mysql>
mysql> ALTER TABLE `decrypterror`
    -> DROP COLUMN `created_at`;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> SET @passphrase = 'Bright stars shine at night';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    -> AES_DECRYPT(`password`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_password,
    -> AES_DECRYPT(`security_token`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_security_token
    -> FROM decrypterror;
+------------------+------------------------+
| decrypt_password | decrypt_security_token |
+------------------+------------------------+
| test password    | test token             |
| test password 2  | test token 2           |
+------------------+------------------------+
2 rows in set (0.00 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 8.0.35-commercial |
+-------------------+
1 row in set (0.01 sec)

However, 8.0.45 works well, so please upgrade.

mysql> SELECT
    -> AES_DECRYPT(`password`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_password,
    -> AES_DECRYPT(`security_token`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_security_token
    -> FROM decrypterror;
+------------------+------------------------+
| decrypt_password | decrypt_security_token |
+------------------+------------------------+
| test password    | test token             |
| test password 2  | test token 2           |
+------------------+------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> ALTER TABLE `decrypterror`
    -> ADD COLUMN `created_at` datetime NULL DEFAULT CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SET @passphrase = 'Bright stars shine at night';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    -> AES_DECRYPT(`password`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_password,
    -> AES_DECRYPT(`security_token`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_security_token
-- Returns data again
SET @passphrase = 'Bright stars shine at night';
SELECT
        AES_DECRYPT(`password`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_password,
        AES_DECRYPT(`security_token`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_security_token
FROM decrypterror;    -> FROM decrypterror;
+------------------+------------------------+
| decrypt_password | decrypt_security_token |
+------------------+------------------------+
| test password    | test token             |
| test password 2  | test token 2           |
+------------------+------------------------+
2 rows in set (0.01 sec)

mysql>
mysql> ALTER TABLE `decrypterror`
    -> DROP COLUMN `created_at`;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> -- Returns data again
Query OK, 0 rows affected (0.00 sec)

mysql> SET @passphrase = 'Bright stars shine at night';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    -> AES_DECRYPT(`password`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_password,
    -> AES_DECRYPT(`security_token`, @passphrase, '', 'hkdf', `encrypt_salt`) as decrypt_security_token
    -> FROM decrypterror;
+------------------+------------------------+
| decrypt_password | decrypt_security_token |
+------------------+------------------------+
| test password    | test token             |
| test password 2  | test token 2           |
+------------------+------------------------+
2 rows in set (0.00 sec)

mysql> select version();
+-------------+
| version()   |
+-------------+
| 8.0.45-asan |
+-------------+
1 row in set (0.00 sec)

This has been fixed since 8.0.40.

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-40.html