While the default session storage mechanism is adequate for many PHP developers, you might find yourself wanting to modify its behavior from time to time. Luckily, PHP makes this task very easy, and I show you exactly how to do it.

Storing Sessions in a Database

I believe that one of the hallmarks of a good writer is the ability to mold a complex topic into something both palatable and interesting. These are the characteristics I strive for in Guru Speak, and I hope you consider my efforts to be a success. Please be sure to let me know what issues tend to trouble you the most or in what areas you would like to expand your knowledge and understanding. I am happy to cater to my readers.

This edition's topic is storing sessions in a database.

Background

While the default session storage mechanism is adequate for many PHP developers, you might find yourself wanting to modify its behavior from time to time. One of the most common reasons for wanting to change the default behavior is to store sessions in a database rather than the filesystem. The top reasons for this desire are:

• The application needs to be able to run on multiple servers without server affinity (methods that direct requests from the same client to the same server). An easy way to make sure that sessions continue to work properly is to store sessions in a central database that is common to all servers.
• The application needs to be able to run on a shared host, where there are significant security concerns associated with storing session data in the filesystem.
• The performance needs of the application are very demanding and require a more sophisticated storage solution for session data. There are many existing ideas and methodologies that address database performance issues, and these can be used when sessions are stored in a database.

Luckily, PHP makes this task very easy.

Session Data Store

Before you can store sessions in a database, you need to create a table. The following example demonstrates the CREATE syntax for such a table:

CREATE TABLE sessions (
id varchar(32) NOT NULL,
access int(10) unsigned,
data text,
PRIMARY KEY (id)
);

1. CREATE TABLE sessions (
2. id varchar(32) NOT NULL,
3. access int(10) unsigned,
4. data text,
5. PRIMARY KEY (id)
6. );

While this is MySQL syntax, the same query should work on many databases with very little modification. Keep in mind that you can store any other information you want - perhaps you want to store the name of the application each session is associated with, the timestamp of each session's creation, or even an extra token to help complicate impersonation. I only show you the basic mechanism, and you can modify and expand it to fit your own needs.

If you DESCRIBE this table, MySQL gives you a nice visual representation:

mysql> DESCRIBE sessions;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| id     | varchar(32)      |      | PRI |         |       |
| access | int(10) unsigned | YES  |     | NULL    |       |
| data   | text             | YES  |     | NULL    |       |
+--------+------------------+------+-----+---------+-------+

1. mysql> DESCRIBE sessions;
2. +--------+------------------+------+-----+---------+-------+
3. | Field | Type | Null | Key | Default | Extra |
4. +--------+------------------+------+-----+---------+-------+
5. | id | varchar(32) | | PRI | | |
6. | access | int(10) unsigned | YES | | NULL | |
7. | data | text | YES | | NULL | |
8. +--------+------------------+------+-----+---------+-------+

Now that you have a table to store the sessions in, you can learn how to put it to use.

session_set_save_handler()

PHP provides a function that lets you override the default session mechanism by specifying the names of your own functions for taking care of the distinct tasks. I have organized this article according to these functions that you must write. For each function, I show you an example (using MySQL) and provide an explanation.

The function is called session_set_save_handler(), and it takes six arguments, each of which is the name of a function that you write. These functions are responsible for the following tasks:

1. Opening the session data store
2. Closing the session data store
3. Reading session data
4. Writing session data
5. Destroying all session data
6. Cleaning out old session data

For the purposes of this article, I assume that this function is called as follows:

<?php
 
session_set_save_handler('_open',
                         '_close',
                         '_read',
                         '_write',
                         '_destroy',
                         '_clean');
 
?>

1. <?php
2. session_set_save_handler('_open',
3. '_close',
4. '_read',
5. '_write',
6. '_destroy',
7. '_clean');
8.  ?>

You must call session_set_save_handler() prior to calling session_start(), but you can define the functions themselves anywhere.

The real beauty of this approach is that you don't have to modify your code or the way you use sessions in any way. $_SESSION still exists and behaves the same way, PHP still takes care of generating and propagating the session identifier, and changes made to session configuration directives still apply. All you have to do is call this one function.

I show you examples of these functions, so that you have a more complete understanding. While I provide everything you need, I hope you take the time to understand what the functions do. This is a critical skill that you will be glad to have whenever problems arise.

