What is the insert query to insert the password in secure form in mssql?

Question

I am using quires as below

Create table UserDetails
(
    UserId int primary key identity(1,1),
    UserName nvarchar(50) Not Null,
    UserContactNumber int Not Null,
    UserEmail varchar(50) Not Null,
    UserPassword nvarchar(50) Not Null,
    UserConfirmPassword nvarchar(50) Not Null
) 

Insert into UserDetails 
values ('Shefali',36547895,'s.jain@gmail.com',HASHBYTES('MD5','Shefali1234$')
,HASHBYTES('MD5','Shefali1234$'))

Result:

+---+---------+----------+------------------+---------------+---------------+
| 1 | Shefali | 36547895 | s.jain@gmail.com | ꉹ㇒ᆔ唡鈑쳕켆� | ꉹ㇒ᆔ唡鈑쳕켆�  |
+---+---------+----------+------------------+---------------+---------------+

score 0 · Accepted Answer · answered Jul 01 '17 at 09:03

HASHBYTES returns a VARBINARY(16) for MD5 (Microsoft Docs). What you're seeing is correct and what you'd expect by what you're doing but BINARY(16) would give you something a little nicer to look at. You don't try to get the password out of the DB, rather you hash on the webserver and test against the hashed value.

Also, you don't need to store both the password and the password confirmation. It should be enough to test that they match before they get stored, probably in javascript in the frontend.

Further, be certain that what you're doing is what you want to be doing. MD5 is not considered secure. EG https://codahale.com/how-to-safely-store-a-password/ If you do go with bcrypt you can store it in BINARY(60) as in https://stackoverflow.com/a/5882472/2281968

What is the insert query to insert the password in secure form in mssql?

1 Answers1