MySQL, PHP

PHP Database Sessions

Why bother?

  • Security – It is harder to hack the web server and then the database than it is to just hack the web server
  • Speed – You can store the session anywhere, including an in-memory database (Redis)
  • Query sessions – “Who is online right now?”
  • Ease of learning & use – Database session handling is very easy to learn & use

Setting up the table

CREATE TABLE IF NOT EXISTS `sessions` (
  `session_id` varchar(250) NOT NULL,
  `user_id` int(11) default NULL,
  `session_data` longtext NOT NULL,
  `expires` varchar(250) NOT NULL,
  PRIMARY KEY  (`session_id`)
) COMMENT='session database storage';

This is only a basic table given in SQL so you can paste it straight into PhpMyAdmin or MySQL Workbench. Most of the fields are self explanatory but I will go through each one:

  • session_id – This houses the session id of the current session. This is the PHP session ID, not your own.
  • user_id – This allows to store the user identification number and use it later to understand which user that session belongs to.
  • session_data – This field just contains all the session data for that session (all the random stuff you put into $_SESSION)
  • expires – This field is compared for garbage collection to understand whether a session has expired or not and if it has the script will delete the session.

Now, we need the actual file which will hook into PHP and do our sessions:

<?php
class Session
{
    public $life_time;
    public $db;

    /**
     * This houses the actual save handler (session_set_save_handler) which then houses an array telling the sessions how to save (what methods to use)
     * @param $db The DB object you have from your application, might be a PDO object
     */
    public function __construct($db)
    {
        // Read the maxlifetime setting from PHP
        $this->life_time = get_cfg_var("session.gc_maxlifetime");

        // Register this object as the session handler
        session_set_save_handler(
            [$this, "open"],
            [$this, "close"],
            [$this, "read"],
            [$this, "write"],
            [$this, "destroy"],
            [$this, "gc"]
        );

        //read in the db object my api uses
        $this->db = $db;
    }

    /**
     * This opens the session for reading or writing
     * @param $save_path
     * @param $session_name
     * @return bool
     */
    public function open($save_path, $session_name)
    {
        global $sess_save_path;
        $sess_save_path = $save_path;
        // Don't need to do anything. Just return TRUE.
        return true;

    }

    /**
     * @return bool
     */
    public function close()
    {
        return true;
    }

    /**
     * This reads all you session data from the table into $_SESSION whilst checking the session has not expires. Also makes a new session if needed.
     * @param $id
     * @return string
     */
    public function read($id)
    {
        // Set empty result
        $data = '';

        // Fetch session data from the selected database
        $time = time();

        $result = $this->db->query(
            "SELECT session_data FROM sessions WHERE session_id = :id AND expires > :time",
            [
                ':id' => $id,
                ':time' => $time
            ]
        )->fetch();

        if ($result) {
            $data = $result->session_data;
        }
        return $data;
    }

    /**
     * Writes all your $_SESSION data to database
     * @param $id
     * @param $data
     * @return bool|integer
     */
    public function write($id, $data)
    {
        // Build query
        $time = time() + $this->life_time;

        if (isset($_SESSION['uid'])) {
            return $this->db->exec(
                "REPLACE sessions(session_id, UserID, session_data,expires) VALUES(:id, :user_id, :data, :time)",
                [
                    ':id' => $id,
                    ':user_id' => $_SESSION['uid'],
                    ':data' => $data,
                    ':time' => $time
                ]
            );
        } else {
            return $this->db->exec(
                "REPLACE sessions(session_id, session_data, expires) VALUES(:id, :data, :time)",
                [
                    ':id' => $id,
                    ':data' => $data,
                    ':time' => $time
                ]
            );
        }

        return false;
    }

    /**
     * This is complex because normally you can just use session_destroy or whatever but due to a bug in PHP which is meant to be fixed you had to logout the user manually. Best to look at my user class for help on this. This is most likely fixed but have never double checked.
     * @param $id
     * @return integer
     */
    public function destroy($id)
    {
        return $this->db->exec(
            "DELETE FROM sessions WHERE session_id = :session_id",
            [
                ':session_id' => $id
            ]
        );
    }

    /**
     * This will delete any old sessions which should no longer exist. For this to work properly you must set carbage collection within your php.ini to 100% of the time. This is done within the session.gc_probability value in your php.ini, set it to 100 for 100%
     * @return integer
     */
    public function gc()
    {
        return $this->db->exec('DELETE FROM sessions WHERE expires < UNIX_TIMESTAMP();');
    }
}

It is time to finish this tutorial with an example:

<?php
//include the session.php database session handler from above
require_once('session.php');

//Set the ini values so that the script works properly
ini_set('session.gc_probability', 100);
ini_set('session.gc_divisor', 100);

//me passing my database object to it.
$sess = new Session($db);

//start session as you would normally
session_start();

//Add and ech as you would normally. The session.php is only a handler for the storage of data it does not over ride the default session.
$_SESSION['username'] = "hello, I am the User. This session is using database sessions.";

echo $_SESSION['username'];