CODEWITHSUNDEEP
sql-server
alex-sh
alex-sh

Reputation: 31

MD5 Hashing in SQL Server

The SQL MD5 hashing function returns a different result to others e.g. passwordsgenerator.net/md5-hash-generator

First of all I pick a source string at random, which in this case is:

спасибо(:

Providing the Miracle salad MD5 generator with my source string returned the following result: 

e1295bb206823340c694b795c17eb4c5

The query I used to generate an MD5 hash in SQL Server is as follows:

SELECT CONVERT(VARCHAR(32), HASHBYTES('md5', 'спасибо(:'), 2)

...which returned the following result:

C4E6F99E8CD676020DC7D3E13612D51A

Please can someone help me figure out a) why are they different and b) an Sql query that will return the same result as passwordsgenerator.net?

Upvotes: 2

Views: 9527

Answers (2)

Zhorov
Zhorov

Reputation: 29993

Explanations

One possible explanation is that this online tool calculates the MD5 hash of an UTF8 encoded string. I'm able to reproduce this behaviour with PHP and T-SQL.

Possible solutions are:

  • convert your UTF-8 text to cyrillic text in your application and after that use HASHBYTES

  • encode your cyrillic text to UTF-8 in the database using functions like ToUTF. I use this function just for the test, but I suggest to make the conversion in your application.

Test, using PHP and conversion from UTF8 to CP1251

PHP:

<?php
$input = "спасибо(:";
$input = iconv("UTF8", "CP1251", $input);
echo md5($input);
?>

T-SQL:

DECLARE @input varchar(32) = 'спасибо(:'
SELECT CONVERT(varchar(32), HASHBYTES('md5', @input), 2)

Output:

C4E6F99E8CD676020DC7D3E13612D51A

Test, using online generator and UDF

T-SQL:

DECLARE @input varchar(32) = 'спасибо(:'
SELECT CONVERT(VARCHAR(32), HASHBYTES('md5', dbo.ToUTF8(@input)), 2)

Output:

E1295BB206823340C694B795C17EB4C5

Notes

Default collation for test database is Cyrillic_General_CS_AS.

Upvotes: 2

Luuk
Luuk

Reputation: 14958

The output of this 

select CONVERT(NVARCHAR(64),HASHBYTES('md5','спасибо(:'),2), HASHBYTES('md5','спасибо(:')

is

6010E112D213366938E6C894F1BE9C2A                                 0x6010E112D213366938E6C894F1BE9C2A

so the output of the function includes the '0x', see How can I get a SQL Server md5 hash to match a previous php md5 hash?

Upvotes: 1

Related Questions