Short manual for storing passwords in the DB

[Stefany93]

If we want to create a login system for our website, we must absolutely store two types of

data - a username and a password. We store them in the database of our choice (be it MySQL, SQLite, MariaBD, whatever really),

and when the user wants to log in, they input their username and password and a script we had written earlier takes care of comparing whether the username 

password that our user has just entered matches those in the database. If they do, we start a session, and we usually assign it the value of the user's ID.

And the user is now logged in. Sounds easy, eh?

 

Our first problem comes with our database design. Let's say we are using MySQL and we want to store the user's username and password.

We first create a table in our database and we name it users

The absolute minimum of fields required are 3. The user's unique ID, username and password. So now our table looks like this*:

 

users_id      users_username     users_password

 

Now, let's imagine that we have a user with the username robert with a password marble

 

users_id     users_username      users_password

1            robert              marble

 

Now, the trickery comes here. We store the users' ID with the INT database type, 

and we store the users' username with the VARCHAR datatype. However, we all know that

we can't store the users' password in a plain text because that will just be plain stupid.

We need to hash it in some way. 

 

Now, when we were young and green, we used the MD5() hash function. That function transforms a given text into a 

32 characters string of random numbers and letters. For example, let's hash the password marble with the

MD5() hash function

 

echo md5('marble');

// Result: 550a6aee24871befa055ffd52f92eba9

 

As you see, the string is converted into a hash value that is not possible to convert back to its original string.

However, as of 2009, this MD5 has been officially deprecated, and it is not reasonable to use it for storing passwords inside databases at least.

 

So MD5() is out of the game, what does that leave us? Our next option is SHA1()

SHA1() does the exact same thing as MD5(), but it converts the string into a 40 characters hash value like so:

 

echo sha1('marble');

// Result: 334851b6547be0d129bf69f984668cfbd70d4da2

 

As of 2011, security vulnerabilities have been found for SHA1(), so using that hash function is not a good idea either.

That leave us with SHA512, the most secure hashing function so far. It's doesn't have its own pre - build method in PHP, but it can be used that way:

 

echo hash('sha512', 'marble');

// Result: 2a7d68bd5ef34809c455209a531318c09c50d92776913b042b384604673e1708620732cdbbea193304130e96102666635c05cb18e03ce6e936bd2ff4c9566a36 

 

Yup, the hash value is that long! 128 characters, no kidding! So far, SHA512 is the most preferred way to use when storing passwords in the DB**.

 

So if we go back to our users table, we shall make some adjustments.

We shall give the users_username field unique index, and we shall set the data type of our users_password field to CHAR, since we 

shall always be storing 128 characters strings, we could use the extra speed CHAR provide us, if we always use the exact number

of characters, as declared.

 

Here is how out users table looks like now:

users_id      users_username     users_password

1             robert                 2a7d68bd5ef34809c455209a531318c09c50d92776913b042b384604673e1708620732cdbbea193304130e96102666635c05cb18e03ce6e936bd2ff4c9566a36 

 

 

 

Notice how we are not storing the user's password in a plain text, but in a hashed value. That way, if, God forbid, an evil user enters our DB,

they could only see the hash value, but they would never know the real password, since it is impossible to reverse the hash back to its original text.

 

Here is how we write a script that compares the user's inputted data with the one in the DB.

Please don't use this code for production, since it is a security risk, because I haven't validated or sanitized any of the user's input,

I am just using this code as an example.

 

if(isset($_POST['username'], $_POST['password']) and !empty($_POST['username']) and !empty($_POST['password'])){

$username = trim($_POST['username']);

$password = trim($_POST['password']);

$password_hash = hash('sha512', $password);

$query = $db->prepare("SELECT users_id, users_username, users_password

                                    FROM users

                                   WHERE users_username = :username

                                   AND users_password = :password");

$query->bindParam(':username', $username, PDO::PARAM_STR);

$query->bindParam(':password', $password_hash, PDO::PARAM_STR);

$query->execute();

$results = $query->fetch(PDO::FETCH_ASSOC);

if(!empty($results)){

$_SESSION['user_id'] = $results['users_id'];

echo 'Logged in!';

 

}else{

echo 'Wrong username/password!';

}

} 

 

* I like to prefix the field names with the name of my table, but you are free to name your table fields anything you like, really.

** Please note that I am only talking about hashing, it is advisable to use salt to better secure your passwords when comparing them.

Edited March 27, 2013 by Stefany93

[requinix]

** Please note that I am only talking about hashing, it is advisable to use salt to better secure your passwords when comparing them.

But if you're writing a "short manual for storing passwords in the DB" then you really should include salting.

[cob05]

But if you're writing a "short manual for storing passwords in the DB" then you really should include salting.

 

 

Agreed, hashing and salts have to go hand-in-hand.  Any discussion of storing hashed passwords without including an explanation of salting is unwise.  Salts add much to the security of the hashed passes in terms of defeating rainbow tables in the event that the DB is compromised.  There were some high profile hacks recently where large numbers of username:password combos were released that would have been effectively rendered null if salts were used with the hashed passes, saving the large companies involved a lot of embarrassment and the affected users a lot of frustration.  It is just too much effort for the attacker to recompile his rainbow tables for (pseudo)randomly salted hashes to attack the DB on a large scale.  Salts are obviously somewhat less effective if there is a single or small number of targets that the attacker is focusing on (admin user, etc...) and in these cases the use of strong passwords is essential.

 

That being said, the above is a very nice starter guide to hashing passwords Stefany93.  Believe it or not, I am still seeing newbie guides that were created recently by others recommending the use of md5 as the preferred hashing method!  Include information on salting and it will be a very good guide!

[Stefany93]

 

 

Agreed, hashing and salts have to go hand-in-hand.  Any discussion of storing hashed passwords without including an explanation of salting is unwise.  Salts add much to the security of the hashed passes in terms of defeating rainbow tables in the event that the DB is compromised.  There were some high profile hacks recently where large numbers of username:password combos were released that would have been effectively rendered null if salts were used with the hashed passes, saving the large companies involved a lot of embarrassment and the affected users a lot of frustration.  It is just too much effort for the attacker to recompile his rainbow tables for (pseudo)randomly salted hashes to attack the DB on a large scale.  Salts are obviously somewhat less effective if there is a single or small number of targets that the attacker is focusing on (admin user, etc...) and in these cases the use of strong passwords is essential.

 

That being said, the above is a very nice starter guide to hashing passwords Stefany93.  Believe it or not, I am still seeing newbie guides that were created recently by others recommending the use of md5 as the preferred hashing method!  Include information on salting and it will be a very good guide!

 

 

Thank you very much for the nice words! You and @requinix are absolutely correct that I should include a description about salting as well, otherwise that makes this manual pretty much useless. I will do that in the coming days, thank you!

And WOW, I didn't know that nowadays some guides still recommend MD5(). That's suicide, to say the least!