Search code examples
sql-serverhashbytes

Hashbytes function output

I have a strange situation using Hashbytes function of MSSQL 2008 R2.

Select password ,Hashbytes('SHA1', 'sa') returns 0x3608A6D1A05ABA23EA390E5F3B48203DBB7241F7.

Later I manually update my table field to this value, but Where Password = Hashbytes('SHA1', 'sa') doesn't give me any result. I already used varbinary, binary, varchar, nvarchar and char data types. Please suggest what can be the problem and how I can solve the problem. Thanks.

Solution

  • Per the documentation, for SHA-1 you have an output size of "160 bits (20 bytes) for SHA and SHA1."

    Therefore this:

    declare @password varbinary(19); 
set @password = HASHBYTES('SHA1','sa'); 
if @password= HASHBYTES('SHA1','sa') 
    print 'yes'; 
else 
    print 'no'

    returns no, since there isn't enough space to correctly store the result; but this

    declare @password varbinary(20); 
set @password = HASHBYTES('SHA1','sa'); 
if @password= HASHBYTES('SHA1','sa') 
    print 'yes'; 
else 
    print 'no'

    returns yes. If your Password field is binary or varbinary and has room for 20 bytes, you should be OK.

    By the way, if you just specify binary or varbinary, without a length, you get 1 byte, which is generally not what you want.