Salting and hashing passwords in MySQL

Question

I'm looking for a way to store and validate mysql passwords with a salt. I do not have access to PHP or any other encryption software. I'm aware of the PASSWORD() function, but that does not allow for a salt.

Is there another option?

Answer 1

I assume since you're talking about salts, that you really mean hashing, which is a form of one-way cryptography, not encyprtion. A hash guarantees that a given input value always yields the same output value. With a secure hashing algorithm, there is no better way to derive the original clear text outside of trying every clear text value in the hash function.

While SHA1 may be adequate to secure the passwords for many systems, there are definitely better hashing algorithms out there. Yet, SHA1 is available in MySQL.

While the SHA1() MySQL function does not accept a separate salt parameter, you can still salt your passwords. In fact, most hash functions that I'm aware of don't have a separate salt parameter.

To salt a clear text value, simply concatenate a random string to the beginning of the clear text value. Unfortunately, MySQL doesn't have a straightforward way to generate a random string, but this will get you close.

To generate a 6 character (hex value only) random string:

SELECT SUBSTRING(SHA1(RAND()), 1, 6) AS salt

The important thing, of course, is that you must always save the salt. You'll need it again.

Once you've saved the salt, simply hash the password like so:

SELECT SHA1(CONCAT(salt, 'password')) AS hash_value

It is common to store the salt and hashed password in the same column by prefixing the hash value with the salt.

To verify the entered password, simply repeat the process. Prefix the clear text password with the stored salt, hash the concatenated string, and then compare the resulting hash value against the stored password hash.

Each record should have a different random salt.