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?
Sheldon LivingstonConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_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
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.