netcarver

<?php namespace ProcessWire;

/**
 * Session handler for storing sessions to database
 *
 * @see /wire/core/SessionHandler.php
 *
 * ProcessWire 3.x, Copyright 2021 by Ryan Cramer
 * https://processwire.com
 *
 * @property int|bool $useIP Track IP address?
 * @property int|bool $useUA Track user agent?
 * @property int|bool $noPS Prevent more than one session per logged-in user?
 * @property int $lockSeconds Max number of seconds to wait to obtain DB row lock.
 * @property int $retrySeconds Seconds after which to retry after a lock fail.
 *
 */

class SessionHandlerDB extends WireSessionHandler implements Module, ConfigurableModule {

	public static function getModuleInfo() {
		return array(
			'title' => 'Session Handler Database - Experiment',
			'version' => "0.0.6",
			'summary' => "Installing this module makes ProcessWire store sessions in the database rather than the file system. Note that this module will log you out after install or uninstall.",
			'installs' => array('ProcessSessionDB')
		);
	}

	/**
	 * Table created by this module
	 *
	 */
	const dbTableName = 'sessions';

	/**
	 * Quick reference to database
	 *
	 * @var WireDatabasePDO
	 *
	 */
	protected $database;

	/**
	 * Construct
	 *
	 */
	public function __construct() {
		parent::__construct();
		$this->set('useIP', 0); // track IP address?
		$this->set('useUA', 0); // track user agent?
		$this->set('noPS', 0); // disallow parallel sessions per user
		$this->set('lockSeconds', 50); // max number of seconds to wait to obtain DB row lock
		$this->set('retrySeconds', 30); // seconds after which to retry on a lock fail
	}

	public function wired() {
		$this->database = $this->wire()->database;
		parent::wired();
	}

	public function init() {
		parent::init();
		// keeps session active
		$this->wire()->session->setFor($this, 'ts', time());
		if($this->noPS) $this->addHookAfter('Session::loginSuccess', $this, 'hookLoginSuccess');
	}

	/**
	 * Read and return data for session indicated by $id
	 *
	 * @param string $id Session ID
	 * @return string Serialized data or blank string if none
	 *
	 */
	public function read($id) {

		$table = self::dbTableName;
		$database = $this->database;
		$data = '';

		$locked = $this->_getLock($id);
		if(!$locked) {
			// 0: attempt timed out (for example, because another client has previously locked the name)
			// null: error occurred (such as running out of memory or the thread was killed with mysqladmin kill)
			//
			if($locked === null) {
				$this->wire()->log->save("session-errors", "NULL Lock return for session [$id]");
			} else {
				$this->wire()->log->save("session-errors", "0 (Zero) Lock return for session [$id]");
			}
			$this->wire()->shutdown->setFatalErrorResponse(array(
				'code' => 429, // http status 429: Too Many Requests (RFC 6585)
				'headers' => array("Retry-After: $this->retrySeconds"),
			));
			throw new WireException("Unable to obtain lock for session (retry in {$this->retrySeconds}s)", 429);
		}

		$query = $database->prepare("SELECT data FROM `$table` WHERE id=:id");
		$query->bindValue(':id', $id);
		$database->execute($query);

		if($query->rowCount()) {
			$data = $query->fetchColumn();
			if(empty($data)) $data = '';
		}

		$query->closeCursor();

		return $data;
	}



	/**
	 * Attempt to gain the named lock.
	 *
	 * Each time a lock is obtained by the read() method,
	 * the lock's usage count is incremented in MySQL.
	 */
	protected function _getLock(string $id) {
		$lockq = $this->database->prepare('SELECT GET_LOCK(:id, :seconds)');
		$lockq->bindValue(':id', $id);
		$lockq->bindValue(':seconds', $this->lockSeconds, \PDO::PARAM_INT);
		$this->database->execute($lockq);
		$locked = $lockq->fetchColumn();
		$lockq->closeCursor();
		return $locked;
	}


	/**
	 * Release the given lock id if we own it.
	 *
	 * Attempts to release as many times as needed in case there
	 * has been a 2nd call to read() via user code calling session_reset().
	 *
	 * This is needed as each call to read(id) increments the lock count,
	 * so we need to release the lock as many times as we locked it.
	 */
	protected function _releaseLock(string $id) {
		$unlockq = $this->database->prepare('SELECT RELEASE_LOCK(:id)');
		$unlockq->bindValue(':id', $id);
		do {
			$this->database->execute($unlockq);
			$unlocked = $unlockq->fetchColumn();
		} while ($unlocked);
		$unlockq->closeCursor();
	}


