MySQL Hashing Function Implementation

What function to use to hash passwords in MySQL?

It's not necessarily that you shouldn't use MD5, as much it's that you shouldn't use just MD5, as this leaves you vulnerable to rainbow-table attacks (a rainbow table is a table of precomputed hash values - if your password is even remotely common or simple, the attacker needs merely to look up the hash and he knows your plaintext password.)

At the very least you should add a salt to every password so that any existing rainbow table is useless, forcing the attacker to generate an entire new rainbow table just for your database of passwords.

Better still is to use a different salt for every password in your database, say the username it's associated with, so that an attacker can't even generate a rainbow table for your whole database and has to crack each entry separately.

MD5 is also a very fast algorithm. Speed is the enemy when it comes to cracking - the longer it takes to generate a hash, the longer it takes for each attempt a hacker makes. Something simple like hashing the plaintext 100 times with a new additional salt each time would be barely perceptible (if at all) to a user logging in to your site, but it would increase the time it takes to brute-force a password by the same 100 times.

Far, far more detail here: http://www.codinghorror.com/blog/archives/000953.html

Steps in implementing hashtable in PHP and Mysql

I think your idea of a hashtable is a little [defunct]. Hashtables break down keys into lists that are alike. For example: hashtable based on first letter of name, so there would be 26 lists. Your hash is the first letter of the name, which then makes it quicker to search through.

md5, sha1 are used to derive hashes that are used to verify that data has not been tampered. they usually come in either 128-bit or 160-bit versions. So it takes X data and sends it through a hash to come up with a 128-bit alphanumeric string that should be the same no matter where it is done. This is usually a security thing.

EDIT: Expanding on Question of how to derive keys.

You can utilize a modulus of the data to create a key to use for the row. In the example data % X where X is the total number of keys you would like to have. The issue with this is that X is difficult to find; if you have 20 items, then making X into 20 is feasible and makes it a quick search as each item has it's own row. But if you have 1000 items, then doing % 1000 is NOT feasible. Doing something like X = 75 would work better for this.

MySQL: UNIQUE text field using additional HASH field

Lets look at your requirements:

You need to ensure that a value field is unique. The value field is a text column and due to the nature of it there is no way to create a unique index on the value field(for now). So using a extra field which is the hash of the field value is your only real option here.

Advantages to this approach:

1. Easy to calculate the hash.
2. It is extremely rare to create a duplicate hash for two different values so your hash values are almost gauranteed to be unqiue.
3. Hashes are normally some numeric value(expressed as hexdecimal) that can be efficiently indexed.
4. The hashes wont take up a lot of space, different hashing function return different length hashes so play around with the different algorithms and test them to find one that suits your need.

Disadvantages of this approach:

1. Extra field to cater for during INSERTS and UPDATES i.e. there is more work to do.
2. If you already have data in the table and this is in production you will have to update the current data and hopefully you dont have duplicates already. Also it will take time to run the update. Thus it might be tricky to apply the change in a already working system.
3. Hashing functions are CPU intensive and can have a negative impact on CPU usage.

I assume you understand what a hash function does and conceptually how it works.

You can find a list of cryptographic functions here: http://dev.mysql.com/doc/refman/5.5/en//encryption-functions.html

MySQL supports as far as I know MD5, SHA, SHA1 and SHA2 hashing functions. Most if not all of these should be sufficient for just hashing. Some functions like MD5 has some issues when used in cryptography applications i.e. when using it in PKI as a signature algorithm etc. However these issues should not be that important when you decide on using it to create a unique value as it is not really being applied in a cryptography context here.

To use the MySQL hashing functions you can try the following examples:

SELECT MD5('1234')
SELECT SHA('1234')
SELECT SHA1('1234')
SELECT SHA2('1234',224);

As with everythig new you should try all the approaches and find the one that will be most successfull in your case.

---

Related Topics