MySql based session mangagement in PHP

By Andreas Wilhelm Apr 10, 2013

Today I would like to show you how to implement and use a class that allows you to store session data within an arbitrary database instead of storing the data in files. At the end of this tutorial I will show you how to use this class in practice.

Session management

If we are talking about a session, we always talk about an established connection between a client and a host. Because users could not be unequivocally identified within the world wide web, since it is based on stateless protocols such as http, it is necessary to implement such an identification within the application layer (OSI model). PHP uses therfore the so called session id, which is transfered from the client to the server. This id allows the unambiguous allocation of fragmented session data to one user.

Session data are stored at the server side and are deleted automatically after a fixed delay!

Typical examples for sessions are systems, which handle information that have to remain state between otherwise stateless transactions between different web pages. Typical applications which require this behavior are forums or online shops.

Let’s stay with the example of an online shop. Every online shop offers a cart in which the products are placed in, that should be ordered. This articles and the number of products, that the customer will ultimately source, have to remain state between different web page calls and have to be unambiguous allocated to the correct user.

Some of you may have noticed that in many web shops a user does not have to be authenticated to use the cart, which means that a session could also be established without prior authentication. This also allows us to track the user’s movements when he visits our web page.

Sessions and PHP

I am sure that most of you already dealt with sessions, but I would like to mention, that there are a few things you should be aware of. The PHP manual points to the fact, that if session.auto_start is activated in the php.ini, objects could just be handled within a session if the class definition is loaded using auto_prepend_file or the object is serialized. In this context it must be remembered that some data types cannot be serialized. These include resource variables or objects with ring references. Furthermore it should be noted that, if a session was started using session_start(), the corresponding dataset is only then created when the variable was registered using session_register().

<?php
	// Start the session ...
	session_start();

	// ... and assign some values. (The session will be created if not exists).
	$_SESSION['name'] = 'bornageek';
	$_SESSION['time'] = time();

	// Get a session value ...
	echo $_SESSION['name'] . 'logged in at ' . date('d.m.Y H:i:s', $_SESSION['time']);

	// ... and finally destroy the session.
	session_destroy();
?>

Storing session data within a database

To store session data within a database, we will implement a short class. This class will overwrite the default behaviour of the PHP filesystem based session functions. To do this we could use the function session_set_save_handler(). The PHP manual already offers a great example on how to use this function, but it does not show how to use it to access class methods instead of functions.

It should be noted that the use of session_set_save_handler() enables us to change the default behavior of the PHP session functions, which enables us to store the session data wherever we want without changing the code that actually manages the session. The only thing you have to do is to instantiate the session class before starting the session.

Storing the session data within a database offers several advantages. First of all you could access the session data within a multiuser system directly, which enables you to setup a list of users currently logged in. Furthermore I believe that this way of handling session data is more secure than the filesystem based variant, because it is much harder to access the data, if you could not just open it with an editor. However there may be cases in which it is more comfortable or reasonable to store the data in files. To be flexible in planning it makes sense to implement a simple interface that could be used to implement more types of storing session data. Such a interface shall be as follows:

interface SessionManager {
     public function _open($save_path, $session_name);
     public function _close();
     public function _read($id);
     public function _write($id, $sess_data);
     public function _destroy($id);
     public function _gc($maxlifetime);
}

You are now able to implement different session handlers based on this interface. This allows you to easily switch between a filesystem and a mysql based session management. If you check out the interface you could see that there are six methods you have to implement, which will allow you to open, read, write, delete and close the underlaying data structure in order to manage the session data. In order to use these methods to store the session data within a database, you have to pass a database object (in this case a PHP Data Object PDO) to the class. Furthermore you have to tell the system to use your implementation instead of the standard one. As already mentioned this could be achieved using the function session_set_save_handler(), which will map the calls to the standard session functions to your own ones. In order to work with this implementation you also have to setup a new session within the constructor using session_start(). Finally you have to ensure that all data of a session is saved. Therefore you have to register a callback function, which should be invoked if the session terminates, using register_shutdown_function(). This allows us to call a method of our session handler class that saves all data of the current session. To implement all the other methods of the class you just need basic sql knowledge, thus I will not cover them here. However the methods are well documented and there are more hints at the manual page of session_set_save_handler(), so it should be very easy to understand the following code.

