MySQL Cookbook

19.3 Using MySQL-Based Storage with the PHP Session Manager

19.3.1 Problem

You want to use session storage for PHP scripts.

19.3.2 Solution

PHP 4 includes session managment. By default, it uses temporary files for backing store, but you can configure it to use MySQL instead.

19.3.3 Discussion

PHP 4 includes a native session manager. This section shows how to use it and how to extend it by implementing a storage module that saves session data in MySQL.^[4] If your PHP configuration has both the track_vars and register_globals configuration directives enabled, session variables will exist as global variables of the same names in your script. (track_vars is enabled automatically for PHP 4.0.3 or later; for earlier versions, you should enable it explicitly.) If register_globals is not enabled, you'll need to access session variables as elements of the $HTTP_SESSION_VARS global array or the $_SESSION superglobal array. This is less convenient than relying on register_globals, but is also more secure. (Recipe 18.6 discusses PHP's global and superglobal arrays and the security implications of register_globals.)

^[4] PHP 3 provides no session support. PHP 3 users who require session support may wish to look into PHPLIB or another package that includes a session manager.

19.3.4 The PHP 4 Session Management Interface

PHP's session management capabilities are based on a small set of functions, all of which are documented in the PHP manual. The following list describes those likely to be most useful for day-to-day session programming:

session_start ( )

Opens a session and extracts any variables previously stored in it, making them available in the script's global namespace. For example, a session variable named x becomes available as $_SESSION["x"] or $HTTP_SESSION_VARS["x"]. If register_globals is enabled, x also becomes available as the global variable $x.

session_register ( var_name)

Registers a variable in the session by setting up an association between the session record and a variable in your script. For example, to register $count, do this:

session_register ("count");

If you make any changes to the variable while the session remains open, the new value will be saved to the session record when the session is closed. Observe that variables are registered by name rather than by value or by reference:

session_register ($count);        # incorrect
session_register (&$count);       # incorrect

Several variables may be registered at once by passing an array that contains multiple names rather than by passing a single name:

session_register (array ("count", "timestamp"));

Registering a variable implicitly starts a session, which means that if a script calls session_register( ), it need not call session_start( ) first. However, session_register( ) is effective only if register_globals is enabled. To avoid reliance on register_globals, you should call session_start( ) explicitly and get your session variables from either the $_SESSION or the $HTTP_SESSION_VARS array.

session_unregister ( var_name)

Unregisters a session variable so that it is not saved to the session record.

session_write_close ( )

Writes the session data and closes the session. Normally you need not call this function; PHP saves an open session automatically when your script ends. However, it may be useful to save and close the session explicitly if you want to modify session variables without having the changes tracked in the session data. In that case, you should call this function to close the session before making the changes.

session_destroy ( )

Removes the session and any data associated with it.

session_name ($name)

The PHP session manager knows which session to use by means of the session identifier. It looks for the identifier in a global variable named $PHPSESSID; in a cookie, GET, or POST variable named PHPSESSID; or in a URL parameter of the form PHPSESSID=value. (If none of these are found, the session manager generates a new identifier and begins a new session.) The default identifier name is PHPSESSID, but you can change it. To make a global (site-wide) change, edit the session.name configuration directive in php.ini. To make the change for an individual script, call session_name($name) before starting the session, where $name represents the session name to use. To find out the current session identifier name, call session_name( ) with no argument.

The following example demonstrates one of the simplest uses for a session, which is to display a counter showing the number of requests received so far during the course of the session:

session_start ( );
session_register ("count");
if (!isset ($count))
    $count = 0;
++$count;
printf ("This session has been active for %d requests.", $count);

session_start( ) opens the session and extracts its contents into the script's global namespace. (For the initial request, this has no effect because the session is empty.) session_register( ) registers the count session variable to cause changes to the corresponding PHP variable $count to be tracked in the session data. For the first request, no such variable will be present in the session. This is detected by the isset( ) test, which initializes the counter. (On subsequent requests, registering count will cause $count to have the value assigned to it during the previous request.) Next, the counter's value is incremented and printed. When the script ends, PHP implicitly invokes session_write_close( ), which saves the new counter value to the session automatically.

The example uses session_register( ) and thus assumes that register_globals is enabled. Later on, we'll discuss how to avoid this limitation.

19.3.5 Specifying a User-Defined Storage Module

