Home » Code » How to save PHP Sessions to a database

How to save PHP Sessions to a database

Posted by on February 4th, 2013

How to save PHP Sessions to a database
Sessions are a critical component of creating dynamic websites or web applications. If you are building these types of website, you will most certainly be required to handle Sessions at some point.

In this post I will be looking at PHP Sessions, why you need them, how you should store them and I’ll give you a complete class for handling Sessions within a database.

What are Sessions?

Before we get into implementing a Sessions solution, it’s important to understand exactly what they are.

By default, the Internet is a “stateless” environment. This means that every request you make in a browser is anonymous in the sense that it is not tied to the previous request in any way. When you login to a website, the application must “remember” that you have logged in and you should be associated with an account. However, in order to maintain this “state”, we need to use Sessions.

A Session is a unique identifier that is recorded on the server and in your browser. By recording this information, the server is able to maintain the state across many page requests.

So essentially, the internet is unable to remember you every time you refresh the page. A Session is simply a small note that enables the server and your browser to maintain certain data.

How do Sessions work?

Sessions work by assigning each user a unique identifier. When you save data into the Session (such as being logged in), the Session will store that data into a file onto the server. The unique identifier is also stored as a cookie on the user’s computer.

When the user visits 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 load it into the Session global variable ready for the application to use.

Are Sessions safe?

Like just about anything on the Internet, Sessions are safe up until a certain point. Whilst it is possible to “hijack” a Session, I wouldn’t worry too much about it unless you are storing really sensitive data on people.

If a hacker is able to hijack another user’s Session, they will be able to log into the user’s account as if they are that person.

There are many ways to make Sessions more secure. For example, you could store a token in the Session that is auto generated on each request. You could then compare these tokens to see if they matched. Another method could be to maintain a record of the user’s IP address and browser data. Both of these methods are not fool-proof as they are both still susceptible to an attack. Both methods will also add significant complexity to your application and will probably annoy the user more than it will keep them safe.

One of the problems with storing Sessions in a file system on the server is when you are using a shared hosting plan. If the server has not be configured correctly, it would be possible for someone to get access to your sessions.

One way to prevent this problem is by storing the Sessions in a database, rather than in file storage. Fortunately, storying Sessions in a database is really easy, and it will not negatively effect your user.

Storing Sessions in a database is also an advantage if you need to expand your application to multiple servers. By storing Session data in a central server that can be accessed by any of the other Servers, you negate the problem that would of arose.

Setting up storing Sessions in a database

I was first introduced to storing Sessions in a database by this great post by Chris Shiflett. Chris provides a perfect introduction to the concept and some sample code to get started. The code in this tutorial is an extension of Chris’ work, and so if you want to read his introduction, you can go do that first.

So in order to store our Session data in a database, we’re going to need a table.

Run the following SQL in your database to generate the table. I’m using MySQL, but hopefully the following should be enough to get you started.

