HASHBYTES (Transact-SQL)

HASHBYTES (Transact-SQL)

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

HASHBYTES (Transact-SQL)

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

Tags

: Add a tag

Community Content

Add new content

Annotations

Error if >8000

Omer van Kloeten ... Thomas Lee | Edit | Show History

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

Tags

: contentbug? (x) Add a tag

Flag as ContentBug

MD2, MD4, MD5 Considered Insecure

Mike C_1 | Edit | Show History

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.

Tags

: Add a tag

Flag as ContentBug

SHA = SHA1

Mike C_1 | Edit | Show History

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").

Tags

: Add a tag

Flag as ContentBug

INPUT LONGER THAN 8000 BYTES

Mike C_1 | Edit | Show History

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

Tags

: Add a tag

Flag as ContentBug