patternsqlModerate
Using HASHBYTES() yields different results for nvarchar and a variable
Viewed 0 times
nvarchardifferentyieldsusingforhashbytesandresultsvariable
Problem
I'm using server-side hashing to transmit passwords, then running PBKDF2 in the database to store the hashed password + salt combination.
Hashing
Yields the following:
This is SQL Server 2012. This particular database is running SQL Server Express, so I'm also curious if this question is version agnostic.
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_INPUTYields the following:
NVARCHAR_INPUT | VARIABLE_INPUT
0xA03BEF0E3EC96CC7C413C6646D3FEC6174DA530F | 0x74B55C42E1E0AB5C5CDF10B28567590B240355C3This is SQL Server 2012. This particular database is running SQL Server Express, so I'm also curious if this question is version agnostic.
Solution
If you want a quoted string to be
This will show matching hashes.
The prefix isn't necessary here because it's declared as
Though I've run into some problems with that, so I'll usually make it clear:
If you want some explanation of why they can be hashed differently, start with this example:
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_INPUTThis will show matching hashes.
NVARCHAR_INPUT |VARIABLE_INPUT
0xCF01AF0DCECF41BA0106A264666544C2590A4660 |0xCF01AF0DCECF41BA0106A264666544C2590A4660The 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)Code Snippets
DECLARE @hash NVARCHAR(MAX) = 'password5baa61e4c9b93f3f0682250b6'
SELECT HASHBYTES('SHA1', N'password5baa61e4c9b93f3f0682250b6') AS NVARCHAR_INPUT,
HASHBYTES('SHA1', @hash) AS VARIABLE_INPUTNVARCHAR_INPUT |VARIABLE_INPUT
0xCF01AF0DCECF41BA0106A264666544C2590A4660 |0xCF01AF0DCECF41BA0106A264666544C2590A4660DECLARE @hash NVARCHAR(MAX) = 'password5baa61e4c9b93f3f0682250b6'DECLARE @hash NVARCHAR(MAX) = N'password5baa61e4c9b93f3f0682250b6'DECLARE @n NVARCHAR(1) = N'N'
DECLARE @v VARCHAR(1) = 'N'
SELECT DATALENGTH(@n), DATALENGTH(@v)Context
StackExchange Database Administrators Q#177590, answer score: 15
Revisions (0)
No revisions yet.