CREATE TABLE IF NOT EXISTS `sessions` (
  `id` varchar(32) NOT NULL,
  `access` int(10) unsigned DEFAULT NULL,
  `data` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The Session Class

We are going to use a Class to handle our Sessions. This means we can simply instantiate a new Object of the class on each page request. It also keeps all of our Session methods all together in one place.

In order to store the Session data in a database, we are going to need to have a way of interacting with the database. For this tutorial I will be passing the Session class and instance of the Database class that I wrote about in Roll your own PDO PHP Class.

If you want to use your own Database abstraction layer, it should be pretty easy to switch it out. If you haven’t already got a database abstraction layer set up, take a look at that tutorial.

So the first thing we need to do is to create the Class.

class Session {

}

Next we declare a property for the database object we will be using.

/**
 * Session
 */
class Session {

  /**
   * Db Object
   */
  private $db;

}

The Constructor

When we instantiate the Session class, we need to set up a couple of things in order to make it work. The constructor method is automatically run when the class is instantiated, and so this is a good place to do just that.

First we instantiate a copy of the database class and store it in the db property.

public function _construct(){
  // Instantiate new Database object
  $this->db = new Database;
}

Next we need to override the Session handler to tell PHP we want to use our own methods for handling Sessions.

// Set handler to overide SESSION
session_set_save_handler(
  array($this, "_open"),
  array($this, "_close"),
  array($this, "_read"),
  array($this, "_write"),
  array($this, "_destroy"),
  array($this, "_gc")
);

This looks complicated, but all it is saying is we want to use our own methods for storing and retrieving data that is associated with the Session. Take a look at the PHP Manual to read more about this.

And finally we need to start the Session.

// Start the session
session_start();

So your constructor method should look like this:

public function _construct(){
  // Instantiate new Database object
  $this->db = new Database;

  // Set handler to overide SESSION
  session_set_save_handler(
    array($this, "_open"),
    array($this, "_close"),
    array($this, "_read"),
    array($this, "_write"),
    array($this, "_destroy"),
    array($this, "_gc")
  );

  // Start the session
  session_start();
}

Next we need to create each of the methods for handling our Session data. Each of these methods are really simple. If you are unfamiliar with the database abstractions from the PDO tutorial, have a read through that post for a more detailed explanation of the methods that will be interacting with the database.

Open

The first method we need to create simply checks to see if there is a database connection available to use.

/**
 * Open
 */
public function _open(){
  // If successful
  if($this->db){
    // Return True
    return true;
  }
  // Return False
  return false;
}

Here we are simply checking to see if there is a database connection. If there is one, we can return true, otherwise we return false.

Close

Very similar to the Open method, the Close method simply checks to see if the connection has been closed.

/**
 * Close
 */
public function _close(){
  // Close the database connection
  // If successful
  if($this->db->close()){
    // Return True
    return true;
  }
  // Return False
  return false;
}

Read

The Read method takes the Session Id and queries the database. This method is the first example of where 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.

/**
 * Read
 */
public function _read($id){
  // Set query
  $this->db->query('SELECT data FROM sessions WHERE id = :id');
  
  // Bind the Id
  $this->db->bind(':id', $id);

  // Attempt execution
  // If successful
  if($this->db->execute()){
    // Save returned row
    $row = $this->db->single();
    // Return the data
    return $row['data'];
  }else{
    // Return an empty string
    return '';
  }
}

If the query returns data, we can return the data. If the query did not return any data, we simple return an empty string. The data from this method is passed to the Global Session array that can be accessed like this:

echo "<pre>";
print_r($_SESSION);
echo "</pre>";

Write

Whenever the Session is updated, it will require the Write method. The Write method takes the Session Id and the Session data from the Global Session array. The access token is the current time stamp.

Again, in order to prevent SQL injection, we bind the data to the query before it is executed. If the query is executed correctly, we return true, otherwise we return false.

/**
 * Write
 */
public function _write($id, $data){
  // Create time stamp
  $access = time();
		
  // Set query	
  $this->db->query('REPLACE INTO sessions VALUES (:id, :access, :data)');
    
  // Bind data
  $this->db->bind(':id', $id);
  $this->db->bind(':access', $access);	
  $this->db->bind(':data', $data);

  // Attempt Execution
  // If successful
  if($this->db->execute()){
    // Return True
    return true;
  }
  
  // Return False
  return false;
}

Destroy

The Destroy method simply deletes a Session based on it’s Id.

/**
 * Destroy
 */
public function _destroy($id){
  // Set query
  $this->db->query('DELETE FROM sessions WHERE id = :id');
    
  // Bind data
  $this->db->bind(':id', $id);
		
  // Attempt execution
  // If successful
  if($this->db->execute()){
    // Return True
    return true;
  }

  // Return False
  return false;
}	

This method is called when you use the session destroy global function, like this:

// Destroy session
session_destroy();

Garbage Collection

And finally, we need a Garbage Collection function. The Garbage Collection function will be run by the server to clean up any expired Sessions that are lingering in the database. The Garbage Collection function is run depending on a couple of settings that you have on your server.

/**
 * Garbage Collection
 */
public function _gc($max){
  // Calculate what is to be deemed old
  $old = time() - $max;

  // Set query
  $this->db->query('DELETE * FROM sessions WHERE access < :old');
    
  // Bind data
  $this->db->bind(':old', $old);
		
  // Attempt execution
  if($this->db->execute()){
    // Return True
    return true;
  }

  // Return False
  return false;
}

The Garbage collection is run based upon the session.gc_probability and session.gc_divisor settings on your server. Say for example the probability is set to 1000 and the divisor is 1. This would mean that for every page request, there would be a 0.01% chance the Garbage collection method would be run.

The method is passed a max variable. This relates to the maximum number of seconds before PHP recognises a Session has expired. Again this is a setting on your server that is open for you to edit.

Both of these settings can be found in your php.ini file.

Conclusion

And there you have, a nice and simple way to get up and running with storing PHP Sessions in a database. Hopefully this was a good introduction to the concept and a practical example so you can see it in action.

Philip Brown

Hey, I'm Philip Brown, a designer and developer from Durham, England. I create websites and web based applications from the ground up. In 2011 I founded a company called Yellow Flag. If you want to find out more about me, you can follow me on Twitter or Google Plus.

Join the Culttt

Become an insider and join the Culttt

  • Mark

    Hi Philip,

    Thank you very much for this! I’m learning PHP & PDO but I’m still a noob (your articles have been a great help). I feel like I understand the purpose of this class but not how to actually use it. Could you provide an example?

    Thanks again,

    Mark.

    • http://culttt.com/ Philip Brown

      Hi Mark

      You would save the class into it’s own file Session.php.

      Then you would include that file into your project. So for example if you had a bootstrap file that included all of your library files, you would write the following:

      include 'Session.php';

      And then you can simply create a new instance of the class to create your session:


      $session = new Session;

      Now you can use sessions as normal, but they will save to the database, rather than to the file system.

      A good way to see this in action is to create a counter that increases every time you refresh the page:


      // Include class
      include 'Session.php';
      // Instantiate new Session
      $session = new Session;
      // Check if the counter is has not been set yet
      if( ! isset( $_SESSION['counter'] ) ){
      $_SESSION['counter'] = 0;
      }
      // Increase counter
      $_SESSION['counter']++;

      Does that make sense?

      • Mark

        Fantastic! Seems it was simpler than I thought… Thank you very much for the help, exactly why I love this site!

        Mark.

        • http://culttt.com/ Philip Brown

          Thanks Mark! If you want to ask anything else, just give me a shout!

          • pinky

            how the value does not change, when the page is reloaded,by using session in php?

          • http://culttt.com/ Philip Brown

            Yeah, the data will be saved in the session.

  • Steve

    Hi Philip !

    Thank you for this awesome tutorial. Just one question:
    Instead of this type of code :

    // Attempt execution
    if($this->db->execute()){
    // Return True
    return true;
    }
    // Return False
    return false;
    wouldn’t be much simplier :
    return $this -> db -> execute () ?
    Thank you,
    Steve

    • http://culttt.com/ Philip Brown

      Hi Steve, yeah you are absolutely right. Nice little optimisation there! :)

  • Steve

    Hi Philip !

    I’ve got a “notice” type warning, at row 16 of session class (session_start();) :

    “Notice: Object of class Database could not be converted to int in”

    Do you think it’s important ? Thank you.

    • http://culttt.com/ Philip Brown

      Hmm, what are you trying to do? Put your code into a Github gist and I’ll take a look

      • Andy

        Hey, firstly great post and secondly I am having the same error, “Notice: Object of class Database could not be converted to int in” did you resolve this issue?

        Thanks again.

        • Andy

          UPDATE: I figured that out. I had implemented the optimisation recommended by Steve but of course that will not work in the case of the Open function.

  • Sean_Dorman

    How well does this work if a user turns off cookies?

    • http://culttt.com/ Philip Brown

      Hi Sean, you would have to do something through GET or POST. Basically you would need to pass the session id through something other than a cookie.

      There are some good answers on Stack Overflow for storing sessions without cookies.

  • Luke

    Hi Philip, great tutorial on the PDO roll your own class :)… I am having a bit of trouble using the PDO class and the session class to save the session in the database, any help would be greatly appreciated. I have created a GitHub account and made a gist of the source code, which can be found here: https://gist.github.com/pigeonator/939be131986e8dfa07f2, Would it be possible if you could take a look and tell me what’s wrong? Cheers.

    • Luke

      Answer to my own question: It seemed that the construct function needed two underscores and in the database.class file, I needed to create a function that killed the instance of PDO. So now all the sessions are saving to the database :). Thanks again for the tutorial.

      • http://culttt.com/ Philip Brown

        Yep, construct is a special PHP method :)

        Glad you got it sorted!

      • Chris Townson

        Hi Luke. Would you mind sharing the additional function to kill the instance of PDO? I’m having the same problem.

        • Luke

          Hi Chris. It has been so long since I’ve worked with PDO and have since lost the file that included the function. If I remember correctly, in the function that killed PDO I set the PDO instance to a null value. I’m not completely sure on the exact code for this either, but hopefully this helps.

          • Chris Townson

            Thanks Luke. I’ll keep on trying. Hopefully Philip can help :-)

          • http://culttt.com/ Philip Brown

            Yeah you can just set it to null. Although, unless your script is really long running, I wouldn’t worry about it.

          • rifqi

            oke

  • ash786

    Why is it that when I call the session_destroy with this class, I get an error page with “No data received” method. How could the session_destroy method map to the destroy function of the class ? The destroy function also needs a parameter ($id).

    • http://culttt.com/ Philip Brown

      You need to use session_set_save_handler.

  • Andy

    Just as a matter of interest the close() function fails because in your database class that you are using in this tutorial you did not define a close function, so $this->db->close() gives the error, “call to undefined method Database::close()”

    What is the best way to close a PDO?

  • Andy

    Hey again,

    I have the code working but I am having an issue. I have an application that calls the same PHP code a number of times per page load. It is important that each call to my php file stores a value. They are differentiated by a variable passed in the URL (eg: domain.com?el=1 or?el=2, etc…)

    I rely on each either a) setting a calculated value to the database or b) each using the same value that was calculated by the very first call. However it seems not all are storing a value back to the database but I am not getting any errors.

    Any ideas?

    Also, how do I best deal with the session_write_close() that I used to use with session cookies? Is this redundant using Database Sessions?

    Thanks

    • http://culttt.com/ Philip Brown

      Hmm, your session isn’t saving? What code are you using? I would try making a counter or something to see why that’s not working.

      Hmm, I’m not sure tbh, I’ve not used it. Do you need it for a specific reason? If you think you can cut it, I would cut it unless you are going to be getting weird side effects.

      • Andy

        I am using the code from this post and your Roll your own PDO PHP Class. I will try adding a counter, good idea, although I can see from sending messages to to error_log() that all are calling the “$session = new Session;” code but not all 4 calls per page load seem to be storing their variables values back to the database. Sometimes, 2 of them, sometimes all 4, sometimes just 3. Very strange!

        Do you think the fact that I am testing it on my MAMP server will make a difference given things are happening faster than from a remote server?

        • http://culttt.com/ Philip Brown

          Why are you calling new Session multiple times?

          • Andy

            The same PHP file is being called multiple times per page load. How do I get around that? Is this a complete school boy error?

            I have set up a very simple test:

            $session = new Session;

            if( !isset($_SESSION['counter']) ) {
            $_SESSION['counter'] = 0; }

            $_SESSION['counter']++;
            error_log($_SESSION['counter']);
            exit();

            ERROR LOG OUTPUT IS:
            [25-Nov-2013 09:06:14 UTC] 1
            [25-Nov-2013 09:06:14 UTC] 1
            [25-Nov-2013 09:06:14 UTC] 2
            [25-Nov-2013 09:06:14 UTC] 3

            I have noticed that more than one call is finding counter not set and hence setting to 0.

            How do I have the 2nd, 3rd and 4th calls to the php file get access to the correct session?

          • http://culttt.com/ Philip Brown

            Yeah you shouldn’t be calling it multiple times. I would set up a bootstrap process which would load your environment including your session stuff so you don’t load it more than once.

          • Andy

            Thanks Philip. Do you have an example of bootstrap process you could point me at? Part of my problem is that I have no control over the calling page as the calls can be embedded in 3rd party web pages.

          • http://culttt.com/ Philip Brown

            Hmm, I see. Well it’s hard to give a perfect example because I don’t think I fully understand your set up.

            I would have a look at how the Laravel framework is bootstrapped https://github.com/laravel/laravel/blob/master/bootstrap/start.php

            This might be a bit overkill for what you are trying to achieve, but hopefully it will give you some ideas.

          • Andy

            I can explain the setup.

            A web page can embed calls to us via the IMG tag src=”myphp.com?el=1″ and they can embed quite a few each with a different el=2 or 3 or 4 or 19, so when the browser wants the page we get the calls and as usual with browsers we get them in any random order.

            Each call will return an image chosen from a list. There are two reasons I am using the session database. Firstly because one of the modes allows them to synchronise the results, so they will all return the second image in their respective list and Secondly, when they click through it does a final call back and stores to a file a record of the images that were sent.

            I hope that helps.

          • Andy

            Hi Philip,

            I have realised that I can catch the ?el=1 call to my PHP file and use that to start a new Session BUT….

            How do I get the right session for the other calls?

            For example and given they come in randomly, let’s say the order went like this:

            First to arrive ?el=2 so I make it wait until after ?el=1 has created the Session.

            Then ?el=3 arrives so I do the same.

            Then ?el=1 arrives so I create the Session and store its value. Then this call is finished.

            I need a way of then letting ?el=2 and ?el=3 know that the Session is there and which one it is!

            Didn’t think this would be so hard :-)

            Thanks for any help and advice you can give Philip!

          • http://culttt.com/ Philip Brown

            Hmm, I’m not sure I 100% understand. But I would perhaps rethink your approach. You will want to make this as robust as possible. By the sound of it, it seems a little flakey.

            I would perhaps take a step back and think about what you are trying to achieve. Forget about the biases of the code you’ve already written and see if you can think of a robust way of completing your objective.

            That’s probably not the direct PHP answer you were looking for, but I hope it helps :)

          • Andy

            Thanks for the reply. In reality all I need is to be able to have all the various calls to the PHP during the same page load to have access to the same variables, and in fact just one of them that they can copy.

            By the way, if I could have an email addr I would happily send you my code so you can see what I am trying to do, if you had the time!

          • http://culttt.com/ Philip Brown

            Yeah for sure, it’s phil [at] ipbrown.com.

            Try to explain the problem you are trying to solve with a real life example. I think finding the most simple solution is always the best idea, and finding the most simple solution is often easiest when you think about it as a real world example.

  • Adev

    Hello there! I have trouble trying to run this code. I am on it already two days.

    This is what I have:

    include ‘Database.php';

    include ‘Sesions.php';

    $session = new Session();

    The database class is from the other example from this blog.

    When I open the page, nothing happens in the database. No rows are recorded.

    When I make some error on the code, for example I change the _construct() to __construct (the session class), I see error on page and one row is recorded in the database. Then I switch back to the original code, the error on the page is gone. The row in the database is there.

    What I am missing? Is this is the right behaviour of this class?

    Then I put this in the page:

    include ‘Database.php';

    include ‘Sesions.php';

    $session = new Session();

    if (isset($_SESSION['current_pages_views'])) {

    $_SESSION['current_pages_views'] = $_SESSION['current_pages_views'] + 1;

    }

    print $_SESSION['current_pages_views'];

    … and I do not see nothing. Looks like this $_SESSION['current_pages_views'] do not exist. When I remove the session class, the page counts and prints $_SESSION['current_pages_views'] like this: 1, 2, 3, 4 .. etc.

    • spellChecker

      include ‘Sesions.php'; are you sure it’s not ‘Sessions.php’

      or is that how you actually spelled it?

  • Adev

    Hi, its me again! I am really exhausted, but that is how with the newbies. I really had to made some small changes to had this class work for me.

    First, public function _construct()

    is now: public function __construct()

    And I had problem with the close method. This is how it is working with me now:

    public function _close() {

    $this->db = null;

    }

    Now the sessions data from the $_SESSION array is stored in the database. Thank you!

    Please, can you tell me, did I manage to make things right and I made correct and right fixes?

    • http://culttt.com/ Philip Brown

      Yeah that looks good to me! Glad you got it sorted! :)

  • Travis

    Phillip…for security purposes do you see any benefits of encrypting the data before storing in the database?

    • http://culttt.com/ Philip Brown

      Your sessions will automatically be encrypted if you have the Suhosin patch installed http://www.hardened-php.net/suhosin/

      So yeah you should encrypt your sessions, but if you have the Suhosin patch installed you don’t have to do it yourself :)

  • Luke

    Hi, I have a website that requires a user authentication / logged in state to be set to view certain content. Everything was working well, until I realized there’s a production issue due to load balancing.

    I asked the server tech about adding persistence but he told me that I should just store the session in a database, which I am attempting to integrate now.

    I’ve got all the information submitting successfully, but I am not unclear on how to use this to authenticate the user’s log in status across both servers.

    I have not seen that portion in any of the examples here or elsewhere on storing sessions in a database.. only how to add/update the database row. Any advice would be highly appreciated.

  • Chris Townson

    Fantastic tutorial. However, I’m struggling to put together the database class from your PDO tutorial. Is there any chance you could provide a zip of the completed files from this tutorial?

    • http://culttt.com/ Philip Brown

      Throw your code up on GitHub and I’ll take a look

      • Chris Townson

        Thanks for getting back to me. Think I’ve sorted it. I added a close function, setting $this->db to null. Seems fine now :-)

        • http://culttt.com/ Philip Brown

          No problem :)

  • Sid

    Hi. I’ve really enjoyed your tutorial. I’ve made it running with PDO and stored prcedures and everything is even running smoothly. However, I have a ‘newbie’ question. If I include the session.class.php and initialize an instance of the session class in file_one.php, how do I start the session in the file_two.php? Initialization of a new instance of session class make all session variables disappear …

    • http://culttt.com/ Philip Brown

      You would need to include both files into the same request.

      • Sid

        I’m sorry Philip, I don’t follow. I’d like to achieve the functionality of ‘standard’ PHP sessions but stored in database. I have two pages: file1.php and file2.php. Usually I did session_start(); at top of each file, and if I created and initialized session variable in the first file ($_SESSION['var1'] = 1), this variable was also available in the second file (echo $_SESSION['var1']). Now, if I use the session class presented above, initialize an instance of the class ($session = new Session()) in the first file and create the session variable ($_SESSION['var1'] = 1) this variable along with session id and access time is stored in database. However, if I go now to the second file, initialize an instance of the session class as I did it in the first one, the count of session variables is equal to 0, though both files return the very same session id. I did a small change to the database query in _write method to prevent the data to be cleared after the second instance of the session class call the constructor and _write but it still doesn’t return any session variables,though the record in database contains the proper data.
        Any thoughts? A short example of the class usage would be very appreciated :-) Thanks a lot.

  • Elikem Tetteh

    Hello Philip Brown

    I am quite new to php and database. I have made some progress over the days as i try to implement something i have on my mind. I wish to create a register and log in system for my personal site. Somehow i have been able to achieve that. What i wish to implement is when a register user logs in,lets say at home and goes to work to log in with his office pc using the same user name and password. i want the pc at home to automatically log him out to and log him in to his office pc. at the moment i am setting things on my localhost and hope to find a solutions before taking it online. Will i be able to achieve this with sessions in the data base?. How do i go about it. Thanks
    Your reply will be very much apprieciated.

    • http://culttt.com/ Philip Brown

      Yeah you would just associate a session to a user. If the same user attempts to log in again you could just clear the existing session.

  • DKT

    Thank you, it really helped me.

    • http://culttt.com/ Philip Brown

      No problem :)

Supported by