w0051977 w0051977 - 4 months ago 9
SQL Question

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

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

Answer

From the documentation,i could see there is no Sha256 algorithm..You will have to use one below.Available ones are..

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

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

select hashbytes('SHA2_256 ','test')

Output:
0x9F86D081884C7D659A2FEAA0C55AD015A3BF4F1B2B0B822CD15D6C15B0F00A08