Signal > Noise

musings of a geek

• Home
• About Me
• Behind The Site

MySQL based Authentication System April 1, 2008

I spend most of my (limited) free time online helping people with their PHP/MySQL woes. One of the most common problems crop up when people try to create a simple authentication system linking PHP with a user database. To help people with this issue (and to avoid having to repeat the same thing over and over again) I’m writing this very simple tutorial to guide the less-experienced.

Setting Up the Environment

To get things started, you need to have a web server (such as Apache) already setup to parse PHP scripts. You must also have installed MySQL on your server. You can follow the instructions posted at the php site for setting up your environment. MySQL installation is a breeze if you are on Windows. Simply download the windows binary and set it up.

Linking PHP with MySQL is tricky, especially if you are using MySQL 4.1+ that changed the authentication process. See the extentions reference at php.net for more information. For the sake of berevity, I will concentrate on code that is designed to run on MySQL versions < 4.1 (which is what most hosting environments provide). In addition, this tutorial will assume you have already setup a username and password to MySQL.

Database Basics

It makes sense to understand a few things about databases before we jump right in and try to access/edit/delete records. A database is simply a method of storing information so that it can be retrieved easily. Examples of databases include:

• Index cards (ie, “rolodex”)
• File cabinets (with folders)
• Computer’s file system (directories, folders, files, etc.)
• The Yellow Pages

All these allow you to access information quickly. However, there is really no way to relate one piece of information with another. Think of it as “cross-referencing” information. In computer terms, when people talk of databases, what they really mean is RDBMS (Relational Database Management Systems) — these include all your usual suspects — Access, MySQL, MSSQL, Oracle, et. al. The R stands for relational, which means that information in these systems has some sort of relationship, or link with each other. The other end (Management Systems) implies that these programs provide a system to manage different databases, not just a single database.

Anatomy of a Database

Now that we understand what is a database, lets concentrate on what is contained inside a database. A database is divided into one or many tables, which is a collection of related sets of information (called records). Each table has one or more columns (also called “fields”). These can be thought of as attributes of an object that is stored in this table. To make sense of all this, lets take an example of a simple address book. An entry in an address book might contain the following pieces of information:

• The name of a person
• Their telephone numbers
• Their address
• Their email address
• Their birthdate

What we have listed above are the attributes (properties, fields) of an entry (record) in an address book. In other words, each entry in an address book might contain some of the information above (it need not contain all information listed above). When designing databases, you must also start from such simple steps. List all the different properties of a piece of information that you would like stored. These properties make good candidates for fields that you might include in a table that would hold this information.

For a more detailed explanation on databases and how information is stored in them, look for an upcoming article that is currently being written.

Authentication System Basics

Our authentication system is very simple. It will verify against a list of known users (stored in a database) the input given. Using SQL, it will determine if a user’s credentials are valid, and if they are, will set appropriate variables and show privileged information.
To start things off, lets create our user table that holds our logins. The following SQL creates our table:

