About the Author

Chris Shiflett

Hi, I’m Chris: entrepreneur, community leader, husband, and father. I live and work in Boulder, CO.


Storing Sessions in a Database

  • Published in PHP Magazine on 14 Dec 2004
  • Last Updated 14 Dec 2004
  • 66 comments

Welcome to another edition of Guru Speak. I believe that one of the hallmarks of a good writer is the ability to mold a complex topic into something both palatable and interesting. These are the characteristics I strive for in Guru Speak, and I hope you consider my efforts to be a success. Please be sure to let me know what issues tend to trouble you the most or in what areas you would like to expand your knowledge and understanding. I am happy to cater to my readers.

This edition's topic is storing sessions in a database.

Background

While the default session storage mechanism is adequate for many PHP developers, you might find yourself wanting to modify its behavior from time to time. One of the most common reasons for wanting to change the default behavior is to store sessions in a database rather than the filesystem. The top reasons for this desire are:

  • The application needs to be able to run on multiple servers without server affinity (methods that direct requests from the same client to the same server). An easy way to make sure that sessions continue to work properly is to store sessions in a central database that is common to all servers.
  • The application needs to be able to run on a shared host, where there are significant security concerns associated with storing session data in the filesystem.
  • The performance needs of the application are very demanding and require a more sophisticated storage solution for session data. There are many existing ideas and methodologies that address database performance issues, and these can be used when sessions are stored in a database.

Luckily, PHP makes this task very easy.

Session Data Store

Before you can store sessions in a database, you need to create a table. The following example demonstrates the CREATE syntax for such a table:

CREATE TABLE sessions (
    id varchar(32) NOT NULL,
    access int(10) unsigned,
    data text,
    PRIMARY KEY (id)
);

While this is MySQL syntax, the same query should work on many databases with very little modification. Keep in mind that you can store any other information you want - perhaps you want to store the name of the application each session is associated with, the timestamp of each session's creation, or even an extra token to help complicate impersonation. I only show you the basic mechanism, and you can modify and expand it to fit your own needs.

If you DESCRIBE this table, MySQL gives you a nice visual representation:

mysql> DESCRIBE sessions;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| id     | varchar(32)      |      | PRI |         |       |
| access | int(10) unsigned | YES  |     | NULL    |       |
| data   | text             | YES  |     | NULL    |       |
+--------+------------------+------+-----+---------+-------+

Now that you have a table to store the sessions in, you can learn how to put it to use.

session_set_save_handler()

PHP provides a function that lets you override the default session mechanism by specifying the names of your own functions for taking care of the distinct tasks. I have organized this article according to these functions that you must write. For each function, I show you an example (using MySQL) and provide an explanation.

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

  1. Opening the session data store
  2. Closing the session data store
  3. Reading session data
  4. Writing session data
  5. Destroying all session data
  6. Cleaning out old session data

For the purposes of this article, I assume that this function is called as follows:

<?php
 
session_set_save_handler('_open',
                         '_close',
                         '_read',
                         '_write',
                         '_destroy',
                         '_clean');
 
?>

You must call session_set_save_handler() prior to calling session_start(), but you can define the functions themselves anywhere.

The real beauty of this approach is that you don't have to modify your code or the way you use sessions in any way. $_SESSION still exists and behaves the same way, PHP still takes care of generating and propagating the session identifier, and changes made to session configuration directives still apply. All you have to do is call this one function.

I show you examples of these functions, so that you have a more complete understanding. While I provide everything you need, I hope you take the time to understand what the functions do. This is a critical skill that you will be glad to have whenever problems arise.

_open() and _close()

The _open() and _close() functions are closely related. These are used to open the session data store and close it, respectively. If you are storing sessions in the filesystem, these functions open and close files (and you likely need to use a global variable for the file handler, so that the other session functions can use it).

Because you're using a database, _open() and _close() can be as simple as the following:

<?php
 
function _open()
{
    mysql_connect('127.0.0.1', 'myuser', 'mypass');
    mysql_select_db('sessions');
}
 
function _close()
{
    mysql_close();
}
 
?>

However, this approach isn't very flexible. When a database handler isn't specified in functions like mysql_select_db() and mysql_query(), MySQL uses the most recently opened connection. If your code uses mysql_select_db() to select a different database, the attempt to write the session data at the end of the script can potentially fail, because the most recently opened connection has a different database selected. This scenario is quite common, and developers find it terribly frustrating due to the difficulty of debugging it.

There are two ways to gracefully avoid this scenario. You can either use a separate MySQL connection for your session mechanism, or you can make a habit of always using mysql_select_db() prior to any function that depends upon a specific database being selected (this includes both your session mechanism and your application).

In the examples in this article, I use the first method - using a separate connection for the session mechanism. I name this $_sess_db, and my modified _open() and _close() functions are as follows:

<?php
 
function _open()
{
    global $_sess_db;
 
    $_sess_db = mysql_connect('127.0.0.1', 'myuser', 'mypass');
    mysql_select_db('sessions', $_sess_db);
}
 
function _close()
{
    global $_sess_db;
 
    mysql_close($_sess_db);
}
 
?>

I begin the name of the database handler with an underscore as a naming convention to indicate that it should not be touched by application code. The idea is that it's a developer's own fault if problems arise as a result of modifying something that follows this convention.

I want to make one more minor modification. If you test the return value of a typical function in PHP, it returns TRUE on success and FALSE on failure. You can make _open() and _close() do the same thing with a small modification to each:

<?php
 
