WWW FAQs: How do I add accounts to my website?


2007-11-07: Many websites, including Google and Yahoo but also smaller sites, allow users to create accounts in order to retain the same settings from one visit to the next. When users log in, days or months later, they retain the same settings they had before. Unlike cookies, which remember the user's choices on a particular computer and usually for a limited period of time, accounts can remember a user's preferences forever. And a user can move from one computer to another and still retain access to their account.

PHP provides us with a built-in mechanism for handling "sessions." That is, we can easily keep track of a user's choices during a particular visit to a website. But PHP does not have a built-in system for handling accounts. So most PHP developers wind up reinventing it, writing PHP and MySQL code to store account information in a database.

That's a shame, because PHP's sessions are convenient and require very little programmer effort. If only there were a similar solution for accounts...

Actually, there is! I've written a free, open-source package for exactly this purpose.

Adding Accounts With Accountify

Accountify is a PHP-based login system that can be easily dropped into an existing website. Any website visitor can apply for an account, log in and log out from any page that displays the login prompt. And as the webmaster, the steps you have to take are very simple.

Accountify began life simply as an example for use in this article. But it has since grown into a capable system that deserves a web page of its own. So to learn how to use Accountify in your pages, please check out the Accountify home page for complete how-to information.

2007-11-07: recently added features in Accountify include a "remember me on this computer" option and an "all accounts must be manually approved" option.

Implementing Accounts: How Accountify Works

Many of you just want to know how to get accounts up and running on your web site with my existing solution. And if that's all you're after... just read the accountify home page for complete instructions. But if you want to learn what's going on "under the hood" and become a better PHP programmer, read on.

In The Beginning There Were Sessions

Accountify is layered on top of PHP's existing session handling features. Sure enough, starting a session is the very first thing that happens in login.php:


session_start();

This command starts a new session or reloads an existing one, repopulating the $_SESSION array. For complete information about this, see my article how do I keep track of user sessions?

Of course, sessions aren't enough— we want accounts! So we take advantage of SQL and Pear::DB to build an account system on top of PHP's existing session system.

We bring in Pear::DB with the following code:


require_once 'DB.php';

We then bring in our configuration settings:


require_once 'login_config.php';

Then things get interesting, especially for those of you who are not familiar with object-oriented programming in PHP. If the phrase "object-oriented programming" (or OOP) strikes fear into your heart, don't worry— here I use OOP simply as a way to organize all of Accountify's functions and data into a single convenient "class" (a set of variables and functions associated with a particular type of object). By doing so, I avoid the need to prefix every single variable with "login" or "accountify."

A Quick Look At Object-Oriented PHP Programming

Take a look at this simple example:


class Myclass {
  var $myvariable = 100;
  function Myclass() {
    echo("An object of class Myclass was created\n");
  }
  function myfunction() {
    echo($this->myvariable . "\n");
  }
}

$object = new Myclass();
$object->myfunction();

The functions and variables we declare inside the class can only be accessed by first referring to an object of the class, and then specifying the variable or function (also called a "method") that we want after the special -> operator.

Notice that this code never calls the Myclass function directly. Because this function has the same name as the class, it is considered a "constructor." That means it is automatically called when we create a new object of the class with the new operator, as in this line of code:


$object = new Myclass();

Then we invoke the myfunction function on that object with the -> syntax:


$object->myfunction();

Notice that myfunction refers to $myvariable. We know that this piece of data is associated with the object because it is declared with a var statement within the class, and not in any particular function. And since myfunction is a member function of the class, it can access that data.

In some object-oriented programming languages, it would be enough simply to write:


echo($myvariable . "\n");

Languages like C++ automatically assume that we are referring to a member variable of the current object. But PHP doesn't, so we must explicitly write:


echo($this->myvariable . "\n");

The special variable $this always refers to the current object. PHP's philosophy is that this is less ambiguous, because it will never refer to a local variable inside the function by mistake.

For More Information About Objects in PHP

This is only a quick overview of the object-oriented features used in Accountify. For information about more advanced features, notably inheritance, check out the official PHP 4 classes and objects page as well as the official PHP 5 classes and objects page.

In the case of Accountify, there is just one object to worry about: $login. This object is automatically created at the very end of login.php, just before control returns to your own code:


$login = new Login();

