MySQL – Resolve Encoding Issues

encryptionfunctionsMySQL

I'm using the MD5 hash function of MySQL to hash some data I import into the database from a CSV file.
The issue is that some lines have an incorrect hash (but not all of them).
When I use a query: 

UPDATE mytable SET email_md5 = (SELECT MD5(email))

MySQL inserts some wrong hashes in the table but when I check with using 

SELECT MD5('email@email.com')

I find the correct answer.

Any idea how to check the encoding of the data I import in the database?

UPDATE #1
For this email onachba@i-pcom.com (not existing anymore)
Mysql insert this hash when I using MySQL query (the first in this post) with data in database from the CSV

c62804a6d7122356cf5f261ebf860684

But when I insert the same email with my hands in database and replay the same query I find this hash (and is the correct hash)

c31383bbaee83e9704fd7b0ad52581ca

UPDATE #2
We using mysql 8 and adminer
I feel it is a problem of encoding.
When I select the line for edit it from adminer and save it without any changes the browser recognize it like an email (she becomes clickable link with mailto:) + after this when I replay the query he add the good hash ! !
The row don't recognize like an email in adminer give me wrong hash
but
the lines from adminer recognize like an email have the good hash…
This is f*** creazy !

UPDATE #3
For datatype, it's varchar(200) NULL
With another email where I find a MD5 issue
From MySQL with 

SELECT MD5(email) FROM mytable WHERE id = 'theID';

f6cd93fc748b7c856a74e6037f6e14f5 

SELECT HEX(email) FROM mytable WHERE id = 'theID';

627269636540696775616E6573747564696F2E636F6D0D

Best Answer

There is a Carriage Return (hex 0D) at the end of 

627269636540696775616E6573747564696F2E636F6D0D
                                            ^^

Recommend you trim emails of all "whitespace" before feeding them into your database in any way.

To confirm with your original email:

SELECT MD5('onachba@i-pcom.com');
+----------------------------------+
| MD5('onachba@i-pcom.com')        |
+----------------------------------+
| c31383bbaee83e9704fd7b0ad52581ca |
+----------------------------------+
1 row in set (0.00 sec)

SELECT MD5(CONCAT('onachba@i-pcom.com', UNHEX('0D')));
+------------------------------------------------+
| MD5(CONCAT('onachba@i-pcom.com', UNHEX('0D'))) |
+------------------------------------------------+
| c62804a6d7122356cf5f261ebf860684               |
+------------------------------------------------+
1 row in set (0.00 sec)
Related Question