Replicate MySQL's MD5() Function in SQL Server 2005 Using HASHBYTES()

Patrick

MySQL and PostgreSQL's MD5() functions are nice, simple, ways to hash your data. Unfortunately, SQL Server 2005 and up (sorry, in order to get it working in SQL Server 2000, you'll need to try a stored proceedure), you can use the HASHBYTES() function.

In a recent SQL Server port of the Actions module for Drupal 5, I ran into the following MySQL query:

SELECT aid FROM {actions} WHERE MD5(aid) = '%s'

In order to get this working in SQL Server 2005, we have to first use the HASHBYTES() function instead:

SELECT aid FROM {actions} WHERE HASHBYTES('MD5',aid) = '%s'

This technically does the trick, BUT, the catch is that SQL Server prepends "0x" onto the result hash. I believe this is because it wants to specify the base of the result (in this case, hexadecimal). In my case, I needed to compare this result with a hash that did not have the 0x prefix. To do so, we need to drop the prefix using SUBSTRING(). Since you can't manipulate binary data directly, I used an "undocumented" SQL Server function, sys.fn_sqlvarbasetostr(), to convert the hash to a string.

SELECT aid FROM {actions} WHERE SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',$1)),3,32) = '%s'

The 32 in the SUBSTRING function refers to the 32 characters in the MD5 hash.