MySQL Cookbook (2007)

---

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

%perldoc CGI

Using MySQL-Based Storage in Ruby Applications

Problem

You want to use session storage for Ruby scripts.

Solution

Use the CGI::Session class interface. By default, it uses temporary files for backing store, but you can configure it to use MySQL instead.

Discussion

The CGI::Session class manages session storage. It identifies sessions by means of cookies, which it adds transparently to the responses sent to the client. CGI::Session allows you to supply a storage-management class to be used in place of the default manager that uses temporary files. We’ll use the mysql-session package, which is based on the Ruby DBI interface and stores session records using MySQL. mysql-session is available from the Ruby Application Archive. See Appendix A for information about obtaining and installing it.

To use mysql-session in a script, you need to access these modules:

require "cgi"

require "cgi/session"

require "mysqlstore"

To create a session, first create a CGI object . Then invoke CGI::Session.new, which takes several arguments. The first is a CGI object associated with the script. (This object must exist before you can open the session.) Other arguments provide information about the session itself. Those following are relevant no matter which storage manager you use:

session_key

The session key that the session manager uses as the name of the cookie to be sent to the client. The default key value is _session_key; we will use RUBYSESSID.

new_session

This argument should be true to force a new session to be created, or false to use an existing session, which is assumed to have already been created during a previous request. It’s also possible to create a session if it does not exist and use the current session if it does. To enable that behavior, omit the new_session argument (which is what the example script in this recipe does).

database_manager

The name of the class that provides storage management for session records. If this argument is omitted, the session manager uses temporary files.

To use the mysql-session package as the storage manager, the database_manager argument should be CGI::Session::MySQLStore. In that case, mysql-session enables several other arguments for the CGI::Session.new method. You can pass in arguments that instruct the session manager to establish its own connection to MySQL, or you can open your own connection and pass its database handle to the session manager.

The following discussion shows both approaches, but either way, we’ll need a table for storing session records. For mysql-session, create a table named ruby_session with the following structure:

CREATE TABLE ruby_session

(

session_id VARCHAR(255) NOT NULL,

session_value MEDIUMBLOB NOT NULL,

update_time DATETIME NOT NULL,

PRIMARY KEY (session_id)

);

Now we return to session creation. To have the session manager open its own connection to MySQL, create the session like this:

cgi = CGI.new("html4")

sess_id = cgi.cookies["RUBYSESSID"]

session = CGI::Session.new(

cgi,

"session_key" => "RUBYSESSID",

"database_manager" => CGI::Session::MySQLStore,

"db.host" => "127.0.0.1",

"db.user" => "cbuser",

"db.pass" => "cbpass",

"db.name" => "cookbook",

"db.table" => "ruby_session",

"db.hold_conn" => 1

)

The db. xxx parameters used in that code tell mysql-session how to connect to the server, as well as the database and table to use for session records:

db.host

The host where the MySQL server is running.

db.user, db.pass

The username and password of the MySQL account to use.

db.name, db.table

The database and table name for the session table.

db.hold_conn

By default, mysql-session opens and closes a connection each time it needs to send a statement to the MySQL server. If you supply the db.hold_conn parameter with a value of 1, mysql-session opens the connection only once and holds it open until the session ends.

Another way to create a session is to open your own database connection first and pass the database handle as a parameter:

cgi = CGI.new("html4")

sess_id = cgi.cookies["RUBYSESSID"]

session = CGI::Session.new(

cgi,

"session_key" => "RUBYSESSID",

"database_manager" => CGI::Session::MySQLStore,

"db.dbh" => dbh,

"db.name" => "cookbook",

"db.table" => "ruby_session"

)

In this case, the db.host, db.user, db.pass, and db.hold_conn parameters are not used. In addition, you are responsible for closing the connection after the session is no longer needed.

Whichever way you create the session, its ID is available while it is open as the session.session_id attribute.

To close the session, invoke the close method of the session object. If you want to destroy the session instead, invoke its delete method.

The session manager stores data using key/value pairs, using strings for the values. It does not know the types of the values that you store. I find the following strategy useful for dealing with type-conversion issues:

1. After opening the session, extract values from the session and convert them from “generic” string form to properly typed values.

2. Work with the typed values until it is time to close the session.

3. Convert the typed values to string form, store them in the session, and close it.

