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!') 
--0x63503D3117AD33F941D20F57144ECE64

Select HashBytes('MD4', 'Hello world!') 
--0x0D7A9DB5A3BED4AE5738EE6D1909649C

Select HashBytes('MD5', 'Hello world!') 
--0x86FB269D190D2C85F6E0468CECA42A20

Select HashBytes('SHA', 'Hello world!') 
--0xD3486AE9136E7856BC42212385EA797094475802

Select HashBytes('SHA1', 'Hello world!') 
--0xD3486AE9136E7856BC42212385EA797094475802


MSDN:
http://msdn2.microsoft.com/en-us/library/ms174415.aspx

Comments (6) -

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

Sam

Good to know. I love to learn new things.

Sam

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

pankaj

Please send interview questions and answers i am searching for job

pankaj

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

Kurt

How do i de-encrypt the value?

Kurt

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

Anastasiosyal

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

Anastasiosyal

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

henrytan

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?

henrytan

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

J.

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

J.

Comments are closed