CODEWITHSUNDEEP
sql-serverhash
khorshid
khorshid

Reputation: 59

Compare Hash Password Using HASHBYTES in MS SQL

I have simple question:

print HASHBYTES('SHA1', 'az09123')

give me:

0xA00592FC3E531C5F7608110F73E8AE4B4F2EA4C3enter

I put it in navchar field called OwnerEn

then I try this:

SELECT * FROM AspNetUsers WHERE OwnerEn =  HASHBYTES('SHA1', 'az09123');

No results, it's empty

but this give me result:

SELECT * FROM AspNetUsers WHERE OwnerEn = '0xA00592FC3E531C5F7608110F73E8AE4B4F2EA4C3';

What is the issue in first sql statement?

Upvotes: 3

Views: 8991

Answers (2)

iamdave
iamdave

Reputation: 12243

As Oscar mentioned in his comment, you are seeing no results because the nvarchar value 0xA00592FC3E531C5F7608110F73E8AE4B4F2EA4C3 is different to the binary value 0xA00592FC3E531C5F7608110F73E8AE4B4F2EA4C3.

Ideally, you would be storing your hash values in an appropriate binary or varbinary data typed column, but if you absolutely cannot change that in your database, you need to convert your nvarchar value to binary or varbinary (or vice versa) in order to actually compare the two values:

Note the 1 at the end of the convert, to specify that you want to include the 0x at the beginning

nvarchar to varbinary:

convert(varbinary(max),'0xA00592FC3E531C5F7608110F73E8AE4B4F2EA4C3',1)

varbinary to nvarchar:

convert(nvarchar(100),hashbytes('SHA1', 'az09123'),1)

This is demonstrated in the following script:

select hashbytes('SHA1', 'az09123') as BinaryHash
      ,convert(varbinary(max),'0xA00592FC3E531C5F7608110F73E8AE4B4F2EA4C3',1) as NvarcharHash
      ,case when hashbytes('SHA1', 'az09123') = '0xA00592FC3E531C5F7608110F73E8AE4B4F2EA4C3'
            then 'Match'
            else 'No Match'
            end as UnconvertedMatchTest
      ,case when hashbytes('SHA1', 'az09123') = convert(varbinary(max),'0xA00592FC3E531C5F7608110F73E8AE4B4F2EA4C3',1)
            then 'Match'
            else 'No Match'
            end as ConvertedMatchTest

Output:

+--------------------------------------------+--------------------------------------------+----------------------+--------------------+
|                 BinaryHash                 |                NvarcharHash                | UnconvertedMatchTest | ConvertedMatchTest |
+--------------------------------------------+--------------------------------------------+----------------------+--------------------+
| 0xA00592FC3E531C5F7608110F73E8AE4B4F2EA4C3 | 0xA00592FC3E531C5F7608110F73E8AE4B4F2EA4C3 | No Match             | Match              |
+--------------------------------------------+--------------------------------------------+----------------------+--------------------+

Upvotes: 5

Khorshed Alam
Khorshed Alam

Reputation: 334

Try this, it works.

DECLARE @AspNetUsers TABLE (OwnerEn VARCHAR(50))

INSERT INTO @AspNetUsers VALUES ('0xA00592FC3E531C5F7608110F73E8AE4B4F2EA4C3')


SELECT *
FROM   @AspNetUsers
WHERE  OwnerEn  = master.dbo.Fn_varbintohexstr(HASHBYTES('SHA1', 'az09123'))

Output:

enter image description here

Upvotes: 2

Related Questions