function _open()
{
    global $_sess_db;
 
    if ($_sess_db = mysql_connect('127.0.0.1', 'myuser', 'mypass')) {
        return mysql_select_db('sessions', $_sess_db);
    }
 
    return FALSE;
}
 
function _close()
{
    global $_sess_db;
 
    return mysql_close($_sess_db);
}
 
?>

Now that you have your _open() and _close()functions written, it's time to look at the other functions.

_read()

The _read() function is called whenever PHP needs to read the session data. This takes place immediately after _open(), and both are a direct result of your use of session_start().

PHP passes this function the session identifier, as the following example demonstrates:

<?php
 
function _read($id)
{
    global $_sess_db;
 
    $id = mysql_real_escape_string($id);
 
    $sql = "SELECT data
            FROM   sessions
            WHERE  id = '$id'";
 
    if ($result = mysql_query($sql, $_sess_db)) {
        if (mysql_num_rows($result)) {
            $record = mysql_fetch_assoc($result);
 
            return $record['data'];
        }
    }
 
    return '';
}
 
?>

PHP expects the session data in return, and you don't have to worry about the format, because PHP provides the data to the _write() function (covered in the next section) in the same format that it expects it. Thus, this function returns exactly what is in the data column for the matching record.

The handler PHP uses to handle data serialization is defined by the session.serialize_handler configuration directive. It is set to php by default.

_write()

The _write() function is called whenever PHP needs to write the session data. This takes place at the very end of the script.

PHP passes this function the session identifier and the session data. You don't need to worry with the format of the data - PHP serializes it, so that you can treat it like a string. However, PHP does not modify it beyond this, so you want to properly escape it before using it in a query:

<?php
 
function _write($id, $data)
{
    global $_sess_db;
 
    $access = time();
 
    $id = mysql_real_escape_string($id);
    $access = mysql_real_escape_string($access);
    $data = mysql_real_escape_string($data);
 
    $sql = "REPLACE
            INTO    sessions
            VALUES  ('$id', '$access', '$data')";
 
    return mysql_query($sql, $_sess_db);
}
 
?>

This function uses REPLACE, which behaves exactly like INSERT, except that it handles cases where a record already exists with the same session identifier by first deleting that record. This allows for a graceful way to handle choosing between INSERT and UPDATE without having to first perform a SELECT to see whether a record already exists. However, although REPLACE works with MySQL, it is not standard and does not work with all databases.

_destroy()

The _destroy() function is called whenever PHP needs to destroy all session data associated with a specific session identifier. An obvious example is when you call session__destroy().

PHP passes the session identifier to the function:

<?php
 
function _destroy($id)
{
    global $_sess_db;
 
    $id = mysql_real_escape_string($id);
 
    $sql = "DELETE
            FROM   sessions
            WHERE  id = '$id'";
 
    return mysql_query($sql, $_sess_db);
}
 
?>

The _destroy() function is only responsible for destroying the record in the session data store. It is up to the developer to destroy the data in $_SESSION if desired.

_clean()

The _clean() function is called every once in a while in order to clean out (delete) old records in the session data store. More specifically, the frequency in which this function is called is determined by two configuration directives, session.gc_probability and session.gc_divisor. The default values for these are 1 and 1000, respectively, which means there is a 1 in 1000 (0.1%) chance for this function to be called per session initialization.

Because the _write() function keeps the timestamp of the last access in the access column for each record, this can be used to determine which records to delete. PHP passes the maximum number of seconds allowed before a session is to be considered expired:

<?php
 
function _clean($max)
{
    global $_sess_db;
 
    $old = time() - $max;
    $old = mysql_real_escape_string($old);
 
    $sql = "DELETE
            FROM   sessions
            WHERE  access < '$old'";
 
    return mysql_query($sql, $_sess_db);
}
 
?>

The value that PHP passes to this function comes directly from the session.gc_maxlifetime configuration directive. You can actually ignore this and determine your own maximum lifetime allowed, but it is much better to adhere to the value PHP passes. Doing so better adheres to the idea of transparently changing the storage mechanism. From a developer's perspective, the behavior of sessions should not change.

Recap

You now have all the tools you need to store sessions in a database. Hopefully you also have a better understanding about the purpose of these six functions and are better prepared to deal with problems as they arise.

That's all for Guru Speak. See you next time.

About this article

Storing Sessions in a Database was last updated on 14 Dec 2004. Follow me on Twitter.

66 comments

1.sc wrote:

I know this doesn't really affect the purpose of this article, but why not use a datatype of TIMESTAMP for the access column? That way the access column is automatically updated during INSERT/UPDATE.

Mon, 01 May 2006 at 00:40:55 GMT Link


2.timvw wrote:

The only real change i would make is to add another 'connection' so that one is used for insert/update/delete (masterdb) and one for reading (slavedb)

If you don't like the "namespace clutter" you can wrap the functions _open, _read, ... in a class and register the class methods...

<?php
 
class SessionHandler {
  function _open() { ... }
  ...
}
 
$sh = new SessionHandler();
session_set_save_handler(
  array(&$sh, '_open'), 
  ...
);
 
?>

Mon, 01 May 2006 at 09:58:07 GMT Link


3.troehr wrote:

PEAR::HTTP_Session provides an easy and convenient way for storing session data in the database. It has containers for PEAR::DB, PEAR::MDB and PEAR::MDB2.

Mon, 01 May 2006 at 10:13:14 GMT Link


4.Mike Willbanks wrote:

One of my simple questions here, is that certain areas sessions become a little slower while storing them in the database when you don't specifically need database access on every page.

