As you should be aware the HTTP protocol, as used for serving web pages, is completely stateless. This means that after the server has received a request, processed it and sent a response, the process which dealt with that request dies.

Saving PHP Session Data to a Database

Introduction

As you should be aware the HTTP protocol, as used for serving web pages, is completely stateless. This means that after the server has received a request, processed it and sent a response, the process which dealt with that request dies. Anything that the process had in its memory therefore dies with it, so when a subsequent request is received from the same client it is unable to refer to its memory about anything that happened previously.

Fortunately PHP provides a standard method of maintaining memory (state) between requests in the form of Session Handling functions. This allows the programmer to maintain a set of variables in the $_SESSION array which is automatically saved to persistent storage at the end of each script, and then automatically loaded back into memory when a subsequent request is received from a client which supplies the same session_id.

By default the medium used as persistent storage by the session handler will be a series of disk files, one per session, where the file name is the session_id. A file is created khan a new session starts, and is deleted when the session terminates (or has expired). This is perfectly adequate for most circumstances, but it has the following drawbacks:

1. If you are using a shared server then other users of that server may be able to access your session files, thus compromising the security of your site.
2. Each server will have its own directory where these session files are maintained, so if you are employing load balancing across multiple servers there is no guarantee that a request for an existing session will be given to the server which is maintaining the state for that session.
3. It would be difficult for a site administrator to perform such queries as "how many sessions are currently active?" or "which users are currently logged in?"

The authors of PHP have provided the ability to store session data using a method other than disk files by means of the session_set_save_handler function. This document will show how I have used this function to store all my session data in my application database.

Define database table

This is how I have defined the database table which will hold all my session data:

CREATE TABLE `php_session` (
`session_id` varchar(32) NOT NULL default '',
`user_id` varchar(16) default NULL,
`date_created` datetime NOT NULL default '0000-00-00 00:00:00',
`last_updated` datetime NOT NULL default '0000-00-00 00:00:00',
`session_data` longtext,
PRIMARY KEY  (`session_id`),
KEY `last_updated` (`last_updated`)
) ENGINE=MyISAM

Please note the following:

Define database class

Within my development infrastructure it is my practice to use a separate class to access each database table. Each table class is actually a subclass to a generic table class which contains all the functionality which is standard across all database tables. This section identifies the contents of the subclass. A copy of the superclass is contained within the source code for my sample application.

The table class exists in a file called <tablename>.class.inc which is described in A Data Dictionary for PHP Applications.

<?php
require_once 'std.table.class.inc';
class php_Session extends Default_Table
{
// ****************************************************************************
// This class saves the PHP session data in a database table.
// ****************************************************************************
// ****************************************************************************
// class constructor
// ****************************************************************************
function php_Session ()
{
// save directory name of current script
$this->dirname   = dirname(__file__);
$this->tablename = 'php_session';
$this->dbname    = 'audit';
$this->fieldspec = $this->getFieldSpec_original();
// there is absolutely NO logging of the audit database
$this->audit_logging = false;
} // php_Session

The member variable $session_open is for use in the close() method (see below).

The constructor forces the member variable $audit_logging to be false as updates to this database table are not to appear in the audit log.

    // ****************************************************************************
function open ($save_path, $session_name)
// open the session.
{
// do nothing
return TRUE;
} // open

The open() function does not have to do anything as the database is not actually opened until control is passed to my DML class.

    // ****************************************************************************
function close ()
// close the session.
{
if (!empty($this->fieldarray)) {
// perform garbage collection
$result = $this->gc(0);
return $result;
} // if
return FALSE;
} // close

The close() function is responsible for calling the gc() function to perform garbage collection.

    // ****************************************************************************
function read ($session_id)
// read any data for this session.
{
$fieldarray = $this->_dml_getData("session_id='$session_id'");
if (isset($fieldarray[0]['session_data'])) {
$this->fieldarray = $fieldarray[0];
$this->fieldarray['session_data'] = '';
return $fieldarray[0]['session_data'];
} else {
return '';  // return an empty string
} // if
} // read

The read() function is responsible for retrieving the data for the specified session. Note that if there is no data it must return an empty string, not the value NULL.

    // ****************************************************************************
function write ($session_id, $session_data)
// write session data to the database.
{
if (!empty($this->fieldarray)) {
if ($this->fieldarray['session_id'] != $session_id) {
// user is starting a new session with previous data
$this->fieldarray = array();
} // if
} // if
if (empty($this->fieldarray)) {
// create new record
$array['session_id']   = $session_id;
$array['date_created'] = getTimeStamp();
$array['last_updated'] = getTimeStamp();
$array['session_data'] = addslashes($session_data);
$this->_dml_insertRecord($array);
} else {
// update existing record
if (isset($_SESSION['logon_user_id'])) {
$array['user_id']  = $_SESSION['logon_user_id'];
} // if
$array['last_updated'] = getTimeStamp();
$array['session_data'] = addslashes($session_data);
$this->_dml_updateRecord($array, $this->fieldarray);
} // if
return TRUE;
} // write

The write() function is responsible for creating or updating the database with the session data which is passed to it.

    // ****************************************************************************
function destroy ($session_id)
// destroy the specified session.
{
$fieldarray['session_id'] = $session_id;
$this->_dml_deleteRecord($fieldarray);
return TRUE;
} // destroy

If the session_destroy() function is issued in the code then this will be responsible for deleting the session data from the database.

    // ****************************************************************************
function gc ($max_lifetime)
// perform garbage collection.
{
$real_now = date('Y-m-d H:i:s');
$dt1 = strtotime("$real_now -2 hours");
$dt2 = date('YmdHis', $dt1);
$count = $this->_dml_deleteSelection("last_updated < $dt2");
return TRUE;
} // gc
// ****************************************************************************
} // end class
// ****************************************************************************
?>

This is the garbage collection or "clean-up" function. Notice that the time limit of 2 hours has been hard-coded. This means that any session record which has not been modified within this time limit Will be deleted.

Define session handler

This is as described in the manual for the session_set_save_handler() function. These lines must come before the session_start() function.

require_once 'classes/php_session.class.inc';
$session_class = new php_Session;
session_set_save_handler(array(&$session_class, 'open'),
array(&$session_class, 'close'),
                         array(&$session_class, 'read'),
                         array(&$session_class, 'write'),
                         array(&$session_class, 'destroy'),
                         array(&$session_class, 'gc'));

Notice that the arguments describe methods in a class and not stand-alone functions.

As you should be able to see it is a relatively straightforward process to switch the recording of session data from ordinary disk files to a database table. This overcomes the drawbacks inherent with ordinary disk files:

1. The session data is more secure as a potential hacker must be able to log into the database before he can access anything.
2. The use of multiple servers would not create a problem as all session data now resides in a single central place and is accessible by all servers.
3. It is much easier to query the database should the site administrator require information about current sessions or current users.