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 false;
		}
		$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 false;
		}
		$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 false;
		}
	}

	/**
	* 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 false;
		}
		$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 false;
		}
		$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 false;
		}
		$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.

<?php
require_once("MySession.php");
new MySession();

$_SESSION[‘test’]++;
print $_SESSION[‘test’];