Recipe 20.3. Using MySQL-Based Storage with the PHP Session Manager

Problem

You want to use session storage for PHP scripts.

Solution

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

Discussion

This section shows how to use the PHP native session manager and how to extend it by implementing a storage module that saves session data in MySQL. If your PHP configuration has the TRack_vars configuration variable enabled, session variables are available as elements of the $HTTP_SESSION_VARS global array or the $_SESSION superglobal array. track_vars is always enabled as of PHP 4.0.3, so I'll assume that this is true for your PHP installation. If the register_globals configuration variable is enabled as well, session variables also exist in your script as global variables of the same names. However, this is less secure, so this variable is assumed not to be enabled here. (Section 19.5 discusses PHP's global and superglobal arrays and the security implications of register_globals.)

The PHP 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" ]. This function must be called first before using the relevant session variable array.

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 is saved to the session record when the session is closed.

However, I mention this function only to point out that we will not use it. session_register⁠(⁠ ⁠ ⁠) is effective only if register_globals is enabled, which is a security risk. To avoid reliance on register_globals, we will get session variables from either the $_SESSION array or the $HTTP_SESSION_VARS array.

session_unregister ( var_name )

Unregisters a session variable so that it is not saved to the session record. Unlike session_register⁠(⁠ ⁠ ⁠), this function is not dependent on the register_globals setting.

session_write_close ⁠(⁠ ⁠ ⁠)

Writes the session data and closes the session. The PHP documentation indicates that normally you need not call this function because PHP saves an open session automatically when your script ends. However, it appears that in PHP 5, that might not always be true when you provide your own session handler. To be safe, call this function to save your changes.

session_destroy ⁠(⁠ ⁠ ⁠)

Removes the session and any data associated with it.

session_name ($name)

The PHP session manager determines which session to use by means of the session identifier. It looks for the identifier by checking the following sources: a global variable named $PHPSESSID; a cookie, get, or post variable named PHPSESSID; or 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 variable 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 determine the current session identifier name, call session_name⁠(⁠ ⁠ ⁠) with no argument.

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.

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  MEDIUMBLOB,   t     TIMESTAMP NOT NULL,   PRIMARY KEY (id),   INDEX (t) );

You'll recognize the structure of this table as quite similar to the perl_session table used in Section 20.1 for 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). The data column 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 statements 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 row (or to create a new one if no such row exists):
```
REPLACE INTO php_session (id,data) VALUES('sess_id','sess_data'); 
```
REPLACE also updates the timestamp in the row when creating or updating a row, 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 row with the given session ID already exists (or an UPDATE with a fallback to INSERT if the UPDATE fails because a row with the ID does not exist). In MySQL, a dual-statement approach is unnecessary; REPLACE performs the required task with a single statement.
To destroy a session, delete the corresponding row:
```
DELETE FROM php_session WHERE id = 'sess_id'; 
```
Garbage collection is performed by removing old rows. The following statement deletes rows that have a timestamp value more than sess_life seconds old:
```
DELETE FROM php_session WHERE t < NOW() - INTERVAL sess_life SECOND; 
```
The PHP session manager supplies the value of sess_life when it invokes the garbage collection routine. (The table definition for php_session indexes t to make DELETE statements faster.)

These statements 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 statements at the appropriate times.

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 );

The order of the handler routines must be as shown, but you can name them as you like. They need not necessarily be named mysql_sess_open⁠(⁠ ⁠ ⁠), mysql_sess_close⁠(⁠ ⁠ ⁠), and so forth. The routines should 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 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 row 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 variable 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 requestwhich 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.

To register the handler routines, call 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 variable. 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 configuration 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 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 want 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. 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 require_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 the connection identifier, select user-defined # session handling and register the handler routines $mysql_sess_conn = 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:

require_once "Cookbook_Session.php";

NOTE

The interface provided by the Cookbook_Session.php library file exposes a global database connection identifier variable ($mysql_sess_conn) as well as 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;   # open connection to MySQL if it's not already open   if (!$mysql_sess_conn)   {     # Do NOT use =& operator here!     $mysql_sess_conn = Cookbook::connect ();     if (PEAR::isError ($mysql_sess_conn))     {       $mysql_sess_conn = FALSE;       return (FALSE);     }   }   return (TRUE); }

