Salting my hashes with PHP and MySQL

Question

Like most users, I'm simply trying to figure out a secure way to store passwords. What I haven't found here (or maybe it's my lack of understanding) is how to retrieve a salted hash in my database and separate the salt from the hashed password, especially with unique salts to each password while maintaining the salt+password in a single column.

I'm finding all these cool ways to encrypt passwords (SHA-256, but does MySQL only support SHA/1 and MD5?) and other things from the PHP manual, but not sure how store and retrieve the passwords.

So, far this is all I understand:

SHA('$salt'.'$password') // My query sends the password and salt 
                         // (Should the $salt be a hash itself?)

After that I'm lost with salts.

Retrieving the password without a salt is easy, but the salt confuses me. Where do I get the value from $salt again, especially if it's unique and secure? Do I hide them in another database? Constant (seems unsafe)?

EDIT: Is the key variable in HMAC supposed to be salt or is this something else?

Answer 1

This seems covered in-depth in a previous post: http://stackoverflow.com/questions/401656/secure-hash-and-salt-for-php-passwords

Answer 2

First of all, your DBMS (MySQL) does not need to have any support for cryptographic hashes. You can do all of that on the PHP side, and that's also what you should do.

If you want to store salt and hash in the same column you need to concatenate them.

// the plaintext password
$password = (string) $_GET['password'];

// you'll want better RNG in reality
// make sure number is 4 chars long
$salt = str_pad((string) rand(1, 1000), 4, '0', STR_PAD_LEFT);

// you may want to use more measures here too
// concatenate hash with salt
$user_password = sha512($password . $salt) . $salt;

Now, if you want to verify a password you do:

// the plaintext password
$password = (string) $_GET['password'];

// the hash from the db
$user_password = $row['user_password'];

// extract the salt
// just cut off the last 4 chars
$salt = substr($user_password, -4);
$hash = substr($user_password, 0, -4);

// verify
if (sha512($password . $salt) == $hash) {
  echo 'match';
}

You might want to take a look at phpass, which also uses this technique. It is a PHP hashing solution which uses salting amongst some other things.

You should definitely take a look at the answer to the question WolfOdrade linked to.

Answer 3

Personally I recommend letting MySQL do this with its built in functions.

They way I do this is to create a function in my database config file which returns a key string. The config file should be outside your sites root so that the webserver can access the file but not others. so for example:

function enc_key(){
     return "aXfDs0DgssATa023GSEpxV";
}

Then in your script use it with the sql query and AES_ENCRYPT and AES_DECRYPT functions in MySQL like this:

require_once('dbconf.inc.php');

$key = enc_key();

//When creating a new user
$sql = "INSERT INTO users (username, password) VALUES ('bob', AES_ENCRYPT('{$key}', {$password}))";

//When retrieving users password
$sql = "SELECT AES_DECRYPT('{$key}', password) AS password FROM users WHERE username like 'bob'";