can't show special characters retrieved from AES_DECRYPT

Question

I'm using codeigniter.
After running the following query:

$names = this->db->query("SELECT AES_DECRYPT(nombre,'".$key."') AS nombre FROM places")

I get the following output from var_dump($names)

[43]=>
array(1) {
  ["nombre"]=>
  string(41) "Industriales de Campeche"
}
[44]=>
array(1) {
  ["nombre"]=>
  string(67) "Evaristo Garc�a ESE"
}
[45]=>
array(1) {
  ["nombre"]=>
  string(39) "San Jose Popay�n"

the result of the query on phpmyadmin looks like this:

So as you can see, the fields containing special characters are shown in it's hexadecimal representation. When i use this tool http://www.rapidtables.com/convert/number/hex-to-ascii.htm i can convert the hexadecimal fields with no problem (the special characters are shown), but the thing is i can´t make it work in codeigniter. Even though i'm using utf-8 encoding, the special characters keep appearing as �.

It's worth to say that i've had no problems before encrypting the database.

http://stackoverflow.com/questions/279170/how-to-support-utf-8-completely-in-a-web-application — Sammitch, Aug 30 '16 at 17:16
You say "I have tried all that", but have you actually read it? MySQL utf-8 encoding is ***not*** UTF-8 encoding. I assume your data column is a `blob` type? and/or `unicode`? please elaborate on your table structure. — Martin, Aug 31 '16 at 16:44

score 0 · Answer 1 · edited Aug 31 '16 at 16:40

0

You need to do 2 things

Set table and field collation to "utf8_general_ci"

Set meta content type in HTML like below

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

edited Aug 31 '16 at 16:40

Sammitch

30,782
7
50
77

answered Aug 31 '16 at 06:46

Rockers Technology

477
4
12

I'm, sorry but this is entirely unhelpful, it may be worth reading [the link](http://stackoverflow.com/questions/279170/how-to-support-utf-8-completely-in-a-web-application) given by Sammitch in his comments – Martin Aug 31 '16 at 16:50

score 0 · Answer 2 · edited May 23 '17 at 11:51

Check your MySQL data column is using _unicode_ or utf8mb4_ character collations. Also check your table as a whole is using the same character set (in PHPMyAdmin look in <table> -> Operations -> Collation ).
Check that your MySQL connection is using UTF8mb4_ connection character set (see below).
Check that your HTML headers are set to UTF-8 (the headers, not the <head> section of the page, although that can't hurt to set to UTF-8 too.).

I'm pretty sure your issue comes that your connection to your database from your PHP (or whatever) is not the required full UTF-8 character set.

 // In your database connection class. 
 // Once initial contact has been established
 $this->dbObject->set_charset("utf8mb4");

Some further reading: UTF-8 all the way through

can't show special characters retrieved from AES_DECRYPT

2 Answers2