CREATE TABLE `user_logins` (
`userid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`login` VARCHAR(8),
`pass` CHAR(32),
`email` VARCHAR(255));

Very simply, this stores the minimum information required for a user to be authenticated. There is their login, a password and an email address (you will see why this is there later). To populate this table, lets add a few users:

INSERT INTO `user_logins` (`userid`,`login`,`pass`,`email`)
VALUES ('','user1',md5('pass'),'user@example.com'),
('','user2',md5('anotherpass'),'user2@example.com');

Now we have two users in our system, and it is time to finally get down to writing some PHP!

Step 1. Connecting To the Database

The first thing we need to do, is to make sure we can connect to our database server. To connect to the server, we need to have a username and password, plus the address of the server itself. PHP provides the mysql_connect() function. Lets see how this would work:

   $dbUser = 'user';
   $dbPass = 'secret';

   $dbHost = 'localhost'; // This is normally 'localhost'
   $dbName = 'testdb';

   $status = mysql_connect($dbHost,$dbUser,$dbPass);

   if (!$status) { die(mysql_error()); }

   $status = mysql_select_db($dbName);

   if (!$status) { die(mysql_error()); }

So we start off by defining some variables to hold our database connection information. The database server ($dbHost in the example) is almost always localhost, which is a special name that loops back to the computer running the software — essentially what this means is that the webserver (where the php code is running) and the MySQL server are the same physical computer.

Then we connect to the database server, and store the returned status value. This is a good habit to get into especially when accessing any external source (files, other websites, database servers) because you cannot determine the status of the external system; in other words you cannot rely on the external service being functional. If the function returned a false value, then we check the returned error message with mysql_error().

Finally we need to select the database that will be used for this connection. The mysql_select_db() function does just that. As always, we check the status to make sure everything was okay.

As this point, we have established a connection to the server and have a selected a database to be used for the connection.

Whenever you are working with MySQL, always use mysql_error() to verify that your queries are functioning properly. Ninety-nine percent of MySQL related problems can be solved by checking the error messages returned.

Step 2. Validating User Input & Authenticating

Now that we have a database setup, and we have verified that we can connect to it, it is time to actually verify some credentials.

To verify credentials, we have to see if a row matches the given username and password combinations. However, since the passwords in our databases are actual MD5 hashes, we have to compare a hash of the password entered. If a row matches, then the username and password provided by the user is valid and they can be authenticated.

There is an important difference between authenticated and authorized. Authenticated means that the system knows who you are; you have provided a valid set of credentials which have been matched by the system. Authorization is the ability to perform an action. You may be authenticated to the system, but you may not be authorized to do a particular task or function. In designing a system, authorization usually implies rights or the ability to do something — generally referred to as permissions or priviledge. They are generally described is ‘action’ terms; ie “can change password”, “cannot view financial reports”. You can think of it in these general terms:

Authenticated = “Who are you?”
Authorized = “What can you do?”

These simple concepts will help you build a proper security authentication and permissions system

$user = 'someuser';
$pass = 'somepass';

// First step, check to see if we have a valid
// username and password entered.

$user = strlen(trim($user)) == 0 ? FALSE : mysql_real_escape_string($user);
$pass = strlen(trim($pass)) == 0 ? FALSE : mysql_real_escape_string($pass);

if ($user == FALSE || $pass == FALSE)
{
    die('Please enter both a username and a password');
}

// Next, we need to build our query string to check
// the entries against the table

$q = "SELECT `userid`, `email` FROM `user_logins` WHERE ";
$q .= "`login` =  '".$user."' AND `pass` = 'MD5('".$pass."')'";

// We execute the query against our database

$result = mysql_query($q);

// Lets check the status

if (!$result) { die(mysql_error()); }

// Great, we have some results, lets fetch them!

$rows = mysql_fetch_assoc($result);

echo "Your user id is ".$result['userid']." and your email is ".$result['email']."!";

if (strlen(trim($user) == 0)
{
    $user = FALSE;
} else {
    $user = mysql_real_escape_string($user);
}

// First a simple check to make sure
// we are requested via POST, otherwise
// we redirect back to the login page

if ($_SERVER['REQUEST_METHOD'] != 'POST')
{
    header("Location: login.php");
    exit();
}

session_start();

$user = $_POST['user'];
$pass = $_POST['pass'];

// First step, check to see if we have a valid
// username and password entered.

$user = strlen(trim($user)) == 0 ? FALSE : mysql_real_escape_string($user);
$pass = strlen(trim($pass)) == 0 ? FALSE : mysql_real_escape_string($pass);

if ($user == FALSE || $pass == FALSE)
{
    echo('Please enter both a username and a password');
    exit();
}

// Next, we need to build our query string to check
// the entries against the table

$q = "SELECT `userid`, `email` FROM `user_logins` WHERE ";
$q .= "`login` =  '".$user."' AND `pass` = 'MD5('".$pass."')'";

// We execute the query against our database

$result = mysql_query($q);

// Lets check the status

if (!$result) { echo(mysql_error()); exit(); }

// Great, we have some results, lets fetch them!

$rows = mysql_fetch_assoc($result);

// Now that we have the results, lets set some
// session variables

$_SESSION['userid'] = $rows['userid'];
$_SESSION['email'] = $rows['email'];

// Finally, we direct the user to the authorized page

header("Location: authorized.php");
exit();

session_start();
if (strlen(trim($_SESSION['email'])) == 0)
{
    // We do not have an email address
    // in our session array, most likely
    // the user is not authorized, so we redirect
    // to the login page
    header("Location: login.php");
    exit();
}

echo "Welcome authorized user!  Your email is ".$_SESSION['email'];