0

Sorry for my ignorance, but I want to store hashed password in my database, How can use the HASHBYTES method to store hashed password in Users table ?

CREATE TABLE [Users](
    EmailAddress NVARCHAR(320) UNIQUE,
    UserID INT IDENTITY(1,1) PRIMARY KEY,
    UserPassword NVARCHAR(32), -- I Edited the length
    FirstName VARCHAR(256) not null,
    LastName VARCHAR(256) not null,
    MobileNumber BIGINT,
)

--I checked and found this is how to hash a password

declare @afterhash varbinary(256) = HASHBYTES('SHA2_256', 'P@ssw0rd')

But how do I combine them both ?

ExtraSun
  • 528
  • 2
  • 11
  • 31
  • 4
    I don't really follow. Apart from that you've defined `UserPassword` as a `varchar(16)`, so a `varbinary(8000)` is never going to fit, what are you struggling with here? – Thom A Jul 27 '21 at 10:30
  • @Larnu Hi here again, I'm struggling of how to hash the password at the first insert, should it be in a separate function that invoked when the user data is passed from the client side ? I mean what if I do the `INSERT INTO` statement here ? – ExtraSun Jul 27 '21 at 10:34
  • 2
    Just include the `HASHBYTES` as part of your `INSERT` in your parametrised statement. Again, I don't understand what the problem is here, other than the column `UserPassword` isn't fit for purpose (as it's too small) – Thom A Jul 27 '21 at 10:35
  • Does this answer your question? [SQL hashing a password](https://stackoverflow.com/questions/41853050/sql-hashing-a-password) – Lizo Matala Jul 27 '21 at 10:38
  • @Larnu Why a 16 chars length password is too short here ? should it be `NVARCHAR` ? – ExtraSun Jul 27 '21 at 10:42
  • @LizoMatala no, its a different question. – ExtraSun Jul 27 '21 at 10:43
  • `HASBYTES` returns a `varbinary(8000)` @ExtraSun . How do you suppose you you fit a value that is up to 8000 bytes in size in a column that is defined as only up to 32 bytes in size? – Thom A Jul 27 '21 at 10:46
  • @Larnu Sure my bad, thanks, I fixed this in my question. but I still dont understand in my code is `varbinary(500)` – ExtraSun Jul 27 '21 at 10:49
  • `@afterhash` is a `varbinary(500)` @ExtraSun . That doesn't change the data type that returned from `HASHBYTES`... Read it's [documentation](https://learn.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15)... – Thom A Jul 27 '21 at 10:53
  • @Larnu I see there the password is `NVARCHAR(32)` -- `CREATE TABLE dbo.Test1 (c1 NVARCHAR(32))` – ExtraSun Jul 27 '21 at 11:22
  • 1
    In addition to previous comments, `UserPassword VARCHAR(8000)` is for storing characters. `HASHBYTES` returns `varbinary` data, the length of which depends on the hashing function chosen. e.g.: `SHA2_256` returns `varbinary(32)` because 256 bits is 32 bytes. – AlwaysLearning Jul 27 '21 at 11:23
  • 1
    ... In the documentation the **unhashed** value is being store, @ExtraSun , not the hashed value. And it's not a password in the documentation, just a value to be hashed. – Thom A Jul 27 '21 at 11:29
  • Personally I don't think the password should be passed in plain-text to the server at all. Hash it client-side – Charlieface Jul 27 '21 at 13:00

2 Answers2

2

As mentioned, I don't understand the problem here. Just use HASHBYTES in your parametrised INSERT:

INSERT INTO dbo.Users (EmailAddress, UserPassword, FirstName, LastName, MobileNumber)
VALUES(@EmailAddress, HASHBYTES('SHA2_256',@Password), @FirstName, @LastName, @MobileNumber);

Side Note: As I mentioned in my other answer, bigint isn't the right choice for a telephone number. Phone Numbers can start with a 0 and contain other characters from digits. A value like '01234567890' would be changed to 1234567890, a number like '+441234567890' would be changed to 441234567890, and a number like '(01234) 567890' would fail to INSERT completely

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • 3
    Adding that password hashes should be [salted](https://stackoverflow.com/questions/5985708/t-sql-salted-passwords) as a general security best practice. – Dan Guzman Jul 27 '21 at 11:02
  • Thank you @DanGuzman, but I don't understand why the need for extra complexity at `Cast(@Password As varbinary(100)`, and what's for the `Cast('|' As binary(1)) ` ? in the link you attached. – ExtraSun Jul 27 '21 at 14:34
  • @ExtraSun, the purpose of a [salt](https://en.wikipedia.org/wiki/Salt_(cryptography)) is to provide additional defense because the hash will differ even for the same password (assuming a random salt). The additional complexity is small. – Dan Guzman Jul 27 '21 at 14:51
  • Thanks, @DanGuzman for credit card number I read it common to use the `ENCRYPTBYPASSPHRASE`, do you think I should use it ? (it's not for real site just for practice) – ExtraSun Jul 27 '21 at 14:55
  • That is a very different question, @ExtraSun , and not on topic for the comments on an answer about a different problem. Though, questions on the "best" ways to store sensitive information could be off-topic for [so] as it generate opinionated answers. – Thom A Jul 27 '21 at 15:01
  • IMHO, it's best to encrypt in the client rather than SQL Server. This provides an extra security layer because the keys are not stored on the db server so not even a DBA can decrypt the value. SQL Server [Always Encrypted ](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine) facilitates this. – Dan Guzman Jul 27 '21 at 15:04
  • 1
    @ExtraSun please ask a new question, ensuring it's on-topic for [so]. The comments aren't for such discussions. – Thom A Jul 27 '21 at 15:14
  • @Larnu Yes, roger roger - https://stackoverflow.com/questions/68547730/the-best-and-safest-whey-to-encrypt-a-credit-card-number-database-or-client-si – ExtraSun Jul 27 '21 at 15:30
  • @DanGuzman https://stackoverflow.com/questions/68547730/the-best-and-safest-whey-to-encrypt-a-credit-card-number-database-or-client-si – ExtraSun Jul 27 '21 at 15:33
  • Wouldn't it make more sense to encrypt the password? Or is the hashing (relatively) sufficient in your opinion? I'm thinking of [this](https://learn.microsoft.com/en-us/sql/t-sql/functions/pwdencrypt-transact-sql?view=sql-server-ver16). – Konrad Viltersten Feb 28 '23 at 07:32
  • Why would the OP need to be able to decrypt their user's passwords, @KonradViltersten ? – Thom A Feb 28 '23 at 08:43
  • @Larnu That's a very good question. I have no good answer to that. Somehow, it felt reasonable to push the encryption into that situation. However, the more I think about it, the more it's apparent that hashing is (as a non-reversable operation) sufficient. – Konrad Viltersten Feb 28 '23 at 12:51
  • Yes, that's the idea. You hash (and salt) the password in the database, @KonradViltersten . Then, when the user tries to authenticate, you hash and salt that password again (using the same algorithm and salt value) and compare that value to the one in the database. If they match, then the authentication is successful. The password itself doesn't need to be available in a unhash/unencrypted format here, as that is actually a security concern. – Thom A Feb 28 '23 at 12:59
1

declare @Users table (passwordColumn NVARCHAR(32));

insert @Users values (HASHBYTES('SHA2_256','Password@1234.'));

select HASHBYTES('SHA2_256', passwordColumn) from @Users;

Lizo Matala
  • 64
  • 7