Storing Passwords in a Database

In this post, I'll go through three common ways to store and retrieve passwords in a database. I'll assume PHP and MySQL, but the techniques should be very similar for other setups.

Only the last method should ever be used for security reasons, but unfortunately, a large number of sites use one of the less secure methods and put their users in danger.

Storing a Password as Plain Text

This is the most basic and definitely least secure method for handling passwords. Never Use This Method!

The basic strategy is to store the password directly in the database. You would then authenticate a user by running a query like this:

SELECT id FROM users WHERE username='johnsmith' AND password='123456'

If the query returns a row, the username and password are correct. As you can see, if anyone intercepts this query along the way, they automatically have the user's username and password. Also if your database gets stolen, the thief has all of your users' usernames and passwords. This is an even bigger problem because most people use the same username and password for everything.

If you ever click a Forgot Your Password link and the site gives you your current password, they are using this method and I would highly suggest not using the site or at least using a unique password just for the site.

Using a Password Hash

This method is better than plain text, but still has some major, relatively little known, security holes in it. A lot of sites use this method thinking it is secure. Again, do not use this method.

This method takes a user's password and converts it to an md5 or similar hash before storing in the database. For example, "123456" becomes "e10adc3949ba59abbe56e057f20f883e". This seems like it solves the problem with plain text since a person cannot look at the hashed string and know the user's password. But, if you type this hash into Google, the second result is titled "Google Hash: md5(123456) = e10adc3949ba59abbe56e057f20f883e". Not as secure as it first looked, is it?

There are md5 hash tables you can download that contain every word in the dictionary and every common password that make this method very susceptible to attacks. Many sites require passwords with numbers, symbols, capital letters, etc., which helps fix the security hole, but why make things harder on your users when you can just use a password salt?

Using a Password Salt

There is no reason not to use this method. It provides an extra layer of security on top of a password hash with very little extra work.

This method generates a random string (salt) and appends it to the user's password before generating an md5 or similar hash. Then, both the password hash and the password salt are stored in the database and used to authenticate the user. For example, "123456" becomes "123456ghjfdweurt" becomes "8e1a92e8f87a5bbf36f26e330cf7f0b5". Try typing that hash into Google and the most you may find is this article.

Here's the PHP code for initially inserting a user into a database. The getRandomString() function is from http://www.lost-in-code.com/programming/php-code/php-random-string-with-numbers-and-letters/.

//get username and password
$username = $_REQUEST['username'];
$password = $_REQUEST['password'];

//generate password salt
$password_salt = genRandomString();

//generate password hash
$password_hash = $password . $password_salt;

//insert into database
$query = "INSERT INTO users (`username`,`password_hash`,`password_salt`) VALUES ('$username', '$password_hash', '$password_salt')";
mysql_query($query);



function  genRandomString() {
    $length = 10;
    $characters = ’0123456789abcdefghijklmnopqrstuvwxyz’;
    $string = ”;    

    for ($p = 0; $p < $length; $p++) {
        $string .= $characters[mt_rand(0, strlen($characters))];
    }

    return $string;
}

Here's the code for authenticating a user once they are already in the database:

//get username and password
$username = $_REQUEST['username'];
$password = $_REQUEST['password'];

//query database
$query = "SELECT * FROM users WHERE username='$username'";
$result = mysql_query($query);

//if no result, username is incorrect
if(!$result) {
    //authentication failed
}

//get database row
$row = mysql_fetch_assoc($result);

//generate password hash from entered password
$password_hash = md5($password . $row['password_salt']);

//check if the generated hash is equal to the hash in the database
if($password_hash === $row['password_hash']) {
     //authentication passed
}
else {
     //authentication failed
}

Important Safety Tip

No matter what method you use, SSL encryption is essential during authentication to protect against man-in-the-middle attacks. This is where an attacker intercepts data between the user and the server. If the user submits a login form and an attacker intercepts it, the password will be compromised no matter which method you use.