symfony · fabpot · Apr 18, 2014 · Apr 16, 2014 · Apr 16, 2014 · Tobion
diff --git a/src/Symfony/Bridge/Doctrine/HttpFoundation/DbalSessionHandler.php b/src/Symfony/Bridge/Doctrine/HttpFoundation/DbalSessionHandler.php
@@ -11,37 +11,64 @@
 
 namespace Symfony\Bridge\Doctrine\HttpFoundation;
 
+use Doctrine\DBAL\Connection;
+use Doctrine\DBAL\Driver\Connection as DriverConnection;
+use Doctrine\DBAL\Driver\Mysqli\MysqliConnection;
+use Doctrine\DBAL\Driver\OCI8\OCI8Connection;
+use Doctrine\DBAL\Driver\PDOConnection;
+use Doctrine\DBAL\Driver\SQLSrv\SQLSrvConnection;
 use Doctrine\DBAL\Platforms\MySqlPlatform;
-use Doctrine\DBAL\Driver\Connection;
+use Doctrine\DBAL\Platforms\OraclePlatform;
+use Doctrine\DBAL\Platforms\SqlitePlatform;
+use Doctrine\DBAL\Platforms\SQLServerPlatform;
 
 /**
  * DBAL based session storage.
  *
+ * This implementation is very similar to Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler
+ * but uses the Doctrine driver connection interface and thus also works with non-PDO-based drivers like mysqli and OCI8.
+ *
  * @author Fabien Potencier <fabien@symfony.com>
  * @author Johannes M. Schmitt <schmittjoh@gmail.com>
+ * @author Tobias Schultze <http://tobion.de>
  */
 class DbalSessionHandler implements \SessionHandlerInterface
 {
     /**
-     * @var Connection
+     * @var DriverConnection
      */
     private $con;
 
     /**
      * @var string
      */
-    private $tableName;
+    private $table;
+
+    /**
+     * @var string Column for session id
+     */
+    private $idCol = 'sess_id';
+
+    /**
+     * @var string Column for session data
+     */
+    private $dataCol = 'sess_data';
+
+    /**
+     * @var string Column for timestamp
+     */
+    private $timeCol = 'sess_time';
 
     /**
      * Constructor.
      *
-     * @param Connection $con       An instance of Connection.
-     * @param string     $tableName Table name.
+     * @param DriverConnection $con       A driver connection, preferably a wrapper Doctrine\DBAL\Connection for lazy connections
+     * @param string           $tableName Table name
      */
-    public function __construct(Connection $con, $tableName = 'sessions')
+    public function __construct(DriverConnection $con, $tableName = 'sessions')
     {
         $this->con = $con;
-        $this->tableName = $tableName;
+        $this->table = $tableName;
     }
 
     /**
@@ -57,7 +84,6 @@ public function open($path = null, $name = null)
      */
     public function close()
     {
-        // do nothing
         return true;
     }
 
@@ -66,12 +92,15 @@ public function close()
      */
     public function destroy($id)
     {
+        // delete the record associated with this id
+        $sql = "DELETE FROM $this->table WHERE $this->idCol = :id";
+
         try {
-            $this->con->executeQuery("DELETE FROM {$this->tableName} WHERE sess_id = :id", array(
-                'id' => $id,
-            ));
-        } catch (\PDOException $e) {
-            throw new \RuntimeException(sprintf('PDOException was thrown when trying to manipulate session data: %s', $e->getMessage()), 0, $e);
+            $stmt = $this->con->prepare($sql);
+            $stmt->bindParam(':id', $id, \PDO::PARAM_STR);
+            $stmt->execute();
+        } catch (\Exception $e) {
+            throw new \RuntimeException(sprintf('Exception was thrown when trying to delete a session: %s', $e->getMessage()), 0, $e);
         }
 
         return true;
@@ -82,12 +111,15 @@ public function destroy($id)
      */
     public function gc($lifetime)
     {
+        // delete the session records that have expired
+        $sql = "DELETE FROM $this->table WHERE $this->timeCol < :time";
+
         try {
-            $this->con->executeQuery("DELETE FROM {$this->tableName} WHERE sess_time < :time", array(
-                'time' => time() - $lifetime,
-            ));
-        } catch (\PDOException $e) {
-            throw new \RuntimeException(sprintf('PDOException was thrown when trying to manipulate session data: %s', $e->getMessage()), 0, $e);
+            $stmt = $this->con->prepare($sql);
+            $stmt->bindValue(':time', time() - $lifetime, \PDO::PARAM_INT);
+            $stmt->execute();
+        } catch (\Exception $e) {
+            throw new \RuntimeException(sprintf('Exception was thrown when trying to delete expired sessions: %s', $e->getMessage()), 0, $e);
         }
 
         return true;
@@ -98,21 +130,23 @@ public function gc($lifetime)
      */
     public function read($id)
     {
+        $sql = "SELECT $this->dataCol FROM $this->table WHERE $this->idCol = :id";
+
         try {
-            $data = $this->con->executeQuery("SELECT sess_data FROM {$this->tableName} WHERE sess_id = :id", array(
-                'id' => $id,
-            ))->fetchColumn();
+            $stmt = $this->con->prepare($sql);
+            $stmt->bindParam(':id', $id, \PDO::PARAM_STR);
+            $stmt->execute();
 
-            if (false !== $data) {
-                return base64_decode($data);
-            }
+            // We use fetchAll instead of fetchColumn to make sure the DB cursor gets closed
+            $sessionRows = $stmt->fetchAll(\PDO::FETCH_NUM);
 
-            // session does not exist, create it
-            $this->createNewSession($id);
+            if ($sessionRows) {
+                return base64_decode($sessionRows[0][0]);
+            }
 
             return '';
-        } catch (\PDOException $e) {
-            throw new \RuntimeException(sprintf('PDOException was thrown when trying to read the session data: %s', $e->getMessage()), 0, $e);
+        } catch (\Exception $e) {
+            throw new \RuntimeException(sprintf('Exception was thrown when trying to read the session data: %s', $e->getMessage()), 0, $e);
         }
     }
 
@@ -121,54 +155,88 @@ public function read($id)
      */
     public function write($id, $data)
     {
-        $platform = $this->con->getDatabasePlatform();
-
-        // this should maybe be abstracted in Doctrine DBAL
-        if ($platform instanceof MySqlPlatform) {
-            $sql = "INSERT INTO {$this->tableName} (sess_id, sess_data, sess_time) VALUES (%1\$s, %2\$s, %3\$d) "
-                  ."ON DUPLICATE KEY UPDATE sess_data = VALUES(sess_data), sess_time = CASE WHEN sess_time = %3\$d THEN (VALUES(sess_time) + 1) ELSE VALUES(sess_time) END";
-        } else {
-            $sql = "UPDATE {$this->tableName} SET sess_data = %2\$s, sess_time = %3\$d WHERE sess_id = %1\$s";
-        }
+        // Session data can contain non binary safe characters so we need to encode it.
+        $encoded = base64_encode($data);
+
+        // We use a MERGE SQL query when supported by the database.
+        // Otherwise we have to use a transactional DELETE followed by INSERT to prevent duplicate entries under high concurrency.
 
         try {
-            $rowCount = $this->con->exec(sprintf(
-                $sql,
-                $this->con->quote($id),
-                //session data can contain non binary safe characters so we need to encode it
-                $this->con->quote(base64_encode($data)),
-                time()
-            ));
-
-            if (!$rowCount) {
-                // No session exists in the database to update. This happens when we have called
-                // session_regenerate_id()
-                $this->createNewSession($id, $data);
+            $mergeSql = $this->getMergeSql();
+
+            if (null !== $mergeSql) {
+                $mergeStmt = $this->con->prepare($mergeSql);
+                $mergeStmt->bindParam(':id', $id, \PDO::PARAM_STR);
+                $mergeStmt->bindParam(':data', $encoded, \PDO::PARAM_STR);
+                $mergeStmt->bindValue(':time', time(), \PDO::PARAM_INT);
+                $mergeStmt->execute();
+
+                return true;
+            }
+
+            $this->con->beginTransaction();
+
+            try {
+                $deleteStmt = $this->con->prepare(
+                    "DELETE FROM $this->table WHERE $this->idCol = :id"
+                );
+                $deleteStmt->bindParam(':id', $id, \PDO::PARAM_STR);
+                $deleteStmt->execute();
+
+                $insertStmt = $this->con->prepare(
+                    "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time)"
+                );
+                $insertStmt->bindParam(':id', $id, \PDO::PARAM_STR);
+                $insertStmt->bindParam(':data', $encoded, \PDO::PARAM_STR);
+                $insertStmt->bindValue(':time', time(), \PDO::PARAM_INT);
+                $insertStmt->execute();
+
+                $this->con->commit();
+            } catch (\Exception $e) {
+                $this->con->rollback();
+
+                throw $e;
             }
-        } catch (\PDOException $e) {
-            throw new \RuntimeException(sprintf('PDOException was thrown when trying to write the session data: %s', $e->getMessage()), 0, $e);
+        } catch (\Exception $e) {
+            throw new \RuntimeException(sprintf('Exception was thrown when trying to write the session data: %s', $e->getMessage()), 0, $e);
         }
 
         return true;
     }
 
-   /**
-    * Creates a new session with the given $id and $data
-    *
-    * @param string $id
-    * @param string $data
-    *
-    * @return bool
-    */
-    private function createNewSession($id, $data = '')
+    /**
+     * Returns a merge/upsert (i.e. insert or update) SQL query when supported by the database.
+     *
+     * @return string|null The SQL string or null when not supported
+     */
+    private function getMergeSql()
     {
-        $this->con->exec(sprintf("INSERT INTO {$this->tableName} (sess_id, sess_data, sess_time) VALUES (%s, %s, %d)",
-            $this->con->quote($id),
-            //session data can contain non binary safe characters so we need to encode it
-            $this->con->quote(base64_encode($data)),
-            time()
-        ));
+        $platform = $pdoDriver = null;
 
-        return true;
+        if ($this->con instanceof Connection) {
+            $platform = $this->con->getDatabasePlatform();
+        } elseif ($this->con instanceof PDOConnection) {
+            $pdoDriver = $this->con->getAttribute(\PDO::ATTR_DRIVER_NAME);
+        }
+
+        switch (true) {
+            case $this->con instanceof MysqliConnection || $platform instanceof MySqlPlatform || 'mysql' === $pdoDriver:
+                return "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time) " .
+                    "ON DUPLICATE KEY UPDATE $this->dataCol = VALUES($this->dataCol), $this->timeCol = VALUES($this->timeCol)";
+            case $this->con instanceof OCI8Connection || $platform instanceof OraclePlatform || 'oci' === $pdoDriver:
+                // DUAL is Oracle specific dummy table
+                return "MERGE INTO $this->table USING DUAL ON ($this->idCol = :id) " .
+                    "WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time) " .
+                    "WHEN MATCHED THEN UPDATE SET $this->dataCol = :data";
+            case $this->con instanceof SQLSrvConnection || $platform instanceof SQLServerPlatform || 'sqlsrv' === $pdoDriver:
+                // MS SQL Server requires MERGE be terminated by semicolon
+                return "MERGE INTO $this->table USING (SELECT 'x' AS dummy) AS src ON ($this->idCol = :id) " .
+                    "WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time) " .
+                    "WHEN MATCHED THEN UPDATE SET $this->dataCol = :data;";
+            case $platform instanceof SqlitePlatform || 'sqlite' === $pdoDriver:
+                return "INSERT OR REPLACE INTO $this->table ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time)";
+        }
+
+        return null;
     }
 }
diff --git a/src/Symfony/Component/HttpFoundation/Resources/stubs/SessionHandlerInterface.php b/src/Symfony/Component/HttpFoundation/Resources/stubs/SessionHandlerInterface.php
@@ -10,9 +10,7 @@
  */
 
 /**
- * SessionHandlerInterface
- *
- * Provides forward compatibility with PHP 5.4
+ * SessionHandlerInterface for PHP < 5.4
  *
  * Extensive documentation can be found at php.net, see links:
  *
@@ -25,76 +23,68 @@
 interface SessionHandlerInterface
 {
     /**
-     * Open session.
+     * Re-initializes existing session, or creates a new one.
      *
      * @see http://php.net/sessionhandlerinterface.open
      *
-     * @param string $savePath    Save path.
-     * @param string $sessionName Session Name.
-     *
-     * @throws \RuntimeException If something goes wrong starting the session.
+     * @param string $savePath    Save path
+     * @param string $sessionName Session name, see http://php.net/function.session-name.php
      *
-     * @return bool
+     * @return bool true on success, false on failure
      */
     public function open($savePath, $sessionName);
 
     /**
-     * Close session.
+     * Closes the current session.
      *
      * @see http://php.net/sessionhandlerinterface.close
      *
-     * @return bool
+     * @return bool true on success, false on failure
      */
     public function close();
 
     /**
-     * Read session.
-     *
-     * @param string $sessionId
+     * Reads the session data.
      *
      * @see http://php.net/sessionhandlerinterface.read
      *
-     * @throws \RuntimeException On fatal error but not "record not found".
+     * @param string $sessionId Session ID, see http://php.net/function.session-id
      *
-     * @return string String as stored in persistent storage or empty string in all other cases.
+     * @return string Same session data as passed in write() or empty string when non-existent or on failure
      */
     public function read($sessionId);
 
     /**
-     * Commit session to storage.
+     * Writes the session data to the storage.
      *
      * @see http://php.net/sessionhandlerinterface.write
      *
-     * @param string $sessionId Session ID.
-     * @param string $data      Session serialized data to save.
+     * @param string $sessionId Session ID , see http://php.net/function.session-id
+     * @param string $data      Serialized session data to save
      *
-     * @return bool
+     * @return bool true on success, false on failure
      */
     public function write($sessionId, $data);
 
     /**
-     * Destroys this session.
+     * Destroys a session.
      *
      * @see http://php.net/sessionhandlerinterface.destroy
      *
-     * @param string $sessionId Session ID.
+     * @param string $sessionId Session ID, see http://php.net/function.session-id
      *
-     * @throws \RuntimeException On fatal error.
-     *
-     * @return bool
+     * @return bool true on success, false on failure
      */
     public function destroy($sessionId);
 
     /**
-     * Garbage collection for storage.
+     * Cleans up expired sessions (garbage collection).
      *
      * @see http://php.net/sessionhandlerinterface.gc
      *
-     * @param int     $lifetime Max lifetime in seconds to keep sessions stored.
-     *
-     * @throws \RuntimeException On fatal error.
+     * @param string|int $maxlifetime Sessions that have not updated for the last maxlifetime seconds will be removed
      *
-     * @return bool
+     * @return bool true on success, false on failure
      */
-    public function gc($lifetime);
+    public function gc($maxlifetime);
 }