How to Save PHP Session Data to a Database Instead of in the File System

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

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.

Storing user session variables in file vs in database

At some point in time you're going to have to store something in a session. Whether it's all the session variables or just the ID of a row in a sessions table. That being the case it would be fairly easy to alter the ID stored in a badly encrypted session and hijack a different session.

Consider this:

Full Session Option. This has the User ID, Username and an encrypted and hashed password stored so that every time a page is called it verifies my login. To hijack someone else's session I'd have to know their User ID, Username and Password Hash and be able to overcome the sessions inherent encryption.

Session + DB Option. This just has a Session ID stored that references a row in a database. All I have to do to change the session I want is to break the encryption on the session and say add one to the Session ID. I'd then be authenticated as the user that logged in after me.

You could store login details in a session and then any none login related data in a session table if you have a lot of extra information but then again you might as well just remove the need for an extra table and extract the data from whatever relevant tables you need.

set session in database in php

You would need to create an object like so:

class SessionHandler 
{ 
    private static $lifetime = 0; 

    private function __construct() //object constructor
    { 
       session_set_save_handler(
           array($this,'open'),
           array($this,'close'),
           array($this,'read'),
           array($this,'write'),
           array($this,'destroy'),
           array($this,'gc')
       );
    }

   public function start($session_name = null)
   {
       session_start($session_name); //Start it here
   }

    public static function open()
    {
        //Connect to mysql, if already connected, check the connection state here.

        return true;
    }

    public static function read($id)
    {
        //Get data from DB with id = $id;
    }

    public static function write($id, $data)
    {
        //insert data to DB, take note of serialize
    }

    public static function destroy($id)
    {
       //MySql delete sessions where ID = $id
    }

    public static function gc()
    {
        return true;
    }
    public static function close()
    {
        return true;
    }
    public function __destruct()
    {
        session_write_close();
    }
}

Then before session_start initiate this class!

include 'classes/sessionHandlerDB.php';

$session = new SessionHandler();

$session->start('userbase');

$_SESSION['name'] = 'Robert Pitt'; //This is sent to SessionHandler::write('my_id','Robert Pitt')
echo $_SESSION['name']; //This calls SessionHandler::read($id)//$id is Unique Identifier for that

http://php.net/manual/en/function.session-set-save-handler.php

http://php.net/manual/en/function.serialize.php

Why is it good save to save sessions in the database?

It doesn't improve security in any way.

The most common and reasonable pattern to store sessions in database is when you have several frontend servers, so you need a shared session storage for them.

For downvoters: a file in filesystem isn't less secured than a record in database.

When using MySQL to store session data, is it faster to use db queries instead of session?

I'm accepting this comment (pasted as an answer) as the answer (credit to IncredibleHat):

---

If you only have a few thousand users, you won't notice much of a difference in use. However if you are talking millions of users, then the constant get/store of the session data to a db table could start to show (by like an extra half second depending on your server). – IncredibleHat

Store in Session Data vs store in Sql Database for temporary data

Keep in mind the session variable is backed by a storage mechanism, that is, when the request finishes the session gets written by the session handler, by default this is to a file. On the next request it is pulled back from that file (or whatever else the session handler uses).

If you're reading and writing this data on every request, just stick with a the $_SESSION variables, the overhead of connecting, querying and updating a database will not be faster than the default $_SESSION.

You'll probably only ever want to use a database backed session if you are running multiple load-balanced servers and need to share the session data between them. In this case, if you find the overhead of the database sessions to be slowing down your site to a noticeable degree you might consider sticking memcached between your web server and the database.

Converting file sessions to database-based sessions

You should look at set_session_save_handler. It allows you to specify how the session handler can retrieve and store it's data. It's fairly straightforward to write an implementation that uses a database.

With this approach you can continue to use $_SESSION like you normally would.

MEMORY and HEAP is the same thing (in MySQL). You can chose to use a MEMORY table since sessions are allowed to be volatile. I don't know much about the performance characteristics for this purpose though. I'm guessing that if your session table is small enough it will be mostly in memory anyway.

Easy way to have PHP use MySQL for sessions rather than flat file?

If you want to change the way sessions are stored without changing all php files, you might be able to achieve that by playing with the php.ini session.save_handler (Even though I would rather do it by code using session_set_save_handler...). For scalability, I would recommend using Memcached rather than mysql though.
Ie:

session.save_handler = memcached
session.save_path = "127.0.0.1:11211"

If you are sharing the sessions between servers obviously change the IP from local to something else.

---

Related Topics