Encryption with UTF8 charset issue

[j***@yahoo.com]

HI faculties,
I have a table tbl_users that has charset set to UTF8. The
password column in this table, should store passwords in encrypted
form. For doing this, I am using the AES_ENCRYPT() function. To my
surprise, when I tried the following statement, no data got inserted
for the password column.

These are the statements:

create table tbl_users
(
id bigint(10),
login varchar(10),
password char(10)
) default charset = utf8;

insert into tbl_users values(1, 'dan', aes_encrypt('dan', 'test'));

select * from tbl_users;

select aes_decrypt(password, 'test') from tbl_users;


The last select statement gives me a NULL value.

I have tried seeing the character set settings using the following:

show variables like '%char%'

Variable_name
Value
------------------------
-------------------------------------------------------
character_set_client
utf8
character_set_connection
utf8
character_set_database
utf8
character_set_filesystem
binary
character_set_results
utf8
character_set_server
latin1
character_set_system
utf8
character_sets_dir C:\Program Files\MySQL\MySQL Server 5.0\share
\charsets\


What can be the possible problem? Please suggest.

Thanks in advance
Jackal

[Rik Wasmus]

Post by j***@yahoo.com
HI faculties,
I have a table tbl_users that has charset set to UTF8. The
password column in this table, should store passwords in encrypted
form. For doing this, I am using the AES_ENCRYPT() function. To my
surprise, when I tried the following statement, no data got inserted
for the password column.
create table tbl_users
(
id bigint(10),
login varchar(10),
password char(10)
) default charset = utf8;
insert into tbl_users values(1, 'dan', aes_encrypt('dan', 'test'));
select * from tbl_users;
select aes_decrypt(password, 'test') from tbl_users;
The last select statement gives me a NULL value.

RTFM:
Because AES is a block-level algorithm, padding is used to encode uneven
length strings and so the result string length may be calculated using
this formula:
16 × (trunc(string_length / 16) + 1)

mysql> insert into tbl_users values(1, 'dan', aes_encrypt('dan', 'test'));
ERROR 1366 (HY000): Incorrect string value: '\xDF<\xA2\x90\xEB\x02...' for
column 'password' at row 1
mysql> select aes_encrypt('dan', 'test');