Sql-server – Using HASHBYTES() yields different results for nvarchar and a variable

encodinghashingsql serversql-server-2012t-sql

I'm using server-side hashing to transmit passwords, then running PBKDF2 in the database to store the hashed password + salt combination.

Hashing nvarchar(max) and a @variable holding the same value yielded different results with the HASHBYTES() function.

DECLARE @hash NVARCHAR(MAX) = 'password5baa61e4c9b93f3f0682250b6'

SELECT HASHBYTES('SHA1', 'password5baa61e4c9b93f3f0682250b6') AS NVARCHAR_INPUT, 
       HASHBYTES('SHA1', @hash) AS VARIABLE_INPUT

Yields the following: 

NVARCHAR_INPUT                             | VARIABLE_INPUT
0xA03BEF0E3EC96CC7C413C6646D3FEC6174DA530F | 0x74B55C42E1E0AB5C5CDF10B28567590B240355C3

This is SQL Server 2012. This particular database is running SQL Server Express, so I'm also curious if this question is version agnostic.

Best Answer

If you want a quoted string to be NVARCHAR (treated as Unicode), you need to prefix it with N.

DECLARE @hash NVARCHAR(MAX) = 'password5baa61e4c9b93f3f0682250b6'

SELECT HASHBYTES('SHA1', N'password5baa61e4c9b93f3f0682250b6') AS NVARCHAR_INPUT, 
       HASHBYTES('SHA1', @hash) AS VARIABLE_INPUT

This will show matching hashes.

NVARCHAR_INPUT                               |VARIABLE_INPUT
0xCF01AF0DCECF41BA0106A264666544C2590A4660   |0xCF01AF0DCECF41BA0106A264666544C2590A4660

The prefix isn't necessary here because it's declared as NVARCHAR and the string will be converted.

DECLARE @hash NVARCHAR(MAX) = 'password5baa61e4c9b93f3f0682250b6'

Though I've run into some problems with that, so I'll usually make it clear:

DECLARE @hash NVARCHAR(MAX) = N'password5baa61e4c9b93f3f0682250b6'

If you want some explanation of why they can be hashed differently, start with this example:

DECLARE @n NVARCHAR(1) = N'N'
DECLARE @v VARCHAR(1) = 'N'

SELECT DATALENGTH(@n), DATALENGTH(@v)
Related Question