How do I store sessions in a database?

Excerpt from The PHP Anthology: 101 Essential Tips, Tricks & Hacks, 2nd Edition

, , , , and

As discussed earlier, in the section called “How do I use sessions?”, the default behavior of sessions in PHP on the server side is to create a temporary file in which session data is stored. This file is usually kept in the temporary directory of the operating system and, as such, presents a security risk to your applications, especially if you’re using a shared server.

Solution

Use the PHP function session_set_save_handler to specify a custom session handler that provides an alternative data store that’s fully under your control. The session_set_save_handler function definition is as follows:

bool session_set_save_handler (callback $open,
    callback $close,
    callback $read,
    callback $write,
    callback $destroy,
    callback $gc
);

Each callback argument is a function that must conform to the PHP session’s API. You can read more about the function on The PHP Manual page. You can simply implement a separate function for each callback; however, in this solution we create a new class—the DatabaseSession class—to encapsulate all our session handling needs, and use a PDO object to connect to a database and store session information there.

Before we delve deep into the details of the class, I’ll show you the create statement for the session table we use. This statement provides a minimal amount of information for you to keep track of, so feel free to add more if you wish—for example, you might like to store the IP address or the last page visited. Just remember to add the new columns and values to the queries that are used throughout the class’s methods below:

CREATE TABLE session (
  sess_id       VARCHAR(255),
  sess_start    DATETIME,
  sess_last_acc DATETIME,
  sess_data     VARCHAR(255),
  PRIMARY KEY (sess_id)
);

The DatabaseSession Class

Now, let’s look at the class. We begin by defining the class properties:

