Monday 27 August 2018

Using MySQL to store session data

For heavily loaded projects, the use of files to store session files becomes invalid.
In this article, we'll look at the use of the MySql database for storing session data .
  1. all session settings are made before the start of the session, so you need to cancel the autostart sessions:
    ini_set ('session.auto_start', '0');
  2. As standard, PHP stores sessions in files to define its own session handlers:
    ini_set ('session.save_handler', 'user');
    'session.save_handler' can have three values:
    • files is the default value, PHP uses standard session processing functions, sessions are stored in files, it is necessary to define ini_set ('session.save_path', path); space for storing session files;
    • mm - PHP uses standard session processing functions, sessions are stored in memory;
    • user - allows you to override the standard functions of processing sessions, and accordingly in these functions indicate where we will store the session and how we will process them.
  3. now define the functions of processing sessions:
    session_set_save_handler (
  "sess_open",
  "sess_close",
  "sess_read",
  "sess_write",
  "sess_destroy",
  "sess_gc");
    consider each:
    • sess_open - opens the session. The function creates a unique session ID. Requires for its work two parameters 'session.save_path' and 'session.name'. Because we store sessions in the database, then we do not need 'session.save_path', but 'session.name' can be defined instead of the standard one - 'PHPSESSID'. So we add to the configuration:
      ini_set ('session.name', 'SID');
    • sess_close - closes session (without destroying session variables).
    • sess_read - reads data from temporary storage, in our case from the database. Requires a session ID, which from the session table must be read and written to the session from the session table.
    • sess_write - writes data to temporary storage. Requires session ID, and writes everything from session to database.
    • sess_destroy destroys the session. Requires session ID. To delete the information, the following function exists.
    • sess_gc is just a garbage collector. Requires a retention period of sessions in temporary storage in seconds specified in the 'session.gc_maxlifetime' parameter (30 minutes by default). Define it, and determine the lifetime of the session cookie:
      ini_set ('session.gc_maxlifetime', XXX);
ini_set ('session.cookie_lifetime', YYY);
      'sess_gc' is not always called when the session is initialized, there is one more setting that controls this parameter - 'session.gc_probability'. This parameter determines the probability of starting 'sess_gc' in percent, respectively, valid values ​​of 1-100. The default value is 1%. Those. this means that with a probability of 1%, when you open a new page of the site, the session table will be cleaned, in my experience the optimal value is 5-10. We add to the configuration:
      ini_set ('session.gc_probability', 5);
Table structure:
CREATE TABLE "session" (
    session_id character varying (32) NOT NULL,
    session_user_id integer DEFAULT 0 NOT NULL,
    session_counter integer DEFAULT 0 NOT NULL,
    session_ip character varying (16),
    session_agent character varying (255),
    session_last integer DEFAULT 0 NOT NULL,
    session_created integer DEFAULT 0 NOT NULL,
    session_data text
);

CREATE TABLE "user" (
    user_id character varying (32) NOT NULL,
    user_ip character varying (16),
    user_agent character varying (255),
    / * there may be other fields * /
);
session.php - Storage of session data in the MySQL table and the function of working with sessions in PHP.
A global $ user [] array is used with fields from the database tables session, user. 
It is assumed that the connection with MySQL is already installed and defined in the global variable $ db. 
The current information is stored in the global variable $ session.
<?
$SERVER_NAME=$_SERVER['HTTP_HOST'];
$SERVER_NAME=preg_replace('/^http:\/\//', '', $SERVER_NAME);
$SERVER_NAME=preg_replace('/^www\./', '', $SERVER_NAME);
define("CookiePath","/");
define("CookieDomain",$SERVER_NAME);    //".".$SERVER_NAME    
define("live_sess_time","1000");

ini_set('session.auto_start', '0'); 

ini_set('session.use_cookies', '1');

ini_set('session.use_trans_sid', '0'); 

ini_set('session.save_handler', 'user');

ini_set('session.name', 'SID'); 

ini_set('session.gc_maxlifetime', '1800'); 

//ini_set ('session.cookie_lifetime', '2000'); 


session_set_cookie_params (0, CookiePath, CookieDomain, false);


ini_set('session.gc_probability', 10);

function sess_open ($save_path, $session_name) {return true;}

function sess_close () {return true;}

