How do you store a password as a has value and then retrieve it?

Question

Please see the code SQL:

create table [User] (ID int identity not null,EmailAddress varchar(50), [Password] BINARY(32),primary key (ID))
INSERT INTO [User] (EmailAddress,[Password]) values ('testuser@hotmail.co.uk',hashbytes('SHA256','test'))

A null value is entered into the password field. Why is this?

I was hoping the following query would return one row, however it returns no rows:

SELECT * FROM [User] where password = hashbytes('sha256','test')

As you can probably tell I am new to hashing algorithms.

The following question states that you should store a password as a BINARY(32): CHAR(64) or BINARY(32) To Store SHA256 Hash in SQL SERVER

never use this, but try `hashbytes( 'SHA2_256',´test´)` you are missing numbre 2 and an undescore character in both insert and select — Horaciux, Jul 16 '16 at 19:15

TheGameiswar · Accepted Answer · 2016-07-16T21:09:34.840

1

From the documentation,i could see there is no Sha256 algorithm.Available ones are..

HASHBYTES ( '', { @input | 'input' } )

::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512

select hashbytes('SHA2_256 ','test')

Output:
0x9F86D081884C7D659A2FEAA0C55AD015A3BF4F1B2B0B822CD15D6C15B0F00A08

edited Jul 16 '16 at 21:09

answered Jul 16 '16 at 19:23

TheGameiswar

27,855
8
56
94

score 0 · Answer 2 · answered Jul 16 '16 at 19:31

It worked on SQL Server 2012 express

create table [User] (ID int identity not null,EmailAddress varchar(50), [Password] BINARY(32),primary key (ID))
INSERT INTO [User] (EmailAddress,[Password]) values ('testuser@hotmail.co.uk',hashbytes('SHA2_256','test'))

SELECT * FROM [User] where password = hashbytes('SHA2_256','test')

ID  EmailAddress    Password
1   testuser@hotmail.co.uk  0x9F86D081884C7D659A2FEAA0C55AD015A3BF4F1B2B0B822CD15D6C15B0F00A08

score 0 · Answer 3 · answered Jul 16 '16 at 20:20

The algorithm might be handled case sensitive. The case of insert and select don't match.

For finding the problem: You could select by email and print the saved password as well as the one of your 'where'-clause. So, you can see if they are actually the same.

How do you store a password as a has value and then retrieve it?

3 Answers3