PHP Sessions in Database

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.

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

Is PHP session still used nowadays?

Your first assumption is incorrect. PHP Sessions are not where you store data. Databases, files, Document stores, etc. are where you store your data.

Session "data" is simply the variables included in the $_SESSION array in serialized form. You can run serialize() and unserialize() on variables to gain some insight into what these look like.

In your script, once you have started a session using session_start(), when you add change or delete variables in $_SESSION, php serializes this and stores it for you.

Once a session exists, and a user makes another request that is identified as being the same user (having the same session id) which has typically passed to the client via a cookie, then upon issuing session_start(), PHP reads the serialized data in the session file, and unserializes it, and stores it back into $_SESSION.

By default, PHP will store the individual session data as files on the filesystem. For a small or medium size application, this is highly performant.

So to be clear, what people store in PHP sessions is basically variables read out of whatever other persistent storage you might have, so that you can avoid doing things like re-querying a database to get the name and user_id for a user who has already logged into your application.

It is not the master version of that data, nor the place through which you will update that data should it change. That will be the original database or mongodb collection.

The article you posted has a number of stated and unstated assumptions including:

Devops/Sysadmins just decide to reconfigure PHP applications to change the session handlers (misleading/false)
The deployment involves a load balancer (possibly)
The load balancer doesn't support or use sticky sessions

He then goes on into some detail as to several alternatives that allow for shared session handlers to solve the race conditions he describes

As you stated, you aren't clear yet what sessions actually are, or how they work or what they do for you. The important thing to know about PHP scripts is that they are tied to a single request and sessions are a way of not repeating expensive database reads. It's essentially variable cache for PHP to use (or not) when it suits your design.

At the point you have a cluster, as pointed out in the article people often store data into shared resources which can be a relational database, or any of many other backends which each have different properties that match their goals.

Again, in order to change the session handlers, there is typically code changes being made to implement the session handler functions required, and there are ways to code things that mitigate the issues brought up in the article you posted, for just about every persistence product that people use.

Last but not least, the problems described exist to whatever degree with pretty much any clustered serverside process and are not unique to PHP or its session mechanism.

Fetching data from mySql database for php user session

$rows is counter variable (having count of number of records) there that's why not working.

Do like below:-

....previous code as it is
$result = mysqli_query($con,$query) or die(mysql_error());
$row = mysqli_fetch_assoc($result); //fetch record
$rows = mysqli_num_rows($result);
if($rows==1){
        $_SESSION['email'] = $row['email'];
        header("Location: index.php");
    }...rest code as it is

Note:-

1.Don't use md5 password encryption, use password hashing technique.

2.Use prepared statements of mysqli_* to prevent your code from SQL Injection

php: sessions vs. database

Considering you're storing an ID in the session anyway, the session makes the most sense. Doing a session_start() loads your session information so whether you've loaded 1 or 10 items after that is largely irrelevant (unless they're really large but that'll be a problem in any case).

So stick with the session.

If you get really concerned about speed use an in-memory cache like APC or memcache. Worrying about speed for 10 items from the filesystem or database is a distraction. The difference is going to be so minimal as to be irrelevant.

Note: the above assumes two things:

The query is performant (retrieving 10 rows out of 100k should be doable in under 0.1 seconds); and
You are doing one query not 10.