The following script uses the CGI::Session session manager to track the number of requests in a session and the time of each request. After 10 requests, the script deletes the session to cause a new session to begin for the next request:

#!/usr/bin/ruby -w

# sess_track.rb - session request counting/timestamping demonstration

require "Cookbook"

require "cgi"

require "cgi/session"

require "mysqlstore"

title = "Ruby Session Tracker";

dbh = Cookbook::connect

cgi = CGI.new("html4")

session = CGI::Session.new(

cgi,

"session_key" => "RUBYSESSID",

"database_manager" => CGI::Session::MySQLStore,

"db.dbh" => dbh,

"db.name" => "cookbook",

"db.table" => "ruby_session"

)

# extract string values from session

count = session["count"]

timestamp = session["timestamp"]

# convert variables to proper types

count = (count.nil? ? 0 : count.to_i)

timestamp = "" if timestamp.nil?

timestamp = timestamp.split(",")

# increment counter and add current timestamp to timestamp array

count = count + 1

timestamp << Time.now().strftime("%Y-%m-%d %H:%M:%S")

# construct content of page body

page = ""

page << cgi.p {"This session has been active for #{count} requests."}

page << cgi.p {"The requests occurred at these times:"}

list = ""

timestamp.each do |t|

list << cgi.li { t.to_s }

end

page << cgi.ul { list }

if count < 10

# convert session variables to strings, store them back

# into the session, and close (and save) the session

session["count"] = count.to_s

session["timestamp"] = timestamp.join(",")

session.close()

else

# destroy session after 10 invocations

session.delete()

end

dbh.disconnect

# generate the output page

cgi.out {

cgi.html {

cgi.head { cgi.title { title } } +

cgi.body("bgcolor" => "white") { page }

}

}

CGI::Session makes no provision for expiring sessions, but you can discard old session records using the technique discussed in Using MySQL-Based Sessions in Perl Applications. If you do this, you should index the update_time column to make DELETE statements faster:

ALTER TABLE ruby_session ADD INDEX (update_time);

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. (Collecting Web Input 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 formPHPSESSID= 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:

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

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

3. 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 Using MySQL-Based Sessions in Perl Applications 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() - INTERVALsess_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 request—which 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 Writing Library Files.) 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_VARSglobal 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:

1. Don’t use session_register(). Instead, copy session variables directly from a global session variable array into the $count and $timestamp variables.

2. 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 Collecting Web Input). 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.

Using MySQL for Session-Backing Store with Tomcat

Problem

You want to use session storage for Java-based scripts.

Solution

Tomcat handles session management for you. By default, it uses temporary files for backing store, but you can configure it to use MySQL instead by supplying the appropriate JDBC parameters in Tomcat’s server.xml configuration file.

Discussion

The Perl, Ruby, and PHP session mechanisms described earlier in this chapter require applications to indicate explicitly that they want to use MySQL-based session storage. For Perl and Ruby, a script must state that it wants to use the appropriate session module. For PHP, the session manager is built into the language, but each application that wants to use a MySQL storage module must register it.

For Java applications that run under Tomcat, a different framework applies. Tomcat itself manages sessions, so if you want to store session information in MySQL, you do so by reconfiguring Tomcat, not your applications. In other words, web-based Java programs are relieved of some of the messy session-related details that must be handled at the application level in other languages. For example, session IDs are handled by the Tomcat server rather than at the application level. If cookies are enabled, Tomcat uses them. Otherwise, it uses URL rewriting to encode the session ID in the URL. Application developers need not care which method is used because the ID is available to them the same way regardless of how it’s transmitted.

To illustrate the independence of applications from the session management method used by Tomcat, this section shows a simple JSP application that uses a session. Then it shows how to reconfigure Tomcat to store session information in MySQL rather than in the default session store—without requiring any changes at all to the application. First, though, it’s necessary to describe the session interface.

The Servlet and JSP Session Interface

Tomcat uses the standard session interface described in the Java Servlet Specification. This interface can be used both by servlets and by JSP pages. Within a servlet, you gain access to the session by importing the javax.servlet.http.HttpSession class and invoking thegetSession() method of your HttpRequest object:

import javax.servlet.http.*;

HttpSession session = request.getSession ();

