如何将 PHP 会话数据保存到数据库而不是文件系统中？

How do I save PHP session data to a database instead of in the file system?

提问by Martin

I have two websites, One is TLS and one is not, both are for the same client but I need the websites to share with each other (and only each other) common data for users, orders, accountsetc.

我有两个网站，一个是 TLS，一个不是，都针对同一个客户，但我需要这些网站彼此共享（并且仅彼此共享）用户、订单、帐户等的公共数据。

This would normally be done with $_SESSIONdata but I obviously these can't work across other sites, and I have found that I can store session data in a database (MySQL) rather than in the file system.

这通常是用$_SESSION数据完成的，但我显然这些不能跨其他站点工作，而且我发现我可以将会话数据存储在数据库 (MySQL) 中而不是文件系统中。

I have dug around and found This useful guideas well as this older but useful guide. I also found this guidewhich has slightly more up to date MySQL.

我四处寻找并找到了这个有用的指南以及这个较旧但 有用的指南。我还发现了这个指南，它有稍微更新的 MySQL。

I have written an interface class but it only partly works, it stores the session data in the database, but it doesn't retrieve it. I have also used the suggested method from the PHP manual.

我编写了一个接口类，但它只能部分工作，它将会话数据存储在数据库中，但不检索它。我还使用了 PHP 手册中建议的方法。

My MySQL(as copied from first couple of the above links):

我的 MySQL（从上面的前几个链接复制）：

