Perl Apache Recipe: Session Cookies in MySQL

Believe it or not. I could not google successfully for a minimal and coherent Perl solution for storing session information in a MySQL database. One that is actually working and has survived the Apache2/mod_perl2 version chasm.

Which obviously rules out MasonX::Request::WithApacheSession. That would be the minimal canonical way as it can be fully configured from the httpd.conf file. But to make this work, I would have to manage a local patch, something I'd like to avoid. So the next attempt is to fall back to good'n'old Apache::Session::MySQL and Apache2::Cookie.

Of course, everything can be eventually blood-squeezed from the man pages (the Apache2 stuff is subtly different from Apache 1), but for a middle-of-the-road problem like this I would have suspected not to spend several hours studying mod_perl2 API changelogs. Enough rant, let's reduce Internet entropy again.

The Database

In my setup I plan to use a cookie identified with SID to carry a session ID between server and client. And the plan includes this table in my database:

mysql> describe sessions;
| Field     | Type      | Null | Key | Default           |
| id        | char(32)  | NO   | PRI |                   |
| a_session | text      | YES  |     | NULL              |
| created   | timestamp | NO   |     | CURRENT_TIMESTAMP |

I normally also add an created column, so that it is easier to outphase obsolete sessions.

When you plan to store bigger things into a session, then maybe you should consider to use blobs of appropriate size. In Perl this is normally not a big issue as the thaw/freeze process is quite fast. The delay in writing this back to the relational database is more a limiting factor.

Cookie Handling

Cookie handling involves typically 3 things:

  • find the incoming cookie and extract the session ID,
  • check with the session table whether there is a session under this ID. Create one if not, and
  • generate a cookie with the same SID and appropriate path/expiry settings and add it to the outgoing HTTP headers.

The session itself is represented as %session hash to the application, written in Mason in my case. That will hold information from previous exchanges in this session. When you modify the session content, you should expect that it will be saved back to the database whenever that particular request is completed.

Autohandler Code

The following code segment could be placed with minor adaptions into an Apache handler or - sufficient for me - also into a Mason autohandler component. It should be executed before the application can take over:

# incoming cookie, find SID
use Apache2::Cookie;
my %in_cookies = Apache2::Cookie->fetch ($r);
my $SID = $in_cookies{"SID"} && $in_cookies{"SID"}->value;

# bind session hash
use Apache::Session::MySQL;
        eval {
            tie %session, 'Apache::Session::MySQL', $SID, {
               DataSource     => 'dbi:mysql:mydb',
                 UserName     => 'myuser',
                 Password     => 'mypwd',
               LockDataSource => 'dbi:mysql:mydb',
                 LockUserName => 'myuser',
                 LockPassword => 'mypwd' };
        if ($@ =~ /Object.*not exist/) {
            $SID = undef;
            redo TIEME;
        } elsif ($@) {
            die $@;

# bake the new cookie
my $out_cookie = Apache2::Cookie->new($r,
                   -name    =>  'SID',
                   -value   =>  $session{_session_id},
                   -expires =>  '+1H',
                   -domain  =>  '',
                   -path    =>  '/',
# if that worked, merge it into the HTTP headers
$out_cookie && $out_cookie->bake($r);

After we have figured out whether there is already a session cookie or not, in the Apache::Session::* tradition we try to tie the %session object against a data record in the sessions table in our MySQL database.

For that purpose, we have to define the database and the account information there. If you already have a database handle, then you can directly use that alternatively:

tie %session, 'Apache::Session::MySQL', $SID, {
       Handle     => $dbh,
       LockHandle => $dbh };

The tie can fail for a variety of reasons. If there is some database problem then we catch that with the following if statement and just reraise the exception with die $@.

Two cases we have to consider, though, is when there is no cookie in the first place (so $SID is empty) or when it is defined, but there happens to be no tieable record in the database (maybe the session was removed there).

In the first case the tie logic will create one record for us, so this is no worry. In the second case the tie will fail with "Object does not exist", or similar. Then we undef the session ID and simply redo the tie. If you are picky, you may want to reduce the number of attempts, or even implement a back-off scheme.

The session object

It is important that the session object is automatically untied when the request completes. The most effective and unobtrusive way is to make it local:

local *HTML::Mason::Commands::session;

As sort-of convention, I have put session here into the HTML::Mason::Commands namespace. Any other would do just fine.

What I also wanted is to access in all other Mason components the object via %session and not as %HTML::Mason::Commands::session. To make that happen, I added it to my list of global variables in httpd.conf:

PerlAddVar MasonAllowGlobals $cgi %session

From then on, I can use it as follows

$session{lastVisited} = time;

Small Variation

I also came across Apache::SessionX. Here you do not have to commit yourself to a particular storage technology in the cookie handler:

use Apache::SessionX;
tie %session, 'Apache::SessionX', $SID, {
    object_store => 'sm_session',

Instead, you ask Apache::SessionX to look up all necessary details in an sm_session configuration entry. That is managed in a separate file:

'sm_session' => {
       'Serialize'        => 'Storable',

       'Store'            => 'MySQL',
         'DataSource'     => 'dbi:mysql:mydb',
         'UserName'       => 'myuser',
         'Password'       => 'mypwd',

       'Lock'             => 'MySQL',
         'LockUserName'   => 'myuser',
         'LockDataSource' => 'dbi:mysql:mydb',
         'LockPassword'   => 'mypwd'

       'Generate'         => 'MD5',

The Debian installer will prompt you for these details.

At the end I decided against it, because I am sure the next time I will have a look at this problem everything will be version-broken again. And I will have to start googling from scratch.

Posted In

Hawaii'an greeting.

Usually I lookup everyday stuff at , not because it also includes a Google, Yahoo, Live Search, Ask, Wikipedia,, YouTube and Flickr search all at once, but because results are human-written and you can request a new page on your search... so if that topic is relevant to enough people - which after a change of technology is very likely - it will be done while I sleep.

Marcus Meisel (not verified) | Sun, 12/30/2007 - 21:31