Some things about CGI::Session

1/4/2004
Eric Low

Ok, this one has nothing to do with my Sys Admin job, but rather, web design in my own time (check out Steep Turns Web Design and Benic Systems). I was trying to keep session state on some of my websites by using the CGI::Session perl module, which I had never used before. For some reason I kept getting errors when creating a new session, and after a lot of debugging, what I figured out was that CGI::Session was having trouble connecting to the PostgreSQL database.

As I just mentioned, on our server, we are running with a PostgreSQL database, but I am certain that I would have run into this problem with a MySQL database as well - or any other SQL database for that matter. In order to run CGI::Session with PostgreSQL, I had to install the non-standard (which may in fact be standard at this point, but I don't remember it being so when I first messed with it) CGI::Session::PostgreSQL module.

I repeatedly got the following error:

showme.cgi: (in cleanup) could not flush: Couldn't acquire data on id 'c4bdfda251da16689a5567f0dc2ca3e4' at showme.cgi line 0

Note that that ID is the one randomly generated as a new session ID when I created a new CGI::Session:

$MakeSession = new CGI::Session("driver:PostgreSQL", undef, {Handle=>$dbh, TableName=>'SexySessions'});

If you look in the CGI::Session or CGI::Session::PostgreSQL docs, that is the exact code it tells you to use if you use a table name other than the default (in my case, of course, I used CurrSessions, but the default is simply sessions. This was simply too generic for me).

 

My full code for creating a new session looked about like this:

use CGI::Session;

use CGI::Cookie;

$MakeSession = new CGI::Session("driver:PostgreSQL", undef,
{Handle=>$dbh, TableName=>'CurrSessions'});

$SessionCookie = new CGI::Cookie(-name=>'CurrSess', -value=>$MakeSession->id); #Set cookie with session ID for all future page loads.

print header(-cookie=>[$SessionUserName,$SessionCookie]);

return 1;

My table was created correctly, using the following PostgreSQL command:

create table CurrSessions (
id CHAR(32) NOT NULL,
a_session TEXT NOT NULL,
CurrIP char(15) null,
LastActivity time)

The last two columns, of course, are for my own use.

--------------------------------------------------------------------------------

So here's what it comes down to... and the docs won't tell you this, nor will you find very much about it on the web. Hence my motive for creating this document. I had to figure this out through much trial and error and a little bit of reading the source code for the module.

Normally when I do database access through PostgreSQL or MySQL or whatever (I recently converted all of my webpages from MySQL to PostgreSQL when we switched servers. I'm much more comfortable with MySQL, having used it for many years, but my partner in programming (and just about every other source I've read) tells me that PostgreSQL is better. Whatever works!), my code to initiate the connection looks about like this:

sub CreateDBConnection{
use DBI;

# $dbh = DBI->connect("DBI:mysql:database=MrEric;host=localhost", "MrEricUser", "1234567") || croak "Cannot connect to MySQL DB: $DBI::errstr\n";

$dbh = DBI->connect ( "DBI:Pg:dbname=MrEric", "MrEricUser", "1234567") || croak "Cannot connect to PostGres DB: $DBI::errstr\n";

if ( !defined $dbh ) {
croak "Cannot connect to database!\n";
}


return ($dbh);
} # End of Create_DB_Connection subroutine.

The commented out line is from my old server where we used MySQL. MrEric is my database name, MrEricUser is my account name, and 1234567 is my password. And no, I'm not that much of an idiot - I changed it for your pleasure!

When you create a new CGI::Session, you need to pass it the same information or it will not be able to open the database, and you will encounter the same could not flush: Couldn't acquire data error that I described above.

My first attempt was to call the above CreateDBConnection sub to open the $dbh handle before creating new CGI::Session, but this caused the same error. After a little fooling around, here's what the correct command looks like:

$MakeSession = new CGI::Session("driver:PostgreSQL", undef, {Handle=>$dbh, TableName=>'CurrSessions', DataSource=> 'DBI:Pg:dbname=MrEric', User=>'MrEricUser', Password=>'1234567'});

If you look at the source for the PostgreSQL.pm module, you will see the DataSource, User, and Password parameters. The DataSource paramater is the key, but it was not immediately obvious how to use it. You cannot simply put the database name in there (ie., DataSource=>'MrEric' will NOT work). You MUST use Datasource=>'DBI:Pg:dbname=MrEric' where MrEric is your database name. Voila, it worked!

On a quick side note, make sure that if you add any other columns to that session keeping table, they allow nulls. Otherwise you will get an error when it tries to write that initial ID.

Update, 1/9/2004:

After hours and hours of debugging some very mysterious problems connecting to the session table, I discovered the following quirks:

1) If you access a different database using the same drivers, such as DBI:Pg:dbname, before you create a new CGI::Session object to retrieve an existing session, you will not be able to connect to fetch from the database if you do an $dbh->disconnect; first! You must leave that command out, and stay connected before you create the new CGI::Session object. Otherwise it will create a whole new entry in the session table, rather than retrieving an existing one. Here's what I mean:

use DBI;
$SQL = qq`SELECT ExistingSessID FROM UserDataTable WHERE UserName='$MyName'`;

$dbh = DBI->connect ( "DBI:Pg:dbname=MrEric", "MrEricUser", "1234567") || croak "Cannot connect to PostGres DB: $DBI::errstr\n";
if ( !defined $dbh ) { croak "Cannot connect to database!\n"; }

$sth = $dbh->prepare($SQL);
if ( !defined $sth ) { croak "Cannot prepare statement: $DBI::errstr\n"; }
$sth->execute or croak "SQL Error: $DBI::errstr<br><br>\n--$SQL--\n";
$str = $sth->errstr;
if(defined($str)) { print "<BR>Postgres Error: $str<BR>\n"; }

($ExistingSessID) = $sth->fetchrow_array;
$sth->finish;
# $dbh->disconnect;

$CheckSession = new CGI::Session("driver:PostgreSQL", $ExistingSessID, {Handle=>$dbh, TableName=>'CurrSessions', DataSource=> 'DBI:Pg:dbname=MrEric', User=>'MrEricUser', Password=>'1234567'});
$StoredCurrIP = $CheckSession->param('CurrIP');

If you were to uncomment that $dbh->disconnect; line up above, the new CGI::Session would create a whole new entry, with a completely new and unique ID, rather than retrieving the existing one in the database.

2) If you need to access more than one CGI::Session in your script, you MUST close the old one, using close(), before you open the second one. Otherwise, you will not be able to open the sessions table to access an existing record OR create a new one! For example, after you are done with the above $CheckSession CGI::Session object, you would run $CheckSession->close(); before you did another $CheckSession - new CGI::Session. The man pages for CGI::Session will tell you that you shouldn't use close() unless absolutely necessary, since it is rather slow, but aside from doing your own stuff manually outside of the CGI::Session module to close that other session, I see no way around it.

Hope this helps!

Now aren't you glad you read all that crap? Happy Session State keeping! ;)