Encoding Problem char looks right but is not

user_5052357 0
Viewed 80

First it is not the common utf8 Problem. All parts of my application is set to utf8 and works fine.

I get a mail over IMAP by PHP and fetch the title. In this title I have s special char. A ö from the German language. Now I search in my DB if there is an entry with this title. I know there is one. Database use utf8mb4_general_ci for encoding to be able to store 4 bit utf8 encoded special chars.

Title from Mail:

Fw: Auflösungsvertrag

Entry in Database:

Fw: Auflösungsvertrag

I put the cursor behind the ö and tried to delete it. First the ö switched to an o and after the second press of the delete key it was fully gone. If I type now and ö over my keyboard, MySQL finds the entry.

If I put both inside notepad ++ You see

Fw: Auflösungsvertrag

FW: Auflösungsvertrag

If you turn the encoding to ASCII you get

Fw: Auflösungsvertrag

Fw: Auflösungsvertrag

So you can see now that the two ö are different encoded, but they get displayed right. So my mySQL select don't find the DB entry.

Can someone explain this to me and give me a hint for a php command to turn the first encoded string to the second one?

I bit longer description how this problem accrues:

I write a ticketing system. Every mail I send out get added the tickets ID to the subject. If I send out a mail I write it to the db in an outgoing table. Then a cronjob sends this mails out asyncron. I use PHP mailer and send over SMTP.

I fetch Incoming mails by IMAP and the PHP IMAP classes. If a mail comes in with an TID in the subject I merge this mail into the ticket in the database. All ticket entries are grouped by the TID column.

The problem is now, if you send a mail from the system to another mail address inside the same system, you get the mail merged into the existing ticket.

That's why I look in the outgoing table for every incoming mail by search for the from Address, to Address and the title. If I find the mail I know the system has sent it out.

So if I send the mail out I have the first encoding. If I get the same mail back in again it has the other encoding. Both encoding seam to be valid utf8 encoding. Everywhere on the website I get the right character and also in the db I get it displayed right. Only if I make an SQL query over PDO, MySQL treat them as two different characters.

encoding mysql php
2 Answers

Here's how I would solve this and according to me it has to be fixed once for all with a oneshot instruction on the database side, and not with just a trick on the PHP, that you would have to repeat everywhere each time you are facing the issue.

First I copied your 2 strings :

Auflösungsvertrag
Auflösungsvertrag

Into Notepad++, in which I do have the (very handy) HEX pluggin.

When I turn the text into HEX, I have those values

4175666c6fcc8873756e677376657274726167
4175666cc3b673756e677376657274726167

If we split that, we see easily the HEX of the 2 ö that causes issues

4175666c 6fcc88 73756e677376657274726167
4175666c c3b6   73756e677376657274726167

The trick is now to tell MySQL to replace all characters having those HEX values from one to another, ie 6fcc88 to c3b6

And you can do that with this statement that uses UNHEX() function

UPDATE your_table
SET your_column=REPLACE(your_column, UNHEX('6fcc88'), UNHEX('c3b6'))

Example and reproduction below

Schema (MySQL v8.0)

/* Creating test data  - Row 1 and 2 are identical */
create table test (id int, txt varchar(50), txthex varchar(100));
INSERT INTO test (id,txt,txthex) VALUES (1, 'Auflösungsvertrag', '4175666c6fcc8873756e677376657274726167');
INSERT INTO test (id,txt,txthex) VALUES (2, 'Auflösungsvertrag', '4175666c6fcc8873756e677376657274726167');
INSERT INTO test (id,txt,txthex) VALUES (3, 'Auflösungsvertrag','4175666cc3b673756e677376657274726167');

Applying fix

/* Running oneshot fix on row 2 only */                                                      
UPDATE test 
SET txt=REPLACE(txt, UNHEX('6fcc88'), UNHEX('c3b6'))
WHERE id=2

Check Query

SELECT  id, txt, txthex hex_original, 
        CAST(UNHEX(txthex) AS CHAR(30)) unexed_original , 
        HEX(txt) hex_replaced
FROM test;
id txt hex_original unexed_original hex_replaced
1 Auflösungsvertrag 4175666c6fcc8873756e677376657274726167 Auflösungsvertrag 4175666C6FCC8873756E677376657274726167
2 Auflösungsvertrag 4175666c6fcc8873756e677376657274726167 Auflösungsvertrag 4175666CC3B673756E677376657274726167
3 Auflösungsvertrag 4175666cc3b673756e677376657274726167 Auflösungsvertrag 4175666CC3B673756E677376657274726167
user_41975050
user_50523570
Related