Click here to Skip to main content
11,478,562 members (67,716 online)
Click here to Skip to main content

Tagged as

Session Storage in a MySQL Database

, 21 Jul 2014 CPOL 6.2K 197 6
Rate this:
Please Sign up or sign in to vote.
This tutorial describes how to release Session storage in a MySQL database.

Introduction

You may know that Sessions are very critical component of creating dynamic websites and web applications. When we build such type of website, we will most certainly be required to add handle of Sessions at some point.

When keeping session variable at the server they are usually placed in special files, at a location specified by session.save_path. To find out where it is you can use a call to phpinfo() function. Unfortunately if your website is placed on a shared server you cannot rule out the possibility of other users (also working in your server) sneaking a peek at these files.

So.. in this tutorial we'll be looking at PHP Sessions: why you need them, how they should be stored and we'll give you a complete class for handling Sessions within a database.

What are Sessions and how they work?

Before we're going into implementation of database storing, it's important to understand what are Sessions and how they work.

By default in Internet every request you make is "anonymous", that means it is not tied to the previous request in any way. When you login to your website, the script must "remember" that you have logged in (your current "state") and you should be associated with some account. And here Sessions come... in order to maintain this "state", we need to use Sessions.

In simple words Session is a unique identifier that is recorded on the server and in your browser. Using this recorded information, the server is able to maintain the state across many page requests. Sessions work by assigning to each visitor a unique identifier. When you save data into the Session (for example when you perform login), the Session will store this information into a certain file on the server. The unique identifier is also stored as a cookie on your computer.

So.. when you visit a new page, the session id from the cookie is used to find the session data from the file storage. If the data is found it will be loaded to the Session variable, where your application may use it.

Are Sessions safe?

Sessions like any other thing related to Internet is safe up until a certain point. One of the problems that you may encounter with storing Sessions in a file system on the server is when you are using a shared hosting plan. In this case if the server is not configured correctly, it would be possible for someone to get access to your Sessions.

There are some ways to prevent this problem and one of the popular is by storing the Sessions in a database, rather than in file storage. The good new is that storing Sessions in a database is really easy, and there are not negative effects for your users - they even don't know how you are storing the Sessions.

Storing Sessions in a database may be also an advantage for you, if you need to expand your application to multiple servers.

How to set up storing Sessions in a database?

Before you can store Sessions in a database, you need to create a table. The following example demonstrates such table (the same query should work on many databases with very little modification):

Sample 1. CREATE syntax for Sessions table.

CREATE TABLE `sessions` (
  `session_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `expires_at` int(11) NOT NULL,
  `session_data` text COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `session_id` (`session_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Now that you have a table to store the Sessions in, you can learn how to put it to use. The good news is that PHP provides a function that allows overriding the default session mechanism by specifying the names of your own functions that can perform the required tasks.

The function is called session_set_save_handler(), and it takes six arguments, each of them represents a name of the function that you use. These functions are responsible for the following tasks:

  • Opening the session data store
  • Closing the session data store
  • Reading session data
  • Writing session data
  • Destroying all session data
  • Cleaning out old session data

For the purposes of this article, let's assume that this function is called as follows (we'll customize it to fit our needs later):

Sample 2. Call session_set_save_handler() function.

session_set_save_handler(
    "openSession",
    "closeSession",
    "readSession",
    "writeSession",
    "destroySession",
    "gcSession"
); 

Creating the Session class

Now we are going to create a Class to handle our Sessions. This means we can simply instantiate a new Object of the class in any place of our code. It also helps to keep all of our Session methods together in one place.

In order to store the Session data in database, we need to have a way of interacting with the database. Lets say you have some Database class that is extension of PDO. You may also use your own Database abstraction layer, it should be pretty easy to switch it out.

So... the first thing we need to do is to create the Class with a property for the database object that will be used.

Sample 3. Create Session class.

/**
 * Session provides session-level data management
 * by using database as session data storage
 */
class Session {
    /** @var Database */
    private $_db;

}

Class Constructor

Now we have to instantiate the Session class and we need to set up a couple of things in order to make it work. The constructor method of the class is automatically runs when the class is instantiated, as you understand this is a good place to place all required definitions.

First we have to instantiate a copy of the database class and store it in the _db property. Then we need to override the Session handler - in this way we tell PHP we want to use our own methods for handling Sessions and finally we need to start the Session.

Sample 4. Class Constructor.

/**
 * Class default constructor
 */
