Quick Tip: Use Hashbytes to create a Hash in TSQL in SQL Server

Just a quick tip.
I was unaware until recently that SQL Server 2005 has nicely built in support for hashing and it is called hashbytes

HashBytes ( '<algorithm>', { @input | 'input' } )
<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1

Parameters are the algorithm you wish to hash to and the input string to hash.
Here is a sample along with the return values commented in the next line:

Select HashBytes('MD2', 'Hello world!') 

Select HashBytes('MD4', 'Hello world!') 

Select HashBytes('MD5', 'Hello world!') 

Select HashBytes('SHA', 'Hello world!') 

Select HashBytes('SHA1', 'Hello world!') 


Comments (6) -

4/23/2008 5:14:40 PM #


Good to know. I love to learn new things.


5/22/2008 5:42:17 AM #


Please send interview questions and answers i am searching for job


7/9/2008 4:24:08 AM #


How do i de-encrypt the value?


7/12/2008 7:38:59 AM #


Hashing is not the same as encryption. When you hash something, you cannot recover it (theoretically). I say theoretically because there are is a technique known as rainbowcracking that works by using a large database containing many hashes for lots of character combinations that can be used to potentially recover a hash value.

As an antimeasure to the above attack when hashing you should always use some sort of long prefix. This prefix is called 'salt' and
since these sort of databases usually contain character combinations of up to 20 chars or so and with limited character sets.

A good salt will use symbols and characters from other languages so your code may look something like:

HashBytes('SHA1', '!AveryLongKeyknown^AsSaltToprεvεntRainbowAttacks!' + @Password)

Suppose you have saved the password to a user table, if you want to check if a user has supplied a valid password then you would use something like:

Select * from Users where username = @username and password = HashBytes('SHA1', '!AveryLongKeyknown^AsSaltToprεvεntRainbowAttacks!' + @SuppliedPassword)

As you can see, hashing is one way and you do not need to know the users password to validate him.  When the data is hashed it becomes unrecoverable so this is not a useful strategy for creditcard number storing for example. Credit card numbers need to be encrypted, so that they can be unencrypted before they are used


8/5/2008 9:29:11 PM #


I tried to insert data rows into a temp table,
with and without apply hashbytes to a column.
Total rows: 200k which is not a big figure

I found that the performance is slow down about 20% cause by this function. Any idea how to improve it?


10/22/2008 2:02:14 AM #


How to encrypt string by SHA-512 in T-SQL? I didn't find it.


Comments are closed