How to add a custom field to the session table

Question

I'm currently using Symfony 2.1.8 and the built-in PdoSessionHandler.

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.

I had a look to the build-in PdoSessionHandler that you can't extends because of those silly private variables.

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).

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.

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.

So my question is: where is the best place to handle this additional field? And how to set this user_id value?

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?

Answer 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;
    }
}

And configure session to use it:

# 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 ]

Answer 2

I'm not sure modifying session is a good idea, you can store session id(s) in user entity instead and delete those when needed. This way you can for example ensure only user can be logged in with only one session at a time.

The easiest way to accomplish it would be to use login listener.

Add sessionId field to user entity (or document or whatever persistance you use):

// Acme/UserBundle/Entity/User.php
namespace Acme\UserBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * ORM\Entity
 * @ORM\Table(name="fos_user")
 */
class User {
  // ...

  /**
   * @ORM\Column(name="session_id", type="string")
   */
  private $sessionId;

  public function getSessionId() {
    return $this->sessionId;
  }

  public function setSessionId($sessionId = null) {
    $this->sessionId = $sessionId;
    return $this;
  }
}

And add a listener:

namespace Dbla\UserBundle\Listener;

use Symfony\Component\HttpFoundation\Session;
use Symfony\Component\Security\Http\Event\InteractiveLoginEvent;

class LoginListener
{
  protected $doctrine;

  protected $session;

  public function __construct(Session $session, Registry $doctrine)
  {
    $this->doctrine = $doctrine;
    $this->session = $session;
  }

  public function onLogin(InteractiveLoginEvent $event)
  {
    $user = $event->getAuthenticationToken()->getUser();

    if ($user) {
      $user->setSessionId($this->session->getId());
      $em = $this->doctrine->getEntityManager();
      $em->persist($user);
      $em->flush();
    }
  }
}

And add it as a service:

services:
  acme_user.listsner.login:
    class: Acme\UserBundle\Listener\LoginListener
    arguments: [ @session, @doctrine ]
    tags:
      - { name: kernel.event_listener, event: security.interactive_login, method: onLogin }

Then you can simply remove session for users:

$users = []; // ... get user list
$sessionIds = array_map(function($user) {
  return $user->getId();
});
if (count(sessionIds) > 0) {
  $sql = 'DELETE FROM session WHERE session_id IN (' . implode($sessionIds, ',') . ')';
  $entityManager->getConnection()->exec($sql);
}
foreach ($users as $user) {
  $user->setSessionId(null);
  $entityManager->persist($user);
}
$entityManager->flush();