MySQL Bugs: #15495: des_encrypt, des_decrypt failing on some combinations

Bug #15495	des_encrypt, des_decrypt failing on some combinations
Submitted:	5 Dec 2005 16:20	Modified:	8 Aug 2006 11:25
Reporter:	Matthew Studley	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	Ver 14.7 Distrib 4.1.13a	OS:	sun-solaris2.9 (sparc)
Assigned to:		CPU Architecture:	Any

Description:

There appears to be a problem with either the des_encrypt or des_decrypt function.  When certain strings are encrypted with certain passwords (strings, not from a key file) the retrieved results appear as they do when not passing the function.  

The password WILL encrypt/decrypt certain strings properly and those it can't, when given a different password it works fine.  This is very strange.

How to repeat:

I cannot supply the password as I cannot reproduce this using anything other than our set des_key.  It contains numbers and letters but no special charaters.

update pending set someField = des_encrypt('3441', 'XXXXXXXX') where ID = '123456789';
Query OK, 0 rows affected (0.68 sec)
Rows matched: 1  Changed: 0  Warnings: 0

select des_decrypt(someField, 'XXXXXXXX') from pending where ID = '324473294';
+--------------------------------+
| des_decrypt(someField, 'XXXXXXXX') |
+--------------------------------+
| ÿ´~ô¦)hÒ                       |
+--------------------------------+
1 row in set (0.30 sec)

Suggested fix:
???

Thank you for a problem report. Can you, please, try to download and install newer version of MySQL (http://dev.mysql.com/downloads/mysql/4.1.html)? Your 4.1.13a is really old. Please, try to repeat the problem with 4.1.15 and inform about the results.

user error.  I was using a VARCHAR column and apparently there were times that the entry was being truncated due to it containing trailing spaces (against ANSI SQL specifications).  Changing the column type to TINYBLOB fixed the problem.

It might be wise to update documentation to specify the use of a BLOB type column when using the des_encrypt/decrypt functions.  Not sure if specifying the BINARY keyword for a VARCHAR type column would have done the trick.

I use the DES_ENCRYPT and DES_DECRYPT with the string 'sfG3853ncr1pt' 
I can't seem to encrypt the number 227968199 because I tried several 
times and also tried other numbers which work fine. 

Here is a sample session: 

mysql> create table `destest` (num varchar(17)); 
Query OK, 0 rows affected (0.04 sec) 

mysql> desc destest; 
+-------+-------------+------+-----+---------+-------+ 
| Field | Type        | Null | Key | Default | Extra | 
+-------+-------------+------+-----+---------+-------+ 
| num   | varchar(17) | YES  |     | NULL    |       | 
+-------+-------------+------+-----+---------+-------+ 
1 row in set (0.00 sec) 

mysql> insert into destest(num) values(DES_ENCRYPT('227968199', 
'sfG3853ncr1pt')); 
Query OK, 1 row affected (0.00 sec) 

mysql> select * from destest; 
+------------------+ 
| num              | 
+------------------+ 
| ÿÿÔ¨ð!?`f4~±ëk† | 
+------------------+ 
1 row in set (0.00 sec) 

mysql> select DES_DECRYPT(num, 'sfG3853ncr1pt') from destest; 
+-----------------------------------+ 
| DES_DECRYPT(num, 'sfG3853ncr1pt') | 
+-----------------------------------+ 
| ÿÿÔ¨ð!?`f4~±ëk†                  | 
+-----------------------------------+ 
1 row in set (0.00 sec) 

mysql> insert into destest(num) values(DES_ENCRYPT('12345678', 
'sfG3853ncr1pt')); 
Query OK, 1 row affected (0.00 sec) 

mysql> select DES_DECRYPT(num, 'sfG3853ncr1pt') from destest; 

+-----------------------------------+ 
| DES_DECRYPT(num, 'sfG3853ncr1pt') | 
+-----------------------------------+ 
| ÿÿÔ¨ð!?`f4~±ëk†                  | 
| 12345678                          | 
+-----------------------------------+ 
2 rows in set (0.00 sec) 

  I have also tried 

FLUSH DES_KEY_FILE; 

It is so weird!

Please see my previous post.  You are using a VARCHAR type field and that doesn't appear to work with these functions.