Require opinion on using Session set save handler for php db sessions

[pkrish]

I am using php session set save handler for my application for database backed sessions. Every request (including the ajax calls) in the application calls the custom handler which I have developed and so there are a lot of SQL read and writes to the database. I have also noticed lot of issues with locking i.e. 1205 and 1213 error codes. Should I be using PHP memcached? Is there a good example that anyone can point me to if so. Below is a code sample that I am using:

 

 

<?php




class SessionHandler {

    private static $_sess_db;
    private static $_instance;


    private function __construct() {
        // do nothing
    }

    /**
     * Function to get the instance of SessionHandler
     */
    public static function get_instance() {
        if (is_null(self::$_instance)) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }

    /**
     * Function used by the instance to invoke the handler
     */
    public function call_handler() {
        session_set_save_handler(
        array($this, "db_open"),
        array($this, "db_close"),
        array($this, "db_read"),
        array($this, "db_write"),
        array($this, "db_destroy"),
        array($this, "db_gc")
        );
    }

    /**
     * Function to set up a database connection using the parameters from conf.php
     * Setting autocommit to false and call commit/rollback down the line. This returns
     * true if the db connection is successful or false if there is a connection error
     */
    public function db_open() {
        $this->_sess_db = new mysqli("somehost", "user", ""test, 'test_schema');
        if (mysqli_connect_errno()) {
              return false;
        } else {
            // Setting autocommit false before any db operations, to allow for session locking
            $this->_sess_db->autocommit(false);
            return true;
        }
    }

    /**
     * Function to close db connection
     */
    public function sess_close() {
        $this->_sess_db->close();
    }

    /**
     * Empty destructor
     */
    public function __destruct() {
        //no-op
    }

    /**
     * Function to call commit explicity on a sql query
     */
    private function commit() {
        $result = $this->_sess_db->commit();
        
        if (!$result) {
            return false;
        } else {
            return true;
        }
    }

    /**
     * Function to call rollback explicity when a sql query fails
     */
    private function rollback() {
        $rollback_result = $this->_sess_db->rollback();
    }

    /**
     * Function to read session data for the respective session id. Locking
     * the row returning the session data in the SELECT query as the session
     * data is updated by various ajax calls using index.php. This returns
     * the session data as a string to be used by the sess_write function
     */
    public function db_read($session_id) {
    	 
        $session_data = '';
        $read_sql = "select session_data from db_sessions where session_id = ? for update";
        if ($stmt = $this->_sess_db->prepare($read_sql)) {
            $stmt->bind_param("s", $session_id);
            if (!$stmt->execute()) {
                $stmt->close();
            } else {
                $stmt->bind_result($session_data);
                $stmt->fetch();
                $stmt->close();
            }
        } else {
            // If there is a SQL Error/Exception, exit gracefully with the session data
             
        }
        if ($session_data == '') {
            $this->logToFile('No session data');
        }
        return $session_data;
    }

    /**
     * Function to insert/update session data for the session id with the current time. This
     * returns true if the insert/update was successful or false when there is a failure with
     * the insert/update query.
     */
    public function db_write($session_id, $session_data) {
    	 
               $write_sql = "insert into db_sessions (session_id, last_updated, session_data) values(?, now(), ?) on duplicate key update last_updated = now(), session_data = ?";
        if ($stmt = $this->_sess_db->prepare($write_sql)) {
            $stmt->bind_param("sss", $session_id, $session_data, $session_data);
            if (!$stmt->execute()) {
                $stmt->close();
                $this->rollback();
                return false;
            } else {
                $stmt->close();
                return $this->commit();
            }
        } else {
            $this->rollback();
            return false;
        }
    }

    /**
     * Function to delete the session data for the session id when the user logs out.
     * This returns true or false if the delete is successful or not respectively
     */
    public function db_destroy($session_id) {
        $delete_session_sql = "delete from db_sessions where session_id = ?";
        if ($stmt = $this->_sess_db->prepare($delete_session_sql)) {
            $stmt->bind_param("s", $session_id);
            if (!$stmt->execute()) 
                $stmt->close();
                $this->rollback();
                return false;
            } else {
                $stmt->close();
                return $this->commit();
            }
        } else {
            $this->rollback();
            return false;
        }
    }


    public function db_gc() {
        $sessions_expire_time = time() - ini_get(MAX_SESSION_LIFETIME);
        $gc_query = "delete from db_sessions where last_updated < FROM_UNIXTIME(?)";
        if ($stmt = $this->_sess_db->prepare($gc_query)) {
            $stmt->bind_param("i", $sessions_expire_time);
            if (!$stmt->execute()) {
               
                $stmt->close();
                $this->rollback();
                return false;
            } else {
                $stmt->close();
                return $this->commit();
            }
        } else {
            
            $this->rollback();
            return false;
        }
    }
}
?>

 

Mod edit: Added code tags.

[jawef]

You didn’t specify the reason that made you use this approach. Are you “sharing” sessions between 2 (or more) domains? If so there are many ways depending on what you are doing. One of those is making a secure web service, make the application in the domain passing the session id to act as client and request from the provider (the application domain from where the user came) to get the session data.

[pkrish]

You didn’t specify the reason that made you use this approach. Are you “sharing” sessions between 2 (or more) domains? If so there are many ways depending on what you are doing. One of those is making a secure web service, make the application in the domain passing the session id to act as client and request from the provider (the application domain from where the user came) to get the session data.

 

 

I am using this as we are using multiple web servers and default php sessions in files would cause issues.