CODEWITHSUNDEEP
sqlsql-server-2017declarehashbytes
Maya Rager
Maya Rager

Reputation: 49

Declare variable for HashBytes

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.

Upvotes: 1

Views: 424

Answers (1)

Maytham Fahmi
Maytham Fahmi

Reputation: 33427

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';

Upvotes: 2

Related Questions