-1

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
  • 508
  • 1
  • 7
  • 27
  • 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? – Larnu 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) – Larnu 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? – Larnu 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://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15)... – Larnu 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. – Larnu 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

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
1

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

Larnu
  • 76,706
  • 10
  • 34
  • 63
  • 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. – Larnu 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://docs.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. – Larnu 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