<?php

/**
 * The MySqlSession class implements all methods to use a
 * database based session management instead of using text files.
 * This has the benefit that all session data can be accessed
 * at a central place. This class is supported since the PHP version 5.0.5
 * because it uses the register_shutdown_function() function to ensure that
 * all session values are stored before the PHP representation is destroyed.
 *
 * @package SessionManager
 * @subpackage MySqlSession
 * @version 1.0
 * @date 09/04/2013
 * @author Andreas Wilhelm <info@bornageek.com>
 * @copyright Andreas Wilhelm
 * @link http://www.bornageek.com
 */
class MySqlSession implements SessionManager {
    /**
     * @var pdo $pdo The PDO object used to access the database
     * @access private
     */
    private $pdo = null;</info@bornageek.com>

    /**
     * Sets the user-level session storage functions which are used
     * for storing and retrieving data associated with a session.
     *
     * @access public
     * @param pdo $pdo The PDO object used to access the database
     * @return void
     */
    public function __construct(pdo $pdo) {
        // Assign the pdo object, ...
        $this->pdo = $pdo;

		    // ... change the ini configuration, ...
        ini_set('session.save_handler', 'user');

        // ... set the session handler to the class methods ...
        session_set_save_handler(
            array(&$this, '_open'),
            array(&$this, '_close'),
            array(&$this, '_read'),
            array(&$this, '_write'),
            array(&$this, '_destroy'),
            array(&$this, '_gc')
        );

        // ... and start a new session.
        session_start();

        // Finally ensure that the session values are stored.
        register_shutdown_function('session_write_close');
    }

    /**
     * Is called to open a session. The method
     * does nothing because we do not want to write
     * into a file so we don't need to open one.
     *
     * @access public
     * @param String $save_path The save path
     * @param String $session_name The name of the session
     * @return Boolean
     */
    public function _open($save_path, $session_name) {
        return true;
    }

    /**
     * Is called when the reading in a session is
     * completed. The method calls the garbage collector.
     *
     * @access public
     * @return Boolean
     */
    public function _close() {
        $this->_gc(100);
        return true;
    }

    /**
     * Is called to read data from a session.
     *
     * @access public
     * @access Integer $id The id of the current session
     * @return Mixed
     */
    public function _read($id) {
        // Create a query to get the session data, ...
        $select = "SELECT
                *
            FROM
                `sessions`
            WHERE
                `sessions`.`id` = :id
            LIMIT 1;";

		    // ... prepare the statement, ...
		    $selectStmt = $pdo->prepare($select);

		    // ... bind the id parameter to the statement ...
		    $selectStmt->bindParam(':id', $id, PDO::PARAM_INT);

		    // ... and try to execute the query.
		    if($selectStmt->execute()) {
			    // Fetch the result as associative array ...
			    $result = $selectStmt->fetch(PDO::FETCH_ASSOC);

			    // ... and validate it.
			    if( !$result ) {
				    throw new Exception("MySqlSession: MySQL error while performing query.");
			    }

			    return $result["value"];
        }

        return '';
    }