	/**
	 * Write the given $data for the given session ID
	 *
	 * @param string $id Session ID
	 * @param string $data Serialized data to write
	 * @return bool
	 *
	 */
	public function write($id, $data) {
        $result = false;
        try {
            $table = self::dbTableName;
            $database = $this->database;
            $user = $this->wire()->user;
            $page = $this->wire()->page;
            $user_id = $user && $user->id ? (int) $user->id : 0;
            $pages_id = $page && $page->id ? (int) $page->id : 0;
            $ua = ($this->useUA && isset($_SERVER['HTTP_USER_AGENT'])) ? substr(strip_tags($_SERVER['HTTP_USER_AGENT']), 0, 255) : '';
            $ip = '';

            if($this->useIP) {
                $session = $this->wire()->session;
                $ip = $session->getIP();
                $ip = (strlen($ip) && strpos($ip, ':') === false ? ip2long($ip) : '');
                // @todo DB schema for ipv6
            }

            $binds = array(
                ':id' => $id,
                ':user_id' => $user_id,
                ':pages_id' => $pages_id,
                ':data' => $data,
            );

            $s = "user_id=:user_id, pages_id=:pages_id, data=:data";

            if($ip) {
                $s .= ", ip=:ip";
                $binds[':ip'] = $ip;
            }
            if($ua) {
                $s .= ", ua=:ua";
                $binds[':ua'] = $ua;
            }

            $sql = "INSERT INTO $table SET id=:id, $s ON DUPLICATE KEY UPDATE $s, ts=NOW()";

            $query = $database->prepare($sql);
            foreach($binds as $key => $value) {
                $type = is_int($value) ? \PDO::PARAM_INT : \PDO::PARAM_STR;
                $query->bindValue($key, $value, $type);
            }
            $result = $database->execute($query, false) ? true : false;
            $query->closeCursor();
        } catch(\Throwable $e) {
            /* if ($e instanceof \Exception) { */
            /*     $this->trackException($e); */
            /* } else { */
            /* } */
            $this->wire()->log->save("session-errors", "Intercepted throwable A: " . $e->getMessage());
        } finally {
            try {
				$this->_releaseLock($id);
            } catch(\Throwable $e) {
                /* $this->trackException($e); */
                $this->wire()->log->save("session-errors", "Intercepted throwable B: " . $e->getMessage());
            }
        }

		return $result;
	}

	/**
	 * Destroy the session indicated by the given session ID
	 *
	 * @param string $id Session ID
	 * @return bool True on success, false on failure
	 *
	 */
	public function destroy($id) {
		$config = $this->wire()->config;
		$table = self::dbTableName;
		$database = $this->database;
		$query = $database->prepare("DELETE FROM `$table` WHERE id=:id");
		$query->execute(array(":id" => $id));
		$secure = $config->sessionCookieSecure ? (bool) $config->https : false;
		$expires = time() - 42000;
		$samesite = $config->sessionCookieSameSite ? ucfirst(strtolower($config->sessionCookieSameSite)) : 'Lax';

		if($samesite === 'None') $secure = true;

		if(PHP_VERSION_ID < 70300) {
			setcookie(session_name(), '', $expires, "/; SameSite=$samesite", $config->sessionCookieDomain, $secure, true);
		} else {
			setcookie(session_name(), '', array(
				'expires' => $expires,
				'path' => '/',
				'domain' => $config->sessionCookieDomain,
				'secure' => $secure,
				'httponly' => true,
				'samesite' => $samesite
			));
		}

		$this->_releaseLock($id);

		return true;
	}

	/**
	 * Garbage collection: remove stale sessions
	 *
	 * @param int $seconds Max lifetime of a session
	 * @return bool True on success, false on failure
	 *
	 */
	public function gc($seconds) {
		$table = self::dbTableName;
		$seconds = (int) $seconds;
		$sql = "DELETE FROM `$table` WHERE ts < DATE_SUB(NOW(), INTERVAL $seconds SECOND)";
		return $this->database->exec($sql) !== false;
	}

