Hashing Text with SQL

Recently I was faced with the task of hashing some clear text in a database. The database was shared between different systems, some written in classic ASP, some written in C#.NET. I didn’t want to write the same hashing code twice. I then stumbled upon this gem, the HashBytes function in SQL Server:

HashBytes('MD5', 'Hellow World');

The function accepts MD2, MD4, MD5, SHA and SHA1 as inputs and returns the hashed result as binary. If you want to convert the binary to something more readable, apply the SubString function to the result:

DECLARE @BinaryHash binary
DECLARE @TextHash varchar(32)
-- Convert string to binary hash
SET @BinaryHash = HashBytes('MD5', 'Hellow World');
-- Convert binary hash to string
SET @TextHash = SUBSTRING(master.dbo.fn_varbintohexstr(@BinaryHash), 3, 32);
-- Return the hashed string
RETURN @TextHash;
This entry was posted in Tips and Tricks and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s