DatabaseSession.class.php (excerpt)
class DatabaseSession
{
  private $sess_table;
  private $sess_db;
  private $sess_db_host;
  private $sess_db_usr;
  private $sess_db_pass;
  private $db;

$sess_table will store the database table name, $sess_db will store the database name, $sess_db_host will store the database server hostname, $sess_db_usr will store the database username, and $sess_db_pass will store the database password. The $db property will store the PDO object used for all the database queries.

Next, we define the constructor method:

DatabaseSession.class.php (excerpt)
  public function __construct($sess_db_usr = 'user', 
      $sess_db_pass = 'passwd', 
      $sess_table = 'session', 
      $sess_db = 'dbname', 
      $sess_db_host = 'locolhost')
  {
    $this->sess_db_usr = $sess_db_usr;
    $this->sess_db_pass = $sess_db_pass;
    $this->sess_table = $sess_table;
    $this->sess_db = $sess_db;
    $this->sess_db_host = $sess_db_host;
  }

The constructor simply stores the database information passed to the method within the object’s properties.

The first function callback that we must pass to the session_set_save_handler function is an open function, which is called when a session is started. The open method of the DatabaseSession class will handle that job:

DatabaseSession.class.php (excerpt)
  public function open($path, $name)
  {
    try
    {
      $dsn = "mysql:host={$this->sess_db_host};".
          "dbname={$this->sess_db}";
      $this->db = new PDO($dsn, $this->sess_db_usr,
          $this->sess_db_pass );
      $this->db->setAttribute(PDO::ATTR_ERRMODE,
          PDO::ERRMODE_EXCEPTION);
    }
    catch (PDOException $e)
    {
      error_log('Error connecting to the session database.');
      error_log('Reason given:'.$e->getMessage()."\n");
      return false;
    }
    return true;
  }

This method is called with two string arguments—the path of the session file and the name of the file—and must return either true or false. The path and filename information is irrelevant to us as we’re using a database, so we do nothing with it. In the method, we make the connection to the database that will hold the session data. If there’s an error, we return false; if the database connection is successful, we return true.

The next function callback we need to implement is the close function, so we add a close method to our class:

DatabaseSession.class.php (excerpt)
  public function close()
  {
    $this->db = null;
    return true;
  }

The close method is called when we end a session, and must return either true or false. It isn’t uncommon to manually call the garbage collection (gc) method here, though it isn’t strictly necessary—PHP will do its own garbage collection throughout. We remove our database connection by setting the close method to null.

session_set_save_handler also requires that a read function be implemented. The read function needs to take the session ID as an argument and return a string—even an empty one, if that’s appropriate. We implement a read method in our class:

DatabaseSession.class.php (excerpt)
  public function read($sess_id)
  {
    try
    {
      $sql = "SELECT sess_data FROM {$this->sess_table} WHERE " .
          "sess_id = :id";
      $stmt = $this->db->prepare($sql);
      $stmt->execute(array(':id'=>$sess_id));
      $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    catch (PDOException $e)
    { 
      error_log('Error reading the session data table in the' .
          ' session reading method.');
      error_log(' Query with error: '.$sql);
      error_log(' Reason given:'.$e->getMessage()."\n");
      return ''; 
    }
    if (count($res) > 0)
    {   
      return isset($res[0]['sess_data']) ? 
          $res[0]['sess_data'] : '';
    }
    else
    {
      return '';
    }
  }

The read method retrieves the session data from the database, using the session ID as the key, and returns the data as a string. If no data is found or there’s a database error, an empty string is returned.

After the read function, the next function callback we need to implement is the write function. This function, as the name implies, handles the writing of the session data. The function is required to take two arguments—the session ID and the session data—and the return value must be either true or false. We implement a write method in our class-based solution. In our method, we first see if the session ID is already in the database:

DatabaseSession.class.php (excerpt)
  public function write($sess_id, $data)
  {
    try
    {
      $sql = "SELECT sess_data FROM {$this->sess_table} WHERE " .
          "sess_id = :id";
      $stmt = $this->db->prepare($sql);
      $stmt->execute(array(':id'=>$sess_id));
      $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    catch (PDOException $e)  
    { 
      error_log('Error reading the session data table in the' .
          ' session writing method.');
      error_log(' Query with error: '.$sql);
      error_log(' Reason given:'.$e->getMessage()."\n");
      return false; 
    }

The $res variable contains the result of our database lookup. Based upon this result, we either update the existing session record with an SQL UPDATE query or insert a new one with an SQL INSERT query:

DatabaseSession.class.php (excerpt)
    try
    {
      if (count($res) > 0) 
      {
        $sql = "UPDATE {$this->sess_table} SET" .
            " sess_last_acc = NOW(), sess_data = :data" .
            " WHERE sess_id = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':data', $data);
        $stmt->bindParam(':id', $sess_id); 

      }
      else 
      {
        $sql ="INSERT INTO {$this->sess_table}(sess_id," .
            " sess_start, sess_last_acc," .
            " sess_data) VALUES (:id, NOW(), NOW(), :data)";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':id', $sess_id);
        $stmt->bindParam(':data', $data); 
      }
      $res = $stmt->execute();
    }

If you know you’ll only be using MySQL as your database, consider using the REPLACE syntax instead.[45] Since we don’t want to limit our class to MySQL, we use the longer but more compatible method above.

Finally, we need to catch any PDOExceptions and return true or false:

DatabaseSession.class.php (excerpt)
    catch (PDOException $e)
    {
      error_log('Error writing to the session data table.');
      error_log('Query with error: '.$sql);
      error_log('Reason given:'.$e->getMessage()."\n");
      return false;
    }
    return true;
  }

Our next task is to implement a destroy function, which, as the name suggests, is called when the session is destroyed. It receives the session ID as an argument and must return either true or false. In our class method destroy, we simply delete the session from the database using the session ID as the key, and return false if an error occurs or true if the operation succeeds:

DatabaseSession.class.php (excerpt)
  public function destroy($sess_id)
  {
    try
    {
      $sql = "DELETE FROM {$this->sess_table} WHERE sess_id = :id";
      $stmt = $this->db->prepare($sql);
      $stmt->execute(array(':id'=>$sess_id)); 
    }
    catch (PDOException $e)
    {
      error_log('Error destroying the session.');
      error_log('Query with error: '.$sql);
      error_log('Reason given:'.$e->errorMessage()."\n");
      return false;
    }
    return true;
  }

The final function we are required to implement is the gc, or garbage collection, function, which is used to clean out any old sessions that were never closed properly. It receives an integer argument for the “time to live” (TTL) value for a session. In our class method, gc, we delete any session record where the last access time is less then the current time, minus the TTL value:

DatabaseSession.class.php (excerpt)
  public function gc($ttl)
  {
    $end = date('Y-m-d H:i:s', time() - $ttl);
    try
    {
      $sql = "DELETE FROM {$this->sess_table} WHERE" .
          " sess_last_acc <:end";
      $stmt = $this->db->prepare($sql);
      $stmt->execute(array(':end'=>$end));
    }
    catch (PDOException $e)
    {
      error_log('Error with the garbage collection method of the' .
          ' session class.');
      error_log('Query with error: '.$sql);
      error_log('Reason given:'.$e->getMessage());
      return false;
    }
    return true;
  }

The garbage collection method is called by PHP as dictated by the php.ini settings session.gc_probability and session.gc_divisor, and is checked every time a new session is started. Again, you can call it manually in the session close method if you wish.

Finally, we implement a class __destruct method. This step is necessitated by the changes that were made in how PHP sessions are closed after version 5.0.5. Basically, we just have to make sure the session is explicitly written and closed by calling the session_write_close function. You can read more about this task on the manual page. Here’s our __destruct method and the end of our class definition:

DatabaseSession.class.php (excerpt)
  public function __destruct()
  {
    session_write_close();
  }
}

Using the DatabaseSession Class

Here’s a simple script to test our new DatabaseSession class:

dbsession.php (excerpt)
<?php
require_once 'DatabaseSession.class.php';

$session = new DatabaseSession('user', 'secret', 'session',
    'access_control','localhost');
session_set_save_handler(array($session, 'open'),
    array($session, 'close'),
    array($session, 'read'),
    array($session, 'write'),
    array($session, 'destroy'),
    array($session, 'gc')
); 
session_start(); 

$name = (isset($_SESSION['name']))? $_SESSION['name'] :'';

if ($name !== '')
{
    echo 'Welcome ', $name, ' to your session!';
}
else
{
    echo 'Lets start the session!';
    $_SESSION['name'] = 'PHP';
}
?>

We include our DatabaseSession class, then instantiate the DatabaseSession object. Next, we use session_set_save_handler to register our custom PHP session-handling methods. Then we have a quick little demonstration to show us that the session is working—the first time you load the web page you should see the message “Let’s start the session!" We then set the $_SESSION['name'] to PHP. When you refresh the web page, the message should change to “Welcome PHP to your session!” which indicates that our session data is being stored and retrieved correctly in the database.

Welcome to database-saved sessions!

Footnotes

[45] REPLACE is a MySQL extension to the SQL standard that either inserts a new row, or deletes an old row and inserts the new row if the old row had the same value as the new row for a PRIMARY KEY or UNIQUE index. You can read more about it at http://dev.mysql.com/doc/refman/5.1/en/replace.html.

Get instant access to all books and courses.

Free Trial