mysql_session_open⁠(⁠ ⁠ ⁠) uses = rather than =& to assign the result of the connection call, to ensure that the connection handler value doesn't disappear when the function returns.

Closing a session

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

function mysql_sess_close () { global $mysql_sess_conn;   if ($mysql_sess_conn)    # close connection if it's open   {     $mysql_sess_conn->disconnect ();     $mysql_sess_conn = 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. It returns the empty string if no such record exists. If an error occurs, it returns FALSE:

function mysql_sess_read ($sess_id) { global $mysql_sess_conn;   $stmt = "SELECT data FROM php_session WHERE id = ?";   $result =& $mysql_sess_conn->query ($stmt, array ($sess_id));   if (!PEAR::isError ($result))   {     list ($data) = $result->fetchRow ();     $result->free ();     if (isset ($data))       return ($data);     return ("");   }   return (FALSE); }

Writing session data

mysql_sess_write⁠(⁠ ⁠ ⁠) creates a new record if there is none for the session yet, or replaces the existing record if there is one:

function mysql_sess_write ($sess_id, $sess_data) { global $mysql_sess_conn;   $stmt = "REPLACE php_session (id, data) VALUES(?,?)";   $result =& $mysql_sess_conn->query ($stmt, array ($sess_id, $sess_data));   return (!PEAR::isError ($result)); }

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;   $stmt = "DELETE FROM php_session WHERE id = ?";   $result =& $mysql_sess_conn->query ($stmt, array ($sess_id));   return (!PEAR::isError ($result)); }

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;   $stmt = "DELETE FROM php_session WHERE t < NOW() - INTERVAL ? SECOND";   $result =& $mysql_sess_conn->query ($stmt, array ($sess_maxlife));   return (TRUE);  # ignore errors }

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/lib/mcb and modify php.ini so that the include_path variable names that directory. See Section 2.3.) To try 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:

<?php # sess_track.php - session request counting/timestamping demonstration require_once "Cookbook_Session.php"; # needed for make_unordered_list(), get_session_val(), set_session_val() require_once "Cookbook_Webutils.php"; $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"); } session_write_close (); # save session changes # Produce the output page ?> <html> <head> <title><?php print ($title); ?></title> </head> <body bgcolor="white"> <?php print ("<p>This session has been active for $count requests.</p>\n"); 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, and then uses the PHP session manager interface in typical fashion. First, it opens the session and attempts to extract the session variables. For the first request, the session variables will not be set and must be initialized. This is determined by the isset⁠(⁠ ⁠ ⁠) tests. The scalar variable $count starts out at zero, and the nonscalar variable $timestamp starts out as an empty array. For successive requests, the session variables will have the values assigned to them by the previous request.

Next, 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.

Finally, sess_track.php calls session_write_close⁠(⁠ ⁠ ⁠) to write out the changes to session data.

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.

As mentioned earlier, we assume that register_globals is not enabled. Thus, we cannot use the PHP session_register⁠(⁠ ⁠ ⁠) function to register session variables and it is necessary to access session variables another way. The two possibilities are to use the $HTTP_SESSION_VARS global array or (as of PHP 4.1) the $_SESSION superglobal array. For example, after calling session_start⁠(⁠ ⁠ ⁠), a session variable named count will be available as $HTTP_SESSION_VARS[ "count" ] or $_SESSION[ "count" ].

It's possible to adopt an approach that uses the PHP session variable arrays but still enables 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 before writing the session.

However, it's messy to determine which global array to use for session variable storage because that may depend on your version of PHP. Instead of making this determination each time you want to access a session variable, it's easier to write a couple of utility functions that do the work. That is the purpose of the get_session_val⁠(⁠ ⁠ ⁠) and set_session_val⁠(⁠ ⁠ ⁠) functions used in the script. They access session variables for the counter value and timestamp array and store modified values back into the session:

function get_session_val ($name) { global $HTTP_SESSION_VARS;   $val = NULL;   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 (isset ($_SESSION))     $_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 script parameter values (see Section 19.5). 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.