CODEWITHSUNDEEP
phpsqlmd5
baked
baked

Reputation: 245

MD5 Hashes not matching

I am trying to match a md5 has (generated through php) to its original value in a SQLExpress database.

I am using the following function in my SQL query

master.sys.fn_varbintohexsubstring(0, HASHBYTES('MD5', 'ID'), 1, 0)

Where 'ID' is the field in the database.

However they both seem to return different values for the md5 hash. I have been using '12290' as a static value to test this.

php md5() returns: 0bd81786a8ec6ae9b22cbb3cb4d88179

The following SQL Statement returns the same output:

DECLARE @password VARCHAR(255)
SET @password = master.sys.fn_varbintohexsubstring(0, HASHBYTES('MD5', '12290'), 1, 0)
SELECT @password

Yet when I run the following statement from the table:

SELECT ID,  master.sys.fn_varbintohexsubstring(0, HASHBYTES('MD5', CONVERT(NVARCHAR(255), ID)), 1, 0) AS temp
FROM Clients
ORDER BY ID ASC

The 'temp' value matching to the 'ID' value of 12290 returns: 1867dce5f1ee1ddb46ff0ccd1fc58e03

Any help on the matter would be much appreciated!

Thanks

Upvotes: 2

Views: 4417

Answers (3)

Susam Pal
Susam Pal

Reputation: 34294

Python helped me to help you.

>>> from hashlib import md5
>>> md5('1\x002\x002\x009\x000\x00').digest().encode('hex')
'1867dce5f1ee1ddb46ff0ccd1fc58e03'

NVARCHAR is Unicode type and it seems from the above experiment that '12990' is stored as UTF-16LE in your database: '1\02\09\09\00\0'.

Assuming that the data encoding in the PHP is UTF-8 data and you don't want to change the existing data in the database, this is how you can fix your PHP script:

<?php
    $password = '12290';
    $hash = md5(mb_convert_encoding($password, 'UTF-16LE', 'UTF-8')) . "\n";
    echo $hash;
?>

Output:

susam@swift:~$ php utf16le-hash.php
1867dce5f1ee1ddb46ff0ccd1fc58e03

In case the data in PHP is in some other encoding such as ASCII, ISO-8859-1, etc. you can change the third argument to mb_convert_encoding accordingly. The list of all supported encodings is available at: http://www.php.net/manual/en/mbstring.supported-encodings.php

Also, see http://www.php.net/manual/en/function.mb-convert-encoding.php

Upvotes: 3

benosteen
benosteen

Reputation: 1366

I don't have SQL server to test this on, but the CONVERT command might be creating the NVARCHAR with 240-odd trailing blanks (as you have specified NVARCHAR(255))

Try setting the NVARCHAR to the length of the ID to test:

ARE @password VARCHAR(255)
SET @password = master.sys.fn_varbintohexsubstring(0, HASHBYTES('MD5', CONVERT(NVARCHAR(5), '12290')), 1, 0)
SELECT @password

Try with different lengths in the CONVERT - is there any difference?

Upvotes: 0

Jon
Jon

Reputation: 437744

One of two things is most likely the problem:

  1. Either the ID column in that row has a value not exactly equal to '12290' (e.g. extra whitespace)
  2. Or the CONVERT function produces such a value

In any case, a standard debugging approach would be to use an SQL query to SELECT the string lengths of that ID field and the return value of CONVERT; if either is not equal to 5, you found the error.

Alternatively you can perform a dump of the table in question including data, and look at the generated INSERT statement to see what the database says the value in that column is.

Upvotes: 0

Related Questions