Later in this chapter we continue the development of the winestore application using the customer table as a source of authentication details. To demonstrate the principles, consider the following simple table:
CREATE TABLE users ( user_name varchar(10) not null, password varchar(15) not null, PRIMARY KEY (user_name), KEY password (password) );
This table defines two attributes: user_name and password. The user_name must be unique, and in the users table, it is defined as the primary key. The password attribute needs to be indexed as you formulate queries on the password in the authentication script developed later in this section. It's unwise to store user passwords as plain text in this table. There are many ways to retrieve passwords from a database, and even with good web site practices and policies, storing plain-text passwords is a security risk.
PHP provides the crypt( ) function that can protect passwords stored in a database:
- string crypt(string
plainText[, stringsalt]) -
Returns an encrypted string using the Unix DES encryption method. The plain text to be encrypted is passed as the first argument, with an optional second argument used to salt the DES encryption algorithm. By default, the
saltis a two-character string used by DES to make the encrypted string harder to crack; PHP generates a random salt if one isn't provided. The first two characters of the returned value is the salt used in the encryption process. This function is one-way: the returned value can't be decrypted back into the original string. There are several PHP constants that control the encryption process, and the default behavior is assumed in the examples. You should consult the PHP manual for more details.
Rather than encrypt the password directly, the crypt( ) function encrypts a digest of the password, and the result is a constant length irrespective of the password length. A two-character seed or salt is used by the crypt( ) function to effectively provide an encryption key. If a salt isn't passed to the function, crypt( ) generates its own random string.
A common strategy for storing passwords uses the first two characters of the username as the salt to the crypt( ) function. This method of salting the encryption process helps to hide the cases where two or more users happen to choose the same password. Example 9-6 shows how a password is encrypted using the username to salt the crypt( ) function and updated in a user row. The updatePassword( ) function takes a MySQL connection handle, a username, and a password as parameters. The function creates the encrypted password and executes an UPDATE statement to update the password for the selected user row.
Example 9-6. A function to update a password in the users table
<?php
include "db.inc";
// Update a password in a users table
function updatePassword($connection,
$username,
$password)
{
// Use the first two characters of the
// username as a salt
$salt = substr($username, 0, 2);
// Create the encrypted password
$stored_password = crypt($password, $salt);
// Update the user row
$update_query =
"UPDATE users
SET password = '$stored_password'
WHERE user_name = '$username'";
// Execute the UPDATE
$result = @ mysql_query ($update_query,
$connection)
or showerror( );
}
?>
The following SELECT statement shows how rows in the users table might look:
mysql> SELECT * FROM users; +-----------+---------------+ | user_name | password | +-----------+---------------+ | robin | roGNvdAjJ1BDw | | sue | suRQ0N4.ZOh0. | | jill | jiDKFQigcAGTc | | margaret | maNLEWbP2wdY. | | sally | saHXb3nOaykJM | | penny | pekh5W4yLAyd. | +-----------+---------------+ 6 rows in set (0.00 sec)
Because crypt( ) is one way, once a password is stored, there is no way to read back the original. This prevents desirable features such as reminding a user of his forgotten password. However, importantly, it prevents all but the most determined attempts to get access to the passwords.
When a script needs to authenticate a username and password collected from an authentication challenge, a query is executed to find a user row in the users table. Example 9-7 shows the authenticateUser( ) function that constructs and executes this query. The function is called by passing in a handle to a connected MySQL server and the username and password collected from the authentication challenge. The script begins by testing $username and $password. If neither is set, the function returns false. The $password is then encrypted using the crypt( ) function with the first two characters from the $username as the salt. A SELECT query is constructed to search the users table. A query is then executed that searches for a user row in which the user_name and password attributes have the respective values of $username and the encrypted password. If a row is found, the $username and $password have been authenticated, and the function returns true.