如何将自定义字段添加到会话表 [英] How to add a custom field to the session table
问题描述
我当前正在使用Symfony 2.1.8和内置的PdoSessionHandler.
I'm currently using Symfony 2.1.8 and the built-in PdoSessionHandler.
我想在会话表中添加一个user_id字段，以标识该会话属于哪个(已登录)用户.我的想法是，我可以强迫用户重新登录以破坏其会话.就我而言，如果更新了用户的权限，就会发生这种情况.
I want to add a user_id field in the session table to identify to which (logged-in) user the session belongs. The idea is that I can force the user to log back in destroying his session. In my case it will happen if the privileges of the user are updated.
我查看了由于这些愚蠢的私有变量而无法扩展的内置PdoSessionHandler.
I had a look to the build-in PdoSessionHandler that you can't extends because of those silly private variables.
所以我尝试创建一个新的(复制/粘贴)并添加我的列user_id.
现在，如果用户未登录(匿名用户)，则此列可以为空.
So I've tried created a new one (copy/paste) and add my column user_id.
Now this column can be null if the user is not logged in (anonymous users).
因此，我想在处理程序的write方法中编写此user_id.该用户已经存储在$data中，因此我认为我可以检查该用户是否存在，抓住它的id并将其添加到插入/更新查询中.
So I want to write this user_id in the write method of the handler. The user is already stored in the $data so I was thinking that I could check if this user exists, grab its id and add it in the insert / update query.
问题是$data是经过编码的-我猜是由session_encode()进行的-因此我不确定这是否是处理我的新字段的最佳地点，但与此同时我看不到任何地方否则，我可以这样做，因为我需要更新此MySQL查询以插入新字段的值.
The problem is that $data is encoded - I guess by session_encode() - so I'm not sure anymore this is the best place ever to handle my new field, but at the same time I can't see anywhere else I could do it as I need to update this MySQL query to insert the value of the new field.
所以我的问题是:哪里是处理此附加字段的最佳地点?以及如何设置此user_id值?
另一方面，真正令人烦恼的是，每次登录或注销时，Symfony都在创建一个新的cookie.因此，数据库最终会有很多记录，而没有任何记录(它始终是同一用户).为什么Symfony一直不使用相同的cookie值?
On another note, somehing really annoying is that Symfony is creating a new cookie each time I'm logging in or out. So the database ends up with lots of records for nothing (it's always the same user). Why Symfony is not using the same cookie value all the time?
推荐答案
您可以扩展PdoSessionHandler(> = Symfony 2.1的解决方案):
You could extend PdoSessionHandler (solution for >=Symfony 2.1):
namespace Acme\DemoBundle\HttpFoundation\Session\Storage\Handler;
use Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler;
use Symfony\Component\Security\Core\SecurityContext;
class UserIdPdoSessionHandler extends PdoSessionHandler
{
/**
* @var \PDO PDO instance.
*/
private $pdo;
/**
* @var array Database options.
*/
private $dbOptions;
/**
* @var SecurityContext
*/
private $context;
public function __construct(\PDO $pdo, array $dbOptions = array(), SecurityContext $context)
{
$this->pdo = $pdo;
$this->dbOptions = array_merge(
array('db_user_id_col' => 'user_id'),
$dbOptions
);
$this->context = $context;
parent::__construct($pdo, $dbOptions);
}
public function read($id)
{
// get table/columns
$dbTable = $this->dbOptions['db_table'];
$dbDataCol = $this->dbOptions['db_data_col'];
$dbIdCol = $this->dbOptions['db_id_col'];
try {
$sql = "SELECT $dbDataCol FROM $dbTable WHERE $dbIdCol = :id";
$stmt = $this->pdo->prepare($sql);
$stmt->bindParam(':id', $id, \PDO::PARAM_STR);
$stmt->execute();
// it is recommended to use fetchAll so that PDO can close the DB cursor
// we anyway expect either no rows, or one row with one column. fetchColumn, seems to be buggy #4777
$sessionRows = $stmt->fetchAll(\PDO::FETCH_NUM);
if (count($sessionRows) == 1) {
return base64_decode($sessionRows[0][0]);
}
// session does not exist, create it
$this->createNewSession($id);
return '';
} catch (\PDOException $e) {
throw new \RuntimeException(sprintf('PDOException was thrown when trying to read the session data: %s', $e->getMessage()), 0, $e);
}
}
/**
* {@inheritDoc}
*/
public function write($id, $data)
{
// get table/column
$dbTable = $this->dbOptions['db_table'];
$dbDataCol = $this->dbOptions['db_data_col'];
$dbIdCol = $this->dbOptions['db_id_col'];
$dbTimeCol = $this->dbOptions['db_time_col'];
$dbUserIdCol = $this->dbOptions['db_user_id_col'];
//session data can contain non binary safe characters so we need to encode it
$encoded = base64_encode($data);
$userId = $this->context->isGranted('IS_AUTHENTICATED_REMEMBERED') ?
$this->context->getToken()->getUser()->getId() :
null
;
try {
$driver = $this->pdo->getAttribute(\PDO::ATTR_DRIVER_NAME);
if ('mysql' === $driver) {
// MySQL would report $stmt->rowCount() = 0 on UPDATE when the data is left unchanged
// it could result in calling createNewSession() whereas the session already exists in
// the DB which would fail as the id is unique
$stmt = $this->pdo->prepare(
"INSERT INTO $dbTable ($dbIdCol, $dbDataCol, $dbTimeCol, $dbUserIdCol) VALUES (:id, :data, :time, :user_id) " .
"ON DUPLICATE KEY UPDATE $dbDataCol = VALUES($dbDataCol), $dbTimeCol = VALUES($dbTimeCol)"
);
$stmt->bindParam(':id', $id, \PDO::PARAM_STR);
$stmt->bindParam(':data', $encoded, \PDO::PARAM_STR);
$stmt->bindValue(':time', time(), \PDO::PARAM_INT);
$stmt->bindParam(':user_id', $userId, \PDO::PARAM_STR);
$stmt->execute();
} elseif ('oci' === $driver) {
$stmt = $this->pdo->prepare("MERGE INTO $dbTable USING DUAL ON($dbIdCol = :id) ".
"WHEN NOT MATCHED THEN INSERT ($dbIdCol, $dbDataCol, $dbTimeCol, $dbUserIdCol) VALUES (:id, :data, sysdate, :user_id) " .
"WHEN MATCHED THEN UPDATE SET $dbDataCol = :data WHERE $dbIdCol = :id");
$stmt->bindParam(':id', $id, \PDO::PARAM_STR);
$stmt->bindParam(':data', $encoded, \PDO::PARAM_STR);
$stmt->bindParam(':user_id', $userId, \PDO::PARAM_STR);
$stmt->execute();
} else {
$stmt = $this->pdo->prepare("UPDATE $dbTable SET $dbDataCol = :data, $dbTimeCol = :time WHERE $dbIdCol = :id");
$stmt->bindParam(':id', $id, \PDO::PARAM_STR);
$stmt->bindParam(':data', $encoded, \PDO::PARAM_STR);
$stmt->bindValue(':time', time(), \PDO::PARAM_INT);
$stmt->execute();
if (!$stmt->rowCount()) {
// No session exists in the database to update. This happens when we have called
// session_regenerate_id()
$this->createNewSession($id, $data);
}
}
} catch (\PDOException $e) {
throw new \RuntimeException(sprintf('PDOException was thrown when trying to write the session data: %s', $e->getMessage()), 0, $e);
}
return true;
}
private function createNewSession($id, $data = '')
{
// get table/column
$dbTable = $this->dbOptions['db_table'];
$dbDataCol = $this->dbOptions['db_data_col'];
$dbIdCol = $this->dbOptions['db_id_col'];
$dbTimeCol = $this->dbOptions['db_time_col'];
$dbUserIdCol = $this->dbOptions['db_user_id_col'];
$userId = $this->context->isGranted('IS_AUTHENTICATED_REMEMBERED') ?
$this->context->getToken()->getUser()->getId() :
null
;
$sql = "INSERT INTO $dbTable ($dbIdCol, $dbDataCol, $dbTimeCol, $dbUserIdCol) VALUES (:id, :data, :time, :user_id)";
//session data can contain non binary safe characters so we need to encode it
$encoded = base64_encode($data);
$stmt = $this->pdo->prepare($sql);
$stmt->bindParam(':id', $id, \PDO::PARAM_STR);
$stmt->bindParam(':data', $encoded, \PDO::PARAM_STR);
$stmt->bindValue(':time', time(), \PDO::PARAM_INT);
$stmt->bindParam(':user_id', $userId, \PDO::PARAM_STR);
$stmt->execute();
return true;
}
}
并配置会话以使用它:
# config.yml
framework:
session:
# ...
handler_id: session.storage.custom
parameters:
pdo.db_options:
db_table: session
db_id_col: session_id
db_data_col: session_value
db_time_col: session_time
db_user_id_col: session_user_id
services:
pdo:
class: PDO
arguments:
dsn: "mysql:host=%database_host%;dbname=%database_name%"
user: "%database_user%"
password: "%database_password%"
session.storage.custom:
class: Acme\DemoBundle\HttpFoundation\Session\Storage\Handler\UserIdPdoSessionHandler
arguments: [ @pdo, "%pdo.db_options%", @security.context ]
这篇关于如何将自定义字段添加到会话表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