The PHP session management interface just described makes no reference to any kind of backing store. That is, the description specifies nothing about how session information actually gets saved. By default, PHP uses temporary files to store session data, but the session interface is extensible so that other storage modules can be defined. To override the default storage method and store session data in MySQL, you must do several things:

• Set up a table to hold session records and write the routines that implement the storage module. This is done once, prior to writing any scripts that use the new module.

• Tell PHP that you're supplying a user-defined storage manager. You can do this globally in php.ini (in which case you make the change once), or within individual scripts (in which case it's necessary to declare your intent in each script).

• Register the storage module routines within each script that wants to use the module.

19.3.5.1 Creating the session table

Any MySQL-based storage module needs a database table in which to store session information. Create a table named php_session that includes the following columns:

CREATE TABLE php_session
(
    id      CHAR(32) NOT NULL,
    data    BLOB,
    t       TIMESTAMP NOT NULL,
    PRIMARY KEY (id)
);

You'll recognize the structure of this table as quite similar to the sessions table used by the Apache::Session Perl module. The id column holds session identifiers, which are unique 32-character strings (they look suspiciously like Apache:Session identifiers, which is not surprising, given that PHP uses MD5 values, just like the Perl module). data holds session information. PHP serializes session data into a string before storing it, so php_session needs only a large generic string column to hold the resulting serialized value. The t column is a TIMESTAMP that MySQL updates automatically whenever a session record is updated. This column is not required, but it's useful for implementing a garbage collection policy based on each session's last update time.

A small set of queries suffices to manage the contents of the php_session table as we have defined it:

• To retrieve a session's data, issue a simple SELECT based on the session identifier:

  SELECT data FROM php_session WHERE id = 'sess_id';

• To write session data, a REPLACE serves to update an existing record (or to create a new one if no such record exists):

  REPLACE INTO php_session (id,data) VALUES('sess_id','sess_data');

  REPLACE also updates the timestamp in the record when creating or updating a record, which is important for garbage collection.

  Some storage manager implementations use a combination of INSERT and a fallback to UPDATE if the INSERT fails because a record with the given session ID already exists (or an UPDATE with a fallback to INSERT if the UPDATE fails because a record with the ID does not exist). In MySQL, a dual-query approach is unnecessary; REPLACE performs the required task with a single query.

• To destroy a session, delete the corresponding record:

  DELETE FROM php_session WHERE id = 'sess_id';

• Garbage collection is performed by removing old records. The following query deletes records that have a timestamp value more than sess_life seconds old:

  DELETE FROM php_session
  WHERE t < DATE_SUB(NOW( ),INTERVAL sess_life SECOND);

These queries form the basis of the routines that make up our MySQL-backed storage module. The primary function of the module is to open and close MySQL connections and to issue the proper queries at the appropriate times.

19.3.5.2 Writing the storage management routines

User-defined session storage modules have a specific interface, implemented as a set of handler routines that you register with PHP's session manager by calling session_set_save_handler( ). The format of the function is as follows, where each argument is a handler routine name specified as a string:

session_set_save_handler (
    "mysql_sess_open",      # function to open a session
    "mysql_sess_close",     # function to close a session
    "mysql_sess_read",      # function to read session data
    "mysql_sess_write",     # function to write session data
    "mysql_sess_destroy",   # function to destroy a session
    "mysql_sess_gc"         # function to garbage-collect old sessions
);

You can name the handler routines as you like; they need not necessarily be named mysql_sess_open( ), mysql_sess_close( ), and so forth. They should, however, be written according to the following specifications:

mysql_sess_open ($save_path, $sess_name)

Performs whatever actions are necessary to begin a session. $save_path is the name of the location where sessions should be stored; this is useful for file storage only. $sess_name indicates the name of the session identifier (for example, PHPSESSID). For a MySQL-based storage manager, both arguments can be ignored. The function should return TRUE or FALSE to indicate whether or not the session was opened successfully.

mysql_sess_close ( )

Closes the session, returning TRUE for success or FALSE for failure.

mysql_sess_read ($sess_id)

Retrieves the data associated with the session identifier and returns it as a string. If there is no such session, the function should return an empty string. If an error occurs, it should return FALSE.

mysql_sess_write ($sess_id, $sess_data)

Saves the data associated with the session identifier, returning TRUE for success or FALSE for failure. PHP itself takes care of serializing and unserializing the session contents, so the read and write functions need deal only with serialized strings.

mysql_sess_destroy ($sess_id)

Destroys the session and any data associated with it, returning TRUE for success or FALSE for failure. For MySQL-based storage, destroying a session amounts to deleting the record from the php_session table that is associated with the session ID.

