Database Authentication

The final, and most complete of the three PHP authentication methods, is the utilization of a database to
maintain and manage the usernames and passwords used to access PHP files. This solution provides
advanced capabilities in administering authentication systems but also provides incredible flexibility and
scalability to incorporate the authentication system into the database system as a whole. The first step of
the process involves creating the user tables that will be used to house the authentication data.

Storing Authentication Data

The following table will be used to manage the storage of the login information that will be used by PHP to
manage logins:

CREATE TABLE `customers` (
`customerEmail` VARCHAR(40) NOT NULL, `lname` VARCHAR(25) NOT NULL,
`fname` VARCHAR(25) NOT NULL, `title` ENUM('Mr.', 'Mrs.', 'Miss', 'Ms.','Dr.'),
`passwd` VARCHAR(30), PRIMARY KEY (`customerEmail`)
);

The idea of using the customerEmail as the login in identifier has become standard practice in many web
applications these days. As far as an email being a primary identifier for the customer, it is perfect; It is
unique (no two people could have the exact same e-mail account), it is specific to the user and it is an
alternate means to contact the user. In this table, there could be additional columns used to support other
areas of the application; however, for the purpose of this training, this table will suffice.

The Database Authentication Script

<?php
// Create a function due to the script components having to be called 2x
function userAuth() {
header('WWW-authenticate: Basic Realm="World Database"');
header('HTTP/1.0 401 Unauthorized');
print "You must provide a valid e-mail and password!";
exit;
}
// Check to see if the login prompt has been submitted
// If not, call up the login prompt, if so, check against
// the database
if (! isset($_SERVER['PHP_AUTH_USER'])) {
userAuth();
} else {
// Connect to the MySQL Server and use the
// world database (which houses the customers table)
$linkID1 = mysql_connect('localhost','root','training')
or die("Could not connect to $dbhost database");
mysql_select_db("world",$linkID1)
or die ("Could not connect to world database");
// Create variables for login data
$serverEmail = $_SERVER['PHP_AUTH_USER'];
$serverPass = $_SERVER['PHP_AUTH_PW'];
// Create and execute the verification
$login_query = "SELECT title, lname FROM customers
WHERE customerEmail = '$serverEmail'
AND passwd = SHA('$serverPass')";
$login_results = mysql_query($login_query, $linkID1);