69

I am in the process of fixing some bad UTF-8 encoding. I am currently using PHP 5 and MySQL.

In my database I have a few instances of bad encodings that print like: î

  • The database collation is utf8_general_ci
  • PHP is using a proper UTF-8 header
  • Notepad++ is set to use UTF-8 without BOM
  • database management is handled in phpMyAdmin
  • not all cases of accented characters are broken

I need some sort of function that will help me map the instances of î, í, ü and others like it to their proper accented UTF-8 characters.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Jayrox
  • 4,335
  • 4
  • 40
  • 43
  • Perhaps you could list the characters those are supposed to represent? And maybe a hex dump? – Managu Aug 28 '09 at 02:35
  • 7
    A quick look seems to suggest that your strings might have been "double" utf-8 encoded. I.e. encoded in utf-8, those bytes taken as unicode characters, and the result encoded in utf-8. Going backwards: "î"="\xC3\x83\xC2\xAE" <-(utf-8)- "\xC3\xAE" <-(utf-8)- "\xEE" = "î". Or perhaps not -- not much data to diagnose here. – Managu Aug 28 '09 at 02:46
  • It is possible that it was double encoded. Is there a safe way to programatically check this, and if so what is the best way to safely decode the double encoding? – Jayrox Aug 28 '09 at 03:10
  • Yes, Jayrox, check out my answer below. – Sebastián Grignoli Dec 25 '11 at 20:17
  • one of the problems afaik is `utf8_general_ci` which apparently will not guarantee good UTF8 http://stackoverflow.com/a/1036459/183677. Also those characters you mention _are_ valid UTF8 http://hexutf8.com/?q=c383c692c382c2ae2c20c383c692c382c2ad2c20c383c692c382c2bc (but I realize its probably just what you're seeing in console or whatever). pays to post the actual bytes – jar Sep 09 '16 at 21:05
  • Acc. to [this answer](http://stackoverflow.com/a/40136978/3832970), `mysqli_set_charset($dbc, "utf8");` might help. – Wiktor Stribiżew Oct 19 '16 at 16:58
  • See "Mojibake" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James Jun 13 '20 at 23:47

14 Answers14

97

If you have double-encoded UTF8 characters (various smart quotes, dashes, apostrophe ’, quotation mark “, etc), in mysql you can dump the data, then read it back in to fix the broken encoding.

Like this:

mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \
    --skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql

mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \
    --default-character-set=utf8 DB_NAME < DB_NAME-dump.sql

This was a 100% fix for my double encoded UTF-8.

Source: http://blog.hno3.org/2010/04/22/fixing-double-encoded-utf-8-data-in-mysql/

Aaron D
  • 7,540
  • 3
  • 44
  • 48
jsdalton
  • 6,555
  • 4
  • 40
  • 39
  • 2
    Seems to have successfully converted a Typo3 database for me. Thanks for posting this; it's much cleaner than any other conversion method. :) – Energiequant Mar 14 '11 at 11:43
  • I wish I could give you more upvotes, you really really deserve them. – Frost Nov 11 '11 at 21:53
  • Yep, also worked for me! Thanks to you sharing it here and thanks to the owner of the blog :) – Prine Mar 28 '12 at 12:10
  • Ran into the problem when transferring a Wordpress DB from staging to local environment by exporting it with Sequel Pro. – Yves Van Broekhoven Nov 12 '12 at 12:55
  • Works perfectly! I also had to fix an old TYPO3 database and this just did the trick! – user828591 Nov 23 '15 at 13:02
  • All comments so far on this answer - including mine!! - are completely useless and just add noise. Aargh! – Simon B. Apr 20 '16 at 12:17
  • Thanks! This is working for me: `ssh user@host 'mysqldump --skip-set-charset --default-character-set=latin1 dbname' | mysql --default-character-set=utf8 dbname` – fnkr May 26 '16 at 13:38
  • almost a week of trying to figure out what was going on, and here's a solution that fixes it in a minute:) – StefanJM Mar 30 '19 at 16:59
90

If you utf8_encode() on a string that is already UTF-8 then it looks garbled when it is encoded multiple times.

I made a function toUTF8() that converts strings into UTF-8.

You don't need to specify what the encoding of your strings is. It can be Latin1 (iso 8859-1), Windows-1252 or UTF8, or a mix of these three.

I used this myself on a feed with mixed encodings in the same string.

Usage:

$utf8_string = Encoding::toUTF8($mixed_string);

$latin1_string = Encoding::toLatin1($mixed_string);