However, performance with a database is hardly seen negatively when the whole site is dynamic. So I suppose there is a trade off here since file access also causes us some time to attempt to open the file if it exists or create it. However there is such software as MCache that can handle this and increase performance.

With this technique what do you see as the drawbacks as many other techniques can be deployed to deal with the other issues that this does solve from a security standpoint?

Mon, 01 May 2006 at 13:06:48 GMT Link


5.Chris Shiflett wrote:

Thanks for the comments and questions. They're all very helpful.

I think I should begin stating a clearer purpose in my articles. :-) My primary intention when writing this article was to share an understanding, not necessarily a particular technique. By keeping it simple, I think people with more sophisticated needs can adapt this code pretty easily, and Tim's comment is a good example. (Thanks, Tim!)

I don't feel qualified to discuss the merits and trade-offs between reading from a database versus a filesystem, but I don't think a "databases are always slower" statement is true. My educated guess is that the network latency is negligible compared to the IO, so both approaches should be on pretty equal footing. More importantly, when using a filesystem, you have to either:

1. Make sure requests from the same client are served by the same server, hindering the benefits of clustering.

2. Introduce a technique, such as NFS, that makes the session data store appear the same on all servers.

With these points in mind, I think storing sessions in a database would be much easier to scale. I'd appreciate any expert input. :-)

Regarding the security aspect, I only see storing sessions in a database as a major advantage when using a shared host, in which case performance should not be an issue anyway. (If it is, time to get a dedicated host.)

Thanks again for the discussion!

Mon, 01 May 2006 at 14:10:42 GMT Link


6.Nate Klaiber wrote:

Chris,

Thanks for the great article! I have been looking to do this for some time now as I am working with a large application and wanted to test storing sessions in a database. We use PDO throughout our site because we are accessing both MySQL and DB2 - the PDO functions fit the bill perfectly.

I am writing the above to be a session class and handle all sessions, replacing the mysql_* calls with PDO. I will have to post it up when I get finished to show the results.

Peace,

Nate

Mon, 01 May 2006 at 15:06:39 GMT Link


7.Nate Klaiber wrote:

I have created something quickly, and posted it to my blog:

http://www.theklaibers.com/2006/05/...ession-handler/

This is implementing what you discuss above, but fitting our needs and our DB connection.

Let me know your thoughts.

Peace,

Nate

Wed, 10 May 2006 at 15:33:51 GMT Link


8.Steve Dickinson wrote:

Hello Chris,

just to point out a small omission from your final examples of the _open and _close routines.

You built up nicely to the use of the global $_sess_db ...but... in the final code example where you add the return values, you suddenly lost the $_sess_db variable from the picture.

Thank you for your article.

--

Steve Dickinson

Fri, 02 Jun 2006 at 14:41:27 GMT Link


9.Chris Shiflett wrote:

Hi Steve,

Thanks very much for pointing out that mistake. It has been corrected.

Wed, 07 Jun 2006 at 00:55:02 GMT Link


10.Oren wrote:

Hello Chris,

I think you should mention 1 more thing to prevent people from getting confused just like what had happened to me.

I know it might sound strange, but when I was just playing with the code for the first time, I didn't call session_start(), and therefore I got an error telling me that the argument supplied to mysql_close() was not a valid one.

You have to call session_start(), because otherwise _open() won't be called and as a result $_sess_db will be set to NULL (not undefined as one might think).

Sun, 09 Jul 2006 at 15:55:53 GMT Link


11.Oren wrote:

Hello again and sorry for the double post...

I forgot to thank you for the great article, so I'm doing it right now.

Also if possible - fix my typo from 'undfined' to 'undefined'.

Thanks,

Oren

Sun, 09 Jul 2006 at 15:59:45 GMT Link


12.Chris Shiflett wrote:

Thanks for taking the time to write, Oren. You're right that I should probably have included an example that uses this session mechanism, including the session_start() call. Hopefully your comment can suffice until I make that improvement. :-)

Thanks again.

Sun, 09 Jul 2006 at 21:13:43 GMT Link


13.Peter wrote:

I too would like to see an example of how this works with session_start. I take it these functions have to be called before session_start at all times.. whether you're creating a session or just reading the contents of one... etc?

Thanks for the tutorial.

Mon, 10 Jul 2006 at 03:43:38 GMT Link


14.Jordan wrote:

I have not gotten a chance to test this (due to lack of example test code lol) so i wanted to know if i still have to call the session_start() on every page like the regular php behavior or can i do it in an include

Mon, 24 Jul 2006 at 12:22:02 GMT Link


15.Oren wrote:

Do it in an include.

Sun, 30 Jul 2006 at 21:10:22 GMT Link


16.James wrote:

I am using this code as it's written above - and it's not throwing any errors when I use it.

But when I modify/create a session variable it writes it all fine to the database, but the pages refuse to output the variable at all.

For example:

signup.php = $_SESSION['e'] = 'This is an error';

Database = 'e|s:16:"This is an error"' etc

mypage.php = <?=nl2br($_SESSION['e'])?>

Looks fine? but there is NO output. Now when I do the following;

mypage.php = <?php $_SESSION['e'] = 'Bla bla'; echo nl2br($_SESSION['e']); ?>

The page outputs "Bla bla" as expected, but the database remains the same as it was ('e|s:16:"This is an error"' etc).

I have no clue what's going on... help? :/

Tue, 01 Aug 2006 at 07:00:04 GMT Link


17.James wrote:

Discovered what the issue was... my other code was overriding the database connection, which was then putting all the session data in the wrong place, hence it couldn't be seen by the session handler.

