PHP Sessions and Data Persistence Storing Sessions in a Database

PHP Sessions and Data Persistence

Storing Sessions in a Database

Problem

You want to store session data in a database instead of in files. If multiple web servers all have access to the same database, the session data is then mirrored across all the web servers.

Solution

Use a class in conjunction with the session_set_save_handler() function to define database-aware routines for session management. 

Example  Database-backed session handler

       /** Implementing SessionHandlerInterface is mandatory as of PHP 5.4
         * and will fail in previous versions.
         */
       class DBHandler implements SessionHandlerInterface {
                
             protected $dbh;

             public function open($save_path, $name) {
                   try {
                         $this->connect($save_path, $name);
                         return true;
                   } catch (PDOException $e) {
                         return false;
                   }
              }

              public function close() {
                    return true;
              }

              public function destroy($session_id) {
                   $sth = $this->dbh->prepare("DELETE FROM sessions WHERE session_id = ?");
                   $sth->execute(array($session_id));
                   return true;
              }

              public function gc($maxlifetime) {
                 $sth = $this->dbh->prepare("DELETE FROM sessions WHERE last_update < ?");
                 $sth->execute(array(time() - $maxlifetime));
                 return true;
              }

              public function read($session_id) {
                 $sth = $this->dbh->prepare("SELECT session_data FROM sessions WHERE
                                                                        session_id = ?");
                 $sth->execute(array($session_id));
                 $rows = $sth->fetchAll(PDO::FETCH_NUM);
                 if (count($rows) == 0) {
                      return '';
                 } else {
                      return $rows[0][0];
                 }
              }

              public function write($session_id, $session_data) {
                 $now = time();
                 $sth = $this->dbh->prepare("UPDATE sessions SET session_data = ?,
                                                                        last_update = ? WHERE session_id = ?");
                 $sth->execute(array($session_data, $now, $session_id));
                 if ($sth->rowCount() == 0) {
                      $sth2 = $this->dbh->prepare('INSERT INTO sessions (session_id,
                                                                               session_data, last_update)
                                                                               VALUES (?,?,?)');
                      $sth2->execute(array($session_id, $session_data, $now));
                 }
              }

              public function createTable($save_path, $name, $connect = true) {
                 if ($connect) {
                      $this->connect($save_path, $name);
                 }
                 $sql=<<<_SQL_
       CREATE TABLE sessions (
           session_id VARCHAR(64) NOT NULL,
           session_data MEDIUMTEXT NOT NULL,
           last_update TIMESTAMP NOT NULL,
           PRIMARY KEY (session_id)
       )
       _SQL_;
                 $this->dbh->exec($sql);
              }

              protected function connect($save_path) {
                  /* Look for user and password in DSN as "query string" params */
                  $parts = parse_url($save_path);
                  if (isset($parts['query'])) {
                       parse_str($parts['query'], $query);
                       $user = isset($query['user']) ? $query['user'] : null;
                        $password = isset($query['password']) ? $query['password'] : null;
                       $dsn = $parts['scheme'] . ':';
                       if (isset($parts['host'])) {
                            $dsn .= '//' . $parts['host'];
                       }
                       $dsn .= $parts['path'];
                       $this->dbh = new PDO($dsn, $user, $password);
                  } else {
                       $this->dbh = new PDO($save_path);
                  }
                  $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                  // A very simple way to create the sessions table if it doesn't exist.
                  try {
                       $this->dbh->query('SELECT 1 FROM sessions LIMIT 1');
                  } catch (Exception $e) {
                       $this->createTable($save_path, NULL, false);
                  }
              }
          }

Discussion

One of the most powerful aspects of the session module is its abstraction of how sessions get saved. The session_set_save_handler() function tells PHP to use different functions for the various session operations such as saving a session and reading session data.

In PHP 5.4 and later, you give session_set_save_handler() an instance of a class that implements the SessionHandlerInterface interface. In earlier versions, there’s no explicit interface, but the methods to define are the same: the public methods open(), close(), destroy(), gc(), read(), and write() are called from PHP’s internal session handling code when necessary. To use this session handler, instantiate the class and pass it to session_set_save_handler():

          include __DIR__ . '/db.php';
          ini_set('session.save_path', 'sqlite:/tmp/sessions.db');
          session_set_save_handler(new DBHandler);

          session_start();
          if (! isset($_SESSION['visits'])) {
               $_SESSION['visits'] = 0;
          }
          $_SESSION['visits']++;
          print 'You have visited here '.$_SESSION['visits'].' times.';

This code block assumes that the DBHandler class is defined in a file called db.php in the same directory as itself. Once session.save_path is set to the PDO DSN describing the database that holds the sessions table, session_set_save_handler(new DBHandler) is all that’s necessary to wire up PHP to the handler. From then on, your session- using code is the same as if you were using PHP’s default handler.

In the additional public createTable() method is provided as a convenient way to create the table into which session data is stored. The connect() method calls it if it can’t find a sessions table to use.

The createTable() and open() functions are also passed the session name as a separate variable. The default value for this is PHPSESSID. It is used as the cookie name by PHP when setting a cookie containing the session ID. If you need to distinguish between differently named sessions that might be assigned to the same user, modify the DBHandler class to incorporate the $name argument into the database table name or as an additional column in the sessions table.