My other function fixUTF8() fixes garbled UTF8 strings if they were encoded into UTF8 multiple times.

Usage:

$utf8_string = Encoding::fixUTF8($garbled_utf8_string);

Examples:

echo Encoding::fixUTF8("Fédération Camerounaise de Football");
echo Encoding::fixUTF8("Fédération Camerounaise de Football");
echo Encoding::fixUTF8("FÃÂédÃÂération Camerounaise de Football");
echo Encoding::fixUTF8("Fédération Camerounaise de Football");

will output:

Fédération Camerounaise de Football
Fédération Camerounaise de Football
Fédération Camerounaise de Football
Fédération Camerounaise de Football

Download:

https://github.com/neitanod/forceutf8

doobop
  • 4,465
  • 2
  • 28
  • 39
Sebastián Grignoli
  • 32,444
  • 17
  • 71
  • 86
  • 1
    Seems to do the trick. I don't use it for normal output, but I do enjoy using your class for data migration help. – Kristopher Ives Jan 10 '12 at 21:59
  • 8
    Thanks. It's magical, isn't it? I think this little piece of code is one of the most satisfying things I've produced, in terms of problems solved with it. :-) – Sebastián Grignoli Jan 11 '12 at 23:33
  • I recommend using it for migrations, as Kristopher said, but not in a production environment. There are cases where you would want the "garbled string" to stay garbled, like in this answer. – Sebastián Grignoli Apr 06 '12 at 19:09
  • I have struggled with third party systems that have mixed encoding. I tested your class out, and it works well. I just ran it on fields in our database that stored outside input with mixed encoding, and it cleaned everything up. Now I am implementing it at our insert junctions. PDO doesn't identify mixed encoding by the way, thus your solution rocks! – Nick Johnson Oct 19 '12 at 16:26
  • Great library, thank you very much! It helped me to fix thousends of broken file names, that occured by copying the files from linux to windows via FTP and back. – sieppl Dec 21 '12 at 20:50
  • 2
    +1 excellent- fixUTF8 even takes care of some weird encoding errors I've seen. – andig Feb 04 '13 at 11:57
  • Kudos! I've been struggling a long time with this too. Until I -accidentially- found this! Many thanks. – Walter81 Mar 05 '13 at 07:46
  • "FÃÂédÃÂération Camerounaise de Football" doesn't seem to work, others do – Desmond Hume Jun 01 '14 at 19:01
  • Thank you Sebastian. This is really helpful – Raja Khoury Nov 06 '15 at 12:16
  • Yep, super script! Had an issue with incorrect database encoding during migration. This solved it. – JustBaron Jun 28 '16 at 08:39
  • @SebastiánGrignoli: it would be very nice but it doesn't work on my code... Encoding::fixUTF8("luminositÃ?") doesn't solve the issue. Any suggestion? – Revious Jun 23 '17 at 22:04
  • @SebastiánGrignoli: have a look to this page. This solves every problem http://www.i18nqa.com/debug/utf8-debug.html – Revious Jun 23 '17 at 22:28
  • Those look like repeatedly treating a utf8 string as if it were latin1 and converting it to utf8. See "double encoding" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James Jun 13 '20 at 23:50
  • This has been such a life saver I cannot express how great it has been. I haven't been able to find anything else like it!! Ive been using it to do real time translation of strings (not a one time fix for bad UTF), do you think its a major resource hog to use it that way? – Mark Feb 06 '22 at 23:56
66

I've had to try to 'fix' a number of UTF8 broken situations in the past, and unfortunately it's never easy, and often rather impossible.

Unless you can determine exactly how it was broken, and it was always broken in that exact same way, then it's going to be hard to 'undo' the damage.

If you want to try to undo the damage, your best bet would be to start writing some sample code, where you attempt numerous variations on calls to mb_convert_encoding() to see if you can find a combination of 'from' and 'to' that fixes your data. In the end, it's often best to not even bother worrying about fixing the old data because of the pain levels involved, but instead to just fix things going forward.

However, before doing this, you need to make sure that you fix everything that is causing this issue in the first place. You've already mentioned that your DB table collation and editors are set properly. But there are more places where you need to check to make sure that everything is properly UTF-8:

  • Make sure that you are serving your HTML as UTF-8:
    • header("Content-Type: text/html; charset=utf-8");
  • Change your PHP default charset to utf-8:
    • ini_set("default_charset", 'utf-8');
  • If your database doesn't ALWAYS talk in utf-8, then you may need to tell it on a per connection basis to ensure it's in utf-8 mode, in MySQL you do that by issuing:
    • charset utf8
  • You may need to tell your webserver to always try to talk in UTF8, in Apache this command is:
    • AddDefaultCharset UTF-8
  • Finally, you need to ALWAYS make sure that you are using PHP functions that are properly UTF-8 complaint. This means always using the mb_* styled 'multibyte aware' string functions. It also means when calling functions such as htmlspecialchars(), that you include the appropriate 'utf-8' charset parameter at the end to make sure that it doesn't encode them incorrectly.