mysql_sess_gc ($gc_maxlife)

Performs garbage collection to remove old sessions. This function is invoked on a probabilistic basis. When PHP receives a request for a page that uses sessions, it calls the garbage collector with a probability defined by the session.gc_probability configuration directive in php.ini. For example, if the probability value is 1 (that is, 1%), PHP calls the collector approximately once every hundred requests. If the value is 100, it calls the collector for every request—which probably would result in more processing overhead than you'd want.

The argument to gc( ) is the maximum session lifetime in seconds. Sessions older than that should be considered subject to removal. The function should return TRUE for success or FALSE for failure.

The handler routines are registered by calling session_set_save_handler( ), which should be done in conjunction with informing PHP that you'll be using a user-defined storage module. The default storage management method is defined by the session.save_handler configuration directive. You can change the method globally by modifying the php.ini initialization file, or within individual scripts:

• To change the storage method globally, edit php.ini. The default directive setting specifies the use of file-based session storage management:

  session.save_handler = files;

  Modify this to indicate that sessions will be handled by a user-level mechanism:

  session.save_handler = user;

  If you're using PHP as an Apache module, you'll need to restart Apache after modifying php.ini so that PHP notices the changes.

  The problem with making a global change is that every PHP script that uses sessions will be expected to provide its own storage management routines. This may have unintended side effects for other script writers if they are unaware of the change. For example, other developers that use the web server may wish to continue using file-based sessions.

• The alternative to making a global change is to specify a different storage method by calling ini_set( ) on a per-script basis:

  ini_set ("session.save_handler", "user");

  ini_set( ) is less intrusive than a global configuration change. The storage manager we'll develop here uses ini_set( ) so that database-backed session storage is triggered only for those scripts that request it.

To make it easy to access an alternative session storage module, it's useful to create a library file, Cookbook_Session.php. Then the only thing a script need do to use the library file is to include it prior to starting the session. The outline of the file looks like this:

<?php
# Cookbook_Session.php - MySQL-based session storage module

include_once "Cookbook.php";

# Define the handler routines

function mysql_sess_open ($save_path, $sess_name) ...
function mysql_sess_close ( ) ...
function mysql_sess_read ($sess_id) ...
function mysql_sess_write ($sess_id, $sess_data) ...
function mysql_sess_destroy ($sess_id) ...
function mysql_sess_gc ($gc_maxlife) ...

# Initialize connection identifier, select user-defined session
# handling and register the handler routines

$mysql_sess_conn_id = FALSE;
ini_set ("session.save_handler", "user");
session_set_save_handler (
                    "mysql_sess_open",
                    "mysql_sess_close",
                    "mysql_sess_read",
                    "mysql_sess_write",
                    "mysql_sess_destroy",
                    "mysql_sess_gc"
                );
?>