The Login member function (the constructor) sets a few variables and then calls check, the function that determines whether the user is already logged in and retrieves session information from the database.

Fetching Session Data From The Database

The check member function is responsible for fetching the user's session data from the database and storing it in an associative array for our convenience. check first looks for $_SESSION['email'], which will only be set if the user has logged in.

When $_SESSION['email'] is set, check calls loadUserDataBody, which fetches the user's information from the database with a Pear::DB SQL query. I make that query using my own wrapper functions, but since these are just simple wrappers around Pear::DB, I'll present the Pear::DB code here for easier understanding:


$db = DB::Connect("mysql://user:password@localhost/mydatabase", false);
if (PEAR::isError($db)) {
  # Connect to database failed
}
# Associative array results for clearer code
$db->setFetchMode(DB_FETCHMODE_ASSOC);
$query = "SELECT user, email, data, id FROM " .
  $loginSettings['table'] . " WHERE email = ?";
$result = $db->getAll($query, array($email));
if (PEAR::isError($result)) {
  # Database request failed
}
if (count($result) == 0) {
  # There were no matching email addresses,
  # the account does not exist
}
# The account exists, let's look at the data
$user = $result[0]['user'];
$email = $result[0]['email'];
$id = $result[0]['id'];
$userData = result[0]['data'];

What's happening here? First, we use the DB::Connect function to connect to our MySQL database. Pear::DB allows us to conveniently specify the type of database (mysql), the database username and password (provided by your web host), the database host (usually localhost, the same computer as the web server) and the database name.

What if the database connection fails? Then DB::Connect returns an error object. We can check for that by calling PEAR::isError() on the value returned by DB::Connect. If there is no error, then $db is a valid database connection and we can use it to make queries.

Next, we'll put our database connection in "associative fetch mode:"


$db->setFetchMode(DB_FETCHMODE_ASSOC);

What does this do for us? Pear::DB offers an enormously convenient getAll function that returns all of the rows that match our query. That function returns a two-dimensional array— the first dimension being the row number, and the second being the column in our query (user, email, data, or id). We could refer to the first user as $result[0][0], the first email address as $result[0][1], and so on. But it's much more convenient to refer to them via an associative array as $result[0]['user'] and $result[0]['email']. So we switch Pear::DB into that mode with the setFetchMode function.

Placeholders and Safe SQL Programming

So we've connected to a database and built a query, and we're ready to send it to the database server. But what is the ? in our query about?

Every time we want to search for something in a database, we need to put that string into a database query. Many naive PHP programmers unfortunately— and very dangerously— assume it is safe to put user input directly into the query string:


# DO NOT DO THIS! EVER!
$db->getAll("SELECT FROM mydatabase WHERE myvalue = " . $_POST['userfield']);

Of course, this is very dangerous because the user's input could include quote characters, allowing the user to slip in SQL commands and cause horrible damage to your database.

One formerly popular but inadequate solution is PHP's magic_quotes "feature." I put feature in quotes because this "feature" is far more trouble than it's worth! Some older PHP installations automatically "escape" certain characters in all user input with a \ prefix to make them safer in SQL queries. But this is not necessarily right for every database. and it causes problems with applications that don't use databases. So most modern PHP installations have magic_quotes turned off. As it should be.

Another solution, adequate but awkward, is to call the appropriate "escape" function for your database engine. Those who code directly for MySQL do it this way:


mysql_real_escape_string($_POST['userfield']);

And Pear::DB programmers can do it this way:


$db->escapeSimple($_POST['userfield']);

But Pear::DB offers a much better solution: wildcards! These are easy to explain and easy to use:

1. Replace every user-input string in your query with a ?, like this:


SELECT (user) FROM users WHERE age > ?

Then pass an array as the second argument to the getAll function. That array contains the right value for each of the question marks. And Pear::DB automatically takes care of escaping these values for you:


$db->getAll("SELECT (user) FROM users WHERE age > ?", array($_POST['age']));

Notice that I use the array() function here, even though there is only one question mark. The Pear::DB documentation suggests that an array is not necessary when there is only one "wildcard," but in my experience the array is always necessary.

Unpacking User Data

So far, so good. We've fetched the data we wanted. And it's clear how to cope with the fields that are stored directly in the database. All we have to do is stuff them into our associative array:


