CODEWITHSUNDEEP
sql-servert-sqlhash
Bendom
Bendom

Reputation: 175

SHA2_256 direct comparison

I would like to find out whether given SHA 256 hash has been already stored in database. 

create table #tmpH (input VARCHAR(20), hsh VARBINARY(8000))

insert into #tmpH values('Hello', HASHBYTES('SHA2_256', 'Hello'))
insert into #tmpH values('Internet', HASHBYTES('SHA2_256', 'Internet'))

input       hsh
Hello       0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969
Internet    0x57E8A431DEEC0D70DA0A26EA3392E59688B11B79EDFD04E9DA823B16BCD1D4D7

select * from #tmpH where hsh = '0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969'                            --Nope
select * from #tmpH where hsh = CAST('0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969' as varbinary(8000))   --Nope
select * from #tmpH where cast(hsh as VARCHAR(100)) = '0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969'      --Nope
select * from #tmpH where CONVERT(VARCHAR(100), hsh, 2) = '0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969'  --Nope
select * from #tmpH where hsh = HASHBYTES('SHA2_256', 'Hello')          --Works

How can I compare two hashes made by the same algorithm when I don't have the original value and can't hash it myself?

I am working with MS SQL Server 2016.

Upvotes: 1

Views: 731

Answers (2)

AdamL
AdamL

Reputation: 13161

You have not tried the simplest (and, incidentally, working) method:

select * from #tmpH where hsh = 0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969

Management Studio recognizes 0x as prefix for binary data in hexadecimal format.

Upvotes: 2

Thom A
Thom A

Reputation: 95659

The varchar value '0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969' as a varbinaryis not the value 0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969. If you actually try casting this, you'll find this out very quickly:

SELECT CAST('0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969' AS varbinary(100)) AS B;

B
--------------------------------------------------------------------------------------------------------------------------------------
0x307831383546384442333232373146453235463536314136464339333842324532363433303645433330344544413531383030374431373634383236333831393639

If you are casting a varchar that represents a literal varbinary you need to use CONVERT and a style code:

SELECT CONVERT(varbinary(100),'0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969',1) AS B;

B
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969

If, however, this is parametrised, just don't wrap your value in single quotes:

SELECT *
FROM #tmpH
WHERE hsh = 0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969

Upvotes: 1

Related Questions