Strange characters from query after encrypt/decrypt

[miguel lisboa]

my goal: encrypt this field "obs"

i tried in several ways: either using javax.cryto either using Mysql function AES_ENCRYPT, AES_DECRYPT

in a technical sense all is fine: i can insert, delete, retrieve and update nicely ... except for this:

imagine i insert the portuguese (latin) word "maçã"

what i get (either using javax.crypto or AES_ENCRYPT) after encrypt/decrypt is:

"ma��"

but if i make a plain insert or uppdate - not using encryption - i do get the right word "maçã"

so i guess the problem lies in the encryption mechanisms



Does anyone has any idea to sort this out?

if you want i can post relevant code; anyway i'm working with java6, Mysql5, eclipse3 and winXP SP3, in portuguese-Portugal language



thanks in advance

[Paul Clapham]

I'm guessing that at some point you take the array of bytes which is the result of the encryption and convert it to a string. Then later you convert that back to an array of bytes and try to decrypt it.

Converting arbitrary binary data to a string is a bad thing to do, as it can easily result in corruption of the data.

But maybe you aren't doing that. Maybe you're letting the database do it by choosing the wrong column type for the encrypted data. Or maybe it's something else. You haven't given us many details about the problem.

[miguel lisboa]

using the mysql function i refered before, i tried changing the column charset to utf8 and just got this error message:

sql: INSERT into registo (nomeUtilizador, palavraPasse, email, obs, site, url) VALUES ( 'mm' , ' mm' , 'mm ' , AES_ENCRYPT('mão', 'pass' ) , 'mm', 'mm')
java.sql.SQLException: Incorrect string value: '\xDA0i\xDBy\xE3...' for column 'obs' at row 1

[miguel lisboa]

the column "obs" in Mysql has the charset "latin1", but that gives the dreaded error with latin characters like:

mão
há
açúcar

and so on

[miguel lisboa]

Paul Clapham wrote:(...)Maybe you're letting the database do it by choosing the wrong column type for the encrypted data. Or maybe it's something else. You haven't given us many details about the problem.

the column obs is set to charset latin1 and has datatype TEXT

further testing shows the problem may be with java, i guess:

using Mysql browser:

INSERT into registo (nomeUtilizador, palavraPasse, email, obs, site, url) VALUES ( 'mm' , ' mm' ,  'mm ' ,
    AES_ENCRYPT('mário', 'pass' ) , 'mm', 'mm')

and then:

SELECT nomeutilizador, palavrapasse, email,
    AES_DECRYPT( obs,  'pass' ), site, url from registo

i get back the word "mário"

now, if i open my java application, i get the word: "m��rio"

curiously, if i export the resultset as cvs, i obtain:

"nomeutilizador","palavrapasse","email","AES_DECRYPT( obs, 'pass' )","site","url"
"mm"," mm","mm ","mário","mm","mm"

(Edited to reduce width of post. PC)

[miguel lisboa]

from http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html: (Mysql site)

Note
The encryption and compression functions return binary strings. For many of these functions, the result might contain arbitrary byte values. If you want to store these results, use a column with a VARBINARY or BLOB binary string data type. This will avoid potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT).

i did change column TYPE "obs" to BLOB, but with the very same result...

[Paul Clapham]

miguel lisboa wrote:curiously, if i export the resultset as cvs, i obtain:

"nomeutilizador","palavrapasse","email","AES_DECRYPT( obs, 'pass' )","site","url"
"mm"," mm","mm ","mário","mm","mm"

What you posted there shows signs of a String having been encoded to bytes using the UTF-8 encoding, and then those bytes having been encoded back to a String using a single-byte encoding (like what MySQL calls "latin1" whose real name is probably ISO-8859-1).

Is the AES_ENCRYPT function supposed to take a string, or is it supposed to take an array of bytes? Perhaps an implicit encoding via UTF-8 is happening if it's supposed to take an array of bytes.

The JDBC driver might also be involved in this. Do you explicitly declare its encoding when you make a connection?

[miguel lisboa]

i dont know how mysql, internally, processes the data

what are my procedures:

i send a plain string to mysql, from my java aplication

INSERT into registo (nomeUtilizador, palavraPasse, email, obs, site, url) 
VALUES ( 'm' ,  'm' ,  'm ' , AES_ENCRYPT('má mário', 'pass' ) , 'm', 'm')

mysql applies the AES_ENCRYPT function to that string

mysql stores it

i query database asking mysql to desencrypt that string, from my java aplication

SELECT nomeutilizador, palavrapasse, email, AES_DECRYPT( obs,  'pass' ), 
site, url from registo where id = ?

(at this point, using mysql brower, the string is perfectly ok)

as i was saying, when i populate my aplication the string is corrupted



also, i tried a variety of combinations witth column type and character encoding, like utf-8, latin1, and so on, with no result



at last i did in fact play around with my connection string, but also with no effects at all, like:

url = "jdbc:mysql://localhost/" + nomeBaseDados;
					
url = "jdbc:mysql://localhost/" + nomeBaseDados+"?useUnicode=true&characterEncoding=latin1";
					
url = "jdbc:mysql://localhost/" + nomeBaseDados+"?useUnicode=true&characterEncoding=UTF-8";
					
url = "jdbc:mysql://localhost/" + nomeBaseDados+"?characterEncoding=latin1";

note: if you want me to post some java code, please ask