Re: Encrypting a column (password column)

"Noah Spitzer-Williams" <noahsw@cyberdude.com> wrote: > > Hey, i have a user database in which one of the columns of a table is their > password. however i would like to have this encrypted just so when i look at > it using somethin like phpmyadmin, their password isnt visible. is there an > easy way to do this? > Not to disagree with anything posted by others, but may I make a couple of suggestions? First, while using the MySQL "password()" function is perfectly fine, you should be aware that not all SQL databases support this feature. So if you let MySQL do the encryption, you will perhaps encounter problems if you later want to run your code on DB2 or Oracle or something else. I recommend using the md5() function that's built into PHP itself. This way you know that your code is database-portable. Simply call md5() to encrypt the string before creating the SQL statement that writes it into the database. Second, you should concatenate the login name and some special character ahead of the password before encryption. The reason for this is so that two people who happen to select the same password won't get the same encrypted string. This strengthens your security in case someone manages to gain access to the encrypted fields in the database. Here's an example of how to store the crypted string, using both of my suggestions above. Assume that $login and $password are the cleartext versions supplied to your code. $concat = $login . "\n" . $password; $crypto = md5($concat); $sql = "INSERT INTO user_table (login,passwd) VALUES ('"; $sql .= $login . "','" . $crypto . "')"; # Execute the SQL here. If you instead needed to update, the SQL would be $sql = "UPDATE user_table SET passwd='" . $crypto; $sql .= "' WHERE login='" . $login . "'"; Now, during login when it's time to compare the user-entered password with the one in the database, you simply do this: $concat = $login . "\n" . $password_they_entered; $entered_crypto = md5($concat); # Use any desired means to get the value of the # 'passwd' column from the database into the local # variable $crypto. if ($entered_crypto == $crypto) { # Good login } else { # Bad password } In the future, I'm hopeful that PHP can support the SHA1 one-way encryption function. It's my understanding that this is stronger even than MD5. For now, however, MD5 is considered strong enough encryption for all but the most sensitive data. The weak link in the system I've outlined above is that the cleartext password is still sent on the wire at every login. You may wish to consider using HTTPS if your application is e-commerce or otherwise involves sensitive financial data. Hope this helps! Scott Courtney courtney@4th.com