Consider this method
declare @pswd nvarchar(max);
set @pswd = '2YKRCqHv';
Select
orig = a.Hash,
hashA = 0x0200 + a.Salt + Hashbytes('SHA2_512', cast('2YKRCqHv' as varbinary(max)) + a.Salt),
hashB = 0x0200 + a.Salt + Hashbytes('SHA2_512', cast(@pswd as varbinary(max)) + a.Salt)
from DB a
where
a.Hash =
0x0200 + a.Salt+ Hashbytes('SHA2_512', cast('2YKRCqHv' as varbinary(max)) + a.Salt)
or
a.Hash =
0x0200 + a.Salt+ Hashbytes('SHA2_512', cast(@pswd as varbinary(max)) + a.Salt)
OUTPUT:
orig 0x0200BB316075603286E929221B9C04411AEC602A98B295CC05FCFFA809C2B553A100F4EBD4AA6FB3458E0B7C7E6D6B36FEA4908DF0AECA5142A26FA06B30F125253E15D585EE
hashA 0x0200BB316075603286E929221B9C04411AEC602A98B295CC05FCFFA809C2B553A100F4EBD4AA6FB3458E0B7C7E6D6B36FEA4908DF0AECA5142A26FA06B30F125253E15D585EE
hashB 0x0200BB316075C91147A2AEC396358C3F950C7E930B8DF0F9AC05628E00A74663502EE7BFCE68AA7BA3EC8303AE65107C72CAEF95111DD85CE45F210291B2800141CA37863A09
Why are A and B different? And how can I create a verify password procedure?
This is because in
hashA
you areCAST
ing aVARCHAR
to aVARBINARY
, and inhashB
you areCAST
ing aNVARCHAR
to aVARBINARY
.The first is non-Unicode, hence the difference.
Try:
Note the addition of
N
in front of the password forHashA
, which causes it to be Unicode, and then produces an identical result.Alternatively, change the
@pswd
declaration to use aVARCHAR
, which produces the same output asorig
.