	/**
	 * Install sessions table
	 *
	 */
	public function ___install() {

		$table = self::dbTableName;
		$charset = $this->wire()->config->dbCharset;

		$sql = 	"CREATE TABLE `$table` (" .
				"id CHAR(32) NOT NULL, " .
				"user_id INT UNSIGNED NOT NULL, " .
				"pages_id INT UNSIGNED NOT NULL, " .
				"data MEDIUMTEXT NOT NULL, " .
				"ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, " .
				"ip INT UNSIGNED NOT NULL DEFAULT 0, " .
				"ua VARCHAR(250) NOT NULL DEFAULT '', " .
				"PRIMARY KEY (id), " .
				"INDEX (pages_id), " .
				"INDEX (user_id), " .
				"INDEX (ts) " .
				") ENGINE=InnoDB DEFAULT CHARSET=$charset";

		$this->database->query($sql);
	}

	/**
	 * Drop sessions table
	 *
	 */
	public function ___uninstall() {
		$this->database->query("DROP TABLE " . self::dbTableName);
	}

	/**
	 * Session configuration options
	 *
	 * @param array $data
	 * @return InputfieldWrapper
	 *
	 */
	public function getModuleConfigInputfields(array $data) {

		$modules = $this->wire()->modules;
		$form = $this->wire(new InputfieldWrapper());

		// check if their DB table is the latest version
		$query = $this->database->query("SHOW COLUMNS FROM " . self::dbTableName . " WHERE field='ip'");
		if(!$query->rowCount()) {
			$modules->error("DB format changed - You must uninstall this module and re-install before configuring."); 
			return $form;
		}

		$description = $this->_('Checking this box will enable the data to be displayed in your admin sessions list.');

		/** @var InputfieldCheckbox $f */
		$f = $modules->get('InputfieldCheckbox');
		$f->attr('name', 'useIP');
		$f->attr('value', 1);
		$f->attr('checked', empty($data['useIP']) ? '' : 'checked');
		$f->label = $this->_('Track IP addresses in session data?');
		$f->description = $description;
		$form->add($f);

		$f = $modules->get('InputfieldCheckbox');
		$f->attr('name', 'useUA');
		$f->attr('value', 1);
		$f->attr('checked', empty($data['useUA']) ? '' : 'checked');
		$f->label = $this->_('Track user agent in session data?');
		$f->notes = $this->_('The user agent typically contains information about the browser being used.');
		$f->description = $description;
		$form->add($f);

		$f = $modules->get('InputfieldCheckbox');
		$f->attr('name', 'noPS');
		$f->attr('value', 1);
		$f->attr('checked', empty($data['noPS']) ? '' : 'checked');
		$f->label = $this->_('Disallow parallel sessions?');
		$f->notes = $this->_('When enabled, successful login expires all other sessions for that user on other devices/browsers.');
		$f->description = $this->_('Checking this box will allow only one single session for a logged-in user at a time.');
		$form->add($f);

		/** @var InputfieldInteger $f */
		$f = $modules->get('InputfieldInteger');
		$f->attr('name', 'lockSeconds');
		$f->attr('value', $this->lockSeconds);
		$f->label = $this->_('Session lock timeout (seconds)');
		$f->description = sprintf(
			$this->_('If a DB lock for the session cannot be obtained in this many seconds, a “%s” error will be sent, telling the client to retry again in %d seconds.'), 
			$this->_('429: Too Many Requests'),
			30
		);
		$form->add($f);

		if(ini_get('session.gc_probability') == 0) {
			$form->warning(
				"Your PHP has a configuration error with regard to sessions. It is configured to never clean up old session files. " . 
				"Please correct this by adding the following to your <u>/site/config.php</u> file: " .
				"<code>ini_set('session.gc_probability', 1);</code>",
				Notice::allowMarkup
			);
		}

		return $form;
	}

	/**
	 * Provides direct reference access to set values in the $data array
	 *
	 * For some reason PHP 5.4+ requires this, as it apparently doesn't see WireData
	 *
	 * @param string $key
	 * @param mixed $value
	 * @return void
	 *
	 */
	public function __set($key, $value) {
		$this->set($key, $value);
	}


	/**
	 * Provides direct reference access to variables in the $data array
	 *
	 * For some reason PHP 5.4+ requires this, as it apparently doesn't see WireData
	 *
	 * Otherwise the same as get()
	 *
	 * @param string $key
	 * @return mixed
	 *
	 */
	public function __get($key) {
		return $this->get($key);
	}