Wed, 02 Aug 2006 at 01:45:13 GMT Link


18.Chandra wrote:

I just want to know, if we are using REPLACE instead of INSERT, will there be any chance of overwriting the session data of another user? I mean if the php session id generated is same for two sessions, what will happen?

Mon, 07 Aug 2006 at 19:01:28 GMT Link


19.TexasTip wrote:

What I first typed:

I am disappointed in this code. I even bought your book and I still cannot get this to work. I have spent a week trying to do this on my own and I just give up.

You don't give a useful working example. As a newbie, I am completely frustrated. But I am not alone. As I surfed the web looking for a solution, I found many who mimicked your code or one of the others and they too do not work. I also found many people looking for answers and there were none. I just give up. I'm going back to my Rocket Science job and giving this mess back to you code heads.

Signed,

Dead tired. Goodnight - but no best wishes.

What I meant to say:

Chris:

I do not understand how to use this code. Where do you put the session_set_save_handler()? Do you need an include statement for this code on every page that requires sessions data? What would a sess.php page look like?

Cheers.

Mon, 28 Aug 2006 at 01:30:44 GMT Link


20.Joseph Crawford wrote:

Chris,

This is a very good article, Last year after i had the conversation with you about session security i wrote something similar, but never published it and now i cannot find it :)

I am going to sit down one of these days and write it again and publish it on my blog. For now i want to thank you for the knowledge that you passed to me about the security of sessions and how to handle them.

Thanks for posting articles like this.

Fri, 01 Sep 2006 at 14:18:17 GMT Link


21.Finn wrote:

Hi Chris,

great article thanks. I have been using the info you gave to great success, after some initial head scratching.

I do have a question though.

When I store large arrays as session variables in a mysql database it only seems to work to up to a point. Is there an upper limit to the size of a session variable array that can be stored in a mysql text field?

It seems to peak out at about 2000 records in the array, each one storing a jpg file name.

I have tried the same when not storing the session data in the database and it works fine. Thus I am a little confused.

Any ideas?

Thanks again for you great article, perhaps I'll go buy the book now!

Cheers,

Finn.

Wed, 06 Sep 2006 at 15:51:36 GMT Link


22.Niels wrote:

Hi,

@Finn

TEXT is 64KB, so there's your limit. Probably.

I'm currently using LONGTEXT, but I'm having problems when the row (ie. a session in the DB) reaches about 1MB. The integrity seems to go bad, but there's no obvious truncation. When I use files there's no such problem, they can easily go to several MBs.

I'm investigating this problem now.

Tue, 12 Sep 2006 at 15:31:23 GMT Link


23.Niels wrote:

Problem solved - it turned out to be that max_allowed_packet was set too low. This is a Mysql setting that limits the size of SQL statements.

Tue, 12 Sep 2006 at 19:35:41 GMT Link


24.Marcus Bointon wrote:

An alternative is to use a dedicated distributed session handler. A prime example is sharedance, available at http://sharedance.pureftpd.org/ and comes with a ready to roll session handler for PHP 4 and 5.

This is small, simple, extremely fast, and more importantly, takes load away from your DB that has more important things to do. I've seen reports of sharedance handling 12k hits per second!

Sharedance is quite similar to memcached, but it stores to disk rather than RAM, so it guarantees that data will never fall off the end of the cache (Digg uses memcached and deals with that by simply having enormous amounts of cache!).

The only good reason I can think of for using a DB instead is if you want to do searches on session data, for example, to find who is logged in right now. Sharedance (like memcached) only does exact match key searches for speed.

Sun, 24 Sep 2006 at 23:26:14 GMT Link


25.Richard Lynch wrote:

For those asking Chris to provide an example of how to use his code, here is an untested example:

<?php
 
  require 'chris_shiflett_session_code.inc';
  session_start();
  $_SESSION['counter']++;
  echo "You have visited this page $_SESSION[counter] times.";
 
?>

If this looks remarkably similar to the example in the manual for session_start, well, there ya go...

You have to require Chris' code on each page, and do session_start() on each page, so moving session_start() into his file would be a Good Idea.

Mon, 16 Oct 2006 at 19:00:31 GMT Link


26.ryan wrote:

Nice article Chris..exactly what i was looking for..

however i need to clarify some things first..

PHP creates unique session_id when calling session_start().. In the technique you posted,what happens to this session_id?

is it still generated? where is it stored now?

we all now that PHP will attach this session_id to url parameters or cookies to identify a user / maintain a state, if im going to use this approach, how am i gonna maintain "state" or track users if they are logged in or not..

i hope u can provide me answers

THANKS in advance

Fri, 03 Nov 2006 at 04:45:28 GMT Link


27.douglas wrote:

Again Chris, Thanks your time.

Would it be a wise idea to make the "Id" {sessionKey) table "unique", because of possible duplication?

Also to Ryan above, From what I'm seeing, I think the ID is stored into the ID table as the primary key.

Thu, 09 Nov 2006 at 18:16:26 GMT Link


28.Jon R. wrote:

Chris,

Excellent Article. We have found it extremely useful, and were able to implement this code inside of 2 hours. Very well done!

We did hit a few snags and would like to extend our comments to you.

We found that in the _close function, adding a check to make sure of closing the session only if it is open, reduced errors and exception throws. Also in the _read function, confirming that you return a string, makes things smoother.

