What the Ã�©Ð”ã° is going on!?

Finding Your Way Out of Charset Hell — Say goodbye to cargo cult solutions. Finally get a grasp on character set encoding, learn how it works for PHP apps and MySQL, and become confident in fixing encoding issues once and for all.

Transcript

1. kevinsmith.io What the éД㰠is going on!? Finding Your Way Out of Charset Hell Oct 26, 2018 1
2. kevinsmith.io The train ticket was £10. 2
3. kevinsmith.io r sum or rsum 3
4. kevinsmith.io 4
5. kevinsmith.io Mojibake ⽂文字化け 5
6. kevinsmith.io A Common Encoding Issue for Early PHP-based Applications 6
7. kevinsmith.io Character Encoding 101 7
8. kevinsmith.io EEConf is powered by the community! 8
9. kevinsmith.io 01000101 01000101 01000011 01101111 01101110 01100110 00100000 01101001 01110011 00100000 01110000 01101111 01110111 01100101 01110010 01100101 01100100 00100000 01100010 01111001 00100000 01110100 01101000 01100101 00100000 01100011 01101111 01101101 01101101 01110101 01101110 01101001 01110100 01111001 00100001 9
10. kevinsmith.io Character Encoding:
 a set of rules that map a sequence of 1s and 0s to characters
 
 Character Set = Charset = Encoding 10
11. kevinsmith.io • American Standard Code for Information Interchange • Developed in the US in the 1960s • Single-byte Character Set • 8 bits = byte • 128 characters defined ASCII 11
12. kevinsmith.io ASCII Table 12 Character Binary a 1100001 b 1100010 c 1100011 d 1100100 e 1100101 f 1100110 Character Binary A 1000001 B 1000010 C 1000011 D 1000100 E 1000101 F 1000110
13. kevinsmith.io ASCII EEConf is powered by the community!
 UTF-16 䕅䍯湦猠灯睥牥搠批⁴桥潭浵湩瑹 13
14. kevinsmith.io Doesn’t include… à, è, ì, ò, ù á, é, í, ó, ú, ý â, ê, î, ô, û ñ, õ, ã ä, ë, ï, ö, ü, ÿ 14
15. kevinsmith.io • Extend encoding using rest of the 256 possible characters • Latin1 (ISO-8859-1) • Western European (like Spanish, Irish, Norwegian, Swedish, etc.) • Latin2 (ISO-8859-2) • Eastern European (like Bosnian, Croatian, Czech, Polish, etc.) • Latin5 (ISO-8859-5) • Cyrillic languages (like Russian, Bulgarian, Serbian, etc.) Extended ASCII 15
16. kevinsmith.io Multi-byte Encodings 16
17. kevinsmith.io Multi-byte Encodings • 2 bytes (16 bits) = 65,536 distinct values • 3 bytes (24 bits) = 16,777,216 distinct values • 4 bytes (32 bits) = 4,294,967,296 distinct values • Variable-length encoding • BIG-5: mostly traditional Chinese characters • GB18030: traditional Chinese and simplified Chinese • Shift JIS: Japanese characters 17
18. kevinsmith.io • Development began in the late 1980’s • Mapping of characters to code points • Up to 1,114,112 code points • 137,374 code points currently used (less than 13%) • 137,468 code points reserved for private use Unicode to Rule Them All! 18
19. kevinsmith.io Revenge is a dish best served cold. 19
20. kevinsmith.io • Multiple ways of encoding code points into bits • UTF-32 uses 32 bits (4 bytes) per code point • Variable width character encodings… • UTF-16 uses 16 or 32 bits • Used internally by Windows, Java, JavaScript • UTF-8 uses 8-32 bits (1-4 bytes) per code point • Efficient storage & backwards compatible with ASCII aka UTF Unicode Transformation Format 20
21. kevinsmith.io What Every Programmer Absolutely, Positively Needs To Know About Encodings And Character Sets To Work With Text http://kunststube.net/encoding/ 21
22. kevinsmith.io UTF-8 is King 22
23. kevinsmith.io The Web, The Application, The Database 23
24. kevinsmith.io • UTF-8 has been dominant and highly recommended
 since mid 2000’s • App or Web Server: Content-Type: text/html; charset=UTF-8 • HTML: <meta charset="UTF-8"> Web Browsers 24
25. kevinsmith.io • PHP files must be encoded ASCII-compatible • Encoding matters when operating on content • Otherwise pretty agnostic about encoding PHP 25
26. kevinsmith.io • latin1 default charset until v8 • utf8mb4 new default charset as of v8 • The connection charset and column charset
 are all that matters at runtime MySQL 26
