Using an SQL backing store for PHP sessions


This article explores the mechanisms by which PHP session handling can be customized to work with non-standard datastore like SQL datbases using the PHP Data Object (PDO) interface.

To frame the context of this discussion, let’s briefly look at how the HTTP protocol is designed. HTTP is a stateless client/server protocol. That is an HTTP client, like a web browser, makes request for a resource on a server, which the server responds with. From the server’s point of view, each request has no relationship with previous requests. For web application developers, this is a challenge. Often applications need to keep state information associated with a particular user. An application state that’s associated with a user is called a session.

What is the default PHP way of addressing this?

Being that PHP was designed for web applications, it’s not surprising to learn that it offers built-in support for sessions.

A typical “vanilla” use of PHP sessions looks like this:

<p>Count: <?=$_SESSION["count"]?></p>

A quick note on session.auto_start and session_register(). You can make PHP start a session when any PHP is loaded by setting the “session.auto_start” attribute to in php.ini or by invoking ini_set(). However, I do not recommend this for applications that have named users. Sessions should begin only after a user has authenticated. PHP also can make session variables global scalars. This too is a bad idea. Global variables in general are a bad engineering practice. PHP’s ability to make GET/POST parameters global variables too creates an opportunity for malicious users to pass in arbitrary session values. Instead, use the super global $_SESSION array to store session variables.

By default, PHP can pass a session ID in URLs or through HTTP cookies. Cookies offer the most transparent and arguably safer mechanism. Also by default, PHP stores session information in flat files on the web server. On a shared web host, this backing store presents a security problem as the files are often stored in the communal /tmp directory. Sessions in flat files also present a challenge to scaling a web application beyond the single host. By storing session information in an SQL system, a user can be shunted to any number of web servers that have access to the session database.

The authors of PHP have defined a simple procedural callback mechanism that allows developers to use any desirable data storage mechanism. Up to six session callback functions can be overridden to customize the way session data is stored using the session_set_save_handler(). These six callbacks are: open, close, read, write, destroy and garbage collection. Let’s look at what these callbacks are responsible for.

When the session begins, the open callback is invoked to initialize the datastore. This can mean that a file is opened and its handle stored in a global variable. The open callback is passed two strings: a path and the session name. Both of these parameters can be controlled by the developer. However for our database backing store, these values will be unused.

The close callback is called when the session has ended. All global resources, like filehandles, should be freed at this point. The close callback receives no additional parameters.

The read callback is invoked when the opened session attempts to retrieve previously stored session data. It is passed the current session ID. It should return a serialized data string that it was passed when the write callback was last called for this session. You are not responsible for serializing or deserializing session data, which is a pity. There are some issues with the serialization method used by PHP sessions.

The write callback is invoked when the session is ready to be closed. It is passed a session ID and a serialized data string. The callback must store this information is a place where it can retrieved later.

The destroy callback is invoked when the session is to be explicitly destroyed. It is passed a session ID. Session destruction often happens when a user explicitly signs out of an application.

Finally, the garbage collection callback is invoked automatically by the PHP session mechanism to clean-up old sessions. It is called with a maximum life value expressed in seconds. All sessions older than that value should be destroyed.

Even though this session customization mechanism is expressed as function callbacks, I find that it is architecturally cleaner to wrap up session handling into a static class. It puts all the callbacks and static members into one descrete namespace that can be more easily consumed by other web applications. Please note that much of the error checking as been left out.

First, you will need create the following table in a mysql database (which is called ‘myapp’ in this code):

CREATE TABLE sessions (
  id int auto_increment not null primary key, 
  d text,
  updated int
class Sessions {
  public static $Db = null;
  public static $SessionName = "MySession";

  public static function Open($path, $session_name) {
    if (Sessions::$Db == null) {
      $dsn = 'mysql:host=localhost;dbname=myapp'
      Sessions::$Db = new PDO($dsn, 'db_user', 'db_pass');
    return True;

  public static function Close() {
    if (Sessions::$Db != null) {
      Sessions::$Db = null;
    return True;

  public static function Read($sid) {
    $sql = "SELECT * FROM sessions WHERE id=?";
    $sth = Sessions::$Db->prepare($sql);
    $tbl = $sth->fetchAll();
    return $tbl[0]["d"];

  public static function Write($sid, $data) {
    $vals = array("d"=>$data,
          "updated" => time());

    $sql = "SELECT COUNT(id) FROM sessions WHERE id=?";
    $sth = Session::$Db->prepare($sql);
    $tbl = $sth->fetchAll();

    if ($tbl[0][0] < 1) {
    // New session
    $vals["id"] = $sid;
        $sql = "INSERT INTO sessions ('id','d','updated') WHERE (?,?,?)";
        $sth = Sessions:$Db->prepare($sql);
        $sth->execute($vals['id'], $vals['d'], $vals['updated']);
      } else {
        // Existing session
        $sql = "UPDATE sessions SET d=?,updated=? WHERE id=?";
        $sth = Sessions::$Db->prepare($sql);
        $sth->execute($vals['d'], $vals['updated'], $vals['id']);
    return True;

  public static function Destroy($sid) {
    if (strlen($sid) > 0) {
      $sql = "DELETE FROM sessions WHERE id=?";
      $sth = Sessions:$Db->prepare($sql);
    return True;

  public static function garbageCollect($max_life) {
    $threshold = time() - $max_life;
    $sql = "DELETE FROM sessions WHERE updated < $threshold";
    return True;

  public static function initCallbacks() {
    $rc = session_set_save_handler(

    if ($rc === False) {
      Utils::Logger("Could not set session handlers.");

    $cookie_args = array(60*60*24*14, // Lifetime in seconds
             "/", // Domain path
             "", // Domain where valid
             False, // Send cookie over unsecure connections
             False, // httponly

    return True;


This static class defines the session save handlers as static method. When the file is included, it calls another static method to install the session handlers. Most of this code is pretty straight forward, thanks to the placeholder facility of PDO. I would note that the session table’s updated column is a unix timestamp rather than a mysql datetime type. This makes the GC reaping code much easier to write.

A note about using auto incrementing IDs for sessions identifiers. It is considered poor security to use an easily predictable sequence for session IDs since an attack can attempt to hijack legitimate sessions by guessing this number. PHP provides a uniqid() function which is much better suited for session IDs.