Taking Control of Your PHP Sessions: Using Database Sessions


Introduction

Recently while working on a project for work, I encountered an interesting issue. I needed to develop a way to add and remove permissions from a User in a system we are building (which will be made open source as a service to the internet community once development is complete), but I needed the ability to push out these permission changes even if a user is currently logged in to the system.

As far as I know PHP does not provide a way to edit User sessions other than the $_SESSION array, which only applies to the current User’s session. After some research, I discovered that it is possible to change PHP’s default session storage method. This can be very useful if you are using a CDN to share server load between multiple servers while still allowing data persistence through the session. Another useful benefit of storing your sessions in the Database is the ability to alter User sessions (for any user) on the fly.

There are a few things to note about editing User Sessions on the fly:

  • If you aren’t careful you can inadvertently edit the wrong session, leading to security issues
  • The way session data is stored in the database is through a single serialized data field. This means that before editing a session variable you must either parse and change only the value you wish to change, or unserialize the entire session data string, edit the individual values, and then re-serialize the data for insert.
  • I have never tried the above method, and therefore cannot provide any code to accomplish this task. If this changes, I will edit this post with the relevant code
  • If you are only storing basic login type data in the session (which would be pulled from the Database), killing a User’s session and allowing them to log back in is the safest way to ensure that any changes to the session will populate correctly

Setting up the Database

The SQL in this tutorial is written to work with MySQL, so if you’re using something else just bear in mind that your syntax may need to be tweaked, especially in the DDL.

Create the Table:

CREATE TABLE SESSION (
id VARCHAR(32) NOT NULL COMMENT 'Stores the Session ID',
access INT(10) UNSIGNED NOT NULL,
data TEXT,
PRIMARY KEY (`id`)
) ENGINE=INNODB
ROW_FORMAT=DEFAULT;

Create a few PHP classes

This first class I built handles the Database connection, and is in two parts: Config.inc.php (for configuring the database variables) and MySQLDatabase.class.php:

Config.inc.php

<?php
define("conf_hostname", "localhost");
define("conf_username", "user");
define("conf_password", "password");
define("conf_schema", "database_to_use");
?>

MySQLDatabase.class.php

<?php
include("Config.inc.php");
class MySQLDatabase{
	private $db;
	private $hostname;
	private $username;
	private $password;
	private $schema;
	
	function __construct() {
		if(func_num_args() == 0){
			$this->hostname = conf_hostname;
			$this->username = conf_username;
			$this->password = conf_password;
			$this->schema = conf_schema;
		}
		else{
			$params = func_get_args();
			$this->hostname = $params[0];
			$this->username = $params[1];
			$this->password = $params[2];
			$this->schema = $params[3];
		}
	}
	
	private function open(){
		$this->db = mysql_connect($this->hostname, $this->username, $this->password) or die ('Error connecting to mysql');
		mysql_select_db($this->schema, $this->db);
	}
	
	public function executeQuery($query){
		$this->open();
		$results = mysql_query($query, $this->db) or die ("Error in query: $query. ".mysql_error());
		return $results;
	}
	
	public function close(){
		mysql_close($this->db);
	}
	
}
?>

Next we need to create a class to handle some session related events:

DatabaseSessionHandler.class.php

<?php
include_once ("MySQLDatabase.class.php");
/**
 *
 */
class DatabaseSessionHandler {
	private $db;

	public function _open($save_path, $session_name) {
		$this -> db = new MySQLDatabase();
		return true;
	}

	public function _close() {
		$this -> db -> close();
	}

	function _read($id) {

		$id = mysql_real_escape_string($id);

		$query = "SELECT data
				FROM SESSION
				WHERE id = '$id'";

		if ($result = $this -> db -> executeQuery($query)) {
			if (mysql_num_rows($result)) {
				$record = mysql_fetch_assoc($result);
				return $record['data'];
			}
		}

		return '';
	}

	function _write($id, $data) {
		$access = time();

		$id = mysql_real_escape_string($id);
		$access = mysql_real_escape_string($access);
		$data = mysql_real_escape_string($data);

		$query = "REPLACE
				INTO SESSION
				VALUES ('$id', '$access', '$data')";

		return $this -> db -> executeQuery($query);
	}

	function _destroy($id) {

		$id = mysql_real_escape_string($id);

		$query = "DELETE
				FROM SESSION
				WHERE id = '$id'";

		return $this -> db -> executeQuery($query);
	}

	function _clean($max) {
		$old = time() - $max;
		$old = mysql_real_escape_string($old);

		$query = "DELETE
				FROM SESSION
				WHERE access < '$old'";

		return $this -> db -> executeQuery($query);
	}
	
	public function killUserSession($username){
		$query = "delete from SESSION where data like('%userID|s:%\"". mysql_real_escape_string($username) ."%\";first_name|s:%')";
		$this->db->executeQuery($query);
	}

}
?>

Replacing PHP’s Session with our Database Session

Now all we have left to do is actually utilize our new session handling. This is actually a relatively painless process. In my application the session is started in the header include file for each page. In this case, all I had to do was change:

<?php
     session_start();
?>

to:

<?php
$sess = new DatabaseSessionHandler();
session_set_save_handler(array(&$sess, '_open'),
                         array(&$sess, '_close'),
                         array(&$sess, '_read'),
                         array(&$sess, '_write'),
                         array(&$sess, '_destroy'),
                         array(&$sess, '_clean'));
session_start();
?>

From here on out, the session can be handled in code exactly like a PHP session, but now you also have the ability to edit User sessions on the fly!

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s