Skip to content

Commit 00d707f

Browse files
TobionTobion
committed
[HttpFoundation] use different approach for duplicate keys in postgres, fix merge for sqlsrv and oracle
1 parent cff4105 commit 00d707f

File tree

2 files changed

+43
-35
lines changed

2 files changed

+43
-35
lines changed

‎src/Symfony/Component/HttpFoundation/Session/Storage/Handler/PdoSessionHandler.php

+39-31
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,13 @@
1212
namespace Symfony\Component\HttpFoundation\Session\Storage\Handler;
1313

1414
/**
15-
* PdoSessionHandler.
15+
* Session handler using a PDO connection to read and write data.
16+
*
17+
* Session data is a binary string that can contain non-printable characters like the null byte.
18+
* For this reason this handler base64 encodes the data to be able to save it in a character column.
19+
*
20+
* This version of the PdoSessionHandler does NOT implement locking. So concurrent requests to the
21+
* same session can result in data loss due to race conditions.
1622
*
1723
* @author Fabien Potencier <fabien@symfony.com>
1824
* @author Michael Williams <michael.williams@funsational.com>
@@ -164,13 +170,10 @@ public function read($sessionId)
164170
*/
165171
public function write($sessionId, $data)
166172
{
167-
// Session data can contain non binary safe characters so we need to encode it.
168173
$encoded = base64_encode($data);
169174

170-
// We use a MERGE SQL query when supported by the database.
171-
// Otherwise we have to use a transactional DELETE followed by INSERT to prevent duplicate entries under high concurrency.
172-
173175
try {
176+
// We use a MERGE SQL query when supported by the database.
174177
$mergeSql = $this->getMergeSql();
175178

176179
if (null !== $mergeSql) {
@@ -183,28 +186,33 @@ public function write($sessionId, $data)
183186
return true;
184187
}
185188

186-
$this->pdo->beginTransaction();
187-
188-
try {
189-
$deleteStmt = $this->pdo->prepare(
190-
"DELETE FROM $this->table WHERE $this->idCol = :id"
191-
);
192-
$deleteStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR);
193-
$deleteStmt->execute();
194-
195-
$insertStmt = $this->pdo->prepare(
196-
"INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time)"
197-
);
198-
$insertStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR);
199-
$insertStmt->bindParam(':data', $encoded, \PDO::PARAM_STR);
200-
$insertStmt->bindValue(':time', time(), \PDO::PARAM_INT);
201-
$insertStmt->execute();
202-
203-
$this->pdo->commit();
204-
} catch (\PDOException $e) {
205-
$this->pdo->rollback();
206-
207-
throw $e;
189+
$updateStmt = $this->pdo->prepare(
190+
"UPDATE $this->table SET $this->dataCol = :data, $this->timeCol = :time WHERE $this->idCol = :id"
191+
);
192+
$updateStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR);
193+
$updateStmt->bindParam(':data', $encoded, \PDO::PARAM_STR);
194+
$updateStmt->bindValue(':time', time(), \PDO::PARAM_INT);
195+
$updateStmt->execute();
196+
197+
// Since Postgres does not support MERGE (without custom stored procedure), we have to use this approach
198+
// that can result in duplicate key errors when the same session is written simultaneously. We can just
199+
// ignore such an error because either the data did not change anyway or which data is written does not
200+
// matter as proper locking to serialize access to a session is not implemented.
201+
if (!$updateStmt->rowCount()) {
202+
try {
203+
$insertStmt = $this->pdo->prepare(
204+
"INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time)"
205+
);
206+
$insertStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR);
207+
$insertStmt->bindParam(':data', $encoded, \PDO::PARAM_STR);
208+
$insertStmt->bindValue(':time', time(), \PDO::PARAM_INT);
209+
$insertStmt->execute();
210+
} catch (\PDOException $e) {
211+
// ignore unique violation SQLSTATE
212+
if ('23505' !== $e->getCode()) {
213+
throw $e;
214+
}
215+
}
208216
}
209217
} catch (\PDOException $e) {
210218
throw new \RuntimeException(sprintf('PDOException was thrown when trying to write the session data: %s', $e->getMessage()), 0, $e);
@@ -230,12 +238,12 @@ private function getMergeSql()
230238
// DUAL is Oracle specific dummy table
231239
return "MERGE INTO $this->table USING DUAL ON ($this->idCol = :id) " .
232240
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time) " .
233-
"WHEN MATCHED THEN UPDATE SET $this->dataCol = :data";
234-
case 'sqlsrv':
235-
// MS SQL Server requires MERGE be terminated by semicolon
241+
"WHEN MATCHED THEN UPDATE SET $this->dataCol = :data, $this->timeCol = :time";
242+
case 'sqlsrv' && version_compare($this->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '10', '>='):
243+
// MERGE is only available since SQL Server 2008 and must be terminated by semicolon
236244
return "MERGE INTO $this->table USING (SELECT 'x' AS dummy) AS src ON ($this->idCol = :id) " .
237245
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time) " .
238-
"WHEN MATCHED THEN UPDATE SET $this->dataCol = :data;";
246+
"WHEN MATCHED THEN UPDATE SET $this->dataCol = :data, $this->timeCol = :time;";
239247
case 'sqlite':
240248
return "INSERT OR REPLACE INTO $this->table ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time)";
241249
}

‎src/Symfony/Component/HttpFoundation/Tests/Session/Storage/Handler/PdoSessionHandlerTest.php

+4-4
Original file line numberDiff line numberDiff line change
@@ -23,9 +23,9 @@ protected function setUp()
2323
$this->markTestSkipped('This test requires SQLite support in your environment');
2424
}
2525

26-
$this->pdo = new \PDO("sqlite::memory:");
26+
$this->pdo = new \PDO('sqlite::memory:');
2727
$this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
28-
$sql = "CREATE TABLE sessions (sess_id VARCHAR(255) PRIMARY KEY, sess_data TEXT, sess_time INTEGER)";
28+
$sql = 'CREATE TABLE sessions (sess_id VARCHAR(128) PRIMARY KEY, sess_data TEXT, sess_time INTEGER)';
2929
$this->pdo->exec($sql);
3030
}
3131

@@ -37,9 +37,9 @@ public function testIncompleteOptions()
3737

3838
public function testWrongPdoErrMode()
3939
{
40-
$pdo = new \PDO("sqlite::memory:");
40+
$pdo = new \PDO('sqlite::memory:');
4141
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_SILENT);
42-
$pdo->exec("CREATE TABLE sessions (sess_id VARCHAR(255) PRIMARY KEY, sess_data TEXT, sess_time INTEGER)");
42+
$pdo->exec('CREATE TABLE sessions (sess_id VARCHAR(128) PRIMARY KEY, sess_data TEXT, sess_time INTEGER)');
4343

4444
$this->setExpectedException('InvalidArgumentException');
4545
$storage = new PdoSessionHandler($pdo, array('db_table' => 'sessions'));

0 commit comments

Comments
 (0)