In a web database application, usernames and passwords can be stored in a table rather than a file. This moves the data stored about users into a database and can simplify the management of an application. In this section we develop techniques to store usernames and passwords securely in a table.
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
[, string
salt
])
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
salt
is 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
.
Example 9-7. Authenticating a user against an encrypted password in the users table
<?php include 'db.inc'; function authenticateUser($connection, $username, $password) { // Test the username and password parameters if (!isset($username) || !isset($password)) return false; // Get the two character salt from the // username collected from the challenge $salt = substr($username, 0, 2); // Encrypt the password collected from // the challenge $crypted_password = crypt($password, $salt); // Formulate the SQL find the user $query = "SELECT password FROM users WHERE user_name = '$username' AND password = '$crypted_password'"; // Execute the query $result = @ mysql_query ($query, $connection) or showerror( ); // exactly one row? then we have found the user if (mysql_num_rows($result) != 1) return false; else return true; } ?>
The authenticateUser( )
function developed in
Example 9-7 is likely to be used in many scripts and
writing the code to a authentication.inc
file
allows the function to be included in the scripts that require
authentication. We could rewrite Example 9-4 to use
the database authentication function by including the
authentication.inc
file:
<?php include("authentication.inc"); include("db.inc"); include("error.inc"); // Connect to the MySQL server // Connect to the Server if (!($connection = mysql_connect($hostName, $username, $password))) die("Could not connect to database"); if (!mysql_selectdb("$databaseName, $connection) showerror(); if !authenticateUser($connection, $PHP_AUTH_USER, $PHP_AUTH_PW))) { // No credentials found - send an unauthorized // challenge response ... header("WWW-Authenticate: Basic realm="Flat Foot""); header("HTTP/1.0 401 Unauthorized"); // ... exit; } // The HTML response to authorized users ... ?>
MySQL
provides the
encryption
function password( )
that can be used instead of the crypt( )
function; we
introduced this function in Chapter 3. The MySQL
password( )
function can be incorporated into the SQL
update or insert queries:
$update_query = "UPDATE users SET password = password($password) WHERE user_name = '$username'";
Like crypt( )
, the MySQL password( )
function is a one-way function, but it is simpler to use
because it doesn’t require a salt string. However,
when identical passwords are used, they are stored as identical
encrypted strings. Another disadvantage to using the MySQL
password( )
function is that the password is
transmitted between the web server and the MySQL DBMS in its
unencrypted form. We recommend that crypt( )
be
used rather than the MySQL password( )
function
when building web database applications.
The PHP crypt( )
and MySQL password( )
functions can be used only to store passwords, personal
identification numbers (PINs), and so on. These functions are
one-way: once the original password is encrypted and stored, you
can’t get it back because there are no corresponding
decode functions. These functions can’t be used to
store sensitive information an application needs to retrieve. For
example, when a customer submits an order, the
customer’s credit-card number needs to be decrypted
and used by the application to complete the transaction.
To store sensitive information the application needs to use, you need two-way functions that use a secret key to encrypt and decrypt the data. We discuss encryption briefly later, in Section 9.5. One significant problem when using a key to encrypt and decrypt data is the need to securely manage the key. The issue of key management is beyond the scope of this book.
PHP provides a set of functions that access the
mcrypt
library, which provides encryption and
decryption support using a variety of encryption standards. To use
mcrypt
functions, you must install the
libmcrypt
library and then compile PHP with the
--with-mcrypt
parameter.
MySQL also has the reversible encode( )
and
decode( )
functions described in Chapter 3.
18.116.36.194