Hướng dẫn aes_decrypt mysql returns null - aes_decrypt mysql trả về null

Tôi đã tìm thấy những câu hỏi tương tự, nhưng không có câu trả lời rõ ràng cho câu hỏi này.Tôi có bảng này:

CREATE DATABASE testDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

CREATE TABLE testTable
(
firstName binary(32) not null,
lastName binary(32) not null
/* Other non-binary fields omitted */
)
engine=INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Tuyên bố này thực thi chỉ tốt:

INSERT INTO testTable (firstName) VALUES (AES_ENCRYPT('Testname', 'test'));

Nhưng, điều này trả về NULL:

SELECT AES_DECRYPT(firstName, 'test') FROM testTable;

Tại sao điều này trở lại null?

FWIW, điều này trả về "testValue" như mong đợi:

SELECT AES_DECRYPT(AES_ENCRYPT('testValue','thekey'), 'thekey');

[2 tháng 9 năm 2005 10:13] Valeriy Kravchuk

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.

[2 tháng 9 năm 2005 14:24] Valeriy Kravchuk

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

[2 tháng 9 năm 2005 15:24] Martins Brivnieks

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

[4 tháng 9 năm 2005 15:14] Hartmut Holzgraefe

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 ...?

[5 tháng 9 năm 2005 7:23] Valeriy Kravchuk

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.

[6 tháng 9 năm 2005 14:38] Valeriy Kravchuk

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 h2,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  | h2
 | 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 h2,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  | h2
 | 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)

[9 tháng 9 năm 2005 20:01] [Tên bị giữ lại]

INSERT INTO testTable (firstName) VALUES (AES_ENCRYPT('Testname', 'test'));

[14 tháng 9 năm 2005 23:10] Jim Winstead

INSERT INTO testTable (firstName) VALUES (AES_ENCRYPT('Testname', 'test'));

[15 tháng 9 năm 2005 6:49] Martins Brivnieks

INSERT INTO testTable (firstName) VALUES (AES_ENCRYPT('Testname', 'test'));