Declare variable for HashBytes

Question

I am following a simple example from this question:

DECLARE @HASHBYTES VARBINARY(128) = hashbytes('sha2_256', 'secret')
SELECT cast(N'' as xml).value('xs:base64Binary(sql:variable("@HASHBYTES"))', 'varchar(128)');

It returns a correct hashed value: K7gNU3sdo+OL0wNhqoVWhr3g6s1xYv72ol/pe/Unols=

Now I tried to declare secret as a variable following Microsoft Hashbytes documentation example:

DECLARE @HashThis nvarchar(32);  
SET @HashThis = CONVERT(nvarchar(32),'secret');
DECLARE @HASHBYTES VARBINARY(128) = hashbytes('sha2_256', @HashThis)
SELECT cast(N'' as xml).value('xs:base64Binary(sql:variable("@HASHBYTES"))', 'varchar(128)');

it returns a wrong hash:

s6jeuSg7FUrj9WBqMj0GbSRudEDi2NTJqHqmG5UYOiY=

Is there any way to declare a secret as a variable to get the correct hash?

I am new to this Hashbytes stuff in SQL. I am using it on SQL Server 2017.

Answer 1

The issue is that you are using nvarchar to declare your secret. but it should be varchar and it would solve the problem.

So let's test it:

DECLARE @HashThis varchar(32);  
SET @HashThis = CONVERT(varchar(32),'secret');
DECLARE @HASHBYTES VARBINARY(128) = hashbytes('sha2_256', @HashThis)
SELECT cast(N'' as xml).value('xs:base64Binary(sql:variable("@HASHBYTES"))', 'varchar(128)');

Will return what you expected initially:

K7gNU3sdo+OL0wNhqoVWhr3g6s1xYv72ol/pe/Unols=

By the way, you do not need to CONVERT, you can pass the secret as varchar. something like:

DECLARE @HashThis varchar(32);
SET @HashThis = 'secret';