_open() and _close()

The _open() and _close() functions are closely related. These are used to open the session data store and close it, respectively. If you are storing sessions in the filesystem, these functions open and close files (and you likely need to use a global variable for the file handler, so that the other session functions can use it).

Because you're using a database, _open() and _close() can be as simple as the following:

<?php
 
function _open()
{
    mysql_connect('127.0.0.1', 'myuser', 'mypass');
    mysql_select_db('sessions');
}
 
function _close()
{
    mysql_close();
}
 
?>

1. <?php
2.  
3. function _open()
4. {
5. mysql_connect('127.0.0.1', 'myuser', 'mypass');
6. mysql_select_db('sessions');
7. }
8.  function _close()
9. {
10. mysql_close();
11. }
12.  ?>

However, this approach isn't very flexible. When a database handler isn't specified in functions like mysql_select_db() and mysql_query(), MySQL uses the most recently opened connection. If your code uses mysql_select_db() to select a different database, the attempt to write the session data at the end of the script can potentially fail, because the most recently opened connection has a different database selected. This scenario is quite common, and developers find it terribly frustrating due to the difficulty of debugging it.

There are two ways to gracefully avoid this scenario. You can either use a separate MySQL connection for your session mechanism, or you can make a habit of always using mysql_select_db() prior to any function that depends upon a specific database being selected (this includes both your session mechanism and your application).

In the examples in this article, I use the first method - using a separate connection for the session mechanism. I name this $_sess_db, and my modified _open() and _close() functions are as follows:

<?php
 
function _open()
{
    global $_sess_db;
 
    $_sess_db = mysql_connect('127.0.0.1', 'myuser', 'mypass');
    mysql_select_db('sessions', $_sess_db);
}
 
function _close()
{
    global $_sess_db;
 
    mysql_close($_sess_db);
}
 
?>

1. <?php
2. function _open()
3. {
4. global $_sess_db;
5. $_sess_db = mysql_connect('127.0.0.1', 'myuser', 'mypass');
6. mysql_select_db('sessions', $_sess_db);
7. }
8.  function _close()
9. {
10. global $_sess_db;
11.  mysql_close($_sess_db);
12. }
13.  ?>

I want to make one more minor modification. If you test the return value of a typical function in PHP, it returns TRUE on success and FALSE on failure. You can make _open() and _close() do the same thing with a small modification to each:

<?php
 
function _open()
{
    global $_sess_db;
 
    if ($_sess_db = mysql_connect('127.0.0.1', 'myuser', 'mypass')) {
        return mysql_select_db('sessions', $_sess_db);
    }
 
    return FALSE;
}
 
function _close()
{
    global $_sess_db;
 
    return mysql_close($_sess_db);
}
 
?>

1. <?php
2. function _open()
3. {
4. global $_sess_db;
5. if ($_sess_db = mysql_connect('127.0.0.1', 'myuser', 'mypass')) {
6. return mysql_select_db('sessions', $_sess_db);
7. }
8. return FALSE;
9. }
10. function _close()
11. {
12. global $_sess_db;
13. return mysql_close($_sess_db);
14. }
16. ?>

Now that you have your _open() and _close()functions written, it's time to look at the other functions.

_read()

The _read() function is called whenever PHP needs to read the session data. This takes place immediately after _open(), and both are a direct result of your use of session_start().

PHP passes this function the session identifier, as the following example demonstrates:

<?php
 
function _read($id)
{
    global $_sess_db;
 
    $id = mysql_real_escape_string($id);
 
    $sql = "SELECT data
            FROM   sessions
            WHERE  id = '$id'";
 
    if ($result = mysql_query($sql, $_sess_db)) {
        if (mysql_num_rows($result)) {
            $record = mysql_fetch_assoc($result);
 
            return $record['data'];
        }
    }
 
    return '';
}
 
?>

1. <?php
2.  function _read($id)
3. {
4. global $_sess_db;
5.  
6. $id = mysql_real_escape_string($id);
7.  $sql = "SELECT data
8. FROM sessions
9. WHERE id = '$id'";
10.  if ($result = mysql_query($sql, $_sess_db)) {
11. if (mysql_num_rows($result)) {
12. $record = mysql_fetch_assoc($result);
13.  return $record['data'];
14. }
15. }
16.  return '';
17. }
18.  ?>

