How do you securely store a user's password and salt in MySQL?

Question

So, I found out on SO that you're supposed to hash the password together with a "salt". (The articles can be found here and here.)

Here's the code:

$password = 'fish';

/* should be "unique" for every user? */
$salt= 'ABC09';

$site_key = 'static_site_key';

hash_hmac('sha1', $password . $salt, $site_key);

And now I need to save both the $password and $salt in MySQL, like so:

+---------+--------+----------+-------+
| user_id |  name  | password |  salt |
+---------+--------+----------+-------+
|    1    | krysis |  fish**  | ABC09 |
+---------+--------+----------+-------+

** fish will of course be hashed and not stored in plain text.

And I'm just wondering whether or not it actually makes sense to do it this way, because this way a hacker or whoever will also know the salt? So, if they crack the password and the see it's fishABC09 they automatically will know the password is fish? Or might he "never" be able to crack the password because he doesn't know the secret_key, as it isn't stored in the database?

I'm sorry if I'm not making any sense. I just always used sha1 for passwords, and today I found these articles that talked about adding a salt.

Answer 1

There are good articles about storing passwords right. One of them for example: Storing Passwords - done right!

You should use different salt for every user, but there's no need to store the salts separately. See similar discussion in another thread

By the way, you probably shouldn't be using sha1 but e.g. sha256 or sha512 something stronger instead (at least to avoid bad publicity). There's a good answer regarding this: How insecure is a salted SHA1 compared to a salted SHA512

Answer 2

Rainbow / Dictionary Attacks, and salted passwords - a heretics approach

Passwords should not be stored in your database. Do not roll your own authentication mechanism - you will almost certainly get it wrong. Use Kereberos for valuable stuff, and well I dont have a good suggestion otherwise.

However, this question has been bashing around in my skull for a while (see the edits) and I would like to put a heretical viewpoint over.

Rainbow tables are so-called because of the way the lookup mechanism (chaining) is used - but they are just dictionary attacks. Millions of dictionary words and common passphrases are hashed up front and then used to compare stolen hashed passwords against.

This worked well for the NT4 password process which used md5 hashes and no salt. But when a salt is added to a password before hashing, then the rainbow table is useless - instead of looking for the md5 hash of "mypass" it has to precompute "mypass-random-string_of-letters"

Its impossible to guess what salt someone will use, so salting makes rainbow tables as a generic, use anywhere against any server solution dead in the water.

but ...

Thats only one use case - certainly a big threat, certainly one to defend against. But salts have a problem. You must keep the salt around for when you want to authenticate the next time user logs in. They send their plaintext (over ssl!), you append the salt and hash, comapre to the hash stored in database. But if you dont keep the salt with the password, you cannot do that, and errr... no login

But we are not only defending against people passing around a table desgined to crack NT4 passwrods. We are supposed to protect our users individually.

A salt adds a two factor defence to the passwords - even with the hash an attacker will need the salt to have any chance of cracking it. But the standard advice just gives away that two factor defence. Probably its reasonable, but I am not convinced.

There is a bit of maths behind this. The usual advice (given by RSA as well - ftp.rsa.com/pub/pkcs/pkcs-5v2/pkcs5v2-0.pdf) is build a 64 bit salt, store it with the hashed password. This way you can re-confirm the password by rehashing with the salt and reversing the hash is next to impossible.

NB - I could be wrong here ...

The "next to impossible" bit comes from simple maths.

Lets assume an 8 digit password, with 62 possible characters (letters, upper lower and numbers)

Thats 62^8 combinations, or a little over 200 million trillion.

To brute force this, by computing the hash directly, (ie making my salt specific rainbow tables) I should see a collision after 62^8/2 and at lets say 100 hashes per second, it will take around 12 million days. Yes days.

OK, so even storing the hash with the password makes the task of finding the hash quite infeasible.

However there are some assumptions in the above. Firstly that the password is a random choice of the range 62^8. In practise most passwords a much much weaker - rainbow tables aren't really based on all 62^8 possibilities - they are built out of dictionaries, and real password tables found over the years.

So the search space, instead of being 62^8 is really smaller. How small ? The depends on the password "strength".

