Match a hash created in C# with sql

Question

I have a method used to generate a hash:

public static string GetMD5Hash(string input)
    {
        System.Security.Cryptography.MD5CryptoServiceProvider x = new System.Security.Cryptography.MD5CryptoServiceProvider();
        byte[] bs = System.Text.Encoding.UTF8.GetBytes(input);

        bs = x.ComputeHash(bs);

        System.Text.StringBuilder s = new System.Text.StringBuilder();

        foreach (byte b in bs)
        {
            s.Append(b.ToString("x2").ToLower());
        }
        return s.ToString();
    }

I then save that hash in a varchar(255) column. Knowing what the original input string was, would it be possible to to arrive at the same hash value stored in the varchar(255) column, using sql (2005)??

I have tried like crazy using different data types, conversions and the hashbytes() function, but have not been able to get close.

Example of my failed attempt :(

select convert(varchar, hashbytes('MD5', convert(varbinary(200), '<censored>',0)))

Answer 1

The problem here is that x2 is getting the hex encoding of the data, where-as convert is getting your server's configured decoding of the bytes (which isn't really valid, since that data is not text-based). Very different things. If you keep as varbinary and compare to a byte[] you should be fine.

If you are using SQL Server 2008, you can also use:

select convert(varchar(32), hashbytes('MD5', convert(varbinary(200),
           '<censored>',0)), 2)

to get the hex-encoded version (you can use 1 instead of 2 to get a leading 0x)

Answer 2

from #c with "bleepbloop" : 04d3f95947702213e23730a0e8fac6c3

Then

select convert(varchar(32), hashbytes('MD5', 'bleepbloop'), 2)

>> 04D3F95947702213E23730A0E8FAC6C3

Or You could just store & compare as binary.