    /**
     * Writes data into a session rather
     * into the session record in the database.
     *
     * @access public
     * @access Integer $id The id of the current session
     * @access String $sess_data The data of the session
     * @return Boolean
     */
    public function _write($id, $sess_data) {
        // Validate the given data.
        if( $sess_data == null ) {
            return true;
        }

        // Setup the query to update a session, ...
        $update = "UPDATE
                `sessions`
            SET
                `sessions`.`last_updated` = :time,
                `sessions`.`value` = :data
            WHERE
                `sessions`.`id` = :id;";

		// ... prepare the statement, ...
		$updateStmt = $pdo->prepare($update);

		// ... bind the parameters to the statement ...
		$updateStmt->bindParam(':time', time(), PDO::PARAM_INT);
		$updateStmt->bindParam(':data', $sess_data, PDO::PARAM_STR);
		$updateStmt->bindParam(':id', $id, PDO::PARAM_INT);

		// ... and try to execute the query.
		if($updateStmt->execute()) {
			// Check if any data set was updated.
			if($updateStmt->rowCount() > 0) {
				return true;
			} else {
				// The session does not exists create a new one, ...
				$insert = "INSERT INTO
						`sessions`
						(id, last_updated, start, value)
					VALUES
						(:id, :time, :time, :data);";

				// ... prepare the statement, ...
				$insertStmt = $pdo->prepare($insert);

				// ... bind the parameters to the statement ...
				$insertStmt->bindParam(':time', time(), PDO::PARAM_INT);
				$insertStmt->bindParam(':data', $sess_data, PDO::PARAM_STR);
				$insertStmt->bindParam(':id', $id, PDO::PARAM_INT);

				// .. and finally execute it.
				return $insertStmt->execute();
			}
		}

		return false;
    }

    /**
     * Ends a session and deletes it.
     *
     * @access public
     * @access Integer $id The id of the current session
     * @return Boolean
     */
    public function _destroy($id) {
        // Setup a query to delete the current session, ...
        $delete = "DELETE FROM
                `sessions`
            WHERE
                `sessions`.`id` = '" . $id . "';";

		// ... prepare the statement, ...
		$deleteStmt = $pdo->prepare($delete);

		// ... bind the parameters to the statement ...
		$deleteStmt->bindParam(':id', $id, PDO::PARAM_INT);

		// ... and execute the query.
		return $deleteStmt->execute();
    }

    /**
     * The garbage collector deletes all sessions from the database
     * that where not deleted by the session_destroy function.
     * so your session table will stay clean.
     *
     * @access public
     * @access Integer $maxlifetime The maximum session lifetime
     * @return Boolean
     */
    public function _gc($maxlifetime) {
        // Set a period after that a session pass off.
        $maxlifetime = strtotime("-20 minutes");

        // Setup a query to delete discontinued sessions, ...
        $delete = "DELETE FROM
                `sessions`
            WHERE
                `sessions`.`last_updated` < :maxlifetime;";

		// ... prepare the statement, ...
		$deleteStmt = $pdo->prepare($delete);

		// ... bind the parameters to the statement ...
		$deleteStmt->bindParam(':maxlifetime', $maxlifetime, PDO::PARAM_INT);

		// ... and execute the query.
		return $deleteStmt->execute();
    }
}
?>

A practical example

Now as we finished the implementation of the session handler class, we could take a short look on how to use this implementation in our projects. Let’s assume that we would like to implement a administration and user login, which should be part of a community page. We instantiate the session handler, assign the “entered” user data and could then access them using the superglobal $_SESSION array. If you now take a look into the MySQL session table, you will recognize that it is now pretty easy to access this data to dump all online members.

<?php
	// Get an instance of the Registry ...
	$reg = Registry::getInstance();

	// ... and fetch a database object.
	$pdo = $reg->get('pdo');

	// Instance SessionHandler, ...
	new MySqlSession($pdo);

	// ... assign some values to the session, ...
	$_SESSION['name'] = 'bornageek';
	$_SESSION['mail'] = 'test@bornageek.com';

	// ... get some session values ...
	print 'Logged in as ' . $_SESSION['name'];

	// ... and destroy the session.
	session_destroy();
?>

This code demonstrates that if you create a new instance of the MySqlSession class, you are able to use the well known default session operations and functions without making any other changes to your code. This way it is very very easy to switch between different types of session management.

I hope you enjoyed this short tutorial and could use it within your own projects. In the next few days I will translate more of my tutorials published at der-webdesigner.net, so I can share them with you.

So stay tuned and until next time - happy coding!


is a Computer Science MSc. interested in hardware hacking, embedded Linux, compilers, etc.