MD5 hash is Different

Question

On sql server : Out put : 0x5C8C8AAFE7AE37EA4EBDF8BFA01F82B8

SELECT HASHBYTES('MD5', convert(varchar,getdate(),112)+'mytest@+')

On JavaScript : Out put : 5c8c8aafe7ae37ea4ebdf8bfa01f82b8

        //to get Md5 Hash bytes
        vm.getMd5Hashbytes = function () {
            var currentDate = moment().format('YYYYMMDD');
             var md5Hash = md5.createHash(currentDate + 'mytest@+');
             return md5Hash;
        }

angular-md5 module

Q : Can you tell me why this difference ? SQL server shows 0x as prefix.Why ?

They are the same but sql is varbinary hence the `0x`, if you converted that to a varchar with formatting it would be the same. — Igor, Aug 18 '16 at 16:38
`SQL server shows 0x as prefix.Why` <= because its a different data type. See the documentation for [HashBytes](https://msdn.microsoft.com/en-us/library/ms174415.aspx). That is how type `varbinary` is displayed visually — Igor, Aug 18 '16 at 16:40
@Igor My webapi accepts sql server hash.But I'm requesting it through JavaScript file.So can you tell me how to fix this ? — Sampath, Aug 18 '16 at 16:41
[SQL Server converting varbinary to string](http://stackoverflow.com/a/12139130/1260204) — Igor, Aug 18 '16 at 16:43
@Igor No,It won't work.B'cos I cannot change the SQL side.I can handle only the JavaScript side.So if I add the `0x` as prefix will it OK for all the use cases ? — Sampath, Aug 18 '16 at 16:47
http://stackoverflow.com/questions/2670639/why-are-hexadecimal-numbers-prefixed-with-0x — Alan, Aug 18 '16 at 17:22

score 2 · Answer 1 · edited May 23 '17 at 10:33

This is purely a formatting issue. Both versions are producing an identical sequence of bytes. SQL Server and node just have different conventions when it comes to presenting these bytes in a human readable format.

You can get similar formatting by specifically telling SQL Server how to format your binary data

declare @hashAsBinary varbinary(max)
declare @hashAsText char(32)
set @hashAsBinary = HASHBYTES('MD5', '20160818mytest@+')
set @hashAsText = LOWER(CONVERT(varchar(max), @hashAsBinary, 2))
select @hashAsText

Which outputs:

5c8c8aafe7ae37ea4ebdf8bfa01f82b8

See SQL Server converting varbinary to string

Igor · Accepted Answer · 2016-08-18T17:41:44.303

I am not sure how else to explain it but it will take more space than a comment allows for so I will post it as an answer.

Look at the source code that you are referencing. At the end (lines 210 and 212) you will see it converts the binary value to a hex string (and then to lower case which does not matter unless you opt for a string comparison at the end). End result = your JavaScript library returns a representation using the type string formatted as hex.

Your Sql function HASHBYTES on the other hand produces a varbinary typed result (which is a different type than string (varchar)).

So you have 2 different data types (each living on their own space as you have not pulled one to the other). You never mention where you are doing the comparison, ie: on the database or are you pulling from the database to script. Either way to do a comparison you need to convert one type so you are either comparing 2 strings types OR comparing two binary types. If you do not compare similar types you will get unexpected results or run time exceptions.

If you are comparing using strings AND in JavaScript then look at your library that you are referencing, it already has a call named wordToHex, copy and paste it and reuse it to convert your Sql result to a string and then do a string comparison (do not forget to compare case insensitive or also make it lower case).

Edit

WebApi is black box for me.It is a 3rd party service.I just need to send the security token as mentioned above.

Assuming that the type accepted by that web api is byt[] appending 0x to your string in javascript and then sending it to the web api should work as in the web api will then translate the incoming parameter as a byte array and execute the comparison using the correct types. As this is a black box there is no way to know for certain unless you either ask them if the accepted type is indeed a byte array or to test it.

Thanks for the explanation.But what if I put a `0x` prefix manually on the JavaScript file ? Will it not work all the time or what ? — Sampath, Aug 18 '16 at 17:27
@Sampath - It depends, how is your application structured? Where is the comparison taking place? Are you sending a string from javascript to the Sql Server as a parameter in a query and doing a comparison there? Or are you pulling in the varbinary from sql server and doing a comparison in javascript? You never really clarified what/where the code is doing the comparison. So just prefixing additional string to the existing string would still not help if the other value is a word/binary value. — Igor, Aug 18 '16 at 17:29
Actually WebApi is black box for me.It is a 3rd party service.I just need to send the security token as mentioned above.They have sent me how to generate the token by using SQL server.I have generated it by using JavaScript and then send it to WebApi. WebApi doesn't accept my request without leading `0x`.No problem about the case-sensitive here. — Sampath, Aug 18 '16 at 17:36
@Sampath - In that case yes, prefix it with `0x` and send it to Web API as the Web API probably accepts a type `byte[]` that will automatically translate the incoming string to a byte array and then do the comparison using 2 byte arrays. — Igor, Aug 18 '16 at 17:38

MD5 hash is Different

2 Answers2

Edit