CODEWITHSUNDEEP
mysqlpasswordscryptography
trex005
trex005

Reputation: 5115

Salting and hashing passwords in MySQL

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?

Upvotes: 6

Views: 12376

Answers (3)

Connor
Connor

Reputation: 698

Here are two great links:

http://www.aspheute.com/english/20040105.asp

http://alias.io/2010/01/store-passwords-safely-with-php-and-mysql/

First off, you don't want to "encrypt" your passwords. Encrypting implies that they could be de-crypted which... is not a good thing. You want to hash them so that they are 1-way tickets (e.g. SHA512()).

NOTE: MD5 should never be used for secure password hashing. It is easily cracked.

Here is salt simplified/explained as I see it: Salt adds random values to the password hash. Salt is unique to EACH password. (The salt for each password needs to be stored alongside the password). With unique salt for each password, dictionary attacks (password cracking technique) basically have no chance.

So just add salt to your passwords with something such as:

$salt = strtr(base64_encode(mcrypt_create_iv(16, MCRYPT_DEV_URANDOM)), '+', '.');

And store it alongside the password, or as a cookie, or both.

Hope that helps.

Upvotes: 0

martinstoeckli
martinstoeckli

Reputation: 24071

Unfortunately it is not possible to store the password safely with only SQL commands.

Since you should add a random salt to the hashing scheme, you cannot verify the password if the user tries to login. You would have to read the salt of every row in the user table and calculate the hash for comparing.

A safe hash function can be tuned to need a certain amount of time (e.g. 10ms), BCrypt for example has a cost factor. If you have to check every row and every calculation needs some time, you will run into problems if your user table grows.

These are the reasons, why passwords should not be hashed by the database itself. First you have to find the hash and its salt by the given username, afterwards you can verify the password for this single row.

Upvotes: 0

Marcus Adams
Marcus Adams

Reputation: 53830

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.

Upvotes: 3

Related Questions