In JSP pages, session support is enabled by default, so it’s as though those statements have already been issued by the time the page begins executing. That is, the session is available implicitly through a session variable that’s already been set up for you.

The complete session interface is defined in the HttpSession section of the Java Servlet Specification (see Appendix D). Some representative methods of session objects are listed here:

isNew ()

Returns true or false to indicate whether the session has just begun with the current request.

getAttribute (String attrName)

Session contents consist of attributes, which are objects that are bound to names. To access a session attribute, specify its name. The getAttribute() method returns the Object bound to the given name, or null if there is no object with that name.

setAttribute (String attrName, Object obj)

Adds the object to the session and binds it to the given name.

removeAttribute (String attrName)

Removes the attribute with the given name from the session.

invalidate ()

Invalidates the session and any data associated with it. The next request from the client will begin a new session.

A sample JSP session application

The following example shows a JSP page, sess_track.jsp, that maintains a session request counter and a log of the request times. To illustrate the session-related operations more explicitly, this page consists primarily of embedded Java code that uses the HttpSession session interface directly:

<%--

sess_track.jsp - session request counting/timestamping demonstration

--%>

<%@ page import="java.util.*" %>

<%

// get session variables, initializing them if not present

int count;

Object obj = session.getAttribute ("count");

if (obj == null)

count = 0;

else

count = Integer.parseInt (obj.toString ());

ArrayList timestamp = (ArrayList) session.getAttribute ("timestamp");

if (timestamp == null)

timestamp = new ArrayList ();

// increment counter, add current timestamp to timestamp array

count = count + 1;

timestamp.add (new Date ());

if (count < 10) // save updated values in session object

{

session.setAttribute ("count", String.valueOf (count));

session.setAttribute ("timestamp", timestamp);

}

else // restart session after 10 requests

{

session.removeAttribute ("count");

session.removeAttribute ("timestamp");

}

%>

<html>

<head>

<title>JSP Session Tracker</title>

</head>

<body bgcolor="white">

<p>This session has been active for <%= count %> requests.</p>

<p>The requests occurred at these times:</p>

<ul>

<%

for (int i = 0; i < timestamp.size (); i++)

out.println ("<li>" + timestamp.get (i) + "</li>");

%>

</ul>

</body>

</html>

Invoke sess_track.jsp a few times from your browser to see how the display changes.

The session.setAttribute() method used in sess_track.jsp places information into the session so that it can be found by later invocations of the script. But session attributes also can be shared with other scripts. To see this, make a copy of sess_track.jsp and invoke the copy from your browser. You’ll see that it accesses the same session information as sess_track.jsp (scripts in the same application context access the same information).

Some of the session-related operations shown in sess_track.jsp can be done using tags from JSTL, which provides a sessionScope variable for getting at the implicit JSP session object:

<%--

sess_track2.jsp - session request counting/timestamping demonstration

--%>

<%@ page import="java.util.*" %>

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<c:if test="${empty sessionScope.count}">

<c:set var="count" scope="session" value="0"/>

</c:if>

<c:set var="count" scope="session" value="${sessionScope.count+1}"/>

<%

ArrayList timestamp = (ArrayList) session.getAttribute ("timestamp");

if (timestamp == null)

timestamp = new ArrayList ();

// add current timestamp to timestamp array, store result in session

timestamp.add (new Date ());

session.setAttribute ("timestamp", timestamp);

%>

<html>

<head>

<title>JSP Session Tracker</title>

</head>

<body bgcolor="white">

<p>This session has been active for

<c:out value="${sessionScope.count}"/>

requests.</p>

<p>The requests occurred at these times:</p>

<ul>

<c:forEach items="${sessionScope.timestamp}" var="t">

<li><c:out value="${t}"/></li>

</c:forEach>

</ul>

<%-- has session limit of 10 requests been reached? --%>

<c:if test="${sessionScope.count ge 10}">

<c:remove var="count" scope="session"/>

<c:remove var="timestamp" scope="session"/>

</c:if>

</body>

</html>

Telling Tomcat to save session records in MySQL

The Tomcat documentation pertaining to session management can be found at:

http://tomcat.apache.org/tomcat-5.0-doc/config/manager.html

Tomcat has its own default session storage mechanism (temporary files). To override the default and save sessions in MySQL via JDBC instead, use the following procedure:

1. Create a table to hold session records.

