SHA2_256直接比较

我想找出给定的SHA 256哈希是否已经存储在数据库中。

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

当我没有原始值并且无法自己对其进行哈希处理时,如何比较由同一算法生成的两个哈希?

我正在使用MS SQL Server 2016。

评论
  • 回忆终止
    回忆终止 回复

    您尚未尝试过最简单的方法(顺便说一句,可以工作):

    select * from #tmpH where hsh = 0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969

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

  • 刺青
    刺青 回复

    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
    

    但是,如果这是参数化的,则不要将您的值用单引号引起来:

    SELECT *
    FROM #tmpH
    WHERE hsh = 0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969