There are about 250,000 - 750,000 words in english language (http://oxforddictionaries.com/page/93). Lets take 500,000 as a simple case. Then lets take variations that can be applied - add a digit, add a year, convert vowels to digits. That gives us say 3 possible new words per word, or 2 million possible passwords.

Generating 2 million hashes at 100 / sec give 20,000 seconds or 5 hours - well within the range of any laptop.

So if there is a specific user being targeted, (root, admin, spolsky etc) then storing a salt with the password immediately makes the cracking feasible.

Storing the salt away from the password increases the difficulty of the crack - not in any mathematical manner, just in difficulty of getting the salt and the password. One could envisage a seperate server that just takes plaintext, username and hash, and looks up the salt used on that users join date, and returns 1/0

So, in short, if you store the salt with the password and someone accesses the passwords, then depending on password strength, each password is crackable in a reasonable length of time. A different salt per user protects all the other users, but if you are after a specific account, that is irrelevant. If the hacker is only after one password then storing a salt with password makes the crack feasible. Keeping a rotating salt elsewhere from the password table means the hacker now needs two data steals to crack the password, because without the salt any attack is doomed to thousands of years of work.

This is all a trade off - forcing people to use 15 digit passwords means they all get post-it noted to the screen, or become "easy to remember" i.e. words.

At this point you may as well move to Kerberos or similar - if the data you are protecting is valuable the users will understand and rspect it. If not why are we bothering.

But I still recommend you do not implement your own auth mechansim. Use Kerberos, use a semi public PKI, dont roll your own. I have no idea how to tell if my server just swapped the RAM holding my salt into disk and thats only one mistake I can immediately think of in rolling my own authentication.

HTH

Answer 3

$username = mysql_real_escape_string($username);
$password = mysql_real_escape_string($password);
$time = time();
$query = "
INSERT INTO user (name, unixcreationtime, passhash) 
VALUES ('$username', '$time', SHA2(CONCAT('$time','$password'),512) ";

Don't use SHA1, it is no longer secure.
I suggest doing all hashing in MySQL, that way you can be sure there's no difference in the outcome of the hash.

Select a user using:

$query = "SELECT id FROM user 
          WHERE name = '$username' 
            AND passhash = SHA2(CONCAT(creationdate,'$password'),512) ";

Answer 4

No, because when the password gets hashed it doesn't look like fishABC09, it looks like: 5f4dcc3b5aa765d61d8327deb882cf99 which is an md5 hash.

To gain access to your system, even if they know the hash, it cannot be reversed. We use salts in order to add complexity to our hashes, and to prevent hash lookups in rainbow tables.

For example: Do a Google search for the md5 hash of "password" which is: 5f4dcc3b5aa765d61d8327deb882cf99

A lot of results right?

Now I'm going to create a hash again, I will still use "password", but I will add a SALT which is "AHG(*@". I'm guessing the only response will be for this post and some bot scrapers that have read this post :)

cbe57e92ffbb0086320891b9f979156d

Should be only a few results, or this post which are this post.

Just Remember

Just remember this... hashes are one way, so even if you gain the hash, you do not know what was used to create it

Answer 5

I know this is old, but for anyone that manages to stumble on this post...

What you are really trying to do HMAC. Trying to do that yourself creates issues. You can partially compute hashes, which reduces the amount of effort required to guess at a password, for instance. HMAC addresses those kinds of concerns.

Better still is scrypt or bcrypt. HMAC still often uses hash algorithms that are designed to be quick and easy to compute; there is even hardware implementations of many of the hash algorithms. bcrypt is computationally expensive and scrypt is memory intensive. Both make things harder for an attacker, but scrypt in particular makes it really hard to build hardware devices to crack a password.

I really like the chart over here: https://github.com/pbhogan/scrypt#why-you-should-use-scrypt

Answer 6

its an old topic but others will come here too so i will try to describe it very easy:

if you do hash(password) you get the same hashvalue for every password [hash(password) = hash(password)]. if two users have the same password, you will see it because the hashvalues are the same. some passwords like "password" or "12345678" are taken very often so: same hashvalue in your database -> maybe password "password" or "12345678" (rainbowtable attack).

if you hash(salt+password) you dont get the same hash for the same passwords because hash(salt1+password) is not hash(salt2+password).

hash(x) is just a mathematical function like f(x)=y. if you put the same x you will get the same y. this function must be "special" to be safe. just dont use sha1 because it is not safe anymore :D

Answer 7

A salt is a random number of a fixed length. This salt must be different for each stored entry. It must be stored as clear text next to the hashed password.

From

https://www.owasp.org/index.php/Hashing_Java#Why_add_salt_.3F

Answer 8

If a hacker gets access to your PHP files, he can simply add mail function, so whoever login, account details are emailed to hacker.

If hacker only gets access to database, he should not get passwords plainly written there, so crypt it before saving. Save it in md5 hash which can't be reversed.

I normally use salt based on username or userID, that PHP program know how to generate for each user along with static_site_key.