| Bug #9621 | mysqldump fails to backup AES_ENCRYPT fields properly | ||
|---|---|---|---|
| Submitted: | 4 Apr 2005 19:26 | Modified: | 6 Apr 2005 4:24 |
| Reporter: | David Pesta | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: mysqldump Command-line Client | Severity: | S2 (Serious) |
| Version: | 4.1.10a | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[6 Apr 2005 4:24]
Jorge del Conde
I was unable to reproduce this behaviour using 4.1.11 from bk:
mysql> create table passtest (pass blob );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into passtest (pass) values (AES_ENCRYPT("password123", "keystring19582"));
Query OK, 1 row affected (0.00 sec)
mysql> select aes_decrypt(pass, "keystring19582") from passtest;
+-------------------------------------+
| aes_decrypt(pass, "keystring19582") |
+-------------------------------------+
| password123 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
jorge-/home/jorge> mysqldump test passtest > tmp.sql
jorge-/home/jorge> mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.11
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop table passtest;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
jorge-/home/jorge> mysql test < tmp.sql
jorge-/home/jorge> mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.11
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select aes_decrypt(pass, "keystring19582") from passtest;
+-------------------------------------+
| aes_decrypt(pass, "keystring19582") |
+-------------------------------------+
| password123 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql>
[7 Apr 2005 11:48]
David Pesta
I noticed that Jorge del Conde's attempt to repeat the bug was creating the table using type "blob" as pass column. My apologies for not taking that into consideration when writing the "How to repeat" section. My difficulty arose when using type "text". I am guessing that the problem has something to do with the conflict between binary and plain text as only some of the characters had a problem. In fact, the documentation does recommend using type "blob" for columns that receive data from encryption functions. Perhaps a MySQL warning or error should be thrown when someone attempts to place data from AES_ENCRYPT into any of the text fields.
Description: When I encryped sensitive data using AES_ENCRYPT and then performed a routine mysqldump for backup purposes, the resulting dump file did not preserve many of the encrypted characters properly. This results in a retrieval failure when restoring the damaged encrypted data. How to repeat: From within the ORIGINAL database: mysql> insert into passtest (pass) values (AES_ENCRYPT("password123", "keystring19582")); mysql> select * from passtest; +------------------+ | pass | +------------------+ | ÈSiàyvw%§Ó M | +------------------+ 1 row in set (0.00 sec) Then I perform a table dump and look inside the DUMP FILE: /*!40000 ALTER TABLE `passtest` DISABLE KEYS */; LOCK TABLES `passtest` WRITE; INSERT INTO `passtest` VALUES ('ÈSià yvw%§Ó„M…'); UNLOCK TABLES; I tried importing this DUMP FILE into a NEW mysql database and then performed a "select * from passtest" on it in the NEW database, which gave: ÈSià yvw%§Ó„M… This string is identical to that within the DUMP FILE and *not* the ORIGINAL database. Furthermore, as expected, when using AES_DECRYPT to recover the original string in the NEW database, I get no response (NULL?). Suggested fix: It appears that some of the characters are preserved but others are not during the mysqldump process.