4

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.

10

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.