Authentication Using a Database (Web Database Applications with PHP & MySQL)

9.3. Authentication Using a Database

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.

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.

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.

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 ...

?>

9.3.1. MySQL encryption

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.

9.3.1.1. Encrypting other data in a database

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.