function sess_read ($session_id) {
    global $db, $user, $session;
    if (strlen ($session_id) != 32) {
        error_log ("sess_read(): Invalid SessionID = ".$session_id);
        return '';
    }
    $sql = "SELECT `session_id`, `session_user_id`, `session_counter`, `session_ip`, `session_agent`, `session_data`
        FROM `session`
        WHERE `session_id` = '".$db->sql_escape($session_id)."' AND `session_last` > '".(time() - live_sess_time)."'";
    $result = $db->sql_query ($sql);
    if ($db->sql_numrows ($result) == 1) {
        $session = $db->sql_fetchrow ($result);
        if ($session AND $session['session_ip'] == $user['user_ip'] AND $session['session_agent'] == $user['user_agent']) {
           
            $sql = "SELECT * FROM `user`
                WHERE `user_id` = '".$db->sql_escape($session['session_user_id'])."' LIMIT 1";
            $result = $db->sql_query ($sql);
            if(!$result) {
                $result = $db->sql_error ($result);
                error_log ('sess_read(): Failed to read user info - '.$result['message']);
                return '';
            }
            else {
                $user_data = $db->sql_fetchrow ($result);
                $user = array_merge ($user, $user_data, $session); 
                unset($user['session_data']);
                return $session['session_data'];
            }
        } else {
            if (isset($_REQUEST[session_name()])) sess_destroy($_REQUEST[session_name()]);
            return '';
        }
    } elseif (!$result) {
        $result = $db->sql_error ($result);
        error_log ('sess_read(): Failed to read sessions - '.$result['message']);
        return '';
    } else {
        $session = NULL;
        if (isset($_REQUEST[session_name()])) sess_destroy($_REQUEST[session_name()]);
        return '';
    }
}

function sess_write ($session_id, $session_data) {
   global $db, $user, $session;
   if (strlen ($session_id) != 32) {
      error_log ('sess_write(): Invalid Session ID = '.$session_id);
      return false;
   }
   if (4294967295 < strlen($session_data)) {
      error_log ('sess_write(): Session data too large. '.$session_id.'(max. 4294967295) -> '.strlen($session_data));
            if (isset($_REQUEST[session_name()])) sess_destroy($_REQUEST[session_name()]);
      return false;
   }
   if ($session AND $session['session_ip'] != $user['user_ip']){
            if (isset($_REQUEST[session_name()])) sess_destroy($_REQUEST[session_name()]);
      return false;
   }
   if ($session) {
      $sql = "UPDATE `session`
        SET `session_user_id` = '".intval ($session['session_user_id'])."',
            `session_last` = '".time ()."',
            `session_counter` = '".intval(++$session['session_counter'])."',
            `session_data` = '".$db->sql_escape($session_data)."'
        WHERE `session_id` = '".$db->sql_escape($session_id)."' LIMIT 1";
   } else {
      $sql = "INSERT INTO `session` (`session_id`, `session_created`, `session_last`,
                    `session_ip`, `session_agent`, `session_data`)
        VALUES ('".$db->sql_escape ($session_id)."', ".time().", ".time().",
            '".$db->sql_escape ($user['user_ip'])."',
            '".$db->sql_escape ($user['user_agent'])."',
            '".$db->sql_escape ($session_data)."')";
   }
   $result = $db->sql_query ($sql);
   if (!$result) {
      $result = $db->sql_error ($result);
      error_log ('sess_write(): Failed to INSERT/UPDATE session. '.$result['message']."<br> Query: ".$sql);
      return false;
   }
   return true;
}

function sess_destroy ($session_id) {
   global $db;
   $sql = "DELETE FROM `session`
        WHERE `session_id` = '".$db->sql_escape ($session_id)."'";
   $result = $db->sql_query ($sql);
   if (!$result) {
      $result = $db->sql_error ($result);
      error_log ('sess_destory(): Failed to DELETE session. '.$result['message']);
      return false;
   }
   return true;
}

function sess_gc ($sess_gc_maxlifetime) {
   global $db;
   $sql = "DELETE FROM `session` WHERE `session_last` < '".(time () - $sess_gc_maxlifetime)."'";
   $result = $db->sql_query ($sql);
   if (!$result) {
      $result = $db->sql_error ($result);
      error_log ('sess_gc(): Failed to DELETE old sessions.'.$result['message']);
      return false;
   }
   $sql = "OPTIMIZE TABLE `session` ";
   $result = $db->sql_query ($sql);
   if (!$result) {
      $result = $db->sql_error ($result);
      error_log ('sess_gc(): Failed to OPTIMIZE sessionstable.'.$result['message']);
      return false;
   }
   return true;
}

session_set_save_handler ("sess_open", "sess_close", "sess_read", "sess_write", "sess_destroy", "sess_gc");
register_shutdown_function ('session_write_close');
session_start ();
?>
At the same time in the database there are only entries about those sessions that are now active, expired sessions are deleted, changing the value of 'session.gc_probability' you can find the one that fits your website. Thus, adjust the size of the table of sessions.

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)