SQL 2005 MD5 Hash and C# MD5 Hash

Question

I currently have a legacy database (SQL 2005) that generates hash strings for tokens. It does it like this...

DECLARE @RowID INT
DECLARE @hashString VARCHAR(128)

SET @RowID = 12345
SET @salt= 0xD2779428A5328AF9

SET @hashBinary = HASHBYTES(('MD5', @salt + CAST(@RowID AS VARBINARY(30)))
SET @hashString = sys.fn_varbintohexstr(@hashBinary)

If I execute this, I my hash string looks like this: "0x69a947fd71b853485007f0d0be0763a5"

Now, I need to replicate the same logic in C# so I can remove the database dependency for generating these hashes, and it has to be backward compatible.

I have implemented in C# like this:

byte[] saltBytes = BitConverter.GetBytes(0xD2779428A5328AF9);
byte[] pidBytes = BitConverter.GetBytes(12345);

byte[] bytesToHash = new byte[saltBytes.Length + pidBytes.Length];

for (int i = 0; i < saltBytes.Length; i++)
{
    bytesToHash[i] = saltBytes[i];
}

for (int i = 0; i < pidBytes.Length; i++)
{
    bytesToHash[saltBytes.Length + 1] = pidBytes[i];
}

MD5CryptoServiceProvider hasher = new MD5CryptoServiceProvider();
byte[] hashedBytes = hasher.ComputeHash(bytesToHash);

string hashString = BitConverter.ToString(hashedBytes).ToLower().Replace("-", "");

The problem is, my C# implementation generates this hash: "715f5d6341722115a1bfb2c82e4421bf"

They are obviously different.

So, is it possible to make the match consistently?

Answer 1

Looks to me like there's a bug in your second loop. Try changing the "+ 1" to "+ i"...

for (int i = 0; i < pidBytes.Length; i++)
{
    // the line below just overwrites the same position multiple times
    // bytesToHash[saltBytes.Length + 1] = pidBytes[i];
    bytesToHash[saltBytes.Length + i] = pidBytes[i];
}

In your example, you're just overwriting the same position in the array multiple times, instead of setting each item from that point forward.

Answer 2

I've solved the problem:

If I do this in SQL:

DECLARE @Value INT
SET @Value = 12345

SELECT sys.fn_varbintohexstr(CAST(@Value AS VARBINARY(30)))

I get this result: 0x00003039

Now, if I do this in C#:

int value = 12345;
byte[] bytes = BitConverter.GetBytes(value);
Console.Write(BitConverter.ToString(bytes))

I get this result: 39-30-00-00

The bytes appear to be in reverse order. Hence, once I apply these byte arrays to the MD5 hasher, I get distinctly different hash values.

If I reverse the C# byte array before putting it through the MD5 Hasher, I get the same hash generated by SQL.