Incorrect string encodings

Question

Note: I have read all of the related PHP, UTF-8, character encoding articles that are usually suggested, but my question relates to data inserted before I applied such techniques. I am wishing to retrospectively fix all character encoding problems.

Now all connections are set as utf8 using PDO.

PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'

Unfortunately, a large amount of data was inserted that is of questionable encoding before I had implemented correct character encoding practices. As displayed by:

$sql = "SELECT name FROM data LIMIT 3";

foreach ($pdo->query($sql) as $row)
{
    $name = $row['name'];

    echo $name . "\n";
    echo utf8_encode($name) . "\n";
    echo utf8_decode($name) . "\n";
    echo htmlspecialchars($name, ENT_QUOTES, 'UTF-8') . "\n";
    echo htmlspecialchars(utf8_encode($name), ENT_QUOTES, 'UTF-8') . "\n";
    echo htmlspecialchars(utf8_decode($name), ENT_QUOTES, 'UTF-8') . "\n";
    echo '<hr/>';
}

Which produces:

AntonÃƒÂn DvoÃ…Â™ÃƒÂ¡k
AntonÃÆÃÂn DvoÃâ¦Ãâ¢ÃÆÃÂ¡k
Anton�?n Dvo�?�?�?¡k
AntonÃƒÂn DvoÃ…Â™ÃƒÂ¡k
AntonÃÆÃÂn DvoÃâ¦Ãâ¢ÃÆÃÂ¡k

----------
Ô±Ö€Õ¡Õ´ Ô½Õ¡Õ¹Õ¡Õ¿Ö€ÕµÕ¡Õ¶
ÃÂ±Ãâ¬ÃÂ¡ÃÂ´ ÃÂ½ÃÂ¡ÃÂ¹ÃÂ¡ÃÂ¿Ãâ¬ÃÂµÃÂ¡ÃÂ¶
Ա�?ամ Խաչատ�?յան
Ô±Ö€Õ¡Õ´ Ô½Õ¡Õ¹Õ¡Õ¿Ö€ÕµÕ¡Õ¶
ÃÂ±Ãâ¬ÃÂ¡ÃÂ´ ÃÂ½ÃÂ¡ÃÂ¹ÃÂ¡ÃÂ¿Ãâ¬ÃÂµÃÂ¡ÃÂ¶

----------
TiÃ«sto
TiÃÂ«sto
Tiësto
TiÃ«sto
TiÃÂ«sto
Tiësto
----------

When removing 'SET NAMES utf8' with PDO it produces the data, which does actually have the correct items, albeit on different lines:

AntonÃn DvoÅÃ¡k
AntonÃÂn DvoÃÂÃÂ¡k
Antonín Dvořák
AntonÃn DvoÅÃ¡k
AntonÃÂn DvoÃÂÃÂ¡k
Antonín Dvořák
----------
Արամ Խաչատրյան
Ô±ÖÕ¡Õ´ Ô½Õ¡Õ¹Õ¡Õ¿ÖÕµÕ¡Õ¶
???? ?????????
Արամ Խաչատրյան
Ô±ÖÕ¡Õ´ Ô½Õ¡Õ¹Õ¡Õ¿ÖÕµÕ¡Õ¶
???? ?????????
----------
Tiësto
TiÃ«sto
Ti�sto
Tiësto
TiÃ«sto

----------

And here is a dump of the database rows concerned:

DROP TABLE IF EXISTS `data`;
CREATE TABLE IF NOT EXISTS `data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`(10)),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;

INSERT INTO `data` (`id`, `name`) VALUES (0, 'AntonÃƒÂn DvoÃ…Â™ÃƒÂ¡k'), (1, 'Ô±Ö€Õ¡Õ´ Ô½Õ¡Õ¹Õ¡Õ¿Ö€ÕµÕ¡Õ¶'), (2, 'TiÃ«sto');

The 3rd and 6th lines of the 3rd row "Tiësto" are then correctly echoed. I'm just unsure what is the best way to correct encodings/detect the encodings of bad strings and correct, etc.

This was asked but IMO not completely answered here: http://stackoverflow.com/questions/1344692/i-need-help-fixing-broken-utf8-encoding (Not voting as duplicate because this *is* fixable) — Pekka, Dec 26 '10 at 21:22
As a first step, to confirm: Are you 100% sure your database data is broken? What did you use to do the dump? Does a client program like phpMyAdmin or HeidiSQL display the broken characters when you browse the table? — Pekka, Dec 26 '10 at 21:23

score 1 · Answer 1 · answered Dec 26 '10 at 21:29

One way that should work - I haven't tried this myself - is to dump the database into a file using phpMyAdmin, importing it, and specifying latin1 as the encoding even though it is UTF-8 encoded. (You need the phpMyAdmin version that offers specifying the character set of the dump file in a drop down menu when importing).

This should turn Ã« back into ë. If the data is consistently broken (i.e. it's not a mix of valid UTF-8 characters and broken ones), this may work.

Obviously, make backups before trying this, and look through the data with a fine comb afterwards.

score 0 · Answer 2 · answered Dec 27 '10 at 05:45

A rather unorthodox solution I've found and after testing, seems to be working is:

Connection A = UTF8 connection Connection B = Old non-UTF8 connection which encoded original data

With A, I return "name" which displays correctly with B but is corrupted with A due to non-UTF8 DB encoding
Find the item ID in B by looking up corrupted A value
Then using A update DB with correctly encoded UTF8 value

Rather convoluted but it seems to be working. Will update if any problems.

Incorrect string encodings

2 Answers2