function __construct()
{
    // instance of new Database object
    $this->_db = CDatabase::init();
    
    // set handler to overide SESSION
    @session_set_save_handler(
        array($this, "openSession"),
        array($this, "closeSession"),
        array($this, "readSession"),
        array($this, "writeSession"),
        array($this, "destroySession"),
        array($this, "gcSession")
    );
    
    // start the session
    session_start();
}

Method "openSession"

Now we need to create the simply checks to see if there is a database connection available to use or not.

Sample 5. Method "openSession".

/**
 * Session open handler
 * @return boolean
 */
public function openSession(
    // if database connection exists
    if($this->_db){
        return true;
    }
    return false;
}

Method "closeSession"

This method is similar to the openSession method, it simply checks to see if the connection has been closed.

Sample 6. Method "closeSession".

/**
 * Session close handler
 * Do not call this method directly
 * @return boolean
 */
public function closeSession()
{
    // if database connection is closed
    if($this->_db->close()){
        @session_write_close();
        return true;
    }
    return false;    
}

Method "readSession"

This takes the Session Id and queries the database. This method is the example of how we bind data to the query. By binding the id to the :id placeholder, and not using the variable directly, we use the PDO method for preventing SQL injection.

Sample 7. Method "writeSession".

/**
 * Session read handler
 * Do not call this method directly
 * @param string $id
 * @return string
 */
public function readSession($id)
{
    $result = $this->_db->select(
        "SELECT session_data FROM sessions WHERE session_id = :session_id",
        array(":session_id"=>$id)
    );
    return isset($result[0]) ? $result[0] : "";
}

Method "writeSession"

To update the Session, it requires the Write method. The Write method takes the Session Id and the Session data, then. The access token is the current time stamp, so you may insert or update your session data. If the query is executed correctly, we return true, otherwise we return false.

Sample 8. Method "writeSession".

/**
 * Session write handler
 * Do not call this method directly
 * @param string $id
 * @param string $data
 * @return boolean
 */
public function writeSession($id, $data)
{        
    $result = $this->_db->select(
        "SELECT session_data FROM sessions WHERE session_id = :session_id",
        array(":session_id"=>$id)
    );
    if(isset($result[0])){
        $result = $this->_db->update(
            "sessions",
            array(
                "expires_at"=>time()+$this->getTimeout(),
                "session_data"=>$data
            ),
            "session_id = :session_id",
            array(":session_id"=>$id)
        );
    }else{
        $result = $this->_db->insert(
            "sessions",
            array(
                "session_id"=>$id,
                "expires_at"=>time()+$this->getTimeout(),
                "session_data"=>$data
            )
        );
    }
    
    return true;
}

Method "destroySession"

The Destroy method deletes a Session based on it's ID. This method will be called when you use the session destroy global function, like this: session_destroy();

Sample 9. Method "destroySession".

/**
* Session destroy handler
* Do not call this method directly
* @param string $id
* @return boolean
*/
public function destroySession($id)
{
    return $this->_db->delete(
        "sessions", "session_id = :session_id", array(":session_id"=>$id)
    );        
}

Method "gcSession" - Garbage Collection

The gcSession function will be run by the server to clean up any expired Sessions in the database. This function is run depending on a couple of settings that you have on your server. The method is passed a $maxLifetime variable. It's related to the maximum number of seconds before PHP recognises a session has expired. rememeber that this is a setting on your server that is open for you to edit. It may be found in php.ini file. Sample 10. Method "gcSession".

/**
 * Session garbage collection handler
 * Do not call this method directly
 * @param int $maxLifetime
 * @return boolean
 */
public function gcSession($maxLifetime)
{
    return $this->_db->delete(
        "sessions", "expires_at < :expires_at", array(":expires_at"=>time())
    );
}

Conclusion

In this tutorial we've reviewed a nice and simple way to get up and running with storing PHP Sessions in a database. We already use this code in CDbHttpSession.php in our PHP MVC Framework and Directy CMF. Hopefully this was a good introduction to the concept and you may find the code example of the class below: Download store-session-data-in-mysql-database.zip - 1.7 KB

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

apphp
Founder ApPHP
United States United States
ApPHP.com founder, PHP Expert
http://www.apphp.com
Follow on   Twitter   Google+

Comments and Discussions

 
QuestionWhat components are needed to implement Pin
kidzopa8-Apr-15 8:07
memberkidzopa8-Apr-15 8:07 
GeneralMy vote of 5 Pin
Mihai MOGA13-Aug-14 4:20
professionalMihai MOGA13-Aug-14 4:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150520.1 | Last Updated 21 Jul 2014
Article Copyright 2014 by apphp
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid