Hi and welcome to my blog. Im Tasos, a software engineer working in the UK. This is where i share some of my findings related with SQL, c#, asp.net and javascript with you. I hope you find something helpful and Im looking forward to your feedback!

Recent Comments

Popular Posts

Recent Posts

Archives

Post Categories

Blog Stats

  • Posts: 14
  • Comments: 55
  • Trackbacks: 9
  • Articles: 1

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

Wednesday, April 23, 2008 8:03 PM,

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

Share this post!
digg it
Kick it on DotNetKicks.com

Comments

# re: Quick Tip: Use Hashbytes to create a Hash in TSQL in SQL Server, Posted by Sam on 4/23/2008 11:14 PM

Good to know. I love to learn new things.

# re: Quick Tip: Use Hashbytes to create a Hash in TSQL in SQL Server, Posted by pankaj on 5/22/2008 11:42 AM

Please send interview questions and answers i am searching for job

# re: Quick Tip: Use Hashbytes to create a Hash in TSQL in SQL Server, Posted by Kurt on 7/9/2008 10:24 AM

How do i de-encrypt the value?

# re: Quick Tip: Use Hashbytes to create a Hash in TSQL in SQL Server, Posted by Anastasiosyal on 7/12/2008 1:38 PM

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

# re: Quick Tip: Use Hashbytes to create a Hash in TSQL in SQL Server, Posted by henrytan on 8/6/2008 3:29 AM

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?

# re: Quick Tip: Use Hashbytes to create a Hash in TSQL in SQL Server, Posted by J. on 10/22/2008 8:02 AM

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

Comments

Title: *
Name: *
Email: (never displayed)
Website:
Comment: *  
Please add 7 and 4 and type the answer here: