Storing Sessions in Database in PHP

HTTP protocol is a stateless protocol. It means that there is no persistent data to identify each requests made using HTTP. Sessions are a way for developers to track users in the web and to store persistent data for each HTTP requests. Of-course there are other ways to persist data, like storing data in the client side using cookie. But that is not so secure since client side data can always be modified.


Problem with the Default Session Storage


PHP by default has a built in mechanism for Storing Sessions. Sessions are stored in the server side. They are stored in file names starting with "sess_" and followed by a unique session id. This way of storing sessions in files has some drawbacks.

In a shared hosting environment, all the websites store their session information in same location. Though php creates a unique session id for each session, there is a chance of session file of one website collapsing with the other.

Since the sessions files are stored in the same location, other websites can access your session files. But you can always change the location of the session file storage. Storing sessions in files is not considered secure when considering security.


Storing Session in Database


To overcome the above drawbacks we must store the session in the database. PHP provides a handy way to accomplish this. To create a custom session storage handler in PHP we must user the "session_set_save_handler" function.

This function params has two prototypes,


OOP Based 


An instance of the class implementing the "SessionHandlerInterface" (An interface provided by php for classes which must implement session storage.) and a register_shutdown paramerter.

Procedural Based


Here you specify the following function or methods individualy. Those function are "open","close", "read", "write", "destroy" and "gc" (Garbage Collector).

We are going to use the OOP based implementation where we must create a class that implements the "SessionHandlerInterface" provided by PHP. I have implemented some interfaces in way for us to write session handling for multiple databases.

Database


To store the session in database you must have the following columns in the your specific database table.
  • id - The unique session id
  • data - The encoded session data
  • last_access - When was the last time the session data accessed.

SessionDBInterface Interface


This interface must be implemented by your database specific class. It must implement the methods in the SessionDbInterface for your specific database, in our case i have implemented it for MySQL.

/**
 * Defines the interface that must be implemented by the
 * Database class that implements the session handling.
 */
interface SessionDBInterface {
     
  /**
   * Read the data corresponding to the $sessionId from the session database
   *
   * @param string $sessionId Id of the session to which data must be read
   * @return string|boolean
   */
  public function readSessionData($sessionId);

  /**
   * Writes the given session data to the session database.
   *
   * @param string $sessionId
   * @param string $sessionData
   */
  public function writeSessionData($sessionId, $sessionData);

  /**
   * Deletes the session data for the given sessionId from the database.
   *
   * @param string $sessionId
   * @return boolean
   */
  public function deleteSessionData($sessionId);

  /**
   * Check if a session for the current $sessionId exists.
   *
   * @param string $sessionId
   * @return boolean
   */
  public function checkSession($sessionId);
}


MySQL Specific Implementation


I have created a class that implements the above interface for mysql. In this class i have used Dependency Injection to pass the mysql database connection. By this way it does not embed the database connection inside the class.

/**
 * MySql specific Session DB class.
 * Implements the SessionDBInterface.
 */
class MysqlSessionDB implements SessionDBInterface {
  
  /**
   * The database connection holder
   */
  private $dbConnection;
  
  /**
   * Errors are displayed when this is set to true.
   */
  const SHOW_ERROR = true;
  
  /**
   * Stores the database connection.
   *
   * @param mysqli $mysqlConnection
   */
  public function __construct($mysqlConnection) {
    $this->dbConnection = $mysqlConnection;
  }
  
  /**
   * Read the data corresponding to the $sessionId from the session database
   *
   * @param string $sessionId Id of the session to which data must be read
   * @return string|boolean
   */
  public function readSessionData($sessionId) {
    $sessionDataQuery = "SELECT `data`
      FROM `mssession`
      WHERE `id` = ?";
    $sessionData = $this->dbConnection->prepare(
                                $sessionDataQuery);
    if ($sessionData !== false) {
      $sessionData->bind_param('s', $sessionId);
      if ($sessionData->execute() !== false) {
        $data = '';
        $sessionData->bind_result($data);
        $sessionData->fetch();
        return $data;
      }
      $sessionData->close();
    } else {
      if (self::SHOW_ERROR) {
        echo $sessionData->error;
      }
      return false;
    }
  }

  /**
   * Writes the given session data to the session database.
   *
   * @param string $sessionId
   * @param string $sessionData
   * @return boolean
   */
  public function writeSessionData($sessionId, $sessionData) {
    $storeDataQuery = "REPLACE INTO
      `mssession` (`id`, `data`, `last_access`)
      VALUES(?, ?, ?)";
    $storeData = $this->dbConnection->prepare($storeDataQuery);

    if ($storeData !== false) {
      $storeData->bind_param('sss', $sessionId, $sessionData,
                            $this->accessTime);
      if ($storeData->execute() !== false) {
        if ($storeData->affected_rows > 0) {
          return true;
        }
      } else {
        if (self::SHOW_ERROR) {
          echo $storeData->error;
        }
      }
      $storeData->close();
    } else {
      if (self::SHOW_ERROR) {
        echo $storeData->error;
      }
    }
  }

  /**
   * Deletes the session data for the given sessionId from the database.
   *
   * @param string $sessionId
   * @return boolean
   */
  public function deleteSessionData($sessionId) {
    $deleteSessionQuery = "DELETE
      FROM `mssession`
      WHERE `id` = ?";
    $deleteSession = $this->dbConnection->prepare($deleteSessionQuery);

    if ($deleteSession !== false) {
      $deleteSession->bind_param('s', $sessionId);
      if ($deleteSession->execute() !== false) {
        return true;
      }
      $deleteSession->close();
    } else {
      if (self::SHOW_ERROR) {
        echo $deleteSession->error;
      }
    }
  }

  /**
   * Check if a session for the current $sessionId exists.
   *
   * @param string $sessionId
   * @return boolean
   */
  public function checkSession($sessionId) {
    $sessionCheckQuery = "SELECT `id`
      FROM `mssession`
      WHERE `id` = ?
      LIMIT 1";
    $sessionCheck = $this->dbConnection->prepare(
                                $sessionCheckQuery);
    if ($sessionCheck !== false) {
      $sessionCheck->bind_param('s', $sessionId);
      $sessionCheck->execute();
      // If greater than 0, session exists
      if ($sessionCheck->affected_rows > 0) {
        return true;
      } else {
        return false;
      }
      $sessionCheck->close();
    } else {
      if (self::SHOW_ERROR) {
        echo $sessionCheck->error;
      }
      return false;
    }
  }
  
  /**
   * Closes the dbConnection
   */
  public function close() {
    $this->dbConnection->close();
  }
}


SesssionHandler


This is the class that actually manages the session. This class implements the "SessionHandlerInterface" provided by PHP. When constructing this class we pass the database specific implementation of SessionDBInterface.

/**
 * This class implements the session storage mechanism.
 * @implement SessionHandlerInterface
 */
class SessionHandler implements SessionHandlerInterface {
        
  /**
   * The accessTime of the session
   */
  public $accessTime;
  
  /**
   * Holds the instance that handles the sessiondb functionality.
   */
  private $sessiondb;
  
  /**
   * Assings the database Connection.
   * Start the session.
   *
   * @param $sessiondb instance of the session database access class
   */
  public function __construct(SessionDBInterface $sessiondb) {
    $this->sessiondb = $sessiondb;
    $this->accessTime = time();
  }
   
  /**
   * Executed when the session is started automatically, or
   * manually with session_start();
   *
   * @param string $savePath
   * @param string $sessionId
   * @return boolean
   */
  public function open($savePath, $sessionName) {
    $savePath = '';
    $sessionName = '';
    return true;
  }

  /**
   * Reads the session data if one exists for the given sessionId,
   * else returns a empty data.
   *
   * @param string $sessionId - The session id to read.
   * @return string
   */
  public function read($sessionId) {
    $data = $this->sessiondb->readSessionData($sessionId);
    if ($data !== false) {
      return $data;
    }
    return '';
  }

  /**
   * Used to save the session and close.
   * close() is called after this function executes.
   *
   * @param string $sessionId Id of the current session
   * @param string $sessionData serialized session data
   */
  public function write($sessionId, $sessionData) {
    $this->sessiondb->writeSessionData($sessionId, $sessionData);
  }

  /**
   * The garbage collector callback is invoked internally by PHP periodically
   * in order to purge old session data. The frequency is controlled by
   * session.gc_probability and session.gc_divisor. The value of lifetime
   * which is passed to this callback can be set in session.gc_maxlifetime.
   * Return value should be TRUE for success, FALSE
   *
   * @param string $maxLifeTime
   * @return boolean
   */
  public function gc($maxLifeTime) {
    $maxLifeTime = '';
    return true;
  }

  /**
   * Closes the session.
   * Called after the write method is called.
   * @return boolean
   */
  public function close() {
    $this->sessiondb->close();
    return true;
  }

  /**
   * Removes all the data corresponding to the $sessionId.
   *
   * @param string $sessionId
   * @return boolean
   */
  public function destroy($sessionId) {
    if ($this->sessiondb->deleteSessionData($sessionId) !== false) {
      return true;
    }
    return false;
  }
}



All the classes shown above are well commented and self explanatory. A sample test case of this classes is show below.

Example



include_once __dir__ . '/SessionDBInterface.php';
include_once __dir__ . '/MysqlSessionDB.php');
include_once __dir__ . '/SessionHandler.php');

$mysqli = new mysqli('localhost', 'root', 'root', 'session');
$sessiondb = new MysqlSessionDB($mysqli);
$session = new SessionHandler($sessiondb);

session_set_save_handler($session, true);
session_start();

// Store and retrieve session data

session_destroy();


In the above code.

Download this code from this Github repository.