CREATE TABLE `sessions` (
  `id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `access` int(10) NOT NULL,
  `data` text COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDb DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Please Note:Before I show you my interface class please know that the Db connetion uses my own custom made interface and that works perfectly, in itself.

The $sessionDBconnectionUrlcontains the Session Database connection details as I am keeping sessions on a seperate Database from the main website contents.

请注意：在我向您展示我的接口类之前，请知道 Db 连接使用我自己定制的接口，并且它本身就可以完美运行。

在$sessionDBconnectionUrl为我保持从主要网站内容的独立数据库会话包含会话数据库连接的详细信息。

My interface class(as based on all the above links)

我的接口类（基于以上所有链接）

<?php
/***
 * Created by PhpStorm.
 ***/
class HafSessionHandler implements SessionHandler {
    private $database = null;

    public function __construct($sessionDBconnectionUrl){

        if(!empty($sessionDBconnectionUrl) && file_exists($_SERVER['DOCUMENT_ROOT'].$sessionDBconnectionUrl)) {
            require_once "class.dataBase.php";
            // Instantiate new Database object
            $this->database = new Database($sessionDBconnectionUrl);
        }
        else {
            error_log("Session could not initialise class.");
        }

    }

    /**
     * Open
     */
    public function open($savepath, $id){
         $openRow = $this->database->getSelect("SELECT `data` FROM sessions WHERE id = ? LIMIT 1",$id);
    if($this->database->selectRowsFoundCounter() == 1){
        // Return True
        return $openRow['data'];
        }
    else {
        // Return False
        return ' ';
    }
    /**
     * Read
     */
    public function read($id)
    {
        // Set query
        $readRow = $this->database->getSelect('SELECT `data` FROM sessions WHERE id = ? LIMIT 1', $id,TRUE);
        if ($this->database->selectRowsFoundCounter() > 0) {
            return $readRow['data'];
        } else {
            error_log("could not read session id ".$id);
            return '';
        }
    }

    /**
     * Write
     */
    public function write($id, $data)
    {
        $access = time();
        // Set query
        $dataReplace[0] = $id;
        $dataReplace[1] = $access;
        $dataReplace[2] = $data;
        if ($this->database->noReturnQuery('REPLACE INTO sessions(id,access,`data`) VALUES (?, ?, ?)', $dataReplace)) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    /**
     * Destroy
     */
    public function destroy($id)
    {
        // Set query
        if ($this->database->noReturnQuery('DELETE * FROM sessions WHERE id = ? ', $id)) {
            return TRUE;
        } else {

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

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

        // Set query
        if ($this->database->noReturnQuery('DELETE * FROM sessions WHERE access < ?', $old)) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function __destruct()
    {
        $this->close();
    }

}

My Test Page(written from scratch!)

我的测试页（从头开始编写！）

<?php
require "class.sessionHandler.inc.php";
$HSH = new HafSessionHandler("connection.session.dbxlink.php");
session_set_save_handler( $HSH, TRUE );
session_start();

print "<p>Hello this is an index page</p>";
$_SESSION['horses'] = "treesx3";
$_SESSION['tiespan'] = (int)$_SESSION['tiespan']+7;

print "<p>There should be some session data in the database now. <a href='index3.php'>link</a></p>";
var_dump($_SESSION);


exit;

Issue:

问题：

The test pages I run save the data to the database ok but they do not seem to retrieve the data,

我运行的测试页将数据保存到数据库中，但它们似乎没有检索到数据，

I have error logging enabled and no PHP errors are reported. No critical MySQL errors are reported.

我启用了错误日志记录，但没有报告任何 PHP 错误。没有报告严重的 MySQL 错误。

Why doesn't it work?

为什么不起作用？

回答by Martin

I have found over the course of several hours debugging that the referenced articles found on numerous Google searches as well as a significant subset of Stack Overflow answers such as here, hereand hereall provide invalid or outdated information.

我在几个小时的调试过程中发现，在众多 Google 搜索中找到的参考文章以及 Stack Overflow 答案的重要子集（例如此处、此处和此处）都提供了无效或过时的信息。

Things that can cause [critical] issues with saving session data to a database:

将会话数据保存到数据库时可能会导致 [严重] 问题的事情：

• While all the examples online state that you can "fill" the session_set_save_handler, none of them state that you must also set the register_shutdown_function('session_write_close')too (reference).

• Several (older) guides refer to an outdated SQL Database structure, and should notbe used. The database structure that you need for saving session data to the database is: id/access/data. That's it. no need for various extra timestamp columns as I've seen on a few "guides" and examples.

  • Several of the older guides also have outdated MySQL syntax such as DELETE * FROM ...

• The class [made in my question] must implementthe SessionHandlerInterface. I have seen guides (referenced above) that give the implementation of sessionHandlerwhich is not a suitable interface. Perhaps previous versions of PHP had a slightly different method (probably <5.4).

• The session class methods mustreturn the values set out by the PHP manual. Again, probably inherited from pre-5.4 PHP but two guides I read stated that class->openreturns the row to be read, whereas the PHP manual statesthat it needs to return trueor falseonly.

• This is the cause of my Original Issue: I was using custom session names (actually id's as session names and session id's are the same thing!) as per this very good StackOverflow postand this was generating a session name that was 128 characters long. As the session name is the sole key that is needed to be cracked to compromise a session and take over with a session hiHymaningthen a longer name/id is a very good thing.

  • But, this caused an issue because MySQL was silently slicing the session iddown to just 32 characters instead of 128, so it was never able to find the session data in the database. This was a completely silent issue (maybe due to my database connection class not throwing warnings of such things). But this is the one to watch out for. If you have any issues with retrieving sessions from a database first check is that the fullsession id can be stored in the field provided.

• 虽然所有在线示例都声明您可以“填充” session_set_save_handler，但它们都没有声明您还必须设置register_shutdown_function('session_write_close')太（参考）。

• 一些（旧的）导游指的是过时的SQL数据库结构，应该不会被使用。数据库结构，你需要的会话数据保存到数据库是：id/ access/ data。就是这样。正如我在一些“指南”和示例中看到的那样，不需要各种额外的时间戳列。

  • 一些较旧的指南也有过时的 MySQL 语法，例如 DELETE * FROM ...

• 类[我的问题作出]必须实现的SessionHandlerInterface。我已经看到指南（上面提到的）给出sessionHandler了不合适的接口的实现。也许以前版本的 PHP 有一个稍微不同的方法（可能 <5.4）。

• 会话类方法必须返回 PHP 手册中规定的值。同样，可能继承自 5.4 之前的 PHP，但我阅读的两个指南指出class->open返回要读取的行，而PHP 手册指出它需要返回true或false仅返回 。

• 这是我的原始问题的原因：根据这篇非常好的 StackOverflow 帖子，我使用自定义会话名称（实际上 id 作为会话名称和会话 id是一回事！），这生成了一个 128 个字符长的会话名称。由于会话名称是破解会话并接管会话劫持所需的唯一密钥，因此更长的名称/ID 是一件非常好的事情。

  • 但是，这导致了一个问题，因为MySQL 正在悄悄地将会话 ID 切成32 个字符而不是 128 个字符，因此它永远无法在数据库中找到会话数据。这是一个完全无声的问题（可能是由于我的数据库连接类没有抛出此类警告）。但这是需要注意的。如果您在从数据库检索会话时遇到任何问题，请首先检查完整的会话 ID 是否可以存储在提供的字段中。

So with all that out of the way there are some extra details to add as well:

因此，除此之外，还需要添加一些额外的细节：

The PHP manual page (linked above) shows an unsuitable pile of lines for a class object:

PHP 手册页（上面链接）显示了一堆不适合类对象的行：

$handler = new MySessionHandler();
session_set_save_handler($handler, true);
session_start();

$handler = new MySessionHandler();
session_set_save_handler($handler, true);
session_start();

Whereas it works just as well if you put this in the class constructor:

而如果你把它放在类构造函数中它也能正常工作：

class MySessionHandler implements SessionHandlerInterface {

    private $database = null;

public function __construct(){

    $this->database = new Database(whatever);

    // 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")
        );
    register_shutdown_function('session_write_close');
    session_start();
    }
...
}

Thismeans that to then start a session on your output page all you need is:

这意味着要在输出页面上启动会话，您只需要：

<?php
require "path/to/sessionhandler.class.php"; 
new MySessionHandler();

//Bang session has been setup and started and works

---

---

For reference the complete Session communication class is as follows, this works with PHP 5.6 (and probably 7 but not tested on 7 yet)

作为参考，完整的 Session 通信类如下，这适用于 PHP 5.6（可能是 7，但尚未在 7 上测试）

<?php
/***
 * Created by PhpStorm.
 ***/
class MySessionHandler implements SessionHandlerInterface {
    private $database = null;

    public function __construct($sessionDBconnectionUrl){
        /***
         * Just setting up my own database connection. Use yours as you need.
         ***/ 

            require_once "class.database.include.php";
            $this->database = new DatabaseObject($sessionDBconnectionUrl);

        // 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")
        );
        register_shutdown_function('session_write_close');
        session_start();
    }

    /**
     * Open
     */
    public function open($savepath, $id){
        // If successful
        $this->database->getSelect("SELECT `data` FROM sessions WHERE id = ? LIMIT 1",$id,TRUE);
        if($this->database->selectRowsFoundCounter() == 1){
            // Return True
            return true;
        }
        // Return False
        return false;
    }
    /**
     * Read
     */
    public function read($id)
    {
        // Set query
        $readRow = $this->database->getSelect('SELECT `data` FROM sessions WHERE id = ? LIMIT 1', $id,TRUE);
        if ($this->database->selectRowsFoundCounter() > 0) {
            return $readRow['data'];
        } else {
            return '';
        }
    }

    /**
     * Write
     */
    public function write($id, $data)
    {
        // Create time stamp
        $access = time();

        // Set query
        $dataReplace[0] = $id;
        $dataReplace[1] = $access;
        $dataReplace[2] = $data;
        if ($this->database->noReturnQuery('REPLACE INTO sessions(id,access,`data`) VALUES (?, ?, ?)', $dataReplace)) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * Destroy
     */
    public function destroy($id)
    {
        // Set query
        if ($this->database->noReturnQuery('DELETE FROM sessions WHERE id = ? LIMIT 1', $id)) {
            return true;
        } else {

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

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

        if ($this->database->noReturnQuery('DELETE FROM sessions WHERE access < ?', $old)) {
            return true;
        } else {
            return false;
        }
    }

    public function __destruct()
    {
        $this->close();
    }

}

Usage: As shown just above the class code text.

用法：如类代码文本正上方所示。