Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PHP Mysql session handling class using PDO. Includes row locking on reads to prevent ajax race condition issues. Needs php >= 5.4.
<?php
/**
* Mysql DB Session class with PDO
*
* Uses row locking to handle concurrent ajax access
*
* Requires a PDO db connection to be handed to the session constructor.
*
* Table:
*
* CREATE TABLE IF NOT EXISTS `sessions` (
* `ses_id` varchar(32) NOT NULL DEFAULT '',
* `ses_time` int(11) NOT NULL DEFAULT '0',
* `ses_start` int(11) NOT NULL DEFAULT '0',
* `ses_value` mediumtext NOT NULL,
* PRIMARY KEY (`ses_id`)
* ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
*
* Good session settings:
* ini_set('session.cookie_httponly', 1);
* ini_set('session.use_strict_mode', 1);
* ini_set('session.gc_probability', 0);
* ini_set('session.use_only_cookies', 1);
* ini_set('session.use_trans_sid', 0);
*/
class PDO_Session {
private $sess_table;// = SESSION_TABLE;
private $dbh;
private $lock_timeout;
private $session_lock;
public function __construct(PDO $dbh, $sess_table, $lock_timeout = 60){
$this->dbh = $dbh;
$this->sess_table = $sess_table;
// the maximum amount of time (in seconds) for which a process can lock the session
$this->lock_timeout = $lock_timeout;
session_set_save_handler([$this, '_open'],
[$this, '_close'],
[$this, '_read'],
[$this, '_write'],
[$this, '_destroy'],
[$this, '_gc'],
[$this, '_create_sid']);
//register shutdown - stops oddness with objects as the handler
register_shutdown_function('session_write_close');
}
/**
* Open session. Always succeeds.
*/
public function _open($path, $name) {
return true;
}
/**
* Close session. Releases corresponding session lock.
*/
public function _close() {
try{
$lock_sql = sprintf("SELECT RELEASE_LOCK(%s) AS c",
$this->session_lock); //(already quoted)
$sth = $this->dbh->query($lock_sql);
$row = $sth->fetch(PDO::FETCH_ASSOC);
if(!array_key_exists('c', $row) || $row['c'] != 1){
throw new PDOException("Could not release session lock on ". $this->session_lock);
}
}catch(PDOException $e){
return false;
}
return true;
}
/**
* Read session data corresponding to $ses_id from db. Gets an exclusive
* lock on the row corresponding to the session id.
*/
public function _read($ses_id) {
try{
// get the lock name, associated with the current session
$this->session_lock = $this->dbh->quote('session_' . $ses_id);
// try to obtain a lock with the given name and timeout
$lock_sql = sprintf("SELECT GET_LOCK(%s, %u) AS c",
$this->session_lock,
$this->lock_timeout);
$sth = $this->dbh->query($lock_sql);
$row = $sth->fetch(PDO::FETCH_ASSOC);
if(!array_key_exists('c', $row) || $row['c'] != 1){
throw new PDOException("Could not obtain session lock on ". $this->session_lock);
}
$session_sql = sprintf("SELECT * FROM `%s` WHERE ses_id = %s LIMIT 1",
$this->sess_table, $this->dbh->quote($ses_id));
$stmt = $this->dbh->query($session_sql);
}catch(PDOException $e){
return '';
}
if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$ses_data = $row["ses_value"];
return $ses_data;
} else {
return '';
}
}
/**
* Write new session data for key $ses_id to db
*/
public function _write($ses_id, $data) {
$time = time();
try{
$session_sql = sprintf('INSERT INTO `%1$s` (ses_id, ses_time, ses_start, ses_value)'
. ' VALUES (%2$s, %3$u, %3$u, %4$s)'
. ' ON DUPLICATE KEY UPDATE ses_time = %3$u, ses_value = %4$s',
$this->sess_table,
$this->dbh->quote($ses_id),
$time,
$this->dbh->quote($data)
);
$res = $this->dbh->exec($session_sql);
}catch(PDOException $e){
return false;
}
if ($res > 0) {
return true;
}
return false;
}
/**
* Destroy session record for key $ses_id in db
*/
public function _destroy($ses_id) {
try{
$session_sql = sprintf("DELETE FROM `%s` WHERE ses_id = %s",
$this->sess_table, $this->dbh->quote($ses_id));
$res = $this->dbh->exec($session_sql);
}catch(PDOException $e){
return false;
}
return true;
}
/**
* Garbage collection - deletes old time-expired sessions ($life in sec)
*/
public function _gc($life) {
$ses_life = time() - intval($life);
try{
$session_sql = sprintf("DELETE FROM `%s` WHERE ses_time < %u",
$this->sess_table, $ses_life);
$res = $this->dbh->exec($session_sql);
}catch(PDOException $e){
return false;
}
return true;
}
/**
* Create new session ID using better random name gen than standard.
*/
public function _create_sid(){
return bin2hex(openssl_random_pseudo_bytes(16));
}
} //eoc
/* end */
@nmcgann

This comment has been minimized.

Copy link
Owner Author

nmcgann commented Feb 10, 2017

Fixed quoting bug. This works fine on shared hosting now and has solved issues with random session expiration when getting booted out of a system memcache-based session handler.

@nmcgann

This comment has been minimized.

Copy link
Owner Author

nmcgann commented Feb 10, 2017

The shared cloud hosting I was using this on had session_gc() disabled so a cron job was needed to garbage collect.

(Ubuntu does this too, gc is run by the system and not randomly by PHP)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.