The biggest recommendations we would make are contained in the _write function. Here we had a good deal of trouble with the MySQL REPLACE Call. This call is a shortcut for a delete and then an insert, the issue we found is that the error handling is different, and in fact worse, then if you were to manually call a delete and then an insert. Basically we got an error if it tried to delete something that was not there. For those new to this, we would recommend explicitly making these calls, instead of relying on the 'magik' of MySQL. The second is much more minor, but would recommend making the accessed date field more human readable with a datetime field or similar. And for security possibly encoding the data field, because it is frightening how much data is human read-able.

Again, Thank you for your post.

It truly saved us a great deal of time.

Thanks!

Mon, 13 Nov 2006 at 17:04:12 GMT Link


29.Gordon JC Pearce wrote:

I've been trying to implement this on a system with two web servers sharing a session for load-balancing. What I am starting to wonder about is how you avoid the possibility of a collision between session IDs. I admit that it's fairly unlikely that two servers will be hit at exactly the same microsecond, but it's possible and could cause a tricky little race condition.

One possible solution would be to append a unique per-server identifier to the session ID, but there doesn't seem to be a clean way to do this (or at least, none that I can see).

Tue, 09 Jan 2007 at 12:06:56 GMT Link


30.equazcion wrote:

Thanks for the article. I think this is the clearest explanation I've seen on the subject.

One thing though:

I don't think it's wise to escape every value before inserting into the database. The session IDs generated by PHP can only contain alphanumeric characters (as far as I know), and the Unix timestamps outputted by time(), as well as the results of our calculations on time(), are never even strings to begin with.

These functions will be called very often within any given script, so in the interest of speed and efficiency, it might be a better idea to forgo the use of mysql_real_escape_string() for all the variables other than $data, since that's the only one that could possibly contain any characters requiring an escape (at least in this particular example).

Sun, 11 Mar 2007 at 18:26:08 GMT Link


31.equazcion wrote:

To Gordon:

If you're using PHP 5, there's a configuration option called session.hash_function. It determines the hashing algorithm PHP will use to generate session IDs. There are two possible settings, MD5 and SHA-1. You could just set one server to use MD5 and the other to use SHA-1. Since they each generate different ID lengths, there would be no chance of a collision.

If you have access to the PHP configuration file you can change the setting there. 0=md5

and 1=sha1. If you don't have access to the config file, you can use ini_set('session.hash_function',[1 or 0]); from within your scripts. Just set the script on one server to 1 and the other to 0.

Sun, 11 Mar 2007 at 18:49:23 GMT Link


32.Deiva wrote:

Hi,

I had tried this and i am facing one annoying problem, that the Write function is called never or something happen in the background which removing the content from the DB and always throw the empty record.

Incase of there is any error in the class file, then the Data is shown in the DB, but not otherwise. I couldn't trace out this peculiar problem and some help would be appreciatable.

Wed, 13 Jun 2007 at 13:31:28 GMT Link


33.Daniel wrote:

Thank you for this very informative article. Hope this isn't too much of dumb question, I have been able to get the suggested code to work and to open a session and store it in the table, but I am having trouble I guess in getting some of the session data when I use the following code to make sure there is a session in progress.

I believe I have to use the id stored in the database either from from id column itself or from the data column and replace $_SESSION['agent'] in the code below with what I get.

But I can't seem to extract it from the page calling the functions, this appears to be a problem with the $record array's scope.

Any help in this matter would be most appreciated.

// If no session value is present, redirect the user.
 
if (!isset($_SESSION['agent']) OR 
 
($_SESSION['agent'] !=
 
md5($_SERVER['HTTP_USER_AGENT'])) ) {
 
// Start defining the URL.
 
$url = 'http://' . $_SERVER['HTTP_HOST'].
 
dirname($_SERVER['PHP_SELF']);
 
// Check for a trailing slash.
 
    if ((substr($url, -1) == '/') OR 
 
(substr($url, -1) == '\\') ) {
        $url = substr ($url, 0, -1); 
 
// Chop off the slash.
 
    }
 
    $url .= '/login.php'; 
 
    header("Location: $url");
 
    exit();
 
}

Sat, 14 Jul 2007 at 14:48:41 GMT Link


34.rezzz wrote:

How would I be able to scale this in order to allow other domains to be able to read session data? For example:

on siteA.com I set a session variable and I want siteB.com to be able to read it.

I'd greatly appreciate any input that you may have on this, thanks.

Mon, 10 Sep 2007 at 19:04:05 GMT Link


35.amagondes wrote:

Hi Chris,

Great article. Thanks very much for posting this. I noticed that if you don't start the session you get a warning: mysql_close etc etc.

I just wrapped an if statement around the mysql_close call and thought it might help someone.

if($_sess_db)
 
{
 
    mysql_close($_sess_db);
 
}

Tue, 18 Sep 2007 at 20:42:59 GMT Link


36.Wes Mahler wrote:

Hey Chris,

And with tokens, if you use the sessions stored in the database, can u still use the token sesssions u mention earlier for CRSF attacks and what not?

Thu, 20 Sep 2007 at 14:32:20 GMT Link


37.John wrote:

Thanks Chris.

I have a question about the _write handler. In my implementation of the custom handler, I chose to use a session table in my main database. The database is instanciated and stored in a global variable.

Everything seems to work well until I get to the execution of _write (after the html stream has terminated). It appears that the global variable value is lost. I am able to get around this by instanciating the database again in the _write function but that is not ideal.

I feel like I have missed something basic. Any help or pointing to other resources is appreciated.

Thank you.

Tue, 30 Oct 2007 at 23:57:32 GMT Link


38.Hasin Hayder wrote:

