Encryption with UTF8 charset issue

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

On Fri, 26 Oct 2007 12:23:54 +0200, <jackal_on_work@yahoo.com> wrote:

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

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');
--
Rik Wasmus