1<?php 2 3/* 4 * This file is part of the Symfony package. 5 * 6 * (c) Fabien Potencier <fabien@symfony.com> 7 * 8 * For the full copyright and license information, please view the LICENSE 9 * file that was distributed with this source code. 10 */ 11 12namespace Symfony\Component\HttpFoundation\Session\Storage\Handler; 13 14/** 15 * Session handler using a PDO connection to read and write data. 16 * 17 * It works with MySQL, PostgreSQL, Oracle, SQL Server and SQLite and implements 18 * different locking strategies to handle concurrent access to the same session. 19 * Locking is necessary to prevent loss of data due to race conditions and to keep 20 * the session data consistent between read() and write(). With locking, requests 21 * for the same session will wait until the other one finished writing. For this 22 * reason it's best practice to close a session as early as possible to improve 23 * concurrency. PHPs internal files session handler also implements locking. 24 * 25 * Attention: Since SQLite does not support row level locks but locks the whole database, 26 * it means only one session can be accessed at a time. Even different sessions would wait 27 * for another to finish. So saving session in SQLite should only be considered for 28 * development or prototypes. 29 * 30 * Session data is a binary string that can contain non-printable characters like the null byte. 31 * For this reason it must be saved in a binary column in the database like BLOB in MySQL. 32 * Saving it in a character column could corrupt the data. You can use createTable() 33 * to initialize a correctly defined table. 34 * 35 * @see https://php.net/sessionhandlerinterface 36 * 37 * @author Fabien Potencier <fabien@symfony.com> 38 * @author Michael Williams <michael.williams@funsational.com> 39 * @author Tobias Schultze <http://tobion.de> 40 */ 41class PdoSessionHandler extends AbstractSessionHandler 42{ 43 /** 44 * No locking is done. This means sessions are prone to loss of data due to 45 * race conditions of concurrent requests to the same session. The last session 46 * write will win in this case. It might be useful when you implement your own 47 * logic to deal with this like an optimistic approach. 48 */ 49 const LOCK_NONE = 0; 50 51 /** 52 * Creates an application-level lock on a session. The disadvantage is that the 53 * lock is not enforced by the database and thus other, unaware parts of the 54 * application could still concurrently modify the session. The advantage is it 55 * does not require a transaction. 56 * This mode is not available for SQLite and not yet implemented for oci and sqlsrv. 57 */ 58 const LOCK_ADVISORY = 1; 59 60 /** 61 * Issues a real row lock. Since it uses a transaction between opening and 62 * closing a session, you have to be careful when you use same database connection 63 * that you also use for your application logic. This mode is the default because 64 * it's the only reliable solution across DBMSs. 65 */ 66 const LOCK_TRANSACTIONAL = 2; 67 68 private const MAX_LIFETIME = 315576000; 69 70 /** 71 * @var \PDO|null PDO instance or null when not connected yet 72 */ 73 private $pdo; 74 75 /** 76 * @var string|false|null DSN string or null for session.save_path or false when lazy connection disabled 77 */ 78 private $dsn = false; 79 80 /** 81 * @var string Database driver 82 */ 83 private $driver; 84 85 /** 86 * @var string Table name 87 */ 88 private $table = 'sessions'; 89 90 /** 91 * @var string Column for session id 92 */ 93 private $idCol = 'sess_id'; 94 95 /** 96 * @var string Column for session data 97 */ 98 private $dataCol = 'sess_data'; 99 100 /** 101 * @var string Column for lifetime 102 */ 103 private $lifetimeCol = 'sess_lifetime'; 104 105 /** 106 * @var string Column for timestamp 107 */ 108 private $timeCol = 'sess_time'; 109 110 /** 111 * @var string Username when lazy-connect 112 */ 113 private $username = ''; 114 115 /** 116 * @var string Password when lazy-connect 117 */ 118 private $password = ''; 119 120 /** 121 * @var array Connection options when lazy-connect 122 */ 123 private $connectionOptions = []; 124 125 /** 126 * @var int The strategy for locking, see constants 127 */ 128 private $lockMode = self::LOCK_TRANSACTIONAL; 129 130 /** 131 * It's an array to support multiple reads before closing which is manual, non-standard usage. 132 * 133 * @var \PDOStatement[] An array of statements to release advisory locks 134 */ 135 private $unlockStatements = []; 136 137 /** 138 * @var bool True when the current session exists but expired according to session.gc_maxlifetime 139 */ 140 private $sessionExpired = false; 141 142 /** 143 * @var bool Whether a transaction is active 144 */ 145 private $inTransaction = false; 146 147 /** 148 * @var bool Whether gc() has been called 149 */ 150 private $gcCalled = false; 151 152 /** 153 * You can either pass an existing database connection as PDO instance or 154 * pass a DSN string that will be used to lazy-connect to the database 155 * when the session is actually used. Furthermore it's possible to pass null 156 * which will then use the session.save_path ini setting as PDO DSN parameter. 157 * 158 * List of available options: 159 * * db_table: The name of the table [default: sessions] 160 * * db_id_col: The column where to store the session id [default: sess_id] 161 * * db_data_col: The column where to store the session data [default: sess_data] 162 * * db_lifetime_col: The column where to store the lifetime [default: sess_lifetime] 163 * * db_time_col: The column where to store the timestamp [default: sess_time] 164 * * db_username: The username when lazy-connect [default: ''] 165 * * db_password: The password when lazy-connect [default: ''] 166 * * db_connection_options: An array of driver-specific connection options [default: []] 167 * * lock_mode: The strategy for locking, see constants [default: LOCK_TRANSACTIONAL] 168 * 169 * @param \PDO|string|null $pdoOrDsn A \PDO instance or DSN string or URL string or null 170 * 171 * @throws \InvalidArgumentException When PDO error mode is not PDO::ERRMODE_EXCEPTION 172 */ 173 public function __construct($pdoOrDsn = null, array $options = []) 174 { 175 if ($pdoOrDsn instanceof \PDO) { 176 if (\PDO::ERRMODE_EXCEPTION !== $pdoOrDsn->getAttribute(\PDO::ATTR_ERRMODE)) { 177 throw new \InvalidArgumentException(sprintf('"%s" requires PDO error mode attribute be set to throw Exceptions (i.e. $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION)).', __CLASS__)); 178 } 179 180 $this->pdo = $pdoOrDsn; 181 $this->driver = $this->pdo->getAttribute(\PDO::ATTR_DRIVER_NAME); 182 } elseif (\is_string($pdoOrDsn) && false !== strpos($pdoOrDsn, '://')) { 183 $this->dsn = $this->buildDsnFromUrl($pdoOrDsn); 184 } else { 185 $this->dsn = $pdoOrDsn; 186 } 187 188 $this->table = isset($options['db_table']) ? $options['db_table'] : $this->table; 189 $this->idCol = isset($options['db_id_col']) ? $options['db_id_col'] : $this->idCol; 190 $this->dataCol = isset($options['db_data_col']) ? $options['db_data_col'] : $this->dataCol; 191 $this->lifetimeCol = isset($options['db_lifetime_col']) ? $options['db_lifetime_col'] : $this->lifetimeCol; 192 $this->timeCol = isset($options['db_time_col']) ? $options['db_time_col'] : $this->timeCol; 193 $this->username = isset($options['db_username']) ? $options['db_username'] : $this->username; 194 $this->password = isset($options['db_password']) ? $options['db_password'] : $this->password; 195 $this->connectionOptions = isset($options['db_connection_options']) ? $options['db_connection_options'] : $this->connectionOptions; 196 $this->lockMode = isset($options['lock_mode']) ? $options['lock_mode'] : $this->lockMode; 197 } 198 199 /** 200 * Creates the table to store sessions which can be called once for setup. 201 * 202 * Session ID is saved in a column of maximum length 128 because that is enough even 203 * for a 512 bit configured session.hash_function like Whirlpool. Session data is 204 * saved in a BLOB. One could also use a shorter inlined varbinary column 205 * if one was sure the data fits into it. 206 * 207 * @throws \PDOException When the table already exists 208 * @throws \DomainException When an unsupported PDO driver is used 209 */ 210 public function createTable() 211 { 212 // connect if we are not yet 213 $this->getConnection(); 214 215 switch ($this->driver) { 216 case 'mysql': 217 // We use varbinary for the ID column because it prevents unwanted conversions: 218 // - character set conversions between server and client 219 // - trailing space removal 220 // - case-insensitivity 221 // - language processing like é == e 222 $sql = "CREATE TABLE $this->table ($this->idCol VARBINARY(128) NOT NULL PRIMARY KEY, $this->dataCol BLOB NOT NULL, $this->lifetimeCol INTEGER UNSIGNED NOT NULL, $this->timeCol INTEGER UNSIGNED NOT NULL) COLLATE utf8mb4_bin, ENGINE = InnoDB"; 223 break; 224 case 'sqlite': 225 $sql = "CREATE TABLE $this->table ($this->idCol TEXT NOT NULL PRIMARY KEY, $this->dataCol BLOB NOT NULL, $this->lifetimeCol INTEGER NOT NULL, $this->timeCol INTEGER NOT NULL)"; 226 break; 227 case 'pgsql': 228 $sql = "CREATE TABLE $this->table ($this->idCol VARCHAR(128) NOT NULL PRIMARY KEY, $this->dataCol BYTEA NOT NULL, $this->lifetimeCol INTEGER NOT NULL, $this->timeCol INTEGER NOT NULL)"; 229 break; 230 case 'oci': 231 $sql = "CREATE TABLE $this->table ($this->idCol VARCHAR2(128) NOT NULL PRIMARY KEY, $this->dataCol BLOB NOT NULL, $this->lifetimeCol INTEGER NOT NULL, $this->timeCol INTEGER NOT NULL)"; 232 break; 233 case 'sqlsrv': 234 $sql = "CREATE TABLE $this->table ($this->idCol VARCHAR(128) NOT NULL PRIMARY KEY, $this->dataCol VARBINARY(MAX) NOT NULL, $this->lifetimeCol INTEGER NOT NULL, $this->timeCol INTEGER NOT NULL)"; 235 break; 236 default: 237 throw new \DomainException(sprintf('Creating the session table is currently not implemented for PDO driver "%s".', $this->driver)); 238 } 239 240 try { 241 $this->pdo->exec($sql); 242 $this->pdo->exec("CREATE INDEX EXPIRY ON $this->table ($this->lifetimeCol)"); 243 } catch (\PDOException $e) { 244 $this->rollback(); 245 246 throw $e; 247 } 248 } 249 250 /** 251 * Returns true when the current session exists but expired according to session.gc_maxlifetime. 252 * 253 * Can be used to distinguish between a new session and one that expired due to inactivity. 254 * 255 * @return bool Whether current session expired 256 */ 257 public function isSessionExpired() 258 { 259 return $this->sessionExpired; 260 } 261 262 /** 263 * @return bool 264 */ 265 public function open($savePath, $sessionName) 266 { 267 $this->sessionExpired = false; 268 269 if (null === $this->pdo) { 270 $this->connect($this->dsn ?: $savePath); 271 } 272 273 return parent::open($savePath, $sessionName); 274 } 275 276 /** 277 * @return string 278 */ 279 public function read($sessionId) 280 { 281 try { 282 return parent::read($sessionId); 283 } catch (\PDOException $e) { 284 $this->rollback(); 285 286 throw $e; 287 } 288 } 289 290 /** 291 * @return bool 292 */ 293 public function gc($maxlifetime) 294 { 295 // We delay gc() to close() so that it is executed outside the transactional and blocking read-write process. 296 // This way, pruning expired sessions does not block them from being started while the current session is used. 297 $this->gcCalled = true; 298 299 return true; 300 } 301 302 /** 303 * {@inheritdoc} 304 */ 305 protected function doDestroy(string $sessionId) 306 { 307 // delete the record associated with this id 308 $sql = "DELETE FROM $this->table WHERE $this->idCol = :id"; 309 310 try { 311 $stmt = $this->pdo->prepare($sql); 312 $stmt->bindParam(':id', $sessionId, \PDO::PARAM_STR); 313 $stmt->execute(); 314 } catch (\PDOException $e) { 315 $this->rollback(); 316 317 throw $e; 318 } 319 320 return true; 321 } 322 323 /** 324 * {@inheritdoc} 325 */ 326 protected function doWrite(string $sessionId, string $data) 327 { 328 $maxlifetime = (int) ini_get('session.gc_maxlifetime'); 329 330 try { 331 // We use a single MERGE SQL query when supported by the database. 332 $mergeStmt = $this->getMergeStatement($sessionId, $data, $maxlifetime); 333 if (null !== $mergeStmt) { 334 $mergeStmt->execute(); 335 336 return true; 337 } 338 339 $updateStmt = $this->getUpdateStatement($sessionId, $data, $maxlifetime); 340 $updateStmt->execute(); 341 342 // When MERGE is not supported, like in Postgres < 9.5, we have to use this approach that can result in 343 // duplicate key errors when the same session is written simultaneously (given the LOCK_NONE behavior). 344 // We can just catch such an error and re-execute the update. This is similar to a serializable 345 // transaction with retry logic on serialization failures but without the overhead and without possible 346 // false positives due to longer gap locking. 347 if (!$updateStmt->rowCount()) { 348 try { 349 $insertStmt = $this->getInsertStatement($sessionId, $data, $maxlifetime); 350 $insertStmt->execute(); 351 } catch (\PDOException $e) { 352 // Handle integrity violation SQLSTATE 23000 (or a subclass like 23505 in Postgres) for duplicate keys 353 if (0 === strpos($e->getCode(), '23')) { 354 $updateStmt->execute(); 355 } else { 356 throw $e; 357 } 358 } 359 } 360 } catch (\PDOException $e) { 361 $this->rollback(); 362 363 throw $e; 364 } 365 366 return true; 367 } 368 369 /** 370 * @return bool 371 */ 372 public function updateTimestamp($sessionId, $data) 373 { 374 $expiry = time() + (int) ini_get('session.gc_maxlifetime'); 375 376 try { 377 $updateStmt = $this->pdo->prepare( 378 "UPDATE $this->table SET $this->lifetimeCol = :expiry, $this->timeCol = :time WHERE $this->idCol = :id" 379 ); 380 $updateStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR); 381 $updateStmt->bindParam(':expiry', $expiry, \PDO::PARAM_INT); 382 $updateStmt->bindValue(':time', time(), \PDO::PARAM_INT); 383 $updateStmt->execute(); 384 } catch (\PDOException $e) { 385 $this->rollback(); 386 387 throw $e; 388 } 389 390 return true; 391 } 392 393 /** 394 * @return bool 395 */ 396 public function close() 397 { 398 $this->commit(); 399 400 while ($unlockStmt = array_shift($this->unlockStatements)) { 401 $unlockStmt->execute(); 402 } 403 404 if ($this->gcCalled) { 405 $this->gcCalled = false; 406 407 // delete the session records that have expired 408 $sql = "DELETE FROM $this->table WHERE $this->lifetimeCol < :time AND $this->lifetimeCol > :min"; 409 $stmt = $this->pdo->prepare($sql); 410 $stmt->bindValue(':time', time(), \PDO::PARAM_INT); 411 $stmt->bindValue(':min', self::MAX_LIFETIME, \PDO::PARAM_INT); 412 $stmt->execute(); 413 // to be removed in 6.0 414 if ('mysql' === $this->driver) { 415 $legacySql = "DELETE FROM $this->table WHERE $this->lifetimeCol <= :min AND $this->lifetimeCol + $this->timeCol < :time"; 416 } else { 417 $legacySql = "DELETE FROM $this->table WHERE $this->lifetimeCol <= :min AND $this->lifetimeCol < :time - $this->timeCol"; 418 } 419 420 $stmt = $this->pdo->prepare($legacySql); 421 $stmt->bindValue(':time', time(), \PDO::PARAM_INT); 422 $stmt->bindValue(':min', self::MAX_LIFETIME, \PDO::PARAM_INT); 423 $stmt->execute(); 424 } 425 426 if (false !== $this->dsn) { 427 $this->pdo = null; // only close lazy-connection 428 } 429 430 return true; 431 } 432 433 /** 434 * Lazy-connects to the database. 435 */ 436 private function connect(string $dsn): void 437 { 438 $this->pdo = new \PDO($dsn, $this->username, $this->password, $this->connectionOptions); 439 $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); 440 $this->driver = $this->pdo->getAttribute(\PDO::ATTR_DRIVER_NAME); 441 } 442 443 /** 444 * Builds a PDO DSN from a URL-like connection string. 445 * 446 * @todo implement missing support for oci DSN (which look totally different from other PDO ones) 447 */ 448 private function buildDsnFromUrl(string $dsnOrUrl): string 449 { 450 // (pdo_)?sqlite3?:///... => (pdo_)?sqlite3?://localhost/... or else the URL will be invalid 451 $url = preg_replace('#^((?:pdo_)?sqlite3?):///#', '$1://localhost/', $dsnOrUrl); 452 453 $params = parse_url($url); 454 455 if (false === $params) { 456 return $dsnOrUrl; // If the URL is not valid, let's assume it might be a DSN already. 457 } 458 459 $params = array_map('rawurldecode', $params); 460 461 // Override the default username and password. Values passed through options will still win over these in the constructor. 462 if (isset($params['user'])) { 463 $this->username = $params['user']; 464 } 465 466 if (isset($params['pass'])) { 467 $this->password = $params['pass']; 468 } 469 470 if (!isset($params['scheme'])) { 471 throw new \InvalidArgumentException('URLs without scheme are not supported to configure the PdoSessionHandler.'); 472 } 473 474 $driverAliasMap = [ 475 'mssql' => 'sqlsrv', 476 'mysql2' => 'mysql', // Amazon RDS, for some weird reason 477 'postgres' => 'pgsql', 478 'postgresql' => 'pgsql', 479 'sqlite3' => 'sqlite', 480 ]; 481 482 $driver = isset($driverAliasMap[$params['scheme']]) ? $driverAliasMap[$params['scheme']] : $params['scheme']; 483 484 // Doctrine DBAL supports passing its internal pdo_* driver names directly too (allowing both dashes and underscores). This allows supporting the same here. 485 if (0 === strpos($driver, 'pdo_') || 0 === strpos($driver, 'pdo-')) { 486 $driver = substr($driver, 4); 487 } 488 489 switch ($driver) { 490 case 'mysql': 491 case 'pgsql': 492 $dsn = $driver.':'; 493 494 if (isset($params['host']) && '' !== $params['host']) { 495 $dsn .= 'host='.$params['host'].';'; 496 } 497 498 if (isset($params['port']) && '' !== $params['port']) { 499 $dsn .= 'port='.$params['port'].';'; 500 } 501 502 if (isset($params['path'])) { 503 $dbName = substr($params['path'], 1); // Remove the leading slash 504 $dsn .= 'dbname='.$dbName.';'; 505 } 506 507 return $dsn; 508 509 case 'sqlite': 510 return 'sqlite:'.substr($params['path'], 1); 511 512 case 'sqlsrv': 513 $dsn = 'sqlsrv:server='; 514 515 if (isset($params['host'])) { 516 $dsn .= $params['host']; 517 } 518 519 if (isset($params['port']) && '' !== $params['port']) { 520 $dsn .= ','.$params['port']; 521 } 522 523 if (isset($params['path'])) { 524 $dbName = substr($params['path'], 1); // Remove the leading slash 525 $dsn .= ';Database='.$dbName; 526 } 527 528 return $dsn; 529 530 default: 531 throw new \InvalidArgumentException(sprintf('The scheme "%s" is not supported by the PdoSessionHandler URL configuration. Pass a PDO DSN directly.', $params['scheme'])); 532 } 533 } 534 535 /** 536 * Helper method to begin a transaction. 537 * 538 * Since SQLite does not support row level locks, we have to acquire a reserved lock 539 * on the database immediately. Because of https://bugs.php.net/42766 we have to create 540 * such a transaction manually which also means we cannot use PDO::commit or 541 * PDO::rollback or PDO::inTransaction for SQLite. 542 * 543 * Also MySQLs default isolation, REPEATABLE READ, causes deadlock for different sessions 544 * due to https://percona.com/blog/2013/12/12/one-more-innodb-gap-lock-to-avoid/ . 545 * So we change it to READ COMMITTED. 546 */ 547 private function beginTransaction(): void 548 { 549 if (!$this->inTransaction) { 550 if ('sqlite' === $this->driver) { 551 $this->pdo->exec('BEGIN IMMEDIATE TRANSACTION'); 552 } else { 553 if ('mysql' === $this->driver) { 554 $this->pdo->exec('SET TRANSACTION ISOLATION LEVEL READ COMMITTED'); 555 } 556 $this->pdo->beginTransaction(); 557 } 558 $this->inTransaction = true; 559 } 560 } 561 562 /** 563 * Helper method to commit a transaction. 564 */ 565 private function commit(): void 566 { 567 if ($this->inTransaction) { 568 try { 569 // commit read-write transaction which also releases the lock 570 if ('sqlite' === $this->driver) { 571 $this->pdo->exec('COMMIT'); 572 } else { 573 $this->pdo->commit(); 574 } 575 $this->inTransaction = false; 576 } catch (\PDOException $e) { 577 $this->rollback(); 578 579 throw $e; 580 } 581 } 582 } 583 584 /** 585 * Helper method to rollback a transaction. 586 */ 587 private function rollback(): void 588 { 589 // We only need to rollback if we are in a transaction. Otherwise the resulting 590 // error would hide the real problem why rollback was called. We might not be 591 // in a transaction when not using the transactional locking behavior or when 592 // two callbacks (e.g. destroy and write) are invoked that both fail. 593 if ($this->inTransaction) { 594 if ('sqlite' === $this->driver) { 595 $this->pdo->exec('ROLLBACK'); 596 } else { 597 $this->pdo->rollBack(); 598 } 599 $this->inTransaction = false; 600 } 601 } 602 603 /** 604 * Reads the session data in respect to the different locking strategies. 605 * 606 * We need to make sure we do not return session data that is already considered garbage according 607 * to the session.gc_maxlifetime setting because gc() is called after read() and only sometimes. 608 * 609 * @return string 610 */ 611 protected function doRead(string $sessionId) 612 { 613 if (self::LOCK_ADVISORY === $this->lockMode) { 614 $this->unlockStatements[] = $this->doAdvisoryLock($sessionId); 615 } 616 617 $selectSql = $this->getSelectSql(); 618 $selectStmt = $this->pdo->prepare($selectSql); 619 $selectStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR); 620 $insertStmt = null; 621 622 do { 623 $selectStmt->execute(); 624 $sessionRows = $selectStmt->fetchAll(\PDO::FETCH_NUM); 625 626 if ($sessionRows) { 627 $expiry = (int) $sessionRows[0][1]; 628 if ($expiry <= self::MAX_LIFETIME) { 629 $expiry += $sessionRows[0][2]; 630 } 631 632 if ($expiry < time()) { 633 $this->sessionExpired = true; 634 635 return ''; 636 } 637 638 return \is_resource($sessionRows[0][0]) ? stream_get_contents($sessionRows[0][0]) : $sessionRows[0][0]; 639 } 640 641 if (null !== $insertStmt) { 642 $this->rollback(); 643 throw new \RuntimeException('Failed to read session: INSERT reported a duplicate id but next SELECT did not return any data.'); 644 } 645 646 if (!filter_var(ini_get('session.use_strict_mode'), FILTER_VALIDATE_BOOLEAN) && self::LOCK_TRANSACTIONAL === $this->lockMode && 'sqlite' !== $this->driver) { 647 // In strict mode, session fixation is not possible: new sessions always start with a unique 648 // random id, so that concurrency is not possible and this code path can be skipped. 649 // Exclusive-reading of non-existent rows does not block, so we need to do an insert to block 650 // until other connections to the session are committed. 651 try { 652 $insertStmt = $this->getInsertStatement($sessionId, '', 0); 653 $insertStmt->execute(); 654 } catch (\PDOException $e) { 655 // Catch duplicate key error because other connection created the session already. 656 // It would only not be the case when the other connection destroyed the session. 657 if (0 === strpos($e->getCode(), '23')) { 658 // Retrieve finished session data written by concurrent connection by restarting the loop. 659 // We have to start a new transaction as a failed query will mark the current transaction as 660 // aborted in PostgreSQL and disallow further queries within it. 661 $this->rollback(); 662 $this->beginTransaction(); 663 continue; 664 } 665 666 throw $e; 667 } 668 } 669 670 return ''; 671 } while (true); 672 } 673 674 /** 675 * Executes an application-level lock on the database. 676 * 677 * @return \PDOStatement The statement that needs to be executed later to release the lock 678 * 679 * @throws \DomainException When an unsupported PDO driver is used 680 * 681 * @todo implement missing advisory locks 682 * - for oci using DBMS_LOCK.REQUEST 683 * - for sqlsrv using sp_getapplock with LockOwner = Session 684 */ 685 private function doAdvisoryLock(string $sessionId): \PDOStatement 686 { 687 switch ($this->driver) { 688 case 'mysql': 689 // MySQL 5.7.5 and later enforces a maximum length on lock names of 64 characters. Previously, no limit was enforced. 690 $lockId = substr($sessionId, 0, 64); 691 // should we handle the return value? 0 on timeout, null on error 692 // we use a timeout of 50 seconds which is also the default for innodb_lock_wait_timeout 693 $stmt = $this->pdo->prepare('SELECT GET_LOCK(:key, 50)'); 694 $stmt->bindValue(':key', $lockId, \PDO::PARAM_STR); 695 $stmt->execute(); 696 697 $releaseStmt = $this->pdo->prepare('DO RELEASE_LOCK(:key)'); 698 $releaseStmt->bindValue(':key', $lockId, \PDO::PARAM_STR); 699 700 return $releaseStmt; 701 case 'pgsql': 702 // Obtaining an exclusive session level advisory lock requires an integer key. 703 // When session.sid_bits_per_character > 4, the session id can contain non-hex-characters. 704 // So we cannot just use hexdec(). 705 if (4 === \PHP_INT_SIZE) { 706 $sessionInt1 = $this->convertStringToInt($sessionId); 707 $sessionInt2 = $this->convertStringToInt(substr($sessionId, 4, 4)); 708 709 $stmt = $this->pdo->prepare('SELECT pg_advisory_lock(:key1, :key2)'); 710 $stmt->bindValue(':key1', $sessionInt1, \PDO::PARAM_INT); 711 $stmt->bindValue(':key2', $sessionInt2, \PDO::PARAM_INT); 712 $stmt->execute(); 713 714 $releaseStmt = $this->pdo->prepare('SELECT pg_advisory_unlock(:key1, :key2)'); 715 $releaseStmt->bindValue(':key1', $sessionInt1, \PDO::PARAM_INT); 716 $releaseStmt->bindValue(':key2', $sessionInt2, \PDO::PARAM_INT); 717 } else { 718 $sessionBigInt = $this->convertStringToInt($sessionId); 719 720 $stmt = $this->pdo->prepare('SELECT pg_advisory_lock(:key)'); 721 $stmt->bindValue(':key', $sessionBigInt, \PDO::PARAM_INT); 722 $stmt->execute(); 723 724 $releaseStmt = $this->pdo->prepare('SELECT pg_advisory_unlock(:key)'); 725 $releaseStmt->bindValue(':key', $sessionBigInt, \PDO::PARAM_INT); 726 } 727 728 return $releaseStmt; 729 case 'sqlite': 730 throw new \DomainException('SQLite does not support advisory locks.'); 731 default: 732 throw new \DomainException(sprintf('Advisory locks are currently not implemented for PDO driver "%s".', $this->driver)); 733 } 734 } 735 736 /** 737 * Encodes the first 4 (when PHP_INT_SIZE == 4) or 8 characters of the string as an integer. 738 * 739 * Keep in mind, PHP integers are signed. 740 */ 741 private function convertStringToInt(string $string): int 742 { 743 if (4 === \PHP_INT_SIZE) { 744 return (\ord($string[3]) << 24) + (\ord($string[2]) << 16) + (\ord($string[1]) << 8) + \ord($string[0]); 745 } 746 747 $int1 = (\ord($string[7]) << 24) + (\ord($string[6]) << 16) + (\ord($string[5]) << 8) + \ord($string[4]); 748 $int2 = (\ord($string[3]) << 24) + (\ord($string[2]) << 16) + (\ord($string[1]) << 8) + \ord($string[0]); 749 750 return $int2 + ($int1 << 32); 751 } 752 753 /** 754 * Return a locking or nonlocking SQL query to read session information. 755 * 756 * @throws \DomainException When an unsupported PDO driver is used 757 */ 758 private function getSelectSql(): string 759 { 760 if (self::LOCK_TRANSACTIONAL === $this->lockMode) { 761 $this->beginTransaction(); 762 763 // selecting the time column should be removed in 6.0 764 switch ($this->driver) { 765 case 'mysql': 766 case 'oci': 767 case 'pgsql': 768 return "SELECT $this->dataCol, $this->lifetimeCol, $this->timeCol FROM $this->table WHERE $this->idCol = :id FOR UPDATE"; 769 case 'sqlsrv': 770 return "SELECT $this->dataCol, $this->lifetimeCol, $this->timeCol FROM $this->table WITH (UPDLOCK, ROWLOCK) WHERE $this->idCol = :id"; 771 case 'sqlite': 772 // we already locked when starting transaction 773 break; 774 default: 775 throw new \DomainException(sprintf('Transactional locks are currently not implemented for PDO driver "%s".', $this->driver)); 776 } 777 } 778 779 return "SELECT $this->dataCol, $this->lifetimeCol, $this->timeCol FROM $this->table WHERE $this->idCol = :id"; 780 } 781 782 /** 783 * Returns an insert statement supported by the database for writing session data. 784 */ 785 private function getInsertStatement(string $sessionId, string $sessionData, int $maxlifetime): \PDOStatement 786 { 787 switch ($this->driver) { 788 case 'oci': 789 $data = fopen('php://memory', 'r+'); 790 fwrite($data, $sessionData); 791 rewind($data); 792 $sql = "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, EMPTY_BLOB(), :expiry, :time) RETURNING $this->dataCol into :data"; 793 break; 794 default: 795 $data = $sessionData; 796 $sql = "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :expiry, :time)"; 797 break; 798 } 799 800 $stmt = $this->pdo->prepare($sql); 801 $stmt->bindParam(':id', $sessionId, \PDO::PARAM_STR); 802 $stmt->bindParam(':data', $data, \PDO::PARAM_LOB); 803 $stmt->bindValue(':expiry', time() + $maxlifetime, \PDO::PARAM_INT); 804 $stmt->bindValue(':time', time(), \PDO::PARAM_INT); 805 806 return $stmt; 807 } 808 809 /** 810 * Returns an update statement supported by the database for writing session data. 811 */ 812 private function getUpdateStatement(string $sessionId, string $sessionData, int $maxlifetime): \PDOStatement 813 { 814 switch ($this->driver) { 815 case 'oci': 816 $data = fopen('php://memory', 'r+'); 817 fwrite($data, $sessionData); 818 rewind($data); 819 $sql = "UPDATE $this->table SET $this->dataCol = EMPTY_BLOB(), $this->lifetimeCol = :expiry, $this->timeCol = :time WHERE $this->idCol = :id RETURNING $this->dataCol into :data"; 820 break; 821 default: 822 $data = $sessionData; 823 $sql = "UPDATE $this->table SET $this->dataCol = :data, $this->lifetimeCol = :expiry, $this->timeCol = :time WHERE $this->idCol = :id"; 824 break; 825 } 826 827 $stmt = $this->pdo->prepare($sql); 828 $stmt->bindParam(':id', $sessionId, \PDO::PARAM_STR); 829 $stmt->bindParam(':data', $data, \PDO::PARAM_LOB); 830 $stmt->bindValue(':expiry', time() + $maxlifetime, \PDO::PARAM_INT); 831 $stmt->bindValue(':time', time(), \PDO::PARAM_INT); 832 833 return $stmt; 834 } 835 836 /** 837 * Returns a merge/upsert (i.e. insert or update) statement when supported by the database for writing session data. 838 */ 839 private function getMergeStatement(string $sessionId, string $data, int $maxlifetime): ?\PDOStatement 840 { 841 switch (true) { 842 case 'mysql' === $this->driver: 843 $mergeSql = "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :expiry, :time) ". 844 "ON DUPLICATE KEY UPDATE $this->dataCol = VALUES($this->dataCol), $this->lifetimeCol = VALUES($this->lifetimeCol), $this->timeCol = VALUES($this->timeCol)"; 845 break; 846 case 'sqlsrv' === $this->driver && version_compare($this->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '10', '>='): 847 // MERGE is only available since SQL Server 2008 and must be terminated by semicolon 848 // It also requires HOLDLOCK according to https://weblogs.sqlteam.com/dang/2009/01/31/upsert-race-condition-with-merge/ 849 $mergeSql = "MERGE INTO $this->table WITH (HOLDLOCK) USING (SELECT 1 AS dummy) AS src ON ($this->idCol = ?) ". 850 "WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (?, ?, ?, ?) ". 851 "WHEN MATCHED THEN UPDATE SET $this->dataCol = ?, $this->lifetimeCol = ?, $this->timeCol = ?;"; 852 break; 853 case 'sqlite' === $this->driver: 854 $mergeSql = "INSERT OR REPLACE INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :expiry, :time)"; 855 break; 856 case 'pgsql' === $this->driver && version_compare($this->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '9.5', '>='): 857 $mergeSql = "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :expiry, :time) ". 858 "ON CONFLICT ($this->idCol) DO UPDATE SET ($this->dataCol, $this->lifetimeCol, $this->timeCol) = (EXCLUDED.$this->dataCol, EXCLUDED.$this->lifetimeCol, EXCLUDED.$this->timeCol)"; 859 break; 860 default: 861 // MERGE is not supported with LOBs: https://oracle.com/technetwork/articles/fuecks-lobs-095315.html 862 return null; 863 } 864 865 $mergeStmt = $this->pdo->prepare($mergeSql); 866 867 if ('sqlsrv' === $this->driver) { 868 $mergeStmt->bindParam(1, $sessionId, \PDO::PARAM_STR); 869 $mergeStmt->bindParam(2, $sessionId, \PDO::PARAM_STR); 870 $mergeStmt->bindParam(3, $data, \PDO::PARAM_LOB); 871 $mergeStmt->bindValue(4, time() + $maxlifetime, \PDO::PARAM_INT); 872 $mergeStmt->bindValue(5, time(), \PDO::PARAM_INT); 873 $mergeStmt->bindParam(6, $data, \PDO::PARAM_LOB); 874 $mergeStmt->bindValue(7, time() + $maxlifetime, \PDO::PARAM_INT); 875 $mergeStmt->bindValue(8, time(), \PDO::PARAM_INT); 876 } else { 877 $mergeStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR); 878 $mergeStmt->bindParam(':data', $data, \PDO::PARAM_LOB); 879 $mergeStmt->bindValue(':expiry', time() + $maxlifetime, \PDO::PARAM_INT); 880 $mergeStmt->bindValue(':time', time(), \PDO::PARAM_INT); 881 } 882 883 return $mergeStmt; 884 } 885 886 /** 887 * Return a PDO instance. 888 * 889 * @return \PDO 890 */ 891 protected function getConnection() 892 { 893 if (null === $this->pdo) { 894 $this->connect($this->dsn ?: ini_get('session.save_path')); 895 } 896 897 return $this->pdo; 898 } 899} 900