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 protected]')

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 [email protected] (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

10
  • 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 protected]') 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, 2020 at 4:26
  • Is asking the purpose of an md5 hash of email going to cause embarrassment?
    – danblack
    Feb 5, 2020 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, 2020 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, 2020 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, 2020 at 12:36

2 Answers 2

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('[email protected]');
+----------------------------------+
| MD5('[email protected]')        |
+----------------------------------+
| c31383bbaee83e9704fd7b0ad52581ca |
+----------------------------------+
1 row in set (0.00 sec)

SELECT MD5(CONCAT('[email protected]', UNHEX('0D')));
+------------------------------------------------+
| MD5(CONCAT('[email protected]', UNHEX('0D'))) |
+------------------------------------------------+
| c62804a6d7122356cf5f261ebf860684               |
+------------------------------------------------+
1 row in set (0.00 sec)
1
  • OMG ! You got it !
    – Azer Tyker
    Feb 8, 2020 at 23:59
0

Maybe:

UPDATE mytable SET email_md5 = MD5(email) WHERE email_md5 != MD5(email)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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