PHP Sessions using MySQL

MySQL sessions are “gotta have it” thing if your site ever grows beyond single server. If you have multiple servers behind a load balancer, you could keep session data on a shared SAN, but MySQL makes for a better session store.

Create a file called MySession.php and put this code in it, replace the definitions to match your database. Also, it requires the PEAR DB.php module, make sure you have pear DB installed: pear install DB.

<?php
/**
* MySession.php
*
* Originally developed by David Koopman
*
*/

/* The MySQL table should look like this:

CREATE TABLE `Sessions` (
`session_id` varchar(32) NOT NULL default ”,
`last_access_time` datetime NOT NULL default ’0000-00-00 00:00:00′,
`data` mediumtext NOT NULL,
PRIMARY KEY (`session_id`),
KEY `last_access_time` (`last_access_time`)
) TYPE=MyISAM;

# You may want to create a MySQL user, like this:
grant select,update,insert,delete on mysess.* to mysess@’localhost’ identified by ‘mysesspassword’;
*/

require_once(“DB.php”);
ini_set(‘session.gc_probability’, 1);
ini_set(‘session.gc_divisor’, 10000); // active sites only need cleanup 1/10,000 hits
ini_set(‘session.gc_maxlifetime’, 60*60*24); // 24 hours
ini_set(‘session.use_cookies’, 1); // yes, the prefered method, no URL passing
ini_set(‘session.use_only_cookies’, 1); // prevent attacks with session ids in URLs
ini_set(‘session.cookie_lifetime’, 0); // the default, when the browser closes
ini_set(‘session.cache_limiter’, ‘nocache’);
ini_set(‘session.use_trans_sid’, 0);

define(‘MY_SESS_DSN’, “mysql://mysess:mysesspassword@localhost/mysess”);
define(‘MY_SESS_SQL_READ’, “SELECT data FROM Sessions WHERE ( session_id=%s )”);
define(‘MY_SESS_SQL_WRITE’, “REPLACE INTO Sessions ( session_id, data, last_access_time ) VALUES ( %s, %s, NOW() )”);
define(‘MY_SESS_SQL_DESTROY’, “DELETE FROM Sessions WHERE ( session_id=%s )”);
define(‘MY_SESS_SQL_STALE’, “DELETE FROM Sessions WHERE last_access_time < %s”);

class MySession
{

/**
* MySession::MySession
*
* { constructor }
*
*
*/
function MySession($id=false)
{

session_set_save_handler( array( $this, ‘sessOpen’ ),
array( $this, ‘sessClose’ ),
array( $this, ‘sessRead’ ),
array( $this, ‘sessWrite’ ),
array( $this, ‘sessDestroy’ ),
array( $this, ‘sessGC’ ) );
if ($id)
session_id($id);

session_start();
}

/**
* MySession::sessOpen
*
* ( PRIVATE METHOD:
* internal open session )
*
*/
function sessOpen( $aSavePath, $aSessionName )
{
// because of DB storage, this is an unneeded call
return true;
}

/**
* MySession::sessClose
*
* ( PRIVATE METHOD:
* internal close session )
*
*/
function sessClose()
{
// because of DB storage, this is an unneeded call
return true;
}

/**
* MySession::sessRead
*
* ( PRIVATE METHOD:
* internal read session variable )
*
*/
function sessRead( $aSessionID )
{
$sessionDB = DB::connect( MY_SESS_DSN );
if(DB::isError($sessionDB)) {
error_log(“sessRead DB ERROR: “.DB::errorMessage($sessionDB));
return ”;
}

$sessionDB->setFetchMode( DB_FETCHMODE_ASSOC );

// set up SQL statement
$aSQL = sprintf( MY_SESS_SQL_READ, $sessionDB->quote($aSessionID) );
$aResult = $sessionDB->query( $aSQL );
if ( DB::isError( $aResult ) )
{
error_log(“sessRead DB error, $aSQL ERROR: “.DB::errorMessage($aResult));
return ”;
}
$aRowCount = $aResult->numRows();

// if the session variable exists, return the value
if ( is_int($aRowCount) && $aRowCount > 0 )
{
$aRow = $aResult->fetchRow();
return $aRow['data'];
}
else // the session variable has no value
{
return ”;
}
}

/**
* MySession::sessWrite
*
* ( PRIVATE METHOD:
* internal write session variable )
*
*/
function sessWrite( $aSessionID, $aValue )
{

$sessionDB = DB::connect( MY_SESS_DSN );
if(DB::isError($sessionDB)) {
error_log(“sessWrite DB ERROR: “.DB::errorMessage($sessionDB));
return ”;
}
$sessionDB->setFetchMode( DB_FETCHMODE_ASSOC );

$aSQL = sprintf( MY_SESS_SQL_WRITE, $sessionDB->quote($aSessionID), $sessionDB->quote($aValue) );
$aResult = $sessionDB->query( $aSQL );
if ( DB::isError( $aResult ) )
{
error_log(“sessWrite DB error,$aSQL ERROR: “.DB::errorMessage($aResult));
return false;
}
error_log(“sessWrite SQL: $aSQL”);
return true;
}

/**
* MySession::sessDestroy
*
* ( PRIVATE METHOD:
* internal destroy session )
*
*/
function sessDestroy( $aSessionID )
{

$sessionDB = DB::connect( MY_SESS_DSN );
if(DB::isError($sessionDB)) {
error_log(“sessRead DB ERROR: “.DB::errorMessage($sessionDB));
return ”;
}
$sessionDB->setFetchMode( DB_FETCHMODE_ASSOC );

$aSQL = sprintf( MY_SESS_SQL_DESTROY, $sessionDB->quote($aSessionID) );
$sessionDB->query( $aSQL );
$_SESSION = array();
return true;
}

/**
* MySession::sessGC
*
* ( PRIVATE METHOD:
* internal cleanup stale sessions )
*
*/
function sessGC( $aMaxLifetime )
{

$sessionDB = DB::connect( MY_SESS_DSN );
if(DB::isError($sessionDB)) {
error_log(“sessRead DB ERROR: “.DB::errorMessage($sessionDB));
return ”;
}
$sessionDB->setFetchMode( DB_FETCHMODE_ASSOC );

$aSQL = sprintf( MY_SESS_SQL_STALE, $sessionDB->quote(Date(“Y-m-d H:i:s”, time()-$aMaxLifetime)) );
$sessionDB->query( $aSQL );

return true;
}
}
?>

To use it is simple, instead of calling session_start(), just include the MySession class and call new MySession(); to start the session. From there, just use the $_SESSION variable.