If you miss up on any one step through your whole process, the encoding can be mangled and problems arise. Once you get in the 'groove' of doing utf-8 though, this all becomes second nature. And of course, PHP6 is supposed to be fully unicode complaint from the getgo, which will make lots of this easier (hopefully)

shaedrich
  • 5,457
  • 3
  • 26
  • 42
Eli
  • 869
  • 7
  • 3
  • 1
    Thank you very much! Because there are also many correctly encoded Strings in the DB, wich makes the Problem worse, i chose to str_replace the Strings i know that are corrupt with their correct Characters. It works great. I have already implemented most of your Tips regarding PHP and Server Setup, but it is a great summary, so i would chose this as the Answer, because my solution is not really beautiful. – Paul Weber Jan 20 '10 at 14:28
  • One important note on this advice: Do NOT add 'utf-8' as the second argument to the function htmlspecialchars(). Without the argument, that function does the correct thing with UTF-8 strings, since it ignores all bytes with the high bit set and passes them. This will preserve them and "does the right thing". With 'utf-8', htmlspecialchars() interprets the UTF-8 string - but doesn't handle characters outside the BMP (those with code points U+10000 and above, encoded in four bytes). It incorrectly encodes those that happen to match the specials mod 65536.. The behavior is both slower and wrong. – MtnViewMark Feb 23 '10 at 14:38
  • Please, see my answer below. I addressed all this problems in a single pure-PHP function: fixUTF8(). You don't need to change your server configuration, and you don't even need to have the multi byte functions installed. The function is smart enough to fix any character independently, even if the encoding is mixed inside the same string (no matter how many times it was converted or if it's in UTF8 already). – Sebastián Grignoli Dec 25 '11 at 16:32
  • PHP 6 was skipped, PHP 7 will be in one month a stable release. – Jose Nobile Sep 04 '15 at 15:27
  • @Jayrox: There is a better answer witg a tool from github: https://stackoverflow.com/a/3521340/196210 – Revious Jun 23 '17 at 21:45
19

I had a problem with an xml file that had a broken encoding, it said it was utf-8 but it had characters that where not utf-8.
After several trials and errors with the mb_convert_encoding() I manage to fix it with

mb_convert_encoding($text, 'Windows-1252', 'UTF-8')
benka
  • 4,732
  • 35
  • 47
  • 58
Celleb
  • 191
  • 1
  • 2
  • 1
    This worked for me after days of banging my head over the issue (everything was UTF-8 end to end but in RSS it wasn't!) Thank you! – Titan Apr 23 '15 at 15:50
  • My problem was: Database fields saved as `latin1_swedish_ci`, output by PHP as utf-8 showing Umlaute `ü` as `ü` and `ö` as `ö`. This helped to fix this. – Avatar Aug 17 '16 at 18:37
11

As Dan pointed out: you need to convert them to binary and then convert/correct the encoding.

E.g., for utf8 stored as latin1 the following SQL will fix it:

UPDATE table
   SET field = CONVERT( CAST(field AS BINARY) USING utf8)
 WHERE $broken_field_condition
blueyed
  • 27,102
  • 4
  • 75
  • 71
  • interesting; i'll remember this if i have the issue again. thanks – Jayrox May 19 '10 at 21:16
  • 1
    Makes sense. I guess it's really double-encoded, it's just that the field is marked latin1 even though it really contains UTF8, so when you request the field as UTF8 it encodes it again. – Eli Feb 16 '11 at 22:53
  • Man, you made my day, it worked for me. Now I'd like to understand the real reason why the dump I'm working with has these wrong characters (maybe it was correctly encoded in utf-8 but the dump process printed the output as latin1) – Diego Pino Dec 13 '11 at 11:01
  • 1
    `WHERE LENGTH( field ) != CHAR_LENGTH( field )` ;) – SuN Apr 26 '16 at 09:10
4
$bad_string = "Luis Pérez Casas, del Collettivo di avvocati “José Alvear Restrepoâ€, Colombia, un’organizzazione soggetta a costanti minacce";

$good_string = fix_broken_chars($bad_string);

echo $good_string;

function fix_broken_chars($garbled_utf8_string)
{   
    $conv_table = unserialize('a:5:{i:0;a:3:{s:8:"’";s:3:"’";s:8:"–";s:3:"–";s:8:"—";s:3:"—";}i:1;a:12:{s:7:"€";s:3:"€";s:7:"‚";s:3:"‚";s:7:"„";s:3:"„";s:7:"…";s:3:"…";s:7:"‡";s:3:"‡";s:7:"‰";s:3:"‰";s:7:"‹";s:3:"‹";s:7:"‘";s:3:"‘";s:7:"“";s:3:"“";s:7:"•";s:3:"•";s:7:"â„¢";s:3:"™";s:7:"›";s:3:"›";}i:2;a:22:{s:5:"À";s:2:"À";s:5:"Â";s:2:"Â";s:5:"Æ’";s:2:"ƒ";s:5:"Ä";s:2:"Ä";s:5:"Ã…";s:2:"Å";s:5:"â€";s:3:"”";s:5:"Æ";s:2:"Æ";s:5:"Ç";s:2:"Ç";s:5:"ˆ";s:2:"ˆ";s:5:"É";s:2:"É";s:5:"Ë";s:2:"Ë";s:5:"Å’";s:2:"Œ";s:5:"Ñ";s:2:"Ñ";s:5:"Ã’";s:2:"Ò";s:5:"Ó";s:2:"Ó";s:5:"Ô";s:2:"Ô";s:5:"Õ";s:2:"Õ";s:5:"Ö";s:2:"Ö";s:5:"×";s:2:"×";s:5:"Ù";s:2:"Ù";s:5:"Û";s:2:"Û";s:5:"Å“";s:2:"œ";}i:3;a:77:{s:4:"Ã";s:2:"Ã";s:4:"È";s:2:"È";s:4:"Ê";s:2:"Ê";s:4:"ÃŒ";s:2:"Ì";s:4:"Ž";s:2:"Ž";s:4:"ÃŽ";s:2:"Î";s:4:"Ëœ";s:2:"˜";s:4:"Ø";s:2:"Ø";s:4:"Å¡";s:2:"š";s:4:"Ú";s:2:"Ú";s:4:"Ãœ";s:2:"Ü";s:4:"ž";s:2:"ž";s:4:"Þ";s:2:"Þ";s:4:"Ÿ";s:2:"Ÿ";s:4:"ß";s:2:"ß";s:4:"¡";s:2:"¡";s:4:"á";s:2:"á";s:4:"¢";s:2:"¢";s:4:"â";s:2:"â";s:4:"£";s:2:"£";s:4:"ã";s:2:"ã";s:4:"¤";s:2:"¤";s:4:"ä";s:2:"ä";s:4:"Â¥";s:2:"¥";s:4:"Ã¥";s:2:"å";s:4:"¦";s:2:"¦";s:4:"æ";s:2:"æ";s:4:"§";s:2:"§";s:4:"ç";s:2:"ç";s:4:"¨";s:2:"¨";s:4:"è";s:2:"è";s:4:"©";s:2:"©";s:4:"é";s:2:"é";s:4:"ª";s:2:"ª";s:4:"ê";s:2:"ê";s:4:"«";s:2:"«";s:4:"ë";s:2:"ë";s:4:"¬";s:2:"¬";s:4:"ì";s:2:"ì";s:4:"­";s:2:"­";s:4:"í";s:2:"í";s:4:"®";s:2:"®";s:4:"î";s:2:"î";s:4:"¯";s:2:"¯";s:4:"ï";s:2:"ï";s:4:"°";s:2:"°";s:4:"ð";s:2:"ð";s:4:"±";s:2:"±";s:4:"ñ";s:2:"ñ";s:4:"²";s:2:"²";s:4:"ò";s:2:"ò";s:4:"³";s:2:"³";s:4:"ó";s:2:"ó";s:4:"´";s:2:"´";s:4:"ô";s:2:"ô";s:4:"µ";s:2:"µ";s:4:"õ";s:2:"õ";s:4:"¶";s:2:"¶";s:4:"ö";s:2:"ö";s:4:"·";s:2:"·";s:4:"÷";s:2:"÷";s:4:"¸";s:2:"¸";s:4:"ø";s:2:"ø";s:4:"¹";s:2:"¹";s:4:"ù";s:2:"ù";s:4:"º";s:2:"º";s:4:"ú";s:2:"ú";s:4:"»";s:2:"»";s:4:"û";s:2:"û";s:4:"¼";s:2:"¼";s:4:"ü";s:2:"ü";s:4:"½";s:2:"½";s:4:"ý";s:2:"ý";s:4:"¾";s:2:"¾";s:4:"þ";s:2:"þ";s:4:"¿";s:2:"¿";s:4:"ÿ";s:2:"ÿ";}i:4;a:1:{s:2:"Ã";s:2:"à";}}');

    foreach ($conv_table as $convert) {
        $garbled_utf8_string = str_replace(array_keys($convert), $convert, $garbled_utf8_string);    
    }

    return $garbled_utf8_string;
}

Implements this table http://www.i18nqa.com/debug/utf8-debug.html

Marek Tichy
  • 59
  • 3
2

I know this isn't very elegant, but after it was mentioned that the strings may be double encoded, I made this function:

function fix_double encoding($string)
{
    $utf8_chars = explode(' ', 'À Á Â Ã Ä Å Æ Ç È É Ê Ë Ì Í Î Ï Ð Ñ Ò Ó Ô Õ Ö × Ø Ù Ú Û Ü Ý Þ ß à á â ã ä å æ ç è é ê ë ì í î ï ð ñ ò ó ô õ ö');
    $utf8_double_encoded = array();
    foreach($utf8_chars as $utf8_char)
    {
            $utf8_double_encoded[] = utf8_encode(utf8_encode($utf8_char));
    }
    $string = str_replace($utf8_double_encoded, $utf8_chars, $string);
    return $string;
}

This seems to work perfectly to remove the double encoding I am experiencing. I am probably missing some of the characters that could be an issue to others. However, for my needs it is working perfectly.

Jayrox
  • 4,335
  • 4
  • 40
  • 43
  • 1
    Take a look at my answer. The function Encoding::fixUTF8(). It fixes all UTF8 characters (there are millions of them), and can handle strings encoded multiple times, not only twice. – Sebastián Grignoli Dec 29 '11 at 21:19
1

The way is to convert to binary and then to correct encoding

Dan
  • 55,715
  • 40
  • 116
  • 154
1

Another thing to check, which happened to be my solution (found here), is how data is being returned from your server. In my application, I'm using PDO to connect from PHP to MySQL. I needed to add a flag to the connection which said get the data back in UTF-8 format

The answer was

$dbHandle = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8", $dbUser, $dbPass, 
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
Community
  • 1
  • 1
Luke Madhanga
  • 6,871
  • 2
  • 43
  • 47
1

In my case, I found out by using "mb_convert_encoding" that the previous encoding was iso-8859-1 (which is latin1) then I fixed my problem by using an sql query :

UPDATE myDB.myTable SET myColumn = CAST(CAST(CONVERT(myColumn USING latin1) AS binary) AS CHAR)

However, it is stated in the mysql documentations that conversion may be lossy if the column contains characters that are not in both character sets.

Jordan Daigle
  • 424
  • 4
  • 12
0

It looks like your utf-8 is being interpreted as iso8859-1 or Win-1250 at some point.

When you say "In my database I have a few instances of bad encodings" - how did you check this? Through your app, phpmyadmin or the command line client? Are all utf-8 encodings showing up like this or only some? Is it possible you had the encodings wrong and it has been incorrectly converted from iso8859-1 to utf-8 when it was utf-8 already?

teambob
  • 1,994
  • 14
  • 19
0

I found a solution after days of search. My comment is going to be buried but anyway...

  1. I get the corrupted data with php.

  2. I don't use set names UTF8

  3. I use utf8_decode() on my data

  4. I update my database with my new decoded data, still not using set names UTF8

and voilà :)

David 天宇 Wong
  • 3,724
  • 4
  • 35
  • 47
0

This script had a nice approach. Converting it to the language of your choice should not be too difficult:

http://plasmasturm.org/log/416/

#!/usr/bin/perl
use strict;
use warnings;

use Encode qw( decode FB_QUIET );

binmode STDIN, ':bytes';
binmode STDOUT, ':encoding(UTF-8)';

my $out;

while ( <> ) {
  $out = '';
  while ( length ) {
    # consume input string up to the first UTF-8 decode error
    $out .= decode( "utf-8", $_, FB_QUIET );
    # consume one character; all octets are valid Latin-1
    $out .= decode( "iso-8859-1", substr( $_, 0, 1 ), FB_QUIET ) if length;
  }
  print $out;
}
Erik Aronesty
  • 11,620
  • 5
  • 64
  • 44
0

i had the same problem long time ago, and it fixed it using 

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-15">
Jose De Gouveia
  • 994
  • 1
  • 14
  • 33