Last Updated:

Storing a session in a database | PHP

Despite the fact that the standard way of storing session data is suitable for most of the tasks that php developers may encounter, sometimes you still have to look for an alternative. The most common reasons for storing session data in a database are:

  • Your site is divided into several parts that are stored on several servers and to be sure that the session works correctly you need to store session data in a database common to all servers.

  • Your website is on the same server as other sites and you want to avoid the security issues that come with it.

  • Your site has very large requests for speed and therefore you need a faster way to store session data. There are many techniques to speed up reading from the database that can be used in this case.

Fortunately, PHP makes this task simple.

Session data storage.

 

Before you can store session data in a database you need to create a table. Here's an example for MySQL:

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

);

This is the simplest example, you can modify the table as you like to store, for example, the time of session creation or a unique identifier to increase security.

Once you've created a table, let's see how to use it.

 

session_set_save_handler()

PHP has a built-in function that allows you to change the default session mechanism. With its help, you can set your functions for various tasks of the session mechanism. This article is organized according to the list of functions that you will need to write. For each function, I'll show you an example (using MySQL) and explain everything in detail.

 

The session_set_save_handler() function takes six parameters, each of which is the name of the function you will have to write. These functions will be responsible for the following tasks:

  1. Opening a Session Data Storage Location

  2. Closing the Session Data Storage Location

  3. Reading Session Data

  4. Recording Session Data

  5. Destruction of all session data

    Deleting previous session data

For convenience, in this article we will adopt the following names for these functions:

session_set_save_handler ('_open', '_close', '_read', '_write', '_destroy', '_clean');

this line should be there before calling session_start(), but you can declare the functions anywhere.

The advantage of this method is that in the code you can continue to do everything the same as with the standard mechanism for storing the data of the session. The $_SESSION array is still the same and behaves exactly the same way, php still generates and transmits the session key. All you have to do is add one line above.

It is also very important to understand what exactly the functions do, in case of unforeseen errors.

_open() and _close()

These functions are interconnected, they are used to open and close the session data storage space respectively. If you store session data in the file system, these functions open and close files (most likely you will need a global file control variable so that other functions can use it).

Because you are using a database, _open() and _close() can be as simple as:

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

This option has a drawback: when the database management variable is not created for the mysql_select_db() and mysql_query() functions, MySQL uses the last connection, that is, if somewhere in the site code you use mysql_select_db to select another database, writing to the session may not happen, because the last connection will be to another database. Such an error will be very difficult to track, despite this, it occurs quite often.

There are two ways to avoid this error: you can use a separate connection to record session data, or you can get in the habit of using mysql_select_db() before each function that needs a specific database, this includes both the session storage mechanism and the site code. This is all, of course, provided that you use more than one database in your code.

In this article, I will use the first method – to use a separate connection for the session mechanism. Let's call this variable $_sess_db, and then the code of our functions will look like this:

<?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 use an underscore at the beginning of a variable name to indicate that the variable should not be changed anywhere in the code that follows.

I want to make one more change. Most built-in php functions return TRUE if there are no errors and FALSE if an error occurred during execution. We can force our functions to do the same:

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

Let's move on to the next function.

_read()

This function is called when it is necessary to write data to a session. This occurs immediately after calling _open(), which in turn is invoked using the session_start().

PHP sends to the _read() of the session identity:

<?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 ' ';
}
?>

 

The handler that PHP uses to serialize data is specified session.serialize_handler the php configuration parameter. By default, it is set to php.

_write()

The _write() function is called when it is necessary to write data to a session, usually at the very end of the script.

PHP transmits the session ID and session data. You don't have to worry about the format of the data, because php serializes this data, they are a string. However, you need to make sure that the string does not contain dangerous elements before using it in the query.

 

<?php
function _write($id, $data)
{
global $_sess_db;
$acess = 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);
}
?>


I used REPLACE because we do the same thing by using INSERT, but in cases where the session identifier in transit already exists, REPLACE will delete the old entry before writing a new one. Thus, there is no need to check for the presence of an entry in the table with a translatable session identifier. Note that REPLACE works in MySQL, but other types of databases may not have this command.
_destroy()
The _destroy() function is called when PHP needs to destroy all data in the current session. The most obvious example is when the session_destroy() is invoked. PHP passes the session ID to the function.


<?php
function _destroy($id)


{

global $_sess_db;
$id = mysql_real_escape_string($id);
$sql = "DELETE FROM sessions WHERE id = '$id'",
return $result = mysql_query($sql, $_sess_db);
}
?>


The _destroy() function erases only the record in the database, without affecting the $_SESSION array.
_clean()

The _clean() function is called periodically to delete old entries in the session table. More precisely, how often this function is called is set by two php configuration parameters: session.gc_probability and session.gc_divisor. Their default values are 1 and 100 respectively, which means that the probability of calling the _clean() function per session is 1 divided by 1000 = 0.1%.

Because the _write() function writes to the table for each record the exact time of the last access to the access column, this can be used to determine which records to delete. PHP transmits the maximum number of seconds after which the session is 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 $result = mysql_query($sql, $_sess_db);
}
?>

 

The number of seconds that PHP passes to this function is the value of the parameter session.gc_maxlifetime from the PHP configuration. You can change this value as needed.

 

Now you have all the necessary tools to change the mechanism for storing session data. Also, I hope you have gained a better understanding of the purpose of these six features and are ready to solve any problems that may appear in the process.