Using MySQL-Based Sessions in Perl Applications

19.2.1 Problem

You want to use session storage for Perl scripts.

19.2.2 Solution

The Apache::Session module provides a convenient way to use several different storage types, including one based on MySQL.

19.2.3 Discussion

Apache::Session is an easy-to-use Perl module for maintaining state information across multiple web requests. Despite the name, this module is not dependent on Apache and can be used in non-web contexts, for example, to maintain persistent state across multiple invocations of a command-line script. On the other hand, Apache::Session doesn't handle any of the issues associated with tracking the session ID (sending it to the client in response to the initial request and extracting it from subsequent requests). The example application shown here uses cookies to pass the session ID, on the assumption that the client has cookies enabled.

19.2.4 Installing Apache::Session

If you don't have Apache::Session, you can get it from the CPAN (visit http://cpan.perl.org). Installation is straightforward, although Apache::Session does require several other modules that you may need to get first. (When you install it, Apache::Session should tell you which required modules you need if any are missing.) After you have everything installed, create a table in which to store session records. The specification for the table comes from the MySQL storage handler documentation, which you can read using this command:

% perldoc Apache::Session::Store::MySQL

The table can be placed in any database you like (we'll use cookbook), but the table name must be named sessions and have this structure:

CREATE TABLE sessions
(
 id CHAR(32) NOT NULL, # session identifier
 a_session BLOB, # session data
 PRIMARY KEY (id)
);

The id column holds session identifiers, which are 32-character MD5 values generated by Apache::Session. The a_session column holds session data in the form of serialized strings. Apache::Session uses the Storable module to serialize and unserialize session data.

19.2.5 The Apache::Session Interface

To use the sessions table in a script, include the MySQL-related session module:

use Apache::Session::MySQL;

Apache:Session represents session information using a hash. It uses Perl's tie mechanism to map hash operations onto the storage and retrieval methods used by the underlying storage manager. Thus, to open a session, you should declare a hash variable and pass it to tie. The other arguments to tie are the name of the session module, the session ID, and information about the database to use. There are two ways to specify the database connection. First, you can pass a reference to a hash that contains connection parameters:

my %session;
tie %session,
 "Apache::Session::MySQL",
 $sess_id,
 {
 DataSource => "DBI:mysql:host=localhost;database=cookbook",
 UserName => "cbuser",
 Password => "cbpass",
 LockDataSource => "DBI:mysql:host=localhost;database=cookbook",
 LockUserName => "cbuser",
 LockPassword => "cbpass"
 };

In this case, Apache::Session uses the parameters to open its own connection to MySQL, which it closes when you close or destroy the session. Second, you can pass the handle for an already open database connection (represented here by $dbh):

my %session;
tie %session,
 "Apache::Session::MySQL",
 $sess_id,
 {
 Handle => $dbh,
 LockHandle => $dbh
 };

If you pass a handle to an open connection like this, Apache::Session leaves it open when you close or destroy the session, on the assumption that you're using the handle for other purposes elsewhere in the script. You should close the connection yourself when you're done with it.

The $sess_id argument to tie represents the session identifier. Its value should be either undef to begin a new session, or a valid ID corresponding to an existing session record. In the latter case, the value should match that of the id column in some existing sessions table record.

After the session has been opened, you can access its contents. For example, after opening a new session, you'll want to determine what its identifier is so you can send it to the client. That value can be obtained like this:

$sess_id = $session{_session_id};

Session hash element names that begin with an underscore (such as _session_id) are reserved by Apache::Session for internal use. Other than that, you can use names of your own choosing for storing session values. For example, you might maintain a scalar counter value as follows, where the counter is initialized if the session is new, then incremented and retrieved for display:

$session{count} = 0 if !exists ($session{count}); # initialize counter
++$session{count}; # increment counter
print "counter value: $session{count}
"; # print value

To save a non-scalar value such as an array or a hash into the session record, store a reference to it:

$session{my_array} = @my_array;
$session{my_hash} = \%my_hash;

In this case, changes made to @my_array or %my_hash before you close the session will be reflected in the session contents. To save an independent copy of an array or hash in the session that will not change when you modify the original, create a reference to it like this:

$session{my_array} = [ @my_array ];
$session{my_hash} = { %my_hash };

To retrieve a non-scalar value, dereference the reference stored in the session:

@my_array = @{$session{my_array}};
%my_hash = %{$session{my_hash}};

To close a session when you're done with it, pass it to untie:

untie (%session);

When you close a session, Apache::Session saves it to the sessions table if you've made changes to it. This also makes the session values inaccessible, so don't close the session until you're done accessing it.

Apache::Session notices changes to "top-level" session record values, but might not detect a change to a member of a value stored by reference (such as an array element). If this is a problem, you can force Apache::Session to save a session when you close it by assigning any top-level session element a value. The session ID is always present in the session hash, so it provides a convenient way to force session saving:

$session{_session_id} = $session{_session_id};

An open session may be terminated rather than closed. Doing so removes the corresponding record from the sessions table, so that it can be used no longer:

tied (%session)->delete ( );

19.2.6 A Sample Application

The following script, sess_track.pl, is a complete (if short) implementation of an application that uses a session. It uses Apache::Session to keep track of the number of requests in the session and the time of each request, updating and displaying the information each time it is invoked. sess_track.pl uses a cookie named PERLSESSID to pass the session ID. This is done with the CGI.pm cookie management interface.[3]

[3] For information about CGI.pm cookie support, use the following command and read the section describing the cookie( ) function:

#! /usr/bin/perl -w
# sess_track.pl - session request counting/timestamping demonstration

use strict;
use lib qw(/usr/local/apache/lib/perl);
use CGI qw(:standard);
use Cookbook;
use Apache::Session::MySQL;

my $title = "Perl Session Tracker";

my $dbh = Cookbook::connect ( ); # connection to MySQL
my $sess_id = cookie ("PERLSESSID"); # session ID (undef if new session)
my %session; # session hash
my $cookie; # cookie to send to client

# open the session

tie %session, "Apache::Session::MySQL", $sess_id,
 {
 Handle => $dbh,
 LockHandle => $dbh
 };
if (!defined ($sess_id)) # this is a new session
{
 # get new session ID, initialize session data, create cookie for client
 $sess_id = $session{_session_id};
 $session{count} = 0; # initialize counter
 $session{timestamp} = [ ]; # initialize timestamp array
 $cookie = cookie (-name => "PERLSESSID", -value => $sess_id);
}

# increment counter and add current timestamp to timestamp array

++$session{count};
push (@{$session{timestamp}}, scalar (localtime (time ( ))));

# construct content of page body

my $page_body =
 p ("This session has been active for $session{count} requests.")
 . p ("The requests occurred at these times:")
 . ul (li ($session{timestamp}));

if ($session{count} < 10) # close (and save) session
{
 untie (%session);
}
else # destroy session after 10 invocations
{
 tied (%session)->delete ( );
 # reset cookie to tell browser to discard session cookie
 $cookie = cookie (-name => "PERLSESSID",
 -value => $sess_id,
 -expires => "-1d"); # "expire yesterday"
}

$dbh->disconnect ( );

# generate the output page

print
 header (-cookie => $cookie) # send cookie in headers (if it's defined)
 . start_html (-title => $title, -bgcolor => "white")
 . $page_body
 . end_html ( );

exit (0);

Try the script by installing it in your cgi-bin directory and requesting it from your browser. To reinvoke it, use your browser's Reload function.

sess_track.pl opens the session and increments the counter prior to generating any page output. This is necessary because the client must be sent a cookie containing the session name and identifier if the session is new. Any cookie sent must be part of the response headers, so the page body cannot be printed until after the headers are sent.

The script also generates the part of the page body that uses session data but saves it in a variable rather than writing it immediately. The reason for this is that, should the session need to be terminated, the script resets the cookie to be one that tells the browser to discard the one it has. This too must be determined prior to sending the headers or any page count.

19.2.7 Session Expiration

The Apache::Session module requires only the id and a_session columns in the sessions table, and makes no provision for timing out or expiring sessions. On the other hand, the module doesn't restrict you from adding other columns, so you could include a TIMESTAMP column in the table to record the time of each session's last update. For example, you can add a TIMESTAMP column t to the sessions table using ALTER TABLE:

ALTER TABLE sessions ADD t TIMESTAMP NOT NULL;

Then you'd be able to expire sessions by running a query periodically to sweep the table and remove old records. The following query uses an expiration time of four hours:

DELETE FROM sessions WHERE t < DATE_SUB(NOW( ),INTERVAL 4 HOUR);

Be aware that deleting session records can cause a problem: tie will raise an exception if you attempt to look up a session record using a non-undef session ID for which no record exists. This means, for example, that if a client provides an ID for a session that has been expired, your script may die with an error. One way to handle this is to open the session within an eval block so that you can trap the error. If one occurs, create a new session record:

eval
{
 tie %session, "Apache::Session::MySQL", $sess_id,
 {
 Handle => $dbh,
 LockHandle => $dbh
 };
};
if ($@) # if an error occurred, old session is unavailable; create a new one
{
 $sess_id = undef;
 tie %session, "Apache::Session::MySQL", $sess_id,
 {
 Handle => $dbh,
 LockHandle => $dbh
 };
}