MD5 Hashing in SQL Server

Question

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?

i think passwordgenerator.net is wrong. The text 'tést' has md5 'AB176F76FEC779A347D4096384EDB094', and in mssql it says '147ACB11180BB723C38841D4845E207D' . When trying to convert 'test', both give same result! — Luuk
– Luuk, Commented Jun 10, 2019 at 8:56
that web site is correct it give a correct result i try it many time. — alex-sh
– alex-sh, Commented Jun 10, 2019 at 9:08
@alex-sh passwordsgenerator.net/md5-hash-generator calculates md5 sum using utf-8 encode string. How do you use this generator in your application? — Zhorov
– Zhorov, Commented Jun 10, 2019 at 10:23

Zhorov · Accepted Answer · 2019-06-10 19:50:24Z

2

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.

edited Jun 10, 2019 at 19:50

answered Jun 10, 2019 at 10:33

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

WonderWorker Over a year ago

Please can you include the listing for your dbo.ToUTF8 function? I did a search and found some, but the output returned 6010E112D213366938E6C894F1BE9C2A

Zhorov Over a year ago

@Knickerless-Noggins There is a link is the answer. Thanks.

WonderWorker Over a year ago

I still get 6010E112D213366938E6C894F1BE9C2A. Can you double check?

Zhorov Over a year ago

@Knickerless-Noggins Because it's a cyrillic text, my test database collation is Cyrillic_General_CS_AS. With server default collation the output is 6010E112D213366938E6C894F1BE9C2A.

WonderWorker Over a year ago

Phew, I eventually got there.

DECLARE @input NVARCHAR(MAX) = dbo.ToUTF8(N'спасибо(:');SELECT CONVERT(NVARCHAR(32), HASHBYTES('md5', @input COLLATE Cyrillic_General_CI_AI), 2)

works. Thanks for your help, Zhorov.

Luuk · Accepted Answer · 2019-06-10 09:10:45Z

1

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?

answered Jun 10, 2019 at 9:10

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

1 Comment

alex-sh Over a year ago

the output with out 0x

Collectives™ on Stack Overflow

MD5 Hashing in SQL Server

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related