#1006 Problems with AES_ENCRYPT and AES_DECRYPT

[Anonymous]

Originally created by: snowrid...@gmail.com

We are trying to make use of these native mysql functions and have run into
problems within the HeidiSQL program.

If you execute the following command from the MySQL monitor on a command
line (v 4.1.22 used in our case) the result is as expected:

SELECT DES_DECRYPT(DES_ENCRYPT('my string of data','my salt'),'my salt');

however, if you run the same command from within heidiSQL the result is
always a string that looks like a binary value or something - not too sure
myself.

Just though I would report this one.

Rob

[Anonymous]

Originally posted by: rosenfie...@gmail.com

Thanks for reporting.

This is per design of the MySQL Server.

The server forgets the character set of your text as soon as it's encrypted,
therefore when you decrypt the binary data again, the output does not have a
character set attached.

HeidiSQL correctly shows this as the raw data stream.  If you want, you can reapply
a character set like this:

CONVERT(..., USING utf8)

So, in your example, you can apply a character to the output data like this:

SELECT CONVERT(DES_DECRYPT(DES_ENCRYPT('my string of data','my salt'),'my salt')
USING utf8);

Assuming of course that the input string literal is in utf8, which it is if you're
using HeidiSQL ;-).  (Otherwise you can determine it by looking at
character_set_connection and character_set_client.)

Status: Upstream

[Anonymous]

Originally posted by: snowrid...@gmail.com

Just a follow up regarding this issue:

When doing a very simple query such as:
SELECT DATE_FORMAT(now(),'%Y-%m-%d')

it also comes back displaying a hex code instead of the proper date.  When I try the
same query via a php script or through the mysqladmin command line they work fine, it
is just heidi that has an issue getting the correct response to display.

After adding the CONVERT / USING utf8 it does display correctly.

Seems like a shortcoming or a bug to me.

Rob

[Anonymous]

Originally posted by: rosenfie...@gmail.com

Hi Rob

You're right, the issue mentioned in comment #2 is a bug.

It has already been fixed in MySQL Server, upgrade to 5.0.48 or 5.1.22 to get the
fix.