0

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

  • I'm afraid that I'm having difficulty understanding your question. You say that MySQL inserts some wrong hashes in the table and then that if you check with using SELECT MD5('email@email.com') you get the correct answer. Can you show us a couple of examples where your system is working and where it is not working so that we can get a better idea of your problem? – Vérace Feb 5 at 4:26
  • Is asking the purpose of an md5 hash of email going to cause embarrassment? – danblack Feb 5 at 4:34
  • @danblack - why is wanting to anonymise one's data a potential source of embarrassment? One might want to prove to a client that one has a certain number of emails but not want to reveal those ids before a contract is signed for example... – Vérace Feb 5 at 5:02
  • @Vérace It's exactly that. We have to find duplicate users from the client for split promotion between existing customers. – Azer Tyker Feb 5 at 12:03
  • Fair enough - now we know why you want to do it. But now, I'd really like to know what EXACTLY it is you want to do? Some examples with data would be good - use a fiddle for example! Also, please specify your MySQL version - they've introduced lots of new features so it's (very) important! – Vérace Feb 5 at 12:36
1

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)
| improve this answer | |
  • OMG ! You got it ! – Azer Tyker Feb 8 at 23:59
0

Maybe:

UPDATE mytable SET email_md5 = MD5(email) WHERE email_md5 != MD5(email)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.