27. kevinsmith.io MySQL Charsets at Runtime Connection Character Set “The content being sent to me
 is encoded with this character encoding.” Column Character Set “The content stored in this column’s fields
 is encoded with this character encoding.” 27
28. kevinsmith.io An Unfortunate Coincidence 28
29. kevinsmith.io The Situation UTF-8 has been dominant encoding for
 the web since mid 2000’s PHP is mostly agnostic about encoding MySQL’s default encoding was latin1 until v8 No MySQL connection charset specified in PHP?
 Default MySQL connection charset will be used. 29
30. kevinsmith.io The Plot Thickens Text content on the web and in PHP
 was UTF-8 encoded. PHP sent that content to MySQL
 and said it was latin1 encoded. MySQL thought it was latin1 encoded
 and stored it as if it were. 30
31. kevinsmith.io The Hidden Problem You were probably storing UTF-8 encoded content
 in database fields that thought it was
 latin1 encoded content. 31
32. kevinsmith.io MySQL accepts any string of
 1’s and 0’s as latin1. 32
33. kevinsmith.io MySQL will search and sort based
 on the charset it thinks the
 content is encoded with. 33
34. kevinsmith.io 34 UTF-8 latin1
35. kevinsmith.io 35 ASCII UTF-8 latin1
36. kevinsmith.io You couldn’t see it,
 but the problem was there… 36
37. kevinsmith.io 37
38. kevinsmith.io 38
39. kevinsmith.io We Fixed* It! So you upgraded your sites to CMS versions that started declaring connection charset to be UTF-8… And content since then has been correctly encoded… Though the UTF-8 to latin1 conversion is lossy… …and the previously encoded non-ASCII content
 comes back as Mojibake. 39
40. kevinsmith.io r sum or rsum 40
41. kevinsmith.io • Switching DB columns or connection to utf8 or utf8mb4 • Slap utf8_encode and utf8_decode on there • A UnicodeFixer() function peppered throughout • Wild guesses using a variety of iconv() conversions Cargo Cult Troubleshooting 41
42. kevinsmith.io There is No Magic Bullet 42
43. kevinsmith.io There is No Magic Bullet • Nearly impossible for computer to autodetect proper encoding • mb_detect_encoding() in PHP is worthless • Ultimately human readers are needed to confirm • Investigate to figure out what happened 43
44. kevinsmith.io If the output from
 the database looks right… 44
45. kevinsmith.io A Targeted Correction 2 Step Process:
 ALTER TABLE FOO MODIFY COLUMN
 V VARCHAR(100) CHARSET binary; ALTER TABLE FOO MODIFY COLUMN
 V VARCHAR(100) CHARSET utf8mb4; 45
46. kevinsmith.io Export the DB with same connection as PHP
 
 mysqldump -u User -h dbhost.com -p dbName \
 --hex-blob --default-character-set=latin1 \
 > export.sql Correcting the Whole Database 46
47. kevinsmith.io Edit the export to change charset declaration
 
 sed \
 -e 's/SET NAMES latin1/SET NAMES utf8mb4/g' \
 -e 's/CHARSET=utf8/CHARSET=utf8mb4 \
 COLLATE=utf8mb4_unicode_520_ci/g' \
 -e 's/CHARSET=latin1/CHARSET=utf8mb4 \
 COLLATE=utf8mb4_unicode_520_ci/g' \
 -i export.sql Correcting the Whole Database 47
48. kevinsmith.io Import into tmp DB with utf8mb4 connection
 
 mysql -u User -h dbhost.com -p dbName_tmp \
 < export.sql Correcting the Whole Database 48
49. kevinsmith.io Then test the output
 
 Look for places where an encoding error may have cut off the rest of the string! Correcting the Whole Database 49
50. kevinsmith.io To Sum it Up 50
51. kevinsmith.io New Project? utf8mb4 51
52. kevinsmith.io Don’t deploy encoding changes
 to production if you don’t
 understand how it works. 52
53. kevinsmith.io Understand the Fundamentals http://kunststube.net/encoding/ http://kunststube.net/frontback/ http://mysqlserverteam.com/debugging- character-set-issues-by-example/ 53
54. kevinsmith.io 54 Any Questions? Feedback @ eeconf.com/speaker-feedback Follow @_KevinSmith
 on Twitter for slides and the links 
 Email me at
 kevin@kevinsmith.io