MySQL Bugs: #74394: AES_ENCRYPT() FAILING ON TABLE'S COLUMNS AFTER LOAD DATA IN FILE PROC

Bug #74394	AES_ENCRYPT() FAILING ON TABLE'S COLUMNS AFTER LOAD DATA IN FILE PROC
Submitted:	15 Oct 2014 9:07	Modified:	27 May 2015 7:54
Reporter:	carlo tollo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Encryption	Severity:	S2 (Serious)
Version:	5.6.21	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	AES_DECRYPT(), AES_ENCRYPT(), LOAD DATA IN FILE

Description:
AES_ENCRYPT() Failing on table's columns filled by LOAD DATA IN FILE procedure. 
The resulting error after every UPDATE command with AES_ENCRYPT function inside is :
err code 1366 incorrect string value

How to repeat:
Create a table,
FILL this table by LOAD DATA IN FILE statement,
and then try to encrypt a column by AES_ENCRYPT() Func

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Can you please add to the bug the SQL commands you've used, (some version) of the file you are trying to read from, some details about your environment (OS, location of the server etc) and relevant configuration data.

I've tried the sequence below, but I couldn't get the effect : 

mysql> select @@version;
--------------
select @@version
--------------

+------------------+
| @@version        |
+------------------+
| 5.6.23-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> create table t1 (a int, b varchar(200));
--------------
create table t1 (a int, b varchar(200))
--------------

Query OK, 0 rows affected (0.01 sec)

mysql> load data infile 'D:\\ade\\work\\mysql-5.6\\bld\\mysql-test\\var\\test1.t
xt' INTO TABLE t1 fields terminated by ',';
--------------
load data infile 'D:\\ade\\work\\mysql-5.6\\bld\\mysql-test\\var\\test1.txt' INT
O TABLE t1 fields terminated by ','
--------------

Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select aes_encrypt(b, UNHEX('F3229A0B371ED2D9441B830D21A390C3')) from t1;

--------------
select aes_encrypt(b, UNHEX('F3229A0B371ED2D9441B830D21A390C3')) from t1
--------------

+-----------------------------------------------------------+
| aes_encrypt(b, UNHEX('F3229A0B371ED2D9441B830D21A390C3')) |
+-----------------------------------------------------------+
| в╪є∙M▐Г┌▀'tрф                                          |
| AтПЙєЯф╖Ш,xЧ◄yКЁ                                          |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
--------------
select * from t1
--------------

+------+-------+
| a    | b     |
+------+-------+
 |   1 | row1
 |   2 | row2
+------+-------+
2 rows in set (0.00 sec)

Maybe I did not mention that we are using MYISAM db, 
did you try it with a MyISAM db ?

Hi,   the result of AES_ENCRYPT is a binary string value.  Therefore it should be stored in a BLOB, LONGBLOB or BINARY type of column.  Using utf8 or latin1 varchar to store the result will not work as expected.