$userData['user'] = $user;
$userData['email'] = $email;
$userData['id'] = $id;

Later, the mergeUserData function "folds" these fields into the $_SESSION array so that code designed to work with sessions can find them. Notice that the foreach statement allows us to visit each of the keys and values in an associative array, merging them into the $_SESSION associative array:


foreach ($userData as $key=>$val) {
  $_SESSION[$key] = $val;
}

But what about those additional fields you're planning to store in $_SESSION yourself? How does Accountify load and save these in the account database?

Serializing Data For Fun And Profit

Normally, databases don't much care for unstructured data. They prefer that we define everything nicely in advance in a CREATE TABLE or ALTER TABLE statement. But SQL does provide a "free-form" BLOB data type that can be used to store arbitrary information... as long as we don't expect SQL to do anything clever with what's "inside" the BLOB.

We'll take advantage of the "BLOB feature" to store the rest of the data in $_SESSION.

But how does a BLOB help us store an associative array? The answer lies in the serialize and unserialize functions, two highly convenient PHP functions that allow us to turn any PHP variable into a string and then convert it back... even if that variable is actually an array, or even an associative array. Nested arrays are OK too! Object-oriented programmers will find that serialize and unserialize do have some limitations— but these limitations also affect PHP's session feature anyway.

The loginSaveUserData function turns the $_SESSION array into a string with a call to serialize:


$userDataString = serialize($_SESSION);

Then, to avoid writing to the database when we don't really need to, we compare this string to the version we fetched when we loaded the account:


if ($userDataString === $login->oldData) {
  return;
}

Notice the use of === rather than the more common ==. Many PHP programmers do not realize that == considers the strings "" and "0" to be equal when == is used. If either parameter "looks like" a number, PHP converts both sides to numbers first before comparing them with ==. For a truly safe comparison of two strings, always use ===.

Executing Code Automatically At Script Exit

I've mentioned repeatedly that Accountify automatically saves session information to the account database when your script ends. But how is that possible? We can accomplish it via PHP's register_shutdown_function feature.

The mergeUserData function, which folds the user's data into the $_SESSION array, also takes care of registering the loginSaveUserData function to be called when the script exits:


register_shutdown_function('loginSaveUserData');

The loginSaveUserData function will now be automatically called when the script exits. And that's our opportunity to take advantage of serialize and store the session data in the database.

Creating Efficient Tables

Just one more feature is worth discussing in some detail: creating the loginusers table correctly in the first place! The code for this is in the tableCreate function of login.php, which is called from setup.php. Here's the SQL code:


CREATE TABLE IF NOT EXISTS loginusers (
  id VARCHAR(16),
  user VARCHAR(40),
  password VARCHAR(40),
  email VARCHAR(80),
  verify VARCHAR(16),
  verified BOOL,
  created DATETIME,
  lastused DATETIME,
  data BLOB,
  closed VARCHAR(1),
  PRIMARY KEY(id),
  UNIQUE KEY(email),
  UNIQUE KEY(user),
  KEY(verify))

The most important issue here is proper indexing. By specifying that id, email, user and verify are keys with KEY and UNIQUE KEY clauses, we tell the database to index them for efficient searches. This ensures that queries searching for them will succeed quickly. Otherwise, the database slows down linearly (i.e. very quickly!) as more users are added to the system... which is a very bad thing. Logins should succeed right away, not after a lengthy search.

By specifying that the email and user fields are unique keys, we forbid more than one user in the system from having the same email address or username. This is important because users are identified by those fields.

Many programmers store dates and times in VARCHAR or NUMERIC fields. This is not ideal because the SQL engine then has no idea that these fields contain date and time information. The use of the DATETIME type for the created and lastused fields allows us to easily search for users based on their last login date. This is convenient for coding features like "purge idle accounts."

Conclusion

Many programmers want account features for their websites. Unlike ASP.NET, PHP does not have a built-in login system. Accountify fills this gap by providing a flexible way of handling user logins, allowing users to create, verify and manage their accounts and "upgrading" PHP's familiar session system to serve as part of an account system as well.

Legal Note: yes, you may use sample HTML, Javascript, PHP and other code presented above in your own projects. You may not reproduce large portions of the text of the article without our express permission.

Got a LiveJournal account? Keep up with the latest articles in this FAQ by adding our syndicated feed to your friends list!