Thanks Shiflett. It came to real help when I designed the DB based session manager for our framework. Kudos.

Fri, 21 Dec 2007 at 15:28:46 GMT Link


39.Colin McKinnon wrote:

Good article as ever Chris.

But....

Regarding the problem of multiple databases on the MySQL DBMS:

<quote>There are two ways to gracefully avoid this scenario.</quote>

The most elegant solution to this problem (IMHO) is to explicitly state in the query which database the table(s) belong to:

SELECT data 
 
FROM my_session_db.sessions 
 
WHERE id='$id'

That way there's no worrying about reusing existing connections and switching DBs

Tue, 22 Jan 2008 at 13:47:15 GMT Link


40.Willy wrote:

Hello, this is my situation:

I have a Login system.

The session expires after 24 minutes. (1440=>session.gc_maxlifetime)

I need that after 30 minutes of inactivity the user be desactivated, then when logging in again, be redirected to the last page visited. (only the password required this time).

But after 60 minutes of inactivity the user be disconnected at all. (username and pass required to log in again).

The problem is that after 24 min. the session expires.

How can i do?? It works on localhost, but not on-line.

Thanks.

Sat, 23 Feb 2008 at 16:23:12 GMT Link


41.Ed wrote:

This is a very helpful beginners guide.

I have a problem though that I can't figure out. PHP never seems to call the write function. If i call it explicitly with session_write_close(); then it works fine, and the data is put in the database.

I'm just wandering if anyone knows a reason for this.

Thanks again though, this is a very helpful article.

Mon, 14 Apr 2008 at 21:26:27 GMT Link


42.Nathan Bentley wrote:

Hi Chris,

A great tutorial, which should help a lot of people! We implemented something similar to this about a year ago to solve the problem of lost sessions on our load balanced boxes when a box unexpectedly dies (especially important for e-commerce systems).

Anyway, as a way of repaying your kindness in answering my security questions at FOWA last year, I thought I'd share some of the lessons we learned through painful trial and error!

1. Take care in choosing the right type of storage for your data - a TEXT is great for most applications, but if you're lazy, or really do need to persist a large amount of data in the session, try a LONGTEXT. Remember, the larger the field type, the slower the response from your db is likely to be - especially when things get busy. Choose too small a field type and you may find data gets truncated - introducing a world of problems.

2. Consider alternatives to the default mysql MYISAM table types - session data tends to change rapidly, and is almost always written as often as it is read - INNODB is much more suited to these types of transaction and could speed up your queries by orders of magnitude (especially if tuned well).

3. Keep an eye out for the rate of growth if your sessions table - especially if you decide to extend the session.gc_maxlifetime configuration directive. We learnt this the hard way after a client demanded we push the max lifetime to 7 days. After a number of spiders hit the site (generating a new session each page hit) on an already busy site, we stressed our db to failing point - over 120,000,000 rows inserted - causing a massive lag across the sites, and general poor performance from our database server.

Hope that may be of some help to someone - if I remember anymore, I'll be sure to post.

Nathan.

Thu, 24 Jul 2008 at 17:01:13 GMT Link


43.Tim wrote:

Hi Chris

I liked your book and I learned a lot from it.

This example is well illustrated however it lacks any usage examples.

I couldn't get it to work and would appreciate any help.

Wed, 06 Aug 2008 at 12:33:54 GMT Link


44.Tim wrote:

There is a spelling mistake in the Recap at the bottom. batter.

Look forward to your reply.....

Wed, 06 Aug 2008 at 12:46:46 GMT Link


45.greg sidberry wrote:

Thanks Chris,

Used this tutorial as the base for a session class.

Tue, 19 Aug 2008 at 06:38:59 GMT Link


46.vk wrote:

what should be session.save_path value in php.ini?

Sun, 24 Aug 2008 at 12:41:13 GMT Link


47.Seth wrote:

Anyone else have this problem?

I call: $_SESSION['somevar'] = 'blah';

the session data is inserted into the database

then I go to another page to print out the $_SESSION['somevar']

(or just replace the above with echo $_SESSION['somevar']; and reload the page)

and it displays nothing. I looked in the database and the session data field has been errased.

Since start_session() calls these functions in this order:

open

read

clean (if cleaning is being done this call)

write

close

the write function is being called and the data is being updated with nothing since I didn't set $_SESSION['somevar'] to equal anything. Understand what I'm saying?

No one seems to be having this problem. Could someone explain why this is happening and how I could prevent it?

Thanks!

Sat, 30 Aug 2008 at 10:56:52 GMT Link


48.Stelian Mocanita wrote:

I had this issue with CodeIgniter using their session to db yet it was fixed from the configuration of CI as it was related to the time the information was actually kept in the database.

Not sure this helps you at all.

Mon, 03 Nov 2008 at 10:13:06 GMT Link


49.Dave wrote:

Hi Seth, I'm experiencing exactly the same problem as you have. Have you fixed it? How?

Wed, 19 Nov 2008 at 04:11:06 GMT Link


50.Peterson Salamat wrote:

Seth and Dave,

Try calling session_write_close() after giving value to a variable.

I guess this forces DB to commit the transaction.

$_SESSION['somevar'] = 'blah';
 
session_write_close();
echo $_SESSION['somevar'];

Sat, 22 Nov 2008 at 11:05:23 GMT Link


51.Gaetan Dekostere wrote:

Seth,

I encountered the exact same problem as you do. When I try to echo the value I've placed into the session it simply doesn't show up on the next page, and all data is gone from the database. Also, when I place another value in the session on the second page, under another name ofcourse, the previous value is overwritten... It's very frustrating and there seems to be no answer to it...

