oreilly.comSafari Books Online.Conferences.


Email.Email article link
The MySQL Cookbook (cover)

MySQL Recipe of the Day

The following recipe is from the MySQL Cookbook, by Paul DuBois. All links in this recipe point to the online version of the book on the Safari Bookshelf.

Buy it now, or read it online on the Safari Bookshelf.

19.2. Using MySQL-Based Sessions in Perl Applications

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,
        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,
        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}\n";           # 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,, 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. uses a cookie named PERLSESSID to pass the session ID. This is done with the cookie management interface.[3]

#! /usr/bin/perl -w
# - 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

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

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

View the past week's recipes: Today | Yesterday | 3 days ago | 4 days ago | 5 days ago | A week ago

Sponsored by: