profile for TheDaveJay at Stack Overflow, Q&A for professional and enthusiast programmers

Friday, 22 July 2011

Hash matching in .net and SQL

I was faced with a problem where passwords were being stored in sql as a hash, and each time a new password was needed, I had to write code to generate a new password --FAIL!

Anyway, I decided to sort this out.

 MSSql offers the Hashbytes function to create a hashed byte array from a string, as follows:

SELECT HashBytes('MD5', 'teststringtohash')

That was easy! Now, since the passwords are being stored as a string, all you have to do to convert it is the following:

SELECT CONVERT(NVARCHAR(32),HashBytes('MD5', 'teststringtohash'),2)

Which returns '5B076E2B8572A2A80645BFD1D4046D23'.

Now, to generate the same hash in c# so we can compare to the database, all you have to do is use the HashAlgorithym class, compute the hash and convert it to a hexstring. Since we are using a MD5 hash in SQL, the HashAlgorything will also have to be a MD5 hash. See code below:

1) Create the hash algorithym.

HashAlgorithm alg = MD5.Create(); 
2)Compute the hash, and use the BitConverter.ToString to get the hex string. The hex string contains '-' between each byte, so remove them to match sql! 
byte[] hashedData = alg.ComputeHash(Encoding.UTF8.GetBytes("teststringtohash"));            
string password = BitConverter.ToString(hashedData);
password = password.Replace("-", ""); 

All you have to do from here is make sure that password == sqlpassword :)