Write T-SQL functionality in .NET

Question

I have this bit of t-sql code

set @UrlHash = convert(bigint, hashbytes('MD5', @Url))

I wonder if I can write a function in C# which returns me the exact same hash as the line above without going to SQL.

Is it possible?

Requirement is that C# MUST created exact same hash.

Curious given that an MD5 hash is 128-bit and a bigint is 64-bit how does this even work? — Lloyd, Feb 26 '13 at 13:48
There is an MD5 class which implements the MD5 hash algorithm. That sounds like what your looking for. — DFord, Feb 26 '13 at 13:50
ok. but how about the convert bigint piece? even if I get a byte array from the MD5 hash... how will the convert and bigint piece workout? — Knows Not Much, Feb 26 '13 at 14:12
Looks like it might be an endian issue: http://stackoverflow.com/q/8467072/21727 — mbeckish, Feb 26 '13 at 15:50

score 7 · Accepted Answer · answered Feb 27 '13 at 08:38

The select

SELECT CONVERT(BIGINT, HASHBYTES('MD5', 'http://stackoverflow.com'))

will yield the following result:

-3354682182756996262

If you now try to create a MD5 hash in C#

MD5 md5 = MD5.Create();
byte[] textToHash = Encoding.UTF8.GetBytes("http://stackoverflow.com");
byte[] result = md5.ComputeHash(textToHash); 
long numeric = BitConverter.ToInt64(result, 0);

numeric will be 8957512937738269783.

So what's the issue (besides the fact that a MD5 hash is 128-bit and BIGINT/long is just 64-bit)?

It's an endian issue (the bytes are in the wrong order). Let's fix it using the BitConverter class and reverse the bytes as needed:

MD5 md5 = MD5.Create();
byte[] textToHash = Encoding.UTF8.GetBytes("http://stackoverflow.com");
byte[] result = md5.ComputeHash(textToHash); 

if (BitConverter.IsLittleEndian)
    Array.Reverse(result);

long numeric = BitConverter.ToInt64(result, 0);

numeric is now -3354682182756996262 as you want.

I missed that mbeckish already found a possible duplicate: [SQL Server varbinary bigint with BitConverter.ToInt64 values are different](http://stackoverflow.com/questions/8467072/sql-server-varbinary-bigint-with-bitconverter-toint64-values-are-different) — sloth, Feb 27 '13 at 09:03

Antonio Bakula · Answer 2 · 2013-02-26T14:13:45.177

0

You should use MD5 class, here is the example from http://blogs.msdn.com/b/csharpfaq/archive/2006/10/09/how-do-i-calculate-a-md5-hash-from-a-string_3f00_.aspx, with output as int 64 :

public int64 CalculateMD5Hash(string input)
{
    // step 1, calculate MD5 hash from input
    MD5 md5 = System.Security.Cryptography.MD5.Create();
    byte[] inputBytes = System.Text.Encoding.ASCII.GetBytes(input);
    byte[] hash = md5.ComputeHash(inputBytes);

    return BitConverter.ToInt64(hash, 0);
}

edited Feb 26 '13 at 14:13

answered Feb 26 '13 at 13:50

Antonio Bakula

20,445
6
75
102

and what about the covert function? we cannot cast the byte[] into a Int64. can we? – Knows Not Much Feb 26 '13 at 14:10
but the hashes don't match if you pass http://www.google.com to the code about (instead of ASCII I use unicode) it produces -5931442742085820074 whereas t-sql produces -7038863548646352319 select convert(bigint, hashbytes('MD5', N'http://www.google.com') – Knows Not Much Feb 26 '13 at 14:39
Sorry the hashes match till at the level of MD5 creation... but when we convert to bigint ... at that point of time the numbers differ. – Knows Not Much Feb 26 '13 at 14:48

score -1 · Answer 3 · answered Feb 26 '13 at 13:50

-1

Isn't an MD5 hash standard? Can't you use a standard MD5 C# implementation? What about using the code in here?

answered Feb 26 '13 at 13:50

reverse_engineer

4,239
4
18
27

Write T-SQL functionality in .NET

3 Answers3