Friday, October 4, 2013

Hashbytes sqlserver

HASHBYTES(), as the name implies, is a function or algorithm that generates a hash from some input. If you're not familiar with what a hash is, here's a good working definition for a hash function:
hash function - A hash function takes in data and returns back a fixed length block of bits such that any change to the data should result in a different block.

HASHBYTES() is actually a function which provides access to several hashing algorithms. In the earlier versions, it supports MD2, MD4, MD5, SHA, SHA1 algorithms and these algorithms are limited up to 20 bytes only.
In SQL Server 2012, we have an enhancement in this function and now it supports SHA2_256, SHA2_512 algorithms that can generate 32 and 64 bytes hash codes for the respective input.
SQL Server 2005 and up have the following protocols (how you specify them in HASHBYTES is in parentheses):

    MD 2     (MD2)
    MD 4     (MD4)
    MD 5     (MD5)
    SHA-0   (SHA)
    SHA-1   (SHA1)

SQL Server 2012 introduces these additional hashing algorithms:
    SHA-2 256 bits AKA SHA-256 (SHA2_256)
    SHA-2 512 bits AKA SHA-512 (SHA2_512)

Hashing Algorithms You Should Probably Avoid
Since a hash function will return a fixed length block, obviously, there are a finite set of possibilities for the output. Therefore, there's bound to be different inputs that will result in the same output. We call these situations collisions. What is of concern is if a collision can be engineered fairly quickly/cheaply. This is referred to as a collision attack. If a hashing algorithm is susceptible to this type of attack or other attacks with reasonable resources that either (a) allow you to create an identical hash with different input or (b) figure out the input from the hash, then those algorithms should be avoided. As of the writing of this tip, the following algorithms fall into that list:
    MD 2 - developed in 1989 by Ronald Rivest, it generates a 128-bit hash value. It is susceptible to several attacks and is no longer considered cryptopgraphically secure.
    MD 4 - Also developed by Rivest (1990), it generates a 128-bit hash value like MD 2. And like MD 2, it is susceptible to a couple of attacks and is no longer considered cryptographically secure.
    MD 5 - Developed by Rivest in 1992, it also generates a 128-bit hash value. While we see it used often to "digitally fingerprint" files and documents, it is also considered "broken" and no longer cryptographically secure.
    SHA-0 - SHA-0 was withdrawn shortly after being made public due to a "significant" flaw. It generates a 160-bit hash value. It was replaced by SHA-1.
Hashing Algorithms Which Are Good for Now
If you're not on SQL Server 2012, SHA-1 is the best choice to use.

The SHA-2 algorithm comes in several block sizes, of which SQL Server 2012 implements two:
    SHA-2 256 bit block size (called SHA-256)
    SHA-2 512 bit block size (called SHA-512)


Some simple examples
 :
DECLARE @String varchar(7);
Set @String ='j4info'

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'MD2' AS [Algorithm type]
, HASHBYTES('MD2', @String) as [HashBytes]
, LEN(HASHBYTES('MD2', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'MD4' AS [Algorithm type]
, HASHBYTES('MD4', @String) as [HashBytes]
, LEN(HASHBYTES('MD4', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'MD5' AS [Algorithm type]
, HASHBYTES('MD5', @String) as [HashBytes]
, LEN(HASHBYTES('MD5', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'SHA' AS [Algorithm type]
, HASHBYTES('SHA', @String) as [HashBytes]
, LEN(HASHBYTES('SHA', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'SHA1' AS [Algorithm type]
, HASHBYTES('SHA1', @String) as [HashBytes]
, LEN(HASHBYTES('SHA1', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2012 and above
SELECT 'SHA2_256' AS [Algorithm type]
, HASHBYTES('SHA2_256', @String) as [HashBytes]
, LEN(HASHBYTES('SHA2_256', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2012 and above
SELECT 'SHA2_512' AS [Algorithm type]
, HASHBYTES('SHA2_512', @String) as [HashBytes]
, LEN(HASHBYTES('SHA2_512', @String)) as [Length in Bytes]
GO

Note : Executions of above script in earlier version of SQL Server, it will return NULL value for SHA2_256 & SHA2_512.

No comments:

Post a Comment

web stats