2. Make sure that Tomcat has access to the proper JDBC driver.

3. Modify Tomcat’s server.xml configuration file to specify use of a persistent session manager for the relevant application context or contexts.

None of these steps involve modifying the sample session application in any way, which is a reflection of how Tomcat implements session support above the application level.

Creating the Tomcat session table. Tomcat stores several types of information into the session table:

§ The session ID. By default, IDs are 32-character MD5 values.

§ The application name.

§ The session data. This is a serialized string.

§ Whether the session is valid, as a single byte.

§ The maximum inactivity time allowed, as a 32-bit integer measured in seconds.

§ The last access time, as a 64-bit integer.

These specifications are satisfied by the following table, which you should create before proceeding to the next section:

CREATE TABLE tomcat_session

(

id VARCHAR(32) NOT NULL,

app VARCHAR(255),

data MEDIUMBLOB,

valid_session CHAR(1) NOT NULL,

max_inactive INT NOT NULL,

last_access BIGINT NOT NULL,

PRIMARY KEY (id),

INDEX (app)

);

Placing the JDBC driver where Tomcat can find it. Because Tomcat itself manages sessions, it must be able to access the JDBC driver used to store sessions in a database. It’s common to store drivers in the lib directory of the Tomcat tree so that all applications have access to them. But for a driver to be accessible to Tomcat as well, it should go in the common/lib directory. (Thus, if you have the MySQL Connector/J driver installed in lib, move it to common/lib.) After a restart, Tomcat will be able to use it. For more information about using MySQL Connector/J with Tomcat, seeUsing Tomcat to Run Web Scripts.

Modifying the Tomcat configuration file. To tell Tomcat to use the tomcat_session table, it’s necessary to modify the server.xml file in Tomcat’s conf directory. Do this by placing a <Manager> element in the body of the <Context> element of each application context that should use MySQL-based session storage. (If a context has no such element, create one.) For the mcb application context, the <Context> element can be created like this:

<Context path="/mcb" docBase="mcb" debug="0" reloadable="true">

<Manager

className="org.apache.catalina.session.PersistentManager"

debug="0"

saveOnRestart="true"

minIdleSwap="900"

maxIdleSwap="1200"

maxIdleBackup="600">

<Store

className="org.apache.catalina.session.JDBCStore"

driverName="com.mysql.jdbc.Driver"

connectionURL=

"jdbc:mysql://localhost/cookbook?user=cbuser&password=cbpass"

sessionTable="tomcat_session"

sessionIdCol="id"

sessionAppCol="app"

sessionDataCol="data"

sessionValidCol="valid_session"

sessionMaxInactiveCol="max_inactive"

sessionLastAccessedCol="last_access"

/>

</Manager>

</Context>

The <Manager> element attributes specify general session-related options. Within the <Manager> element body, the <Store> element attributes provide the specifics pertaining to the JDBC driver. The following discussion focuses on the attributes shown in the example, but there are others that you can use. See the Tomcat session-management documentation for more information.

The <Manager> attributes shown in the example have the following meanings:

className

Indicates the Java class that implements persistent session storage. It must be org.apache.catalina.session.PersistentManager.

debug

Indicates the logging detail level. A value of zero disables debug output; higher numbers generate more output.

saveOnRestart

Enables application sessions to survive server restarts. It should be true if you want Tomcat to save current sessions when it shuts down (and reload them when it starts up).

maxIdleBackup

Indicates the number of seconds before inactive sessions are eligible for being saved to MySQL. A value of -1 means “never.”

minIdleSwap

Indicates how many seconds a session can be idle before becoming eligible to be swapped (saved to MySQL and passivated out of server memory). A value of -1 means “never.”

maxIdleSwap

Indicates how many seconds a session can be idle before it should be swapped. A value of -1 means “never.” If this feature is enabled, the value should be greater than minIdleSwap and maxIdleBackup.

Within the body of the <Manager> element, the <Store> element indicates how to connect to the database server, which database and table to use for storing session records, and the names of the columns in the table:

className

The name of a class that implements the org.apache.catalina.Store interface. For JDBC-based storage managers, the value of this attribute must be org.apache.catalina.session.JDBCStore.

driverName

The class name for the JDBC driver. For the MySQL Connector/J driver, the attribute value should be com.mysql.jdbc.Driver.

connectionURL