If any one should be interested in the script: script.

it's straight from the php.net manual modified to work with MySQL, maybe not up to standards but I'm still learning :)

Any help would be appreciated, thx!

Gaetan Dekostere

Tue, 23 Dec 2008 at 23:30:41 GMT Link


52.Gaetan Dekostere wrote:

Succes! I just realized I forgot to read out the data from the database and return it... The following script has the solution: script. In compare to my earlier post, the function read has been altered to return the read out of the data...

Good luck with it!

Great source of information here by the way! Much appreciated...

Gaetan

Wed, 24 Dec 2008 at 12:29:22 GMT Link


53.sean wrote:

Hey all, I created and tested a wrapper class to avoid global variables, make it reusable, etc. Enjoy...

<?php

class MySqlSessionHandler

{

private $mySqlConnection = NULL;

private $myDbHost = '';

private $myDbUser = '';

private $myDbPwd = '';

private $myDbName = '';

function MySqlSessionHandler($db_host, $db_user, $db_pass, $db_name) {

$this->myDbHost = $db_host;

$this->myDbUser = $db_user;

$this->myDbPwd = $db_pass;

$this->myDbName = $db_name;

}

public function _open()

{

$this->mySqlConnection = mysql_connect($this->myDbHost,

$this->myDbUser, $this->myDbPwd);

if ($this->mySqlConnection) {

return mysql_select_db($this->myDbName, $this->mySqlConnection);

}

return FALSE;

}

function _close()

{

return mysql_close($this->mySqlConnection);

}

function _read($id)

{

$id = mysql_real_escape_string($id);

$sql = "SELECT data

FROM sessions

WHERE id = '$id'";

$result = mysql_query($sql, $this->mySqlConnection);

if ($result) {

if (mysql_num_rows($result)) {

$record = mysql_fetch_assoc($result);

return $record['data'];

}

} else

$lastError = mysql_error($this->mySqlConnection);

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);

$sql = "REPLACE

INTO sessions

VALUES ('$id', '$access', '$data')";

return mysql_query($sql, $this->mySqlConnection);

}

function _destroy($id)

{

$id = mysql_real_escape_string($id);

$sql = "DELETE

FROM sessions

WHERE id = '$id'";

return mysql_query($sql, $this->mySqlConnection);

}

function _clean($max)

{

$old = time() - $max;

$old = mysql_real_escape_string($old);

$sql = "DELETE

FROM sessions

WHERE access < '$old'";

return mysql_query($sql, $this->mySqlConnection);

}

}

?>

Thu, 19 Feb 2009 at 02:33:47 GMT Link


54.Sebastian wrote:

I am developing a site that allows different branches to log on. for each user they have a different database. how do i create this on the fly or static for these branches. as in if a branch logs on they are connected to their own database table? is this possible?

Sun, 01 Mar 2009 at 03:39:35 GMT Link


55.Roman wrote:

In my experience, implementing sessions from scratch is much, much simpler than making PHP's built-in implementation to do what you want. All you need to do is to read/wire your cookie and store/load data using some variable. By doing that you get complete control over session behavior, without the need to write wrappers and tinker with ini options.

Mon, 02 Mar 2009 at 16:27:01 GMT Link


56.Alex wrote:

Aren't you forgetting that the session will expire if _write() is never called?

That excludes whole lot of web activity. I can do a lot on a website that may not cause any changes to my session.

Shouldn't _read() update the access datetime as well?

Moreover, shouldn't _read() also check to make sure the session is not expired already?

Wed, 01 Jul 2009 at 18:37:08 GMT Link


57.Toon Yew wrote:

Hi Chris,

I having a problem in function _write($id, $data).

I was expecting the $data is in serialize string format as below.

"member_id|s:7:"TOONYEW";name|s:12:"Ang Toon Yew";browser_type|s:2:"ie";browser_version|s:3:"7.0";"

however, i echo the $data in _write() function, it return me the string not in serialize format as expected. the string display is

"JieBcvTfB2eenSVIogMUXg6BC_rO4gUmlOAoO1PLeoDJA8Tc-gZeHmbMdLQBx59M_jaSg-W_-CX6TZUJFrlcXohBbSwF9YLVu8fsiIef5_metHIlE2F2K1B0APhdMk6A-HPjmbxypbCX9mU2hrUYTredLL31veJg_oiP2rBmFYw."

Do you have any idea why $data is not in serialize string format? Appreciate your help and thanks very much.

Thu, 03 Sep 2009 at 13:24:02 GMT Link


58.ste wrote:

Hi Chris, as Toon Yew I have the same problem: datas stored in mysql table are like crypted...

When I use it on localhost is all perfect, but on production server I can't use the datas stored in...

thanks,

stefano

Tue, 29 Dec 2009 at 17:58:40 GMT Link


59.Eric B wrote:

Hi Chris,

Thanks for this clean, concise article on this topic. You are a life saver!

-E

Fri, 15 Jan 2010 at 18:53:11 GMT Link


60.Ray Low Dake wrote:

Hi Chris,

I enjoyed reading this article as well as your article about session security titled "The Truth About Sessions". Gave me deeper insight into how I should be handling sessions in my web apps. Thanks.

Wed, 24 Feb 2010 at 04:10:02 GMT Link


61.Helper Monkey wrote:

