How to encrypt and check a login password.

Question

How to encrypt and check a login password.

skw05

SSC Veteran

Points: 200
More actions
July 6, 2014 at 10:49 am

#287579

Hi,
I am trying to learn how to store a web form password and than check it when the user log in. So far none of the code I can find works.
Can someone explain why the following test does not work and what the correct code should be?
Insert Into
values ('name', 'email', HashBytes('SHA1', 'bob'))
GO
Why does the following produce no rows?
SELECT *
From
Where HashBytes('SHA1', password) = HashBytes('SHA1', 'bob')
Thanks
For the help

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

skw05 (7/6/2014)
Hi,
I am trying to learn how to store a web form password and than check it when the user log in. So far none of the code I can find works.
Can someone explain why the following test does not work and what the correct code should be?
Insert Into
values ('name', 'email', HashBytes('SHA1', 'bob'))
GO
Why does the following produce no rows?
SELECT *
From
Where HashBytes('SHA1', password) = HashBytes('SHA1', 'bob')
Thanks
For the help

If the "password" column of the table stores the encrypted passwords, why are you encrypting it again? It seems to me that the following would work...

SELECT *

From

Where password = HashBytes('SHA1', 'bob')

As a bit of a sidebar, I'd ensure that no such simple password were allowed and I'd also add a "salt" to the password encryption.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

skw05 SSC Veteran Points: 200 More actions · Answer 2

Thanks Jeff, what a bone headed mistake 🙂

Do you have any good resources on adding salt. I have seen it as a separate column in the table or as part of the insert statement sql code.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Just a terminology thing...

Hashbytes doesn't encrypt a password, it creates a cryptographic hash. Point of a hash is it's one way and deterministic, which is why it's good for passwords. Encryption is non-deterministic (encrypt a value twice and you'll get different results) and reversible.

The EncryptBy* and DecryptBy* functions are the ones which encrypt.

You do want to salt the hash, otherwise, since hashes are deterministic, you risk someone comparing pre-hashed values to guess a password (Rainbow tables).

Offhand no good articles. Google search for Salted hash SQL Server should turn something up. I know there was a chapter in Pro SQL Server 2005 Development if you can get hold of a copy.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 4

HASHBYTES() itself does not support a parameter for a salt. You'll have to be a little creative, like so:

Adding Salt to HASHBYTES() (StackOverflow)

K. Brian Kelley
@kbriankelley

Ross McMicken SSCarpal Tunnel Points: 4395 More actions · Answer 5

I've been thinking for a while that the userid, especially if it's an email address, would be a good salt. It's unique per user, doesn't have to be stored separately, is entered each time the user logs in, and they are generally reasonably long.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

I'm pretty sure I wouldn't use an email address for a salt, especially for a company login. It's pretty easy to find out what the email address for an individual is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

From the Stack Overflow link:

Remember that the salt should be cryptographically random so I would not recommend using NewId(). Instead, I would generate that using something like .NET's RNGCryptoServiceProvider class.

Email addresses are not cryptographically random. The salt should be strongly protected. I've seen suggestions before that the salt be encrypted itself (encrypted, not hashed) for additional protection.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ross McMicken SSCarpal Tunnel Points: 4395 More actions · Answer 8

The problem with random salts is they have to be stored somewhere, which probably makes them available to the hacker, especially if they are prepended to the hash, which is apparently common. Can my suggestion be broken? Sure, but it will take far longer than an unsalted password, requires that the hacker crack each password separately, and it's easy to implement. Password protection is like using a physical safe, you know it can be broken, you just have to decide how long you want the cracker to have to work to get in.

Here's another stack overflow discussion on the topic http://stackoverflow.com/questions/536584/non-random-salt-for-password-hashes

And a site with a password entropy calculator http://rumkin.com/tools/password/passchk.php