Improved MySQL and PHP login security using salts

Obviously in the age of Drupal and other CMS systems, you generally don’t need to write your own user login/password schemes, but just in case you do, heres some code I fiddled around with recently to

  • Provide a reasonable safe password storage mechanism that was safe from rainbow dictionary attacks.
  • Implement a simple ‘I forgot my password scheme’, which sends an email to confirm the request to the users email address with a clickable link.
    • This could be combined with a Captcha scheme or similar to prevent an automation attacks.


The first thing to realize with passwords, is that storing them as plaintext in a database is just asking for trouble. You always need to encode, encrypt or in someway protect passwords.

Typically, this is done via a one-way cryptographic hash function such as the Secure Hash Algorithm 1 (SHA-1), which returns a pseudo-random 40 character string. For example, calling sha1(‘biteme’) returns:


There are some weaknesses in the SHA-1 algorithm that make it a less than ideal choice for an encoding scheme these days. But there is also a general weakness of such a hash based encodings. Because the hashing function always returns the same hash for the same key, you can create rainbow tables of keys and their hashes. If you can access to the DB, it then becomes relatively easily to start matching user passwords to the rainbow table.

Obviously the longer a user password is and the more non-alphabetic characters it includes all help make the generation of a sufficient rainbow table pretty hard.

The solution to preventing such attacks is to use a stronger encoding scheme and to ‘salt’ the password to make the key to hash mapping unique.

Sadly a lot of PHP/MySQL books(*) use SHA-1 only in their examples to encode passwords, such as ‘PHP and MySQL WebDevelopment’ by Luke Welling and Laura Thompson, probably for clarity and also as they’re trying to cover a lot of ground.

Just remember SHA-1 is not your friend any longer.

Our Data

We’re going to use a few sample MySQL tables to discuss better ways of encoding passwords and handling password resets.

First is our user table which stores an email address, a password field, the date and time of account creation and some password ‘seasoning':

| Field      | Type             | Null | Key | Default | Extra |
| email      | varchar(60)      | NO   | PRI |         |       |
| password   | varchar(64)      | YES  |     | NULL    |       |
| created    | datetime         | NO   |     | NULL    |       |
| pepper     | varchar(10)      | YES  |     | NULL    |       |

And a password reset table, which holds an email address, a password reset token and a date time of the password request. We will uses this when a user forgets a password.

| Field     | Type        | Null | Key | Default | Extra |
| email     | varchar(60) | NO   | PRI | NULL    |       |
| token     | varchar(60) | NO   |     | NULL    |       |
| requested | datetime    | NO   |     | NULL    |       |

Creating an account

You would use a standard webform to capture user details such as name and password. Setting this up is beyond this posting, but you probably want to do this via an SSL session to prevent anyone sniffing user passwords as they’re sent from the browser to the server.

Say that the user was and the password was ‘biteme’.

Conventional examples would have you insert this into your MySQL database by doing something similar to  this:


But as we saw the weakness of SHA-1 and rainbow table attacks, we need to do something a little more complex. We will use the following approach to ‘season’ the password:

  • Use a generic ‘salt’ phrase. That is a somewhat random salt phrase which we will apply to the password in some hidden way to provide a salted-hash.
  • A user specific ‘pepper’ password phrase. Randomly generated at the time of a password change/creation and stored alongside the user account.
  • We then combine these with the password phrase and the SHA-256 function to create a strongly encoded password which is resistant to rainbow table attacks.

The Code

We first generate our unique global salt value (either using the above code or by creating a nice password string by hand) and store it away in a place that is accessible, for example in a config file which is in a protected directory, ie:

 * Global salt value
 $cfg_salt = 'GFGFB-8ba72a57ff'; 

Really this global salt can be anything and the larger the value, the more secure things will be.

We then take then generate a unique salt for each user, the ‘pepper’ in our seasoning using the generate_salt function again. This pepper value will be saved in the DB with the user record.

$pepper = generate_salt(10);

Finally, we season the user’s password by applying both the salt and pepper and use the much more secure SHA-256 variant:

$db_password = hash('sha256', $cfg_salt . $user_password . $pepper);

Obviously you can combine this in many different ways, and some people hash the hash to really randomize things. Its this combination of salts that makes the mapping between the users actually password and the hash keys generated almost impossible to discover.

sha256 is a reference to a specific version of the SHA-2 algorithm and returns a hash of 64 characters. But you could use sha512, md5, bcrypt etc here. See this thread for more details and the pros and cons.

Checking a provided password

The user returns the site and wants to login in. So we once again capture their password using some suitable means and simple:

  • Extract their unique salt ($pepper) from the db.
  • Season the supplied password in the same as before, using the $pepper and $salt
  • Compare against the database for the given email record.
 * @param string $email
 *     User supplied email address
 * @param string $password
 *     User supplied password
function is_authorised_user ( $email, $password )
	global $db_users;
	$result = null;

	// connect to our db
	$db = open_db();
	if ( !mysqli_connect_errno() )
		// clean input before using in sql
		$name = clean_input_for_sql_use($db, $name);

		// load our user specific seasoning from the users database
		$pepper = get_seasoning($db, $email);
		// generate a password by applying global salt + local pepper		
		$password = season_password($password, $pepper);

		$query = "SELECT * FROM ". $db_users. " WHERE email=" .$email. " and password = '" .$password. "'";

		$db_response = $db->query($query);

		if ($db_response) {
			$result = $db_response->fetch_assoc();
	else {
		throw new Exception('Connect Error: ' . mysqli_connect_error());
	return $result;
If the passwords match, then the user can be logged in.
As a final measure, when the user changes their password, I re-generate a new $pepper value for them just to keep things fresh.
Note: If you change the global salt, or the way you combine the seasoning for passwords, you will have to change any encoded passwords. There is plenty of really useful articles on the web about the best approaches to combine techniques to strongly encode passwords (ie double hashing, performance of bcrypt vs sha512 etc).
Obviously the best thing you can is to ensure your users give strong passwords to begin with:
In the next post, we’ll look at reseting user passwords. In the meantime check out these threads:

Working code will be available in the next post.

Comments are closed.