• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 34
  • Last Modified:

Retrieving hashed passwords problem

I updated my user table with the following SQL code:
update td.dbo.Users set td.dbo.Users.newPW =  HASHBYTES('SHA2_512','myPassword')

where un = 'ME'

Open in new window

In the user table, newPW is binary(64).
I try using the code below to verify that it works:
doortrak.dbo.loginCredentials 'ME','myPassword'

Open in new window

loginCredentials is a stored procedure:

USE [DoorTrak]
GO
/****** Object:  StoredProcedure [dbo].[loginCredentials]    Script Date: 4/5/2018 10:34:35 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[loginCredentials] 
@userName as nvarchar(20),
@passWord as nvarchar(50)
AS 
Select UN from TD.dbo.Users
where 
  UN  = @userName
and
  newPW = HASHBYTES('SHA2_512',@passWord)

Open in new window


The code above is not working... returning 0 rows.

Thoughts?
0
Sheldon Livingston
Asked:
Sheldon Livingston
  • 5
  • 4
  • 2
1 Solution
 
_agx_Commented:
Check the hashed value in the table.  Don't know what version you're using, but when I ran it under 2008 and it returned 0 records because the hashed value is NULL.  512 isn't supported under my version, only later versions.

https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql

Returns the MD2, MD4, MD5, SHA, SHA1, or SHA2 hash of its input in SQL Server.
0
 
Olaf DoschkeSoftware DeveloperCommented:
I'd say it's because of nvarchar vs varchar -  'myPassword' should be N'myPassword'.

At least there's a difference in the hashes:
SELECT HASHBYTES('SHA2_512','myPassword') 
UNION 
SELECT HASHBYTES('SHA2_512',N'myPassword')

Open in new window


In detail: The hash you store is the one of 'myPassword'. You call loginCredentials with the same 'myPassword', but the variable receiving this parameter is declared as @passWord as nvarchar(50), and that might convert the passed in value from Windows-1252 to Unicode, so you get the different hash.

Bye, Olaf.
1
 
Sheldon LivingstonConsultantAuthor Commented:
_agx_  There is a value there...
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Sheldon LivingstonConsultantAuthor Commented:
Olaf... I don't understand where to put this union clause
0
 
_agx_Commented:
Okay, then it's likely what Olaf said.
0
 
Sheldon LivingstonConsultantAuthor Commented:
_AGX_ do you understand what Olaf meant?  Where is the union statement to go?
0
 
Olaf DoschkeSoftware DeveloperCommented:
The union statement just demonstrates these hashes are differing, different encoding means different bytes are hasged. You don't need to put this anywhere.

Either you make your stored proc parameter varchar instead of nvarchar, or you use N'myPassword' everywhere you now used 'myPassword'. Using Unicode, in general, is a good idea, so I'd opt for N'myPassword'.

Bye, Olaf.
0
 
Sheldon LivingstonConsultantAuthor Commented:
Thanks Olaf... I had already tried that.  The design of the field is varbinary(128) for the password.  Should I try changing it around or do hashes need binary settings?
0
 
Olaf DoschkeSoftware DeveloperCommented:
The result is binary(64), but just look at the values of HASHBYTES('SHA2_512','myPassword')  and HASHBYTES('SHA2_512',N'myPassword'), they differ.

You have to redo your initial storage with
update td.dbo.Users set td.dbo.Users.newPW =  HASHBYTES('SHA2_512',N'myPassword') 
where un = 'ME'

Open in new window


For a test then call:
doortrak.dbo.loginCredentials 'ME',N'myPassword'

Open in new window


Bye, Olaf.
0
 
Sheldon LivingstonConsultantAuthor Commented:
Thanks Olaf!  Just had to get the TYPES figured out.
0
 
Olaf DoschkeSoftware DeveloperCommented:
I tested passing 'myPassword' to an nvarchar parameter actually converts it from ANSI to UNICODE, so that's essential.
Since 'ME' also is passed in to a nvarchar parameter you might also not find it in the data. That might also need to be mended.

Yes, the types matter. In Unicode, every letter is the same ascii code byte followed by a 0 byte. In many situations, SQL Server gracefully compares ANSI and Unicode strings, but not when you call HASHBYTES.

SELECT CASE WHEN N'abc' = 'abc' THEN 'Strings are equal' ELSE 'Strings differ' END,
CASE WHEN HASHBYTES('SHA_512',N'abc') = HASHBYTES('SHA_512','abc') THEN 'Hashbytes are equal' ELSE 'Hasbytes differ' END

Open in new window


Bye, Olaf.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now