MySQL Bugs: #10915: AES_Decrypt() returns NULL values

Bug #10915	AES_Decrypt() returns NULL values
Submitted:	27 May 2005 13:08	Modified:	14 Sep 2005 23:10
Reporter:	Martins Brivnieks	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	mysql-standard-4.1.7; 4.1.14-nt	OS:	pc-linux-i686-icc-glibc23, Windows
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
AES_Decrypt() returns NULL values
when i decrypt packets crypted with binary key,
which i can't change because that is protocol of an other program

AES_Decrypt() allways can decrypt first 16 of package but next hardly ever

i have fixed it for me
with adding crypted regular 'texttexttext' with same Key at the end of received crypted string
and after decode i replace(decodedSTR,'texttexttext','')

How to repeat:
http://www.SuperFM.lv/m/MySQL/AES_Decrypt_with_not_NULL.JPG
http://www.SuperFM.lv/m/MySQL/AES_Decrypt_with_not_NULL.sql
http://www.SuperFM.lv/m/MySQL/AES_Decrypt_with_not_NULL.txt

Suggested fix:
maybe is posible add optional parameter for check padding On/Off

AES_Decrypt()      #On
AES_Decrypt()+0  #Off

Thank you for your bug report. I tried a simplified test on latest 4.1.14:

mysql> Set @key='F696282AA4CD4F614AA995190CF442FE';
Query OK, 0 rows affected (0.00 sec)

mysql> select AES_Decrypt(AES_Encrypt('abcdefghijklmnopqrstuvwxwz1234567890', Un
Hex(@key)), UnHex(@key));
+-------------------------------------------------------------------------------
-------------+
| AES_Decrypt(AES_Encrypt('abcdefghijklmnopqrstuvwxwz1234567890', UnHex(@key)),
UnHex(@key)) |
+-------------------------------------------------------------------------------
-------------+
| abcdefghijklmnopqrstuvwxwz1234567890
             |
+-------------------------------------------------------------------------------
-------------+
1 row in set (0.00 sec)