Indicates how to connect to the database server. The following URL connects to the MySQL server on the local host, using a database, username, and password of cookbook, cbuser, and cbpass, respectively:

jdbc:mysql://localhost/cookbook?user=cbuser&password=cbpass

However, server.xml entries are written in XML, so the & character that separates the user and password connection parameters must be written as the & entity, like so:

jdbc:mysql://localhost/cookbook?user=cbuser&password=cbpass

When Tomcat reads the server.xml file, the XML parser converts & back to &, which is what gets passed to the JDBC driver.

sessionTable

Names the table in which to store session records. For our example, this is the tomcat_session table described earlier. (The database that contains the table is given in the connectionURL value.)

The remaining <Store> attributes in the example indicate the column names in the session table. These attributes are sessionIdCol, sessionAppCol, sessionDataCol, sessionValidCol, sessionMaxInactiveCol, and sessionLastAccessedCol, which correspond in the obvious way to the columns contained in the tomcat_session table.

After you modify the server.xml file, restart Tomcat. Then invoke the sess_track.jsp or sess_track2.jsp scripts a few times to initiate a session. Each should behave the same way as before you reconfigured Tomcat. After a period of inactivity equal to the <Manager> element maxIdleBackupattribute value, you should see a session record appear in the tomcat_session table. If you watch the MySQL query log, you should also see sessions being saved to MySQL when you shut down Tomcat.

Changing server.xml is a global change, somewhat similar to changing session.save_handler in PHP’s php.ini file. However, unlike PHP, in which modifying the global initialization file affects other developers on the same host in such a way that they may have to change their session-based scripts, modifying Tomcat’s configuration to use JDBC-based backing store for session management is completely invisible to servlets and JSP pages. Thus, you can make the change without worrying that other developers who use the same Tomcat server will accuse you of acting toward them with malice aforethought.

Session expiration in Tomcat

Session persistence is 60 minutes by default. To provide an explicit duration for a session manager, add a maxInactiveInterval to the appropriate <Manager> element in the server’s conf/server.xml file. To provide a duration that is specific to a particular application context, add a<session-config> element to the context’s WEB-INF/web.xml file. For example, to use a value of 30 minutes, specify it like this:

<session-config>

<session-timeout>30</session-timeout>

</session-config>

If you modify either server.xml or web.xml, restart Tomcat.

Session tracking in Tomcat

Although your JSP pages need do nothing to have Tomcat set up sessions or to use JDBC for session storage, they may need to take a small step to make sure that sessions move from request to request properly. This is necessary if you generate pages that contain hyperlinks to other pages that participate in the same session.

Tomcat automatically generates a session identifier and tracks the session using cookies if it receives a cookie from the client that contains the session ID. If the client has cookies disabled, Tomcat tracks the session by rewriting URLs to include the session ID. You need not determine which method Tomcat is using, but you should take care to ensure proper propagation of the session ID in case it is being passed by URL rewriting. This means that if you create a page that includes a link to another page that is part of the session, you should not just list the path to the page like this:

To go to the next page,

<a href="nextpage.jsp">click here</a>.

This link doesn’t contain the session ID. If Tomcat happens to be tracking the session using URL rewriting, you’ll lose the ID when the user selects the link. Instead, pass the link to encodeURL() to enable Tomcat to add the session ID to the URL as necessary:

To go to the next page,

<a href="<%= response.encodeURL ("nextpage.jsp") %>">click here</a>.

If Tomcat is tracking the session with cookies, encodeURL() returns the URL unchanged. However, if Tomcat is tracking the session by means of URL rewriting, encodeURL() adds the session ID to the page path automatically, so that it looks something like this:

mypage.jsp;jsessionid=xxxxxxxxxxxxxxxx

You should generate URLs using encodeURL() like this for links in any tag that takes the user to a page in the current session. This includes <a>, <form>, and <frame> tags, and possibly even <img> tags, if for some reason those tags invoke a script that generates images on a session-specific basis.

It’s probably best to develop the habit of using encodeURL() as a matter of routine when writing URLs for session-based applications. Even if you think everyone who uses the application will have cookies enabled, your assumption may prove incorrect some day.

The java.net.URLEncoder.encode() method has a name similar to encodeURL(), but it’s different. It converts special characters to % xx notation to make them safe for use in URLs.