HASHBYTES (Transact-SQL)

HASHBYTES (Transact-SQL)
SQL Server 2005

SQL Server 2008 Books Online (October 2009)

Returns the MD2, MD4, MD5, SHA, or SHA1 hash of its input.

Topic link icon Transact-SQL Syntax Conventions

Syntax

HashBytes ( '<algorithm>', { @input | 'input' } )

<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1

Arguments

'<algorithm>': Identifies the hashing algorithm to be used to hash the input. This is a required argument with no default. The single quotation marks are required.

@input: Specifies a variable containing the data to be hashed. @input is varchar, nvarchar, or varbinary.

' input ': Specifies a string to be hashed.

Return Value

varbinary (maximum 8000 bytes)

Remarks

Allowed input values are limited to 8000 bytes. The output conforms to the algorithm standard: 128 bits (16 bytes) for MD2, MD4, and MD5; 160 bits (20 bytes) for SHA and SHA1.

Examples

The following example returns the SHA1 hash of the nvarchar data stored in variable @HashThis.

DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf');
SELECT HashBytes('SHA1', @HashThis);
GO

Other Resources

Choosing an Encryption Algorithm

Help and Information

Getting SQL Server 2008 Assistance

Community Content

Error if >8000

Added by:Thomas Lee

If the input is longer than 8000, it will not be truncated, but an error ("String or binary data would be truncated.") is raised.

MD2, MD4, MD5 Considered Insecure

Added by:Mike C_1

The MD2, MD4 and MD5 hash functions are widely considered insecure hash functions, as it has been proven that meaningful hash collisions can be generated for them. It's recommended to avoid these hash functions for secure applications (such as hashing passwords, etc.) It's recommended instead that you use the more secure SHA family of hash functions for secure hash requirements.

SHA = SHA1

Added by:Mike C_1

The SHA and SHA1 identifiers are equivalent to this function. There may be some confusion as the term "SHA" without a number behind it is often associated with the original SHA function (sometimes referred to as "SHA-0").

INPUT LONGER THAN 8000 BYTES

Added by:Mike C_1

Truncation effect on input longer than 8,000 bytes.

  DECLARE @v varchar(max);
  SET @v = REPLICATE ('A', 9000); -- Input longer than 8,000 bytes
  SELECT HashBytes('SHA1', @v);

SET @v = REPLICATE ('A', 8000); -- Input exactly 8,000 bytes
  SELECT HASHBYTES('SHA1', @v);

The output, shown below, is the same hash value twice. This indicates that the two strings that were actually hashed were exactly the same.

0xD2967D6425E56C18BA979EEFB4E0DBD1269D9BC9

0xD2967D6425E56C18BA979EEFB4E0DBD1269D9BC9