The library file includes Cookbook.php so that it can access the connection routine for opening a connection to the cookbook database. Then it defines the handler routines (we'll get to the details of these functions shortly). Finally, it initializes the connection identifier, tells PHP to get ready to use a user-defined session storage manager, and registers the handler functions. Thus, a PHP script that wants to store sessions in MySQL performs all the necessary setup simply by including the Cookbook_Session.php file:

include_once "Cookbook_Session.php";

The interface provided by the Cookbook_Session.php library file exposes a global database connection identifier variable ($mysql_sess_conn_id) and a set of handler routines named mysql_sess_open( ), mysql_sess_close( ), and so forth. Scripts that use the library should avoid using these global names for other purposes.

Now let's see how to implement each handler routine.

• Opening a session.

  PHP passes two arguments to this function: the save path and the session name. The save path is used for file-based storage, and we don't need to know the session name, so both arguments are irrelevant for our purposes and can be ignored. The function therefore need do nothing but open a connection to MySQL:

  function mysql_sess_open ($save_path, $sess_name)
  {
  global $mysql_sess_conn_id;
  
      # open connection to MySQL if it's not already open
      $mysql_sess_conn_id or $mysql_sess_conn_id = cookbook_connect ( );
      return (TRUE);
  }

• Closing a session.

  The close handler checks whether or not a connection to MySQL is open, and closes it if so:

  function mysql_sess_close ( )
  {
  global $mysql_sess_conn_id;
  
      if ($mysql_sess_conn_id)        # close connection if it's open
      {
          mysql_close ($mysql_sess_conn_id);
          $mysql_sess_conn_id = FALSE;
      }
      return (TRUE);
  }

• Reading session data.

  The mysql_sess_read( ) function uses the session ID to look up the data for the corresponding session record and returns it. If no such record exists, it returns the empty string:

  function mysql_sess_read ($sess_id)
  {
  global $mysql_sess_conn_id;
  
      $sess_id = addslashes ($sess_id);
      $query = "SELECT data FROM php_session WHERE id = '$sess_id'";
      if ($res_id = mysql_query ($query, $mysql_sess_conn_id))
      {
          list ($data) = mysql_fetch_row ($res_id);
          mysql_free_result ($res_id);
          if (isset ($data))
              return ($data);
      }
      return ("");
  }

• Writing session data.

  mysql_sess_write( ) updates a session record (or creates one if there is no record for the session yet):

  function mysql_sess_write ($sess_id, $sess_data)
  {
  global $mysql_sess_conn_id;
  
      $sess_id = addslashes ($sess_id);
      $sess_data = addslashes ($sess_data);
      $query = "REPLACE php_session (id, data) VALUES('$sess_id','$sess_data')";
      return (mysql_query ($query, $mysql_sess_conn_id));
  }

• Destroying a session.

  When a session is no longer needed, mysql_sess_destroy( ) removes the corresponding record:

  function mysql_sess_destroy ($sess_id)
  {
  global $mysql_sess_conn_id;
  
      $sess_id = addslashes ($sess_id);
      $query = "DELETE FROM php_session WHERE id = '$sess_id'";
      return (mysql_query ($query, $mysql_sess_conn_id));
  }

• Performing garbage collection.

  The TIMESTAMP column t in each session record indicates when the session was last updated. mysql_sess_gc( ) uses this value to implement garbage collection. The argument $sess_maxlife specifies how old sessions can be (in seconds). Older sessions are considered expired and candidates for removal, which is easily done by deleting session records having a timestamp that differs from the current time by more than the allowed lifetime:

  function mysql_sess_gc ($sess_maxlife)
  {
  global $mysql_sess_conn_id;
  
      $query = sprintf ("DELETE FROM php_session
                          WHERE t < DATE_SUB(NOW( ),INTERVAL %d SECOND)",
                          $sess_maxlife);
      mysql_query ($query, $mysql_sess_conn_id);
      return (TRUE);  # ignore errors
  }

19.3.5.3 Using the storage module

Install the Cookbook_Session.php file in a public library directory accessible to your scripts. (On my system, I put PHP library files in /usr/local/apache/lib/php.) To try out the storage module, install the following example script, sess_track.php, in your web tree and invoke it a few times to see how the information display changes (or, rather, to see if it changes; under some circumstances, the script will fail, as we'll discuss shortly):

<?php
# sess_track.php - session request counting/timestamping demonstration
# (assumes that register_globals is enabled)

include_once "Cookbook_Session.php";
include_once "Cookbook_Webutils.php";   # needed for make_unordered_list( )

$title = "PHP Session Tracker";

# Open session and register session variables

session_start ( );
session_register ("count");
session_register ("timestamp");

# If the session is new, initialize the variables

if (!isset ($count))
    $count = 0;
if (!isset ($timestamp))
    $timestamp = array ( );

# Increment counter, add current timestamp to timestamp array

++$count;
$timestamp[ ] = date ("Y-m-d H:i:s T");

if ($count >= 10)   # destroy session variables after 10 invocations
{
    session_unregister ("count");
    session_unregister ("timestamp");
}

# Produce the output page

?>
<html>
<head>
<title><?php print ($title); ?></title>
</head>
<body bgcolor="white">

<?php

printf ("<p>This session has been active for %d requests.</p>\n", $count);
print ("<p>The requests occurred at these times:</p>\n");
print make_unordered_list ($timestamp);

?>

</body>
</html>

The script includes the Cookbook_Session.php library file to enable the MySQL-based storage module, then uses the PHP session manager interface in typical fashion. First, it opens the session, registers the session variables, and initializes them if the session is new. The scalar variable $count starts out at zero, and the non-scalar variable $timestamp starts out as an empty array. Then the script increments the counter, adds the current timestamp to the end of the timestamp array, and produces an output page that displays the count and the access times.

If the session limit of 10 invocations has been reached, the script unregisters the session variables, which causes $count and $timestamp not to be saved to the session record. The effect is that the session restarts on the next request.

sess_track.php does not call session_write_close( ) explicitly; that is unnecessary because PHP saves the session automatically when the script terminates.

The output page is produced only after updating the session record because PHP might determine that a cookie containing the session ID needs to be sent to the client. That determination must be made before generating the page body because cookies are sent in the headers.

The problem with sess_track.php as written is that it works only if PHP's register_globals configuration setting is enabled. If that is so, registering session variables named count and timestamp causes their values to be made available as the PHP global variables $count and $timestamp. However, when register_globals is disabled, session_register( ) does nothing and sess_track.php will not work properly (the count will always be one, and only a single timestamp will be shown).

The issue is a significant one because the PHP developers now recommend that register_globals be turned off for security reasons. That means session_register( ) is essentially obsolete and that existing session-based applications that rely on it will begin to fail as more and more sites follow the recommendation to disable register_globals. To deal with this problem and write code that works regardless of the register_globals setting, we need to get session variables another way. The two possiblities are to use the $HTTP_SESSION_VARS global array or (as of PHP 4.1) the $_SESSION superglobal array. For example, a session variable named count will be available as $HTTP_SESSION_VARS["count"] or $_SESSION["count"].

It's possible to modify the sess_track.php script relatively easily so that it does not rely on the setting of register_globals, but still allows you to work with simple variable names to manipulate session variables:

• Don't use session_register( ). Instead, copy session variables directly from a global session variable array into the $count and $timestamp variables.

• After you're done using your session variables, copy them back into the session variable array. Do this before writing the session, if you call session_write( ) explicitly.

This approach does require that you determine which global array to use for session variable storage, which may depend on your version of PHP. Instead of doing this each time you want to access a session variable, it's easier to write a couple of utility functions that do the work:

function get_session_val ($name)
{
global $HTTP_SESSION_VARS;

    unset ($val);
    if (isset ($_SESSION[$name]))
        $val = $_SESSION[$name];
    else if (isset ($HTTP_SESSION_VARS[$name]))
        $val = $HTTP_SESSION_VARS[$name];
    return (@$val);
}

function set_session_val ($name, $val)
{
global $HTTP_SESSION_VARS;

    if (PHP_VERSION >= "4.1")
        $_SESSION[$name] = $val;
    $HTTP_SESSION_VARS[$name] = $val;
}

These routines can be found in the Cookbook_Webutils.php library file, along with the routines that get other kinds of web parameter values (see Recipe 18.6). They are in Cookbook_Webutils.php rather than in Cookbook_Session.php so that you can call them even if you elect not to use the MySQL-based session storage that Cookbook_Session.php implements.

The following script, sess_track2.php, shows how avoid reliance on register_globals by making only small changes to the main logic of the script:

<?php
# sess_track2.php - session request counting/timestamping demonstration
# (does not rely on register_globals)

include_once "Cookbook_Session.php";
include_once "Cookbook_Webutils.php";   # needed for make_unordered_list( )
                                        # get_session_val( ), set_session_val( )

$title = "PHP Session Tracker";

# Open session and extract session values

session_start ( );
$count = get_session_val ("count");
$timestamp = get_session_val ("timestamp");

# If the session is new, initialize the variables

if (!isset ($count))
    $count = 0;
if (!isset ($timestamp))
    $timestamp = array ( );

# Increment counter, add current timestamp to timestamp array

++$count;
$timestamp[ ] = date ("Y-m-d H:i:s T");

if ($count < 10)    # save modified values into session variable array
{
    set_session_val ("count", $count);
    set_session_val ("timestamp", $timestamp);
}
else                # destroy session variables after 10 invocations
{
    session_unregister ("count");
    session_unregister ("timestamp");
}

# Produce the output page

?>
<html>
<head>
<title><?php print ($title); ?></title>
</head>
<body bgcolor="white">

<?php

printf ("<p>This session has been active for %d requests.</p>\n", $count);
print ("<p>The requests occurred at these times:</p>\n");
print make_unordered_list ($timestamp);

?>

</body>
</html>

sess_track2.php is identical to sess_track.php, with two exceptions:

• sess_track.php calls session_start( ) to open a session, but that is not strictly required, because it uses session_register( ), which implicitly opens the session for you. sess_track2.php does not use session_register( ). Instead, it gets the variable values directly from global session variable storage. With that approach, you must call session_start( ) first to open the session explicitly.

• If the session limit of 10 requests has not yet been reached, sess_track2.php explicitly stores the $count and $timestamp session values back into the global session variable arrays by invoking set_session_val( ).