PHP expects the session data in return, and you don't have to worry about the format, because PHP provides the data to the _write() function (covered in the next section) in the same format that it expects it. Thus, this function returns exactly what is in the data column for the matching record.

_write()

The _write() function is called whenever PHP needs to write the session data. This takes place at the very end of the script.

PHP passes this function the session identifier and the session data. You don't need to worry with the format of the data - PHP serializes it, so that you can treat it like a string. However, PHP does not modify it beyond this, so you want to properly escape it before using it in a query:

<?php
 
function _write($id, $data)
{
    global $_sess_db;
 
    $access = time();
 
    $id = mysql_real_escape_string($id);
    $access = mysql_real_escape_string($access);
    $data = mysql_real_escape_string($data);
 
    $sql = "REPLACE
            INTO    sessions
            VALUES  ('$id', '$access', '$data')";
 
    return mysql_query($sql, $_sess_db);
}
 
?>

1. <?php
3. function _write($id, $data)
4. {
5. global $_sess_db;
6. $access = time();
7. $id = mysql_real_escape_string($id);
8. $access = mysql_real_escape_string($access);
9. $data = mysql_real_escape_string($data);
10. $sql = "REPLACE
11. INTO sessions
12. VALUES ('$id', '$access', '$data')";
13. return mysql_query($sql, $_sess_db);
14. }
15. ?>

This function uses REPLACE, which behaves exactly like INSERT, except that it handles cases where a record already exists with the same session identifier by first deleting that record. This allows for a graceful way to handle choosing between INSERT and UPDATE without having to first perform a SELECT to see whether a record already exists. However, although REPLACE works with MySQL, it is not standard and does not work with all databases.

_destroy()

The _destroy() function is called whenever PHP needs to destroy all session data associated with a specific session identifier. An obvious example is when you call session__destroy().

PHP passes the session identifier to the function:

<?php
 
function _destroy($id)
{
    global $_sess_db;
 
    $id = mysql_real_escape_string($id);
 
    $sql = "DELETE
            FROM   sessions
            WHERE  id = '$id'";
 
    return mysql_query($sql, $_sess_db);
}
 
?>

1. <?php
2.  function _destroy($id)
3. {
4. global $_sess_db;
5.  $id = mysql_real_escape_string($id);
6.  $sql = "DELETE
7. FROM sessions
8. WHERE id = '$id'";
9.  return mysql_query($sql, $_sess_db);
10. }
11. ?>

The _destroy() function is only responsible for destroying the record in the session data store. It is up to the developer to destroy the data in $_SESSION if desired.

_clean()

The _clean() function is called every once in a while in order to clean out (delete) old records in the session data store. More specifically, the frequency in which this function is called is determined by two configuration directives, session.gc_probability and session.gc_divisor. The default values for these are 1 and 1000, respectively, which means there is a 1 in 1000 (0.1%) chance for this function to be called per session initialization.

Because the _write() function keeps the timestamp of the last access in the access column for each record, this can be used to determine which records to delete. PHP passes the maximum number of seconds allowed before a session is to be considered expired:

<?php
 
function _clean($max)
{
    global $_sess_db;
 
    $old = time() - $max;
    $old = mysql_real_escape_string($old);
 
    $sql = "DELETE
            FROM   sessions
            WHERE  access < '$old'";
 
    return mysql_query($sql, $_sess_db);
}
 
?>

1. <?php
2.  
3. function _clean($max)
4. {
5. global $_sess_db;
6.  
7. $old = time() - $max;
8. $old = mysql_real_escape_string($old);
9.  
10. $sql = "DELETE
11. FROM sessions
12. WHERE access < '$old'";
13.  return mysql_query($sql, $_sess_db);
14. }
15.  ?>

The value that PHP passes to this function comes directly from the session.gc_maxlifetime configuration directive. You can actually ignore this and determine your own maximum lifetime allowed, but it is much better to adhere to the value PHP passes. Doing so better adheres to the idea of transparently changing the storage mechanism. From a developer's perspective, the behavior of sessions should not change.

Recap

You now have all the tools you need to store sessions in a database. Hopefully you also have a batter understanding about the purpose of these six functions and are better prepared to deal with problems as they arise.