Article
The PHP Anthology Volume 2, Chapter 1 - Access Control
How do I store sessions in MySQL?
As discussed earlier in this chapter, the default behavior of sessions in PHP on the server side is to create a temporary file in which session data is stored. This is usually kept in the temporary directory of the operating system and, as such, presents a security risk to your applications, especially if you are using a shared server. It's a good idea to be aware of the alternative; using a custom session handler provides an alternative data store which is fully under your control.
In this solution, I'll provide you with a custom session handler that will store all session data in MySQL in a manner that will require no modification of any code. The custom handler code is a port of the PostgreSQL Session Handler for PHP, written by Jon Parise, and is supplied with the code for this chapter, in the subdirectory mysql_session_handler.
To install it, the first thing you need to do is modify the file mysql_session_handler.php, changing the lines that identify your database connection, shown here in bold:
Example 1.54. mysql_session_handler/mysql_session_handler.php (excerpt)
function mysql_session_open($save_path, $session_name)
{
global $mysql_session_handle;
/* See: http://www.php.net/manual/function.mysql-connect.php */
$host = 'localhost';
$user = 'harryf';
$pass = 'secret';
/* See: http://www.php.net/manual/function.mysql-select-db.php*/
$dbas = 'sitepoint';
$mysql_session_handle = mysql_connect($host, $user, $pass);
mysql_select_db($dbas, $mysql_session_handle);
return $mysql_session_handle;
}
Once you've done that, the next step is to override some php.ini settings with a .htaccess file that contains the following. Note that the PHP configuration setting auto_prepend_file can only be set in php.ini or by a .htaccess file. If you can't use .htaccess in that way, you'll need to include mysql_session_handler.php in all your scripts, which is best done by adding it to a script which is already included by all others, such as that where you keep central configuration information. The configuration values session.save_handler and session_save_path can both be controlled with the ini_set function (see Appendix A, PHP Configuration for more details). These will also need to be included in every script. In this case, your .htaccess file must contain the following:
Example 1.55. mysql_session_handler/.htaccess
php_value session.save_handler 'user'
php_value session.save_path 'php_sessions'
php_value auto_prepend_file '/path/to/mysql_session_handler.php'
The first line tells PHP that rather than using its default session handling mechanism, it will be told how to handle sessions by your own code. The value session.save_path refers to the name of the table where sessions are stored. The last line tells PHP to execute the mysql_session_handler.php file every time any other script is executed.
Finally, you need to create a table called php_sessions with the following structure:
CREATE TABLE php_sessions (
session_id VARCHAR(40) NOT NULL DEFAULT '',
last_active INT(11) NOT NULL DEFAULT '0',
data TEXT NOT NULL,
PRIMARY KEY (session_id)
)
With the .htaccess file placed in your Web root directory, all PHP scripts using sessions will store the session data in MySQL rather than in files.
The data stored by sessions has a format that looks like this:
myVar|s:11:"Hello World";ip_address|s:9:"127.0.0.1";
Variables themselves are separated by either semicolons or {} in the case of arrays, while within each variable, the name and value is separated by |. The value itself is stored in a serialized form (see the PHP Manual for details).
So the above example corresponds to two variables:
$myVar = "Hello World";
$ip_address = "127.0.0.1";
Being able to decode this information with PHP will become important in the next solution.
How do I track who is online?
You may have seen applications such as vBulletin and phpBB, which let visitors see how many users are online (and sometimes, which users are online) at a given moment. Now that we have an authentication system, and a custom session handler that stores sessions in MySQL, implementing "Who is Online?" functionality is a breeze (well, almost)!
The first thing we need is (you guessed it!) a class that we can use to read and interpret stored session data from MySQL.
The constructor for the class simply initializes an array. This is used as a "first in first out" (FIFO) queue to which raw session data is added, and from which objects are returned along with properties that correspond to the variables stored in a given session.
Example 1.56. Session/SessionAnalyzer.php (in SPLIB) (excerpt)
/**
* Session Analyzer
* Examines serialized session data (as it appears on the file
* system or in a database) and builds objects into which it
* places the data stored in the session
* <code>
* $sa = new SessionAnalyzer();
* $sa->addSession($some_serialized_session_data);
* $sessionStore = $sa->fetch();
* </code>
* @package SPLIB
* @access public
*/
class SessionAnalyzer {
/**
* A list of sessions, their data held in SessionStore objects
* @var array
*/
var $sessions;
/**
* SessionAnalyzer constructor
* @param object instance of database connection
* @access public
*/
function SessionAnalyzer()
{
$this->sessions = array();
}
The addSession method is used to put raw session data in the queue. It calls the private parseSession method, which is where the analysis of the raw session data actually occurs. The fetchSessions method allows us to get objects back from the queue, the properties of the object being the variables stored in the raw session data. Note that the objects returned are not related to the Session class we worked with earlier in this chapter. They are simply data containers—objects of class SessionStore (below)—and have no methods.
Example 1.57. Session/SessionAnalyzer.php (in SPLIB) (excerpt)
/**
* Gathers the sessions into a local array for analysis
* @param string raw serialized session data to parse
* @return void
* @access public
*/
function addSession($rawData)
{
$this->sessions[] = $this->parseSession($rawData);
}
/**
* Iteraters over the SessionStore array
* @return SessionStore
* @access public
*/
function fetch()
{
$session = each($this->sessions);
if ($session) {
return $session['value'];
} else {
reset($this->sessions);
return FALSE;
}
}
I'll leave the parseSession method to your imagination (or you can look at the code archive), as it has to do some serious string manipulation to interpret the session data correctly.
One word of warning. If you're storing objects of your own classes in sessions, the class file needs to be included before you use SessionAnalyzer, or you'll receive PHP errors about undeclared classes.
For your information, here's the SessionStore class, which acts as the container for parsed session data:
Example 1.58. Session/SessionAnalyzer.php (in SPLIB) (excerpt)
/**
* SessionStore
* Container class in which to place unserialized session data
* @package SPLIB
* @access public
*/
class SessionStore {}
Now that we're capable of analyzing sessions, here's a simple script that counts the number of users online. It assumes we're using the MySQL session handler you saw in the previous solution:
Example 1.59. 15.php
<?php
// Include MySQL class
require_once 'Database/MySQL.php';
// Include SessionAnalyzer class
require_once 'Session/SessionAnalyzer.php';
$host = 'localhost'; // Hostname of MySQL server
$dbUser = 'harryf'; // Username for MySQL
$dbPass = 'secret'; // Password for user
$dbName = 'sitepoint'; // Database name
// Instantiate MySQL connection
$db = &new MySQL($host, $dbUser, $dbPass, $dbName);
// Instantiate the SessionAnalyzer class
$sAnalyzer = &new SessionAnalyzer();
$sql = "SELECT data FROM php_sessions";
$result = $db->query($sql);
while ($row = $result->fetch()) {
// Add the raw session data
$sAnalyzer->addSession($row['data']);
}
// Initialize variables for results of session analysis
$guests = 0;
$members = '';
// Loop through the queue of parsed sessions
while ($sessionStore = $sAnalyzer->fetch()) {
if (isset($sessionStore->login)) {
$members .= $sessionStore->login . ' ';
} else {
$guests++;
}
}
// Format the output nicely
echo 'There are currently ' . $guests . ' guests online<br />';
echo 'Members online: ' . $members;
?>
The display looks like this:
There are currently 6 guests online
Members online: HarryF, BillG
I confess—I faked the number of users online for the purposes of having something to show you! If my private development PC was getting unknown visitors, I'd be worried.
"Who is Online?" functionality is more than just a nice gimmick. If you plan to add any kind of real time chat system to your site, "Who is Online?" is essential for allowing people to meet up. It also provides a user administration "snap shot" of what's happening on your site, particularly if your authentication system has just sent you an SMS telling you someone is trying to break in.
Further Reading
- The WWW Security FAQ This is essential reading!
- HTTP Header Reference This summary of all HTTP headers is one for the bookmarks…
- Apache HTTP Authentication with PHP This tutorial takes a look at the essentials of HTTP authentication with PHP.
- Session Fixation Vulnerability in Web-based Applications This paper examines a session hijacking strategy where an intruder effectively hijacks the session before it's even started. The PHP Authentication class developed in this chapter should prevent this from happening to your site's users.
- Managing Users with PHP Sessions and MySQL This article introduces the principles of user authentication with sessions.
- Write Secure Scripts with PHP 4.2! Here's a tutorial that explains the importance of writing scripts with
register_globalsswitched off. - PHP and the OWASP Top Ten Security Vulnerabilities This article provides a summary of recent findings from Open Web Application Security Project, described in PHP terms. It's required reading!
- Custom Session Handlers in PHP4 This handy tutorial does a good job of explaining how to build custom session handlers. Be warned, though, that there have been mixed reports about the final code developed for this tutorial; in some environments it seems to cause various miscellaneous or intermittent errors.
Look out for more chapters from The PHP Anthology on SitePoint in coming weeks! If you can't wait, download the sample chapters, or order your very own copy now!