Page 1 of 1

Session handling with SQLite

Posted: Sat Jul 01, 2006 9:38 pm
by 0x42.0x4c
I've been reading up on changing the default session handling method, usually to log sessions to MySQL vs the filesystem for the sake of security on a shared server. Has anyone done or heard of doing this with SQLite? Seems to me this would be more effective, both over using MySQL or another database, and over changing the session.save_path directive to just use a local directory. I'm curious as to what performance gains there might be vs MySQL access. I'm interested in building the class for this, I just thought I'd ask around before I reinvent the wheel (or make a huge mistake).

Posted: Sun Jul 02, 2006 12:09 am
by RobertGonzalez
Isn't SQL Lite flat file based? Regardless, essentially the same logic would be used for whatever database engine you are using to store your session data. Since you are not using PHP's built-in session array or session handling features, you would do anything to the session settings in php.ini, you would handle your sessions code-side.

Re: Session handling with SQLite

Posted: Sun Jul 02, 2006 7:19 pm
by santosj
0x42.0x4c wrote:I've been reading up on changing the default session handling method, usually to log sessions to MySQL vs the filesystem for the sake of security on a shared server. Has anyone done or heard of doing this with SQLite? Seems to me this would be more effective, both over using MySQL or another database, and over changing the session.save_path directive to just use a local directory. I'm curious as to what performance gains there might be vs MySQL access. I'm interested in building the class for this, I just thought I'd ask around before I reinvent the wheel (or make a huge mistake).
Well, it is explained on the manual page that if you add '.db' then it would use SQLite, if you have it installed and are using PHP 5 or 5.1. You don't have to set the php.ini. You can do in PHP. You also don't need to use the session_set_handler() either.

The security advantages are better since it is a database, instead of many flat files. Um, you'll still want it below your public web directory, because anyone could use SQLite to access it and none of the values are encrypted so, yeah. I'm not sure about the performance, my guess is that it would preform faster.

Posted: Sun Jul 02, 2006 9:20 pm
by 0x42.0x4c
Well, thanks for the replies. I worked on it a bit and think I've got my answer. Unless you just want to specifically use SQLite for this, there seems to be no practical advantage over the basic PHP session handling, especially when it comes to storage space. SQLite appears to use roughly twice the disk space storing the same amount of session data. The one nice thing is having all your session data in the same place, and as I mention below, you can potentially secure the session data by 'security through obscurity' in your /home directory.

Here's the class I built and tested, if anyone's interested. Should run straight out of the box with PHP 5 using the standard built-in SQLite 2.x extension.

Code: Select all

<?php
// SQLite_SessionHandler.php
/*
    20060702
    PHP > V5, w/ built-in SQLite extension
    basic implementation of an alternative session handler using SQLite
    loosely based on Chris Shiflett's blog entry:
    http://shiflett.org/articles/guru-speak-jan2005
*/

class SQLite_SessionHandler
{
    private $sid;                // Session ID
    private $SQLiteDb;      // SQLite database file
    private $DBH;             // SQLite DB Handler
    private $sqlite_err;     // any error messages returned from SQLite

    public function __construct ( $dbfile, Array $o= array() ) {

        // set database file
        $this->SQLiteDb= $dbfile;

        // override PHP's session handler
        session_set_save_handler(array($this, '_open'),
                                 array($this, '_close'),
                                 array($this, '_read'),
                                 array($this, '_write'),
                                 array($this, '_destroy'),
                                 array($this, '_clean')
                                 );

        // application specific
        // if the options array contains a specific session id, set it
        if ( isset($o['session_id']) && !empty($o['session_id']) ) {
            session_id($o['session_id']);
        } else {
            // start the session with a new session ID
            session_start();
        }


        // application specific
        // options array - regenerate = TRUE, regenerate the ID
        if ( isset($o['regenerate']) && $o['regenerate'] === TRUE){
            $d=(isset($o['delete']) && !empty($o['delete']))?$o['delete']:FALSE;
            session_regenerate_id($d);
         }

        // set the session ID (not implemented yet)
        $this->sid= session_id();

        register_shutdown_function('session_write_close');

    }


    public function _open() {
        // open the SQLite database, set permissions to 0600
        $this->DBH= sqlite_open($this->SQLiteDb, 0600, $this->sqlite_err);
        // chmod to 0600 to keep prying eyes out
        @chmod($this->SQLiteDb, 0600);
        // workaround for SQLite < v3
        // apparently v2 doesn't allow IF NOT EXISTS constraint for some reason
        $test= 'SELECT SID FROM session';
        // if the table doesn't exist, in other words, CREATE it
        if ( @sqlite_exec($this->DBH, $test, $this->sqlite_err) === FALSE ) {
            $create=  'CREATE TABLE session ';
            $create.= '(ID INTEGER PRIMARY KEY,SID VARCHAR(32) UNIQUE,';
            $create.= 'data TEXT, access INTEGER(10))';
            sqlite_exec($this->DBH, $create, $this->sqlite_err);
        }
        return TRUE;
    }

    public function _close() {
        // close the handler
        if ( is_resource($this->DBH) ) {
            sqlite_close($this->DBH);
        }
        return TRUE;
    }

    public function _read($id) {
        // read session data, very simple implementation
        $sql= "SELECT data FROM session WHERE SID='{$id}'";
        $data= sqlite_single_query($this->DBH, $sql, TRUE);
        if ( !empty($data) ) {
            return $data;
        }
        return '';
    }

    public function _write($id, $data) {
        // set the current time
        $time= time();
        // REPLACE INTO deletes the current session data if necessary first
        $sql=  'REPLACE INTO session (SID,data,access) VALUES';
        $sql.= " ('{$id}','{$data}',{$time})";

        return sqlite_exec($this->DBH, $sql, $this->sqlite_err);

    }

    public function _destroy($id) {
        // delete session data
        $sql= "DELETE FROM session WHERE SID = '{$id}'";
        return sqlite_exec($this->DBH, $sql, $this->sqlite_err);
    }

    public function _clean($max) {
        // clean based on PHP's session.gc_maxlifetime value
        $old_sessions= (int) (time() - $max);
        $sql= "DELETE FROM session WHERE access < {$old_sessions}";

        return sqlite_exec($this->DBH, $sql, $this->sqlite_err);

    }

    // SQLite error accessor method
    public function getError() {
        return $this->sqlite_err;
    }

    // SID accessor method
    public function getSID() {
        return $this->sid;
    }
}
?>

Here's a basic implementation. Note I use the /tmp directory to store the database file, as the HTTPD/PHP user has to have access to write to the specific directory. I have also tested this as in the home directory, as in '/home/someuser/SessionData/sqlite.db', but the directory permissions have to be 0757 for the HTTPD/PHP user to access it. Pros: obscurity of the session database, outside of prying eyes. Cons: It's world readable / writeable. Either way, the sqlite.db file is tucked away in your /home dir, or non read/write. It might be wise to come up with a method to use a random string as a directory / database / table name for the handler to use.

Code: Select all

<?php
require_once 'SQLite_SessionHandler.php';

// the database file you want to use / initiate
$db= '/tmp/sqlite.db';

// options, if any needed (they're optional, no pun intended)
$options= array('session_id' => NULL, 'regenerate' => FALSE, 'delete' => FALSE);

// instantiate
$Session= new SQLite_SessionHandler($db, $options);

// store some session data
$_SESSION['uname'] = 'Current User';
$_SESSION['identifier'] = sha1('SecretSALT'.$_SERVER['HTTP_USER_AGENT']);

?>
[ edit ] rephrase, minor bug in code [ /edit ]