The other day, a friend of mine was asking for the best practice regarding stored passwords in a SQL database.  His first idea was a simple hash (SHA1 in his case).  While this is significantly better than plaintext and even MD5, it is still computationally feasible to reverse/guess the values if the server was compromised.  Assuming a strong password of 12 characters (alpha-numeric and symbols available on a standard keyboard is ~95 possibilities) we have a max of 12^95 possibilities for passwords.  A rainbow table of MD5 and SHA1 lists can be downloaded without a problem, generating them takes significantly more time (but still feasible).

The next logical course of action would then be to include some sort of salt with the password.  This salt would have to be stored somewhere (to allow for password verification) and in the end would only slightly increase the amount of time required to guess the password (as new rainbow tables would have to be generated).

The best option, the same as I used in AreYouAG33k.com, would be to have layers of hashing with salts, example:

md5(lowercase_emailaddress + md5(password))

To brute-force reverse a single password, you would need to generate 32^16 rainbow keys of (32 + email) characters long just to get the md5 of the password for one user.  This is more powerful then a basic salt, frustrating to reverse, and incredibly easy to implement.

At work, I occasionally need to move database changes from the development server to the live server.  We have about a hundred tables, which makes it difficult to review by hand.  So there is a comparison script that basically runs a `mysqladmin` schema dump on both databases and then diffs the two files.  The result is a little difficult to decipher at times and still requires manual scripting to make the final ALTER commands.

What if I could run a PHP script that would compare any two schemas and generate proper ALTER commands to correct the two of them.  I would probably use some combination of these SQL commands:

• SHOW TABLES
• SHOW CREATE TABLE ‘tablename’
• SHOW COLUMNS FROM ‘tablename’

I’m going to have to think about this tonight.  Maybe punch out a test script tomorrow.