MySQL Bugs: #49249: The result from des_encrypt is different from Java DESede Algorithm

Bug #49249	The result from des_encrypt is different from Java DESede Algorithm
Submitted:	1 Dec 2009 5:25	Modified:	1 Dec 2009 10:41
Reporter:	wei zhang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:		OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When I use the mysql des_encrypt to encrypt a string, I will get a result different from Java DESede Algorithm.

I am confused. I think two result shall be same.

I use following sql:(The charset is utf-8)
des_encrypt('111','123456789123456789123456') and I will get
byte[] = {-1,27,-34,9,95,-55,-19,95,85}

And I use following java code
SecretKey deskey = new SecretKeySpec("123456789123456789123456"
				.getBytes(), "DESede");
Cipher c1 = Cipher.getInstance("DESede");
c1.init(Cipher.ENCRYPT_MODE, deskey);
byte[] data = c1.doFinal("111".getBytes());
and I will get 
byte[] = {96, 36, 82, 24, 110, -11, -5, -97}

Even if the first byte {-1} means char 127. But the remaining bytes are still different.

My application need insert some encrypted data into database, and I need use des_decrypt to order these data. So I must confirm the DESede Algorithm in java and mysql shall be same.

How to repeat:
You can reproduce this issue by the codes which I provided

I forget to say the mysql version is mysql-essential-5.4.3-beta-win32.msi

Hi Wei and thanks for your report.

I would like you to attach complete Java test case together with connection string so I can test this. The expected behavior might depend on various connection-string options.

Also, there is no DES-related code in c/J afais and this will probably boil down to differences, if any, in DES algorithm implementation.

TestDse

Attachment: TestDes.java (text/plain), 1.27 KiB.

Hello Grgin,

The following code is for mysql table and data creation. You can run the TestDes after creating table and inserting data.

create table test(
name blob
);
insert into test(name) values(des_encrypt('111','123456789123456789123456'));

Wei, the more I look into this the more I'm convinced it should *not* work as you expect it to...

Checking DES code in server one can find this:

Function des_encrypt() by tonu@spam.ee & monty
Works only if compiled with OpenSSL library support.
@return    A binary string where first character is CHAR(128 | key-number).
If one uses a string key key_number is 127. (<<< Thus your 127)
Encryption result is longer than original by formula: @code new_length= org_length + (8-(org_length % 8))+1

Further more, you can see const char *append_str="********"; and
  /*
     The problem: DES algorithm requires original data to be in 8-bytes
     chunks. Missing bytes get filled with '*'s and result of encryption
     can be up to 8 bytes longer than original string. When decrypted,
     we do not know the size of original string :(
     We add one byte with value 0x1..0x8 as the last byte of the padded
     string marking change of string length.
  */

This, in effect, means that des works with data aligned to 8 bytes. So MySQL
implementation fills short strings by '*'.

Then, in code, you will find:
/* We make good 24-byte (168 bit) key from given plaintext key with MD5 */

This means we use md5 hash as password (instead of plaintext key, which provided as argument of des_encrypt)

This all means our implementation of 3des is actually for internal using only and it's hard to believe it's compatible with other implementations.

So, what do you want us to do here? Our DES is for internal use only while Java DES is defined in JRE we can not affect.

Do you mean I must use MD5 to hash my key and then using that key to encrypt my data in java code?

But I find after using MD5 to hash my key, I will get a 40 byte length key. But the DES can just use 24 length as the key in JAVA.

Is there any other solytion to resolve this issue? Or would you please provide me the des_encrypt source code in mysql and then I can use JAVA to implement it with same algorithm.

Wei, I don't get it. What am I to give you, we are open source... Or you want me to look for exact place for you?

Ah well, it's in item_strfunc.cc, around line 450. String *Item_func_des_encrypt::val_str(String *str)

As for length, I have already given you the explanation, see my previous post:
Encryption result is longer than original by formula: @code new_length=
org_length + (8-(org_length % 8))+1

There is no mention of DES functionality implementation in MySQL being suitable for use with any particular code and/or framework/API in manual so I'm closing this one as !Bg.