Securing login credentials in a MySQL database using PHP

Here's a little tidbit from a project I've been working on.  I decided to write a new user account system from scratch over the weekend to replace a very old system I used in a Content Management System that I've been updating.  One of the things I've been focusing on is security, as I've seen a rise in attempts to hack some of my older security systems.  (Hey, it's the innarwebs, and there are 1337 people out there.)

The core of my new system is a double salted SHA256 password hash.  There are many different ways to encrypt password data, but after a good deal of experimentation, I find this to be a great combination of security and efficiency, and is definitely more secure than my previous implementation (which wasn't flawed, just simply outdated).

This requires three components: a user-supplied password, a salt stored in the user record, and a master salt stored in a server-side file.

We have no control over the user's password other than to impose certain character length or combination requirements, so that can be as strong or as weak as the user creates.  However, the point of this is primarily to protect the user's password in case the database becomes compromised, so we have to focus on the salts.

The master salt is stored in code, and remains constant.  Because of this, we can (and should) be complex with it. A master salt could be something like:

OhFeioA03*&#wij24

or whatever suits your fancy.

The user salt is something that gets generated at the time the user record gets created (at new user account generation), and is a random string.  For that, I use a function like:

function create_salt() {
    return substr(md5(uniqueid(rand(), true)), 0, 8);
}

So:

$password = "userpass";
$master_salt = "OhFeioA03*&#wij24";
$user_salt = create_salt();

A sample password of "secret" using this combination might look like:

8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92543bb60db959662524dae2c4dd4d1c245f0dee9947d39fc10737ff74515b2446

To get the full hash for the above, I'm basically using:

$hash_string = hash('sha256', $password).hash('sha256', $master_salt).hash('sha256', $user_salt);

Now this is part of a MySQL query, so of course there's a little more to it, and that means we have to protect against SQL injection attacks.  Fortunately for us, PHP has a built-in function that helps us do just that:

mysql_real_escape_string();

This escapes SQL control and delimiter characters (with a \ character) to make them literal, so that MySQL processes them as part of the lookup string instead of appending them to the query.  (For more detail on this and how to prevent it, Google SQL injection.)

So using this injection prevention technique, we need to construct the lookup query to make sure a user's credentials are valid.  There may be better ways to accomplish this, but mine is a two-step process.

First, find the record associated with the user's login name:

$query = "SELECT * FROM `users_table` WHERE `username` = '".mysql_real_escape_string($username)."' LIMIT 1";

Since usernames are unique, I only want 1 result, and using the LIMIT keyword speeds up the query by returning the first result it finds, rather than continuing through the table looking for additional matches (which it will obviously never find).

If no matches are found, we simply drop out of our lookup, throwing a generic username/password fail message.  In my opinion, telling a potential hacker that the specific username wasn't found makes for a weaker system.  We don't want them to know that a username does or doesn't exist, because if they find one that does exist, that cuts their work in half.

If we find a match, however, it's time to employ our password lookup, which requires a little preparation. We need to grab a couple things from the record returned.

In most tables, each user record has a unique record ID set as the primary key, so we'll use that as the user ID.  We'll store that as the $user_id variable.  (Querying against the primary key instead of the username is just to save a few milliseconds.)

Remember that user salt that I stored in the user record?  Grab that as well, and place it in the $user_salt variable.  Our $master_salt variable should already be set in the code somewhere, so we already have that.  Using a secure version of the $hash_string formula above, we can construct a query that will check the username against the password provided.

It should look something like this:

$query = "SELECT * FROM `users_table` WHERE `user_id` = ".$user_id." AND `password` = '".hash('sha256', mysql_real_escape_string($password).hash('sha256', $master_salt).hash('sha256', $user_salt)."' LIMIT 1";

The rest is simple.  If the password matches, we have a valid login.  If it doesn't match, we throw the same username/password error message we would if the username was invalid.  (Remember, even security through obscurity is still security, and every little bit helps.)

There's more that we can do to protect our users' data, but protecting the password is perhaps the most important consideration in any system.

Have you built a better mouse trap?  If so, I'm very interested in hearing about it.