	/**
	 * Return the number of active sessions in the last 5 mins (300 seconds)
	 *
	 * @param int $seconds Optionally specify number of seconds (rather than 300, 5 minutes)
	 * @return int
	 *
	 */
	public function getNumSessions($seconds = 300) {
		$seconds = (int) $seconds;
		$sql = "SELECT COUNT(*) FROM " . self::dbTableName . " WHERE ts > DATE_SUB(NOW(), INTERVAL $seconds SECOND)";
		$query = $this->database->query($sql);
		$numSessions = (int) $query->fetchColumn();
		return $numSessions;
	}

	/**
	 * Get the most recent sessions
	 *
	 * Returns an array of array for each session, which includes all the
	 * session info except or the 'data' property. Use the getSessionData()
	 * method to retrieve that.
	 *
	 * @param int $seconds Sessions up to this many seconds old
	 * @param int $limit Max number of sessions to return
	 * @return array Sessions newest to oldest
	 *
	 */
	public function getSessions($seconds = 300, $limit = 100) {

		$seconds = (int) $seconds;
		$limit = (int) $limit;

		$sql = 	"SELECT id, user_id, pages_id, ts, UNIX_TIMESTAMP(ts) AS tsu, ip, ua " .
				"FROM " . self::dbTableName . " " .
				"WHERE ts > DATE_SUB(NOW(), INTERVAL $seconds SECOND) " .
				"ORDER BY ts DESC LIMIT $limit";

		$query = $this->database->prepare($sql);
		$query->execute();

		$sessions = array();
		while($row = $query->fetch(\PDO::FETCH_ASSOC)) {
			$sessions[] = $row;
		}

		return $sessions;
	}

	/**
	 * Return all session data for the given session ID
	 *
	 * Note that the 'data' property of the returned array contains the values
	 * that the user has in their $session.
	 *
	 * @param $sessionID
	 * @return array Blank array on fail, populated array on success.
	 *
	 */
	public function getSessionData($sessionID) {
		$sql = "SELECT * FROM " . self::dbTableName . " WHERE id=:id";
		$query = $this->database->prepare($sql);
		$query->bindValue(':id', $sessionID);
		$this->database->execute($query);
		if(!$query->rowCount()) return array();
		$row = $query->fetch(\PDO::FETCH_ASSOC) ;
		$sess = $_SESSION; // save
		session_decode($row['data']);
		$row['data'] = $_SESSION;
		$_SESSION = $sess; // restore
		return $row;
	}

	/**
	 * Upgrade module version
	 *
	 * @param int $fromVersion
	 * @param int $toVersion
	 *
	 */
	public function ___upgrade($fromVersion, $toVersion) {
		// $this->message("Upgrade: $fromVersion => $toVersion");
		// if(version_compare($fromVersion, "0.0.5", "<") && version_compare($toVersion, "0.0.4", ">")) {
		if($fromVersion <= 4 && $toVersion >= 5) {
			$table = self::dbTableName;
			$database = $this->database;
			$sql = "ALTER TABLE $table MODIFY data MEDIUMTEXT NOT NULL";
			$query = $database->prepare($sql);
			$query->execute();
			$this->message("Updated sessions database for larger data storage", Notice::log);
		}
	}

	/**
	 * Hook called after Session::loginSuccess to enforce the noPS option
	 *
	 * @param HookEvent $event
	 *
	 */
	public function hookLoginSuccess(HookEvent $event) {
		if(!$this->noPS) return;
		/** @var User $user */
		$user = $event->arguments(0);
		$table = self::dbTableName;
		$query = $this->database->prepare("DELETE FROM `$table` WHERE user_id=:user_id AND id!=:id");
		$query->bindValue(':id', session_id());
		$query->bindValue(':user_id', $user->id, \PDO::PARAM_INT);
		$query->execute();
		$n = $query->rowCount();
		if($n) $this->message(sprintf(
			$this->_('Previous login session for “%s” has been removed/logged-out.'),
			$user->name
		));
		$query->closeCursor();
	}

}

Prevents possible re-throwing of logged exceptions leading to bypass of lock releases in the write() handler.

Specifically, it releases locks

• as many times as needed (in case session_reset() was called somewhere causing a second call to SessionHandler::read() - in which case the lock could be owned more than once by the same process, needing multiple releases.)
• as part of the SessionHandler::destroy() method called from PHP's session_destroy() call or session_regenerate_id(true).