PHP MYSQL based session handler with locking using select for update

mysql-session.php

<?php

$sessionHandler = new \com\indigloo\core\MySQLSession();
session_set_save_handler(array($sessionHandler,"open"),
                            array($sessionHandler,"close"),
                            array($sessionHandler,"read"),
                            array($sessionHandler,"write"),
                            array($sessionHandler,"destroy"),
                            array($sessionHandler,"gc"));

ini_set('session_use_cookies',1);
//Defaults to 1 (enabled) since PHP 5.3.0
//no passing of sessionID in URL
ini_set('session.use_only_cookies',1);
// the following prevents unexpected effects 
// when using objects as save handlers
// @see http://php.net/manual/en/function.session-set-save-handler.php 
register_shutdown_function('session_write_close');
session_start();

?>

MySQLSession.php

<?php

namespace com\indigloo\core {
    
    use \com\indigloo\Configuration as Config;
    use \com\indigloo\mysql\PDOWrapper;
    use \com\indigloo\Logger as Logger;

    /*
     * custom session handler to store PHP session data into mysql DB
     * we use a -select for update- row level lock 
     *
     */
    class MySQLSession {

        private $dbh ;

        function __construct() {

        }
        
        function open($path,$name) {
            $this->dbh = PDOWrapper::getHandle();
            return TRUE ;
        }

        function close() {
            $this->dbh = null;
            return TRUE ;
        }

        function read($sessionId) {
            //start Tx
            $this->dbh->beginTransaction(); 
            $sql = " select data from sc_php_session where session_id = :session_id  for update ";
            $stmt = $this->dbh->prepare($sql);
            $stmt->bindParam(":session_id",$sessionId, \PDO::PARAM_STR);
            $stmt->execute();
            $result = $stmt->fetch(\PDO::FETCH_ASSOC);
            $data = '' ;
            if($result) {
                $data = $result['data'];
            }

            return $data ;
        }

        function write($sessionId,$data) {

            $sql = " select count(session_id) as total from sc_php_session where session_id = :session_id" ;
            $stmt = $this->dbh->prepare($sql);
            $stmt->bindParam(":session_id",$sessionId, \PDO::PARAM_STR);
            $stmt->execute();
            $result = $stmt->fetch(\PDO::FETCH_ASSOC);
            $total = $result['total'];

            if($total > 0) {
                //existing session
                $sql2 = " update sc_php_session set data = :data, updated_on = now() where session_id = :session_id" ;
            } else {
                $sql2 = "insert INTO sc_php_session(session_id,data,updated_on) VALUES(:session_id, :data, now())" ;
            }
            
            $stmt2 = $this->dbh->prepare($sql2);
            $stmt2->bindParam(":session_id",$sessionId, \PDO::PARAM_STR);
            $stmt2->bindParam(":data",$data, \PDO::PARAM_STR);
            $stmt2->execute();

            //end Tx
            $this->dbh->commit(); 
        }
        
        /*
         * destroy is called via session_destroy
         * However it is better to clear the stale sessions via a CRON script
         */

        function destroy($sessionId) {
            $sql = "DELETE FROM sc_php_session WHERE session_id = :session_id ";
            $stmt = $this->dbh->prepare($sql);
            $stmt->bindParam(":session_id",$sessionId, \PDO::PARAM_STR);
            $stmt->execute();

        }

        /* 
         * @param $age - number in seconds set by session.gc_maxlifetime value
         * default is 1440 or 24 mins.
         *
         */
        function gc($age) {
            $sql = "DELETE FROM sc_php_session WHERE updated_on < (now() - INTERVAL :age SECOND) ";
            $stmt = $this->dbh->prepare($sql);
            $stmt->bindParam(":age",$age, \PDO::PARAM_INT);
            $stmt->execute();
        }

    }
}
?>

PDOWrapper.java

<?php
namespace com\indigloo\mysql{

    use \com\indigloo\Configuration as Config ;
    
    class PDOWrapper {

        static function getHandle() {

            $host = Config::getInstance()->get_value("mysql.host");
            $dbname = Config::getInstance()->get_value("mysql.database");
            $dsn = sprintf("mysql:host=%s;dbname=%s",$host,$dbname);

            $user = Config::getInstance()->get_value("mysql.user");
            $password = Config::getInstance()->get_value("mysql.password");
            $dbh = new \PDO($dsn, $user, $password);

            //throw exceptions
            $dbh->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
            return $dbh ;
        }
    }


}
?>

remove-stale-sessions.php

    function remove_stale_sessions(){
        //clean sessions inactive for half an hour
        $mysql_session = new \com\indigloo\core\MySQLSession();
        $mysql_session->open(null,null);
        //30 minutes * 60 seconds
        $mysql_session->gc(1800);
        $mysql_session->close();
    }

The internet is full of examples of how to do a mysql bases php sesison save handler. Most of them very conveniently ignore the need to implement locking to simulate correct default PHP session handler behavior. So this is my attempt to create a PDO based MYSQL session backend for PHP code. This code implements locking. You can call the remove_stale_sessions() functions via cron scripts.

Hello, i don't understant how you implement lock feature in your script.

In my mind race condition happen when 2 script modify the same session variable simultaneously or nearly simultaneously. If it true then all we need to di is to look that row when read the session data and unlock the row after we write the data. That is TRUE?

Where is \com\indigloo\Configuration and other classes?

Hello, i don't understant how you implement lock feature in your script.

In my mind race condition happen when 2 script modify the same session variable simultaneously or nearly simultaneously. If it true then all we need to di is to look that row when read the session data and unlock the row after we write the data. That is TRUE?

We are using select for update