Home Library Learn Downloads Support Community
Sign in | United States - English | |
0 out of 2 rated this helpful - Rate this topic

HASHBYTES (Transact-SQL)

SQL Server 2008

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

Topic link iconTransact-SQL Syntax Conventions

Copy 

HashBytes ('<algorithm>', { @input | 'input' } )<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1
'<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.

varbinary (maximum 8000 bytes)

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.

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

Copy 
DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf');
SELECT HashBytes('SHA1', @HashThis);
GO
Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
SHA = SHA1
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")   .
History
Converting Binary Data to Hexadecimal String

Function based on:
http://support.microsoft.com/kb/104829

CREATE function [dbo].[hexchar](
@binvalue varbinary(255)
) returns varchar(255)
as begin
declare @charvalue varchar(255)
declare @i int
declare @length int
declare @hexstring char(16)
select @charvalue = ''
select @i = 1
select @length = datalength(@binvalue)
select @hexstring = '0123456789ABCDEF'
while (@i <= @length)
begin
declare @tempint int
declare @firstint int
declare @secondint int
select @tempint = convert(int, substring(@binvalue,@i,1))
select @firstint = floor(@tempint/16)
select @secondint = @tempint - (@firstint*16)
select @charvalue = @charvalue + substring(@hexstring, @firstint+1, 1) + substring(@hexstring, @secondint+1, 1)
select @i = @i + 1
end
return @charvalue
end

History
REPLICATE Truncates not HashBytes

I'm siting the following MSDN article on REPLICATE(): http://msdn.microsoft.com/en-us/library/ms174383.aspx

REPLICATE() truncates its VARCHAR returns to 8000 characters. Consider the following code.

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


The output is as follows:

VarcharLength        HashValue
8000                0xD2967D6425E56C18BA979EEFB4E0DBD1269D9BC9

VarcharLength        HashValue
8000                0xD2967D6425E56C18BA979EEFB4E0DBD1269D9BC9


You will see that the hash values are the same because the input is truncated to 8000 characters by REPLICATE() and that it is not HashBytes() that is truncating the value.

To see the error for yourself try the following code sample:

DECLARE @v varchar(max);
SET @v = REPLICATE ('A', 8000);
SET @v = @v + 'A';
SELECT HashBytes('SHA1', @v)


To avoid the 'String or binary data would be truncated.' error, make sure you trap for any VARCHAR whose LEN is greater than 8000.

History
Documentation should provide better descriptions of the following:
  1. What *IS* a HASHBYTE?
  2. Why would I use it?
  3. What the heck are MD2, MD4, MD5, SHA and SHA1?

I ran across a comment about hashbytes and didn't know what it was (yes, I'm a SQL programmer) and so I looked it up here, and I still don't know what it is!

History
How do you determine how many bytes are needed?
The return type is varbinary (maximum 8000 bytes), but how many bytes do I actually need to store, say, an encrypted 15-character password? How big is the output compared with the input? Reading the remarks is only confusing me more. It seems to imply the hash created is the same length regardless of the input, but I don't think that makes sense.
History
The documentation should provide descriptions of the different algorithms
This documentation is incomplete without providing descriptions or at least links to descriptions of the different algorithms.
History
INPUT LONGER THAN 8000 BYTES

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
History
MD2, MD4, MD5 Considered Insecure
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.
History
Error if &gt;8000
If the input is longer than 8000, it will not be truncated, but an error ("String or binary data would be truncated.") is raised.
History