mysql> select AES_Decrypt(AES_Encrypt('abcdefghijklmnopqrstuvwxwz123456789012345
67890123456789012345678901234567890', UnHex(@key)), UnHex(@key));
+-------------------------------------------------------------------------------
-----------------------------------------------------+
| AES_Decrypt(AES_Encrypt('abcdefghijklmnopqrstuvwxwz123456789012345678901234567
89012345678901234567890', UnHex(@key)), UnHex(@key)) |
+-------------------------------------------------------------------------------
-----------------------------------------------------+
| abcdefghijklmnopqrstuvwxwz12345678901234567890123456789012345678901234567890
                                                     |
+-------------------------------------------------------------------------------
-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.03 sec)

So, looks like data decrypted correctly for me, for lenght > 16 too.

Please, check in on newer version of MySQL and give a (simple) repeatable test case if the problem still exists.

i have tryed with 4.1.13a - same result :(
http://www.superfm.lv/m/MySQL/dump.frm
http://www.superfm.lv/m/MySQL/dump.MYD
http://www.superfm.lv/m/MySQL/dump.MYI

udachi

OK, i'll try with your new files. But have you tried my simple select on 4.1.13? What are the results?

yes, simple examples work
but my data in table is binary data crypted with binary key

Can you please add queries showing the eratic behavior with the files you provided?
I think it will be hard to do anything without knowing the key used ...?

from first post :)
http://www.superfm.lv/m/MySQL/AES_Decrypt_with_not_NULL.sql

Please, try the following test, based on my previous one:

mysql> Set @key='F696282AA4CD4F614AA995190CF442FE';
Query OK, 0 rows affected (0.03 sec)

mysql> Set @val='F696282AA4CD4F614AA995190CF442FE01010101010101010ABCDEF';
Query OK, 0 rows affected (0.00 sec)

mysql> select length(@val);
+--------------+
| length(@val) |
+--------------+
|           55 |
+--------------+
1 row in set (0.00 sec)

So, the value is longer that 16 bytes.

mysql> select Hex(AES_Decrypt(AES_Encrypt(UnHex(@val), UnHex(@key)), UnHex(@key)));
+----------------------------------------------------------------------+
| Hex(AES_Decrypt(AES_Encrypt(UnHex(@val), UnHex(@key)), UnHex(@key))) |
+----------------------------------------------------------------------+
| 0F696282AA4CD4F614AA995190CF442FE01010101010101010ABCDEF             |
+----------------------------------------------------------------------+
1 row in set (0.01 sec)

So, I've got the same result I encrypted (note the leading zero - I used 55 hex digits, but each byte is rtepresented by 2 hex digits).

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.12-beta-nt |
+----------------+
1 row in set (0.00 sec)

Looks like it works as expected (on 5.0.12). Try it on your version, please.

youre example works allso on 4.1.7-standard

have you tryed import table in your db:
http://www.superfm.lv/m/MySQL/dump.frm
http://www.superfm.lv/m/MySQL/dump.MYD
http://www.superfm.lv/m/MySQL/dump.MYI
and run:
http://www.superfm.lv/m/MySQL/AES_Decrypt_with_not_NULL.sql

I was able to repeat the bug with the dump table mentioned in the last note. I stopped the server, copied the files to my data/test directory, started the server and, in the test database I performed:

mysql> Set @key='F696282AA4CD4F614AA995190CF442FE';
Query OK, 0 rows affected (0.00 sec)

mysql> Set @MyCrStr=AES_Encrypt('texttexttext',UnHex(@key));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT packet_binary,length(mid(packet_binary,5))/16 as LEN,
    -> hex(AES_Decrypt(mid(packet_binary,5,16),UnHex(@key))) as h1,hex(AES_Decrypt(mid(packet_binary,5,32),UnHex(@key))) as h2,hex(AES_Decrypt(mid(packet_binary,5,48),UnHex(@key))) as h3
    -> ,hex(AES_Decrypt(mid(packet_binary,5,64),UnHex(@key))) as h4,hex(AES_Decrypt(mid(packet_binary,5,80),UnHex(@key))) as h5,hex(AES_Decrypt(mid(packet_binary,5,96),UnHex(@key))) as h6
    -> ,hex(AES_Decrypt(mid(packet_binary,5,112),UnHex(@key))) as h7#,hex(AES_Decrypt(mid(packet_binary,5,128),UnHex(@key))) as h8,hex(AES_Decrypt(mid(packet_binary,5,144),UnHex(@key))) as h9
    -> ,hex(Replace(AES_Decrypt(Concat(mid(packet_binary,5),@MyCrStr),UnHex(@key
)),'text','')) as DecryptedStr
    -> FROM `dump` T
    -> LIMIT 1;
+-------------------------------------------------------------------------------
---------------------------------------+------+---------------------------------
-+------+------+------+------+------+------+------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------+
| packet_binary
                                       | LEN  | h1
 | h2   | h3   | h4   | h5   | h6   | h7   | DecryptedStr

                              |
+-------------------------------------------------------------------------------
---------------------------------------+------+---------------------------------
-+------+------+------+------+------+------+------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------+
R
       | 7.00 | 00540067158F74EE00A5170200000000 | NULL | NULL | NULL | NULL | NULL | NULL | 00540067158F74EE00A517020000000050A5DBB0817051C90000000110100048130
74505018600889AB5C08EEECE77CFEFC6028A2CF404F5B7466A4DF19D1D224E7BE6F970F53A08D97
21A558C7CFF70170C0C84A14DD3F28B4822B4E843CE286F14E1871AB4348BFFFFFFFFFFFFFFFF |
+-------------------------------------------------------------------------------
---------------------------------------+------+---------------------------------
-+------+------+------+------+------+------+------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------+
1 row in set (0.00 sec)

mysql> SELECT packet_binary,length(mid(packet_binary,5))/16 as LEN,
    -> hex(AES_Decrypt(mid(packet_binary,5,16),UnHex(@key))) as h1,hex(AES_Decry
pt(mid(packet_binary,5,32),UnHex(@key))) as h2,hex(AES_Decrypt(mid(packet_binary
,5,48),UnHex(@key))) as h3
    -> ,hex(AES_Decrypt(mid(packet_binary,5,64),UnHex(@key))) as h4,hex(AES_Decr
ypt(mid(packet_binary,5,80),UnHex(@key))) as h5,hex(AES_Decrypt(mid(packet_binar
y,5,96),UnHex(@key))) as h6
    -> ,hex(AES_Decrypt(mid(packet_binary,5,112),UnHex(@key))) as h7#,hex(AES_De
crypt(mid(packet_binary,5,128),UnHex(@key))) as h8,hex(AES_Decrypt(mid(packet_bi
nary,5,144),UnHex(@key))) as h9
    -> ,hex(Replace(AES_Decrypt(Concat(mid(packet_binary,5),@MyCrStr),UnHex(@key
)),'text','')) as DecryptedStr
    -> FROM `dump` T
    -> LIMIT 1;
+-------------------------------------------------------------------------------
---------------------------------------+------+---------------------------------
-+------+------+------+------+------+------+------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------+
| packet_binary
                                       | LEN  | h1
 | h2   | h3   | h4   | h5   | h6   | h7   | DecryptedStr

                              |
+-------------------------------------------------------------------------------
---------------------------------------+------+---------------------------------
-+------+------+------+------+------+------+------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------+
R
       | 7.00 | 00540067158F74EE00A5170200000000 | NULL | NULL | NULL | NULL | N
ULL | NULL | 00540067158F74EE00A517020000000050A5DBB0817051C90000000110100048130
74505018600889AB5C08EEECE77CFEFC6028A2CF404F5B7466A4DF19D1D224E7BE6F970F53A08D97
21A558C7CFF70170C0C84A14DD3F28B4822B4E843CE286F14E1871AB4348BFFFFFFFFFFFFFFFF |
+-------------------------------------------------------------------------------
---------------------------------------+------+---------------------------------
-+------+------+------+------+------+------+------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------+
1 row in set (0.00 sec)

Hi is there any update on this bug?. Is it affecting any other version of mysql other than 4.1.7 and 4.1.14-nt? I am having the same problem with 4.1.7, I just need to know if I can get around this problem by switching to newer version and if yes, which version.

Thanks

The problem is the way that MySQL's AES_ENCRYPT() and AES_DECRYPT() handle the padding of the data to create a complete blocksize. AES_DECRYPT() extracts the length of the padding from the last bytes of the decoded data. Because the example query is attempting to pass individual blocks to AES_DECRYPT(), those blocks which have a last byte with a value greater than 16 are seen as invalid data, so NULL is returned.

MySQL's AES_DECRYPT() is not able to handle the data encrypted by the external program because it is not using a similar method to encode the padding in the final block.

RE: MySQL's AES_DECRYPT() is using diferent method to encode the padding in the
final block.

that is an other problem:
to mede correct outgoing data i have to:
1. before AES_DECRYPT add at the end of data some fake data
Concat(Data,UnHex(Repeat('F',30)))
2. AES_DECRYPT(data+fake data)
3. cut last 16 of crypted string
Left(CryptedData,Length(CryptedData)-16)

onlly then i can get CryptedData which can be encrypted by another programm