How do I save PHP session data to a database instead of in the file system?

Question

I have two websites, One is TLS and one is not, both are for the same client but I need the websites to share with each other (and only each other) common data for users, orders, accounts etc.

This would normally be done with $_SESSION data but I obviously these can't work across other sites, and I have found that I can store session data in a database (MySQL) rather than in the file system.

I have dug around and found This useful guide as well as this older but useful guide. I also found this guide which has slightly more up to date MySQL.

I have written an interface class but it only partly works, it stores the session data in the database, but it doesn't retrieve it. I have also used the suggested method from the PHP manual.

My MySQL (as copied from first couple of the above links):

CREATE TABLE `sessions` (
  `id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `access` int(10) NOT NULL,
  `data` text COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDb DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Please Note: Before I show you my interface class please know that the Db connetion uses my own custom made interface and that works perfectly, in itself.

The $sessionDBconnectionUrl contains the Session Database connection details as I am keeping sessions on a seperate Database from the main website contents.

My interface class (as based on all the above links)

<?php
/***
 * Created by PhpStorm.
 ***/
class HafSessionHandler implements SessionHandler {
    private $database = null;
    
    public function __construct($sessionDBconnectionUrl){

        if(!empty($sessionDBconnectionUrl) && file_exists($_SERVER['DOCUMENT_ROOT'].$sessionDBconnectionUrl)) {
            require_once "class.dataBase.php";
            // Instantiate new Database object
            $this->database = new Database($sessionDBconnectionUrl);
        }
        else {
            error_log("Session could not initialise class.");
        }
        
    }

    /**
     * Open
     */
    public function open($savepath, $id){
         $openRow = $this->database->getSelect("SELECT `data` FROM sessions WHERE id = ? LIMIT 1",$id);
    if($this->database->selectRowsFoundCounter() == 1){
        // Return True
        return $openRow['data'];
        }
    else {
        // Return False
        return ' ';
    }
    /**
     * Read
     */
    public function read($id)
    {
        // Set query
        $readRow = $this->database->getSelect('SELECT `data` FROM sessions WHERE id = ? LIMIT 1', $id,TRUE);
        if ($this->database->selectRowsFoundCounter() > 0) {
            return $readRow['data'];
        } else {
            error_log("could not read session id ".$id);
            return '';
        }
    }

    /**
     * Write
     */
    public function write($id, $data)
    {
        $access = time();
        // Set query
        $dataReplace[0] = $id;
        $dataReplace[1] = $access;
        $dataReplace[2] = $data;
        if ($this->database->noReturnQuery('REPLACE INTO sessions(id,access,`data`) VALUES (?, ?, ?)', $dataReplace)) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    /**
     * Destroy
     */
    public function destroy($id)
    {
        // Set query
        if ($this->database->noReturnQuery('DELETE * FROM sessions WHERE id = ? ', $id)) {
            return TRUE;
        } else {

            return FALSE;
        }
    }
    /**
     * Close
     */
    public function close(){
        // Close the database connection
        // If successful
        if($this->database->dbiLink->close){
            // Return True
            return true;
        }
        // Return False
        return false;
    }

    /**
     * Garbage Collection
     */
    public function gc($max)
    {
        // Calculate what is to be deemed old
        $old = time() - $max;

        // Set query
        if ($this->database->noReturnQuery('DELETE * FROM sessions WHERE access < ?', $old)) {
            return TRUE;
        } else {
            return FALSE;
        }
    }
    
    public function __destruct()
    {
        $this->close();
    }

}

My Test Page (written from scratch!)

<?php
require "class.sessionHandler.inc.php";
$HSH = new HafSessionHandler("connection.session.dbxlink.php");
session_set_save_handler( $HSH, TRUE );
session_start();

print "<p>Hello this is an index page</p>";
$_SESSION['horses'] = "treesx3";
$_SESSION['tiespan'] = (int)$_SESSION['tiespan']+7;

print "<p>There should be some session data in the database now. <a href='index3.php'>link</a></p>";
var_dump($_SESSION);


exit;

Issue:

The test pages I run save the data to the database ok but they do not seem to retrieve the data,

I have error logging enabled and no PHP errors are reported. No critical MySQL errors are reported.

Why doesn't it work?

Answer 1

I have found over the course of several hours debugging that the referenced articles found on numerous Google searches as well as a significant subset of Stack Overflow answers such as here, here and here all provide invalid or outdated information.

Things that can cause [critical] issues with saving session data to a database:

• While all the examples online state that you can "fill" the session_set_save_handler, none of them state that you must also set the register_shutdown_function('session_write_close') too (reference).

• Several (older) guides refer to an outdated SQL Database structure, and should not be used. The database structure that you need for saving session data to the database is: id/access/data. That's it. no need for various extra timestamp columns as I've seen on a few "guides" and examples.

  • Several of the older guides also have outdated MySQL syntax such as DELETE * FROM ...

• The class [made in my question] must implement the SessionHandlerInterface . I have seen guides (referenced above) that give the implementation of sessionHandler which is not a suitable interface. Perhaps previous versions of PHP had a slightly different method (probably <5.4).

• The session class methods must return the values set out by the PHP manual. Again, probably inherited from pre-5.4 PHP but two guides I read stated that class->open returns the row to be read, whereas the PHP manual states that it needs to return true or false only.

• This is the cause of my Original Issue: I was using custom session names (actually id's as session names and session id's are the same thing!) as per this very good StackOverflow post and this was generating a session name that was 128 characters long. As the session name is the sole key that is needed to be cracked to compromise a session and take over with a session hijacking then a longer name/id is a very good thing.

  • But, this caused an issue because MySQL was silently slicing the session id down to just 32 characters instead of 128, so it was never able to find the session data in the database. This was a completely silent issue (maybe due to my database connection class not throwing warnings of such things). But this is the one to watch out for. If you have any issues with retrieving sessions from a database first check is that the full session id can be stored in the field provided.

So with all that out of the way there are some extra details to add as well:

The PHP manual page (linked above) shows an unsuitable pile of lines for a class object:

$handler = new MySessionHandler();
session_set_save_handler($handler, true);
session_start();

Whereas it works just as well if you put this in the class constructor:

class MySessionHandler implements SessionHandlerInterface {

    private $database = null;

public function __construct(){

    $this->database = new Database(whatever);

    // Set handler to overide SESSION
    session_set_save_handler(
        array($this, "open"),
        array($this, "close"),
        array($this, "read"),
        array($this, "write"),
        array($this, "destroy"),
        array($this, "gc")
        );
    register_shutdown_function('session_write_close');
    session_start();
    }
...
}

This means that to then start a session on your output page all you need is:

<?php
require "path/to/sessionhandler.class.php"; 
new MySessionHandler();

//Bang session has been setup and started and works

---

For reference the complete Session communication class is as follows, this works with PHP 5.6 (and probably 7 but not tested on 7 yet)

<?php
/***
 * Created by PhpStorm.
 ***/
class MySessionHandler implements SessionHandlerInterface {
    private $database = null;

    public function __construct($sessionDBconnectionUrl){
        /***
         * Just setting up my own database connection. Use yours as you need.
         ***/ 

            require_once "class.database.include.php";
            $this->database = new DatabaseObject($sessionDBconnectionUrl);

        // Set handler to overide SESSION
        session_set_save_handler(
            array($this, "open"),
            array($this, "close"),
            array($this, "read"),
            array($this, "write"),
            array($this, "destroy"),
            array($this, "gc")
        );
        register_shutdown_function('session_write_close');
        session_start();
    }

    /**
     * Open
     */
    public function open($savepath, $id){
        // If successful
        $this->database->getSelect("SELECT `data` FROM sessions WHERE id = ? LIMIT 1",$id,TRUE);
        if($this->database->selectRowsFoundCounter() == 1){
            // Return True
            return true;
        }
        // Return False
        return false;
    }
    /**
     * Read
     */
    public function read($id)
    {
        // Set query
        $readRow = $this->database->getSelect('SELECT `data` FROM sessions WHERE id = ? LIMIT 1', $id,TRUE);
        if ($this->database->selectRowsFoundCounter() > 0) {
            return $readRow['data'];
        } else {
            return '';
        }
    }

    /**
     * Write
     */
    public function write($id, $data)
    {
        // Create time stamp
        $access = time();

        // Set query
        $dataReplace[0] = $id;
        $dataReplace[1] = $access;
        $dataReplace[2] = $data;
        if ($this->database->noReturnQuery('REPLACE INTO sessions(id,access,`data`) VALUES (?, ?, ?)', $dataReplace)) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * Destroy
     */
    public function destroy($id)
    {
        // Set query
        if ($this->database->noReturnQuery('DELETE FROM sessions WHERE id = ? LIMIT 1', $id)) {
            return true;
        } else {

            return false;
        }
    }
    /**
     * Close
     */
    public function close(){
        // Close the database connection
        if($this->database->dbiLink->close){
            // Return True
            return true;
        }
        // Return False
        return false;
    }

    /**
     * Garbage Collection
     */
    public function gc($max)
    {
        // Calculate what is to be deemed old
        $old = time() - $max;

        if ($this->database->noReturnQuery('DELETE FROM sessions WHERE access < ?', $old)) {
            return true;
        } else {
            return false;
        }
    }

    public function __destruct()
    {
        $this->close();
    }

}

Usage: As shown just above the class code text.