0

I am trying to encrypt part of a mySQL-database with AES_ENCRYPT, and of course retrieve the data later on with AES_DECRYPT. But when the field contain special characters like æ, ø eller å, these come back as �

I have been trying to read up on why this is, but haven't found a solution.

First of I change the field-type to BLOB:

ALTER TABLE 'table' MODIFY field BLOB

Then I do the encryption:

UPDATE 'table' SET field = AES_ENCRYPT(field, '$key')

Everything is stored, but when trying to extract the data again:

SELECT AES_DECRYPT(field,'$key') AS variable FROM table WHERE....

all the special characters are displayed as � when echo in PHP

I think I got the UTF-8 covered, in both HTML and mySQL:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

and

mysqli_set_charset($connect,'utf8');

so storing the special characters in the mySQL-database, retrieving and displaying them with PHP works just fine as long as the field is VARCHAR and no encryption has been done. I also read about problems with the special characters in binary fields like BLOB, but I recon I'm not the first one that tries to encrypt and store an Æ or an Ø :)

Any hints would be really appreciated! Thanks!

Alexander N
  • 160
  • 1
  • 10
  • as per the usual, SOME stage of your rendering pipeline is using a different charset, causing multi-byte characters to get trashed. you are correct: blob fields do NOT change characters, because blobs aren't text. character set translation only applies to text-type fields (char, varchar, text). – Marc B Aug 29 '16 at 19:40
  • The mojibake likely occurred either when values were inserted into the (unfortunately named) `field` column (no indication of what characterset of that column was before it was converted to BLOB), or before the values got to a MySQL INSERT statement. (Converting the column to BLOB wouldn't have done any characterset conversion.) Recommended: http://kunststube.net/encoding/ and http://www.joelonsoftware.com/articles/Unicode.html – spencer7593 Aug 29 '16 at 19:51

0 Answers0