8000 bug #18501 [HttpFoundation] changed MERGE queries (hjkl) · symfony/symfony@6a8a113 · GitHub
[go: up one dir, main page]
Skip to content

Commit 6a8a113

Browse files
fabpotfabpot
committed
bug #18501 [HttpFoundation] changed MERGE queries (hjkl)
This PR was merged into the 2.7 branch. Discussion ---------- [HttpFoundation] changed MERGE queries | Q | A | ------------- | --- | Branch? | 2.7 | Bug fix? | yes | New feature? | no | BC breaks? | no | Deprecations? | no | Tests pass? | yes | Fixed tickets | #17284 | License | MIT | Doc PR | N/A Changed the MERGE queries for Oracle and SQL Server to use question mark parameter markers so they work with emulation disabled or enabled - fixes #17284 Commits ------- ebf3a2f Fixed oci and sqlsrv merge queries when emulation is disabled - fixes #17284
2 parents 41fd5a1 + ebf3a2f commit 6a8a113

File tree

1 file changed

+47
-20
lines changed

1 file changed

+47
-20
lines changed

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

Lines changed: 47 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -325,14 +325,8 @@ public function write($sessionId, $data)
325325

326326
try {
327327
// We use a single MERGE SQL query when supported by the database.
328-
$mergeSql = $this->getMergeSql();
329-
330-
if (null !== $mergeSql) {
331-
$mergeStmt = $this->pdo->prepare($mergeSql);
332-
$mergeStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR);
333-
$mergeStmt->bindParam(':data', $data, \PDO::PARAM_LOB);
334-
$mergeStmt->bindParam(':lifetime', $maxlifetime, \PDO::PARAM_INT);
335-
$mergeStmt->bindValue(':time', time(), \PDO::PARAM_INT);
328+
$mergeStmt = $this->getMergeStatement($sessionId, $data, $maxlifetime);
329+
if (null !== $mergeStmt) {
336330
$mergeStmt->execute();
337331

338332
return true;
@@ -653,32 +647,65 @@ private function getSelectSql()
653647
}
654648

655649
/**
656-
* Returns a merge/upsert (i.e. insert or update) SQL query when supported by the database for writing session data.
650+
* Returns a merge/upsert (i.e. insert or update) statement when supported by the database for writing session data.
657651
*
658-
* @return string|null The SQL string or null when not supported
652+
* @param string $sessionId Session ID
653+
* @param string $data Encoded session data
654+
* @param int $maxlifetime session.gc_maxlifetime
655+
*
656+
* @return \PDOStatement|null The merge statement or null when not supported
659657
*/
660-
private function getMergeSql()
658+
private function getMergeStatement($sessionId, $data, $maxlifetime)
661659
{
660+
$mergeSql = null;
662661
switch (true) {
663662
case 'mysql' === $this->driver:
664-
return "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
663+
$mergeSql = "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
665664
"ON DUPLICATE KEY UPDATE $this->dataCol = VALUES($this->dataCol), $this->lifetimeCol = VALUES($this->lifetimeCol), $this->timeCol = VALUES($this->timeCol)";
665+
break;
666666
case 'oci' === $this->driver:
667667
// DUAL is Oracle specific dummy table
668-
return "MERGE INTO $this->table USING DUAL ON ($this->idCol = :id) ".
669-
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
670-
"WHEN MATCHED THEN UPDATE SET $this->dataCol = :data, $this->lifetimeCol = :lifetime, $this->timeCol = :time";
668+
$mergeSql = "MERGE INTO $this->table USING DUAL ON ($this->idCol = ?) ".
669+
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (?, ?, ?, ?) ".
670+
"WHEN MATCHED THEN UPDATE SET $this->dataCol = ?, $this->lifetimeCol = ?, $this->timeCol = ?";
671+
break;
671672
case 'sqlsrv' === $this->driver && version_compare($this->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '10', '>='):
672673
// MERGE is only available since SQL Server 2008 and must be terminated by semicolon
673674
// It also requires HOLDLOCK according to http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx
674-
return "MERGE INTO $this->table WITH (HOLDLOCK) USING (SELECT 1 AS dummy) AS src ON ($this->idCol = :id) ".
675-
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
676-
"WHEN MATCHED THEN UPDATE SET $this->dataCol = :data, $this->lifetimeCol = :lifetime, $this->timeCol = :time;";
675+
$mergeSql = "MERGE INTO $this->table WITH (HOLDLOCK) USING (SELECT 1 AS dummy) AS src ON ($this->idCol = ?) ".
676+
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (?, ?, ?, ?) ".
677+
"WHEN MATCHED THEN UPDATE SET $this->dataCol = ?, $this->lifetimeCol = ?, $this->timeCol = ?;";
678+
break;
677679
case 'sqlite' === $this->driver:
678-
return "INSERT OR REPLACE INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time)";
680+
$mergeSql = "INSERT OR REPLACE INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time)";
681+
break;
679682
case 'pgsql' === $this->driver && version_compare($this->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '9.5', '>='):
680-
return "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
683+
$mergeSql = "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
681684
"ON CONFLICT ($this->idCol) DO UPDATE SET ($this->dataCol, $this->lifetimeCol, $this->timeCol) = (EXCLUDED.$this->dataCol, EXCLUDED.$this->lifetimeCol, EXCLUDED.$this->timeCol)";
685+
break;
686+
}
687+
688+
if (null !== $mergeSql) {
689+
$mergeStmt = $this->pdo->prepare($mergeSql);
690+
691+
if ('sqlsrv' === $this->driver || 'oci' === $this->driver) {
692+
$mergeStmt->bindParam(1, $sessionId, \PDO::PARAM_STR);
693+
$mergeStmt->bindParam(2, $sessionId, \PDO::PARAM_STR);
694+
$mergeStmt->bindParam(3, $data, \PDO::PARAM_LOB);
695+
$mergeStmt->bindParam(4, $maxlifetime, \PDO::PARAM_INT);
696+
$mergeStmt->bindValue(5, time(), \PDO::PARAM_INT);
697+
$mergeStmt->bindParam(6, $data, \PDO::PARAM_LOB);
698+
$mergeStmt->bindParam(7, $maxlifetime, \PDO::PARAM_INT);
699+
$mergeStmt->bindValue(8, time(), \PDO::PARAM_INT);
700+
}
701+
else {
702+
$mergeStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR);
703+
$mergeStmt->bindParam(':data', $data, \PDO::PARAM_LOB);
704+
$mergeStmt->bindParam(':lifetime', $maxlifetime, \PDO::PARAM_INT);
705+
$mergeStmt->bindValue(':time', time(), \PDO::PARAM_INT);
706+
}
707+
708+
return $mergeStmt;
682709
}
683710
}
684711

0 commit comments

Comments
 (0)
0