For those who couldn't get it to work, the function names starting with underscore screwed me up for a while. Here's working code: [note: change your mysql connection info and I made the table just session and columns id,access, and data.

session.php

______________________________________________

[code]

<?php

function open()

{

global $_sess_db;

if ($_sess_db = mysql_connect('localhost', 'user', 'password')) {

return mysql_select_db('session', $_sess_db);

}

return FALSE;

}

function close()

{

global $_sess_db;

return mysql_close($_sess_db);

}

function read($id)

{

global $_sess_db;

$id = mysql_real_escape_string($id);

$sql = "SELECT data

FROM session

WHERE id = '$id'";

if ($result = mysql_query($sql, $_sess_db)) {

if (mysql_num_rows($result)) {

$record = mysql_fetch_assoc($result);

return $record['data'];

}

}

return '';

}

function write($id, $data)

{

global $_sess_db;

$access = date('Y-m-d H:i:s'); // REPLACED WITH DATETIME

$id = mysql_real_escape_string($id);

$access = mysql_real_escape_string($access);

$data = mysql_real_escape_string($data);

$sql = "REPLACE

INTO session

VALUES ('$id', '$access', '$data')";

return mysql_query($sql, $_sess_db);

}

function destroy($id)

{

global $_sess_db;

$id = mysql_real_escape_string($id);

$sql = "DELETE

FROM session

WHERE id = '$id'";

return mysql_query($sql, $_sess_db);

}

function clean($max)

{

global $_sess_db;

$old = time() - $max;

$old = mysql_real_escape_string($old);

$sql = "DELETE

FROM session

WHERE access < '$old'";

return mysql_query($sql, $_sess_db);

}

session_set_save_handler('open',

'close',

'read',

'write',

'destroy',

'clean');

// IF YOU'RE USING SSL session_set_cookie_params ( 3600 , '/' , 'domainname.com' , true , true );

session_start();

session_regenerate_id(true); // replaces id with fresh one each time

?>

[/code]

=============================

seshtest.php

_______________________________________

[code]

<?php

require 'session.php';

if (isset($_SESSION['security1']) && isset($_SESSION['userid'])) // VERIFIES UA AND HOST WITH ADDED STRING AGAINST LAST LOGIN

{

$newcrypt = crypt($_SERVER['HTTP_USER_AGENT'].'%$#h${}'.$_SERVER['HTTP_HOST'], $_SESSION['security1']);

if ($_SESSION['security1'] != $newcrypt)

{

echo '<br>'.$_SESSION['security1'].'<br>'.$newcrypt.'<br>user agent mismatch, attempted security breach, force user login';/* Prompt for password */

exit;

}

echo 'passed security checkpoint';

}

else

{

$_SESSION['security1'] = crypt($_SERVER['HTTP_USER_AGENT'].'%$#h${}'.$_SERVER['HTTP_HOST']);

$_SESSION['userid'] = true;

}

?>

[/code]

===================================

As you can see from above it compares the crypt of each subsequent User Agent and Host with a random string FROM the one before. So if it changes it is flagged as a potential hijack. I put in several security measures in there, probably missed some, I'm new to this; but this code works!

It'd be easy to edit this code for an actual login to make the session user id the user id of the person at log in, and send them back if they fail one of the checks.

ENJOY! :D

mysql

___________________________

CREATE TABLE IF NOT EXISTS `session` (

`id` varchar(32) NOT NULL,

`access` datetime NOT NULL,

`data` varchar(300) NOT NULL,

PRIMARY KEY (`id`),

KEY `access` (`access`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Thu, 03 Jun 2010 at 00:08:52 GMT Link


62.abdul wrote:

For those who have problems with the _write function because of the global db object being null after the output stream is closed, you have to call

session_write_close();

at the very end of your script.

Wed, 14 Jul 2010 at 09:24:42 GMT Link


63.Matteo Lovatti wrote:

Hello ! newbie question sorry...

Is normal that PHP still places a PHPSESSID in the browser cookies ?

Thu, 12 Jan 2012 at 20:27:06 GMT Link


64.Xero Harrison wrote:

@matteolovatti that's totally expected behavior. php still "uses" the session in the same way (passing it to and from the user) but it "manages" it differently on the server side, e.g. the db vs the filesys.

Mon, 04 Jun 2012 at 13:21:39 GMT Link


65.Liberica wrote:

You can implement PHP Sessions in a database without writing any code, and it will automatically work for all of your websites on a server.

See here for the full article:

http://www.facebook.com/notes/liber...441907552494395

In summary, PHP has built in support for session handling using SQLite, and all you need to do is to change the php.ini settings.

session.save_handler = sqlite
 
session.save_path = /path/to/phpsession.sdb

To check if you have sqlite configured as a registered save handler in PHP just run the following at the command line:

php -i | grep "Registered save"

If you get back something like this, with sqlite in there:

Registered save handlers => files user sqlite

you are good to go with just the php.ini change, if not, you need to install sqlite (just yum install php-sqlite if you are on CentOS)

Using MySQL as per your article, you could have made the table a MEMORY table so that the sessions are stored in memory instead of disk, which would also improve performance. Similarly, there is a registered save handler for PHP called mm which does much the same thing.

Thu, 07 Jun 2012 at 04:50:47 GMT Link


66.Vincent Wansink wrote:

Beware that storing the session in the database can be a huge performance hit if you're using the session a lot and have high traffic.

It's great if you're only storing authentication and userid for example, but if you're using it to remember state and parameters for every page, then a high number of users will quickly chew up your CPU cycles.

Thu, 27 Oct 2016 at 19:56:27 GMT Link


Hello! What’s your name?

Want to comment? Please connect with Twitter to join the discussion.