From 75d957e9e1162472d19dcacdd301c74ec8ffd250 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?M=C3=A1t=C3=A9=20Bartus?= <mate.bartus@gmail.com>
Date: Sat, 9 Sep 2017 12:37:52 +0200
Subject: [ticket/15351] Fix missing global usage

PHPBB3-15351
---
 phpBB/phpbb/session.php | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

(limited to 'phpBB/phpbb/session.php')

diff --git a/phpBB/phpbb/session.php b/phpBB/phpbb/session.php
index 31f32af7c4..b47038e120 100644
--- a/phpBB/phpbb/session.php
+++ b/phpBB/phpbb/session.php
@@ -99,8 +99,8 @@ class session
 		else
 		{
 			// current directory within the phpBB root (for example: adm)
-			$root_dirs = explode('/', str_replace('\\', '/', $phpbb_filesystem->realpath($root_path)));
-			$page_dirs = explode('/', str_replace('\\', '/', $phpbb_filesystem->realpath('./')));
+			$root_dirs = explode('/', str_replace('\\', '/', filesystem_helper::realpath($root_path)));
+			$page_dirs = explode('/', str_replace('\\', '/', filesystem_helper::realpath('./')));
 		}
 
 		$intersection = array_intersect_assoc($root_dirs, $page_dirs);
-- 
cgit v1.2.1


From a9a28859d4852be72ce782d079ee3183c4d54852 Mon Sep 17 00:00:00 2001
From: luzpaz <luzpaz@@users.noreply.github.com>
Date: Tue, 15 Aug 2017 15:00:12 -0400
Subject: [ticket/15424] Multiple typo fixes in docs & comments

Fixed typos in some docs, guidelines, some non-user-facing files.

PHPBB3-15424
---
 phpBB/phpbb/session.php | 8 ++++----
 1 file changed, 4 insertions(+), 4 deletions(-)

(limited to 'phpBB/phpbb/session.php')

diff --git a/phpBB/phpbb/session.php b/phpBB/phpbb/session.php
index b47038e120..e5621a4ee7 100644
--- a/phpBB/phpbb/session.php
+++ b/phpBB/phpbb/session.php
@@ -250,7 +250,7 @@ class session
 			$ips = explode(' ', $this->forwarded_for);
 			foreach ($ips as $ip)
 			{
-				// check IPv4 first, the IPv6 is hopefully only going to be used very seldomly
+				// check IPv4 first, the IPv6 is hopefully only going to be used very seldom
 				if (!empty($ip) && !preg_match(get_preg_expression('ipv4'), $ip) && !preg_match(get_preg_expression('ipv6'), $ip))
 				{
 					// contains invalid data, don't use the forwarded for header
@@ -478,7 +478,7 @@ class session
 				}
 				else
 				{
-					// Added logging temporarly to help debug bugs...
+					// Added logging temporarily to help debug bugs...
 					if (defined('DEBUG') && $this->data['user_id'] != ANONYMOUS)
 					{
 						if ($referer_valid)
@@ -1336,7 +1336,7 @@ class session
 	* Only IPv4 (rbldns does not support AAAA records/IPv6 lookups)
 	*
 	* @author satmd (from the php manual)
-	* @param string 		$mode	register/post - spamcop for example is ommitted for posting
+	* @param string 		$mode	register/post - spamcop for example is omitted for posting
 	* @param string|false	$ip		the IPv4 address to check
 	*
 	* @return false if ip is not blacklisted, else an array([checked server], [lookup])
@@ -1395,7 +1395,7 @@ class session
 
 	/**
 	* Check if URI is blacklisted
-	* This should be called only where absolutly necessary, for example on the submitted website field
+	* This should be called only where absolutely necessary, for example on the submitted website field
 	* This function is not in use at the moment and is only included for testing purposes, it may not work at all!
 	* This means it is untested at the moment and therefore commented out
 	*
-- 
cgit v1.2.1


From 303b823847d23edf381203bddb7af58a3ad68173 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Rub=C3=A9n=20Calvo?= <rubencm@gmail.com>
Date: Sat, 1 Sep 2018 01:16:57 +0200
Subject: [ticket/12636] Add log.session_errors

PHPBB3-12636
---
 phpBB/phpbb/session.php | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'phpBB/phpbb/session.php')

diff --git a/phpBB/phpbb/session.php b/phpBB/phpbb/session.php
index e5621a4ee7..719a5fb104 100644
--- a/phpBB/phpbb/session.php
+++ b/phpBB/phpbb/session.php
@@ -479,7 +479,7 @@ class session
 				else
 				{
 					// Added logging temporarily to help debug bugs...
-					if (defined('DEBUG') && $this->data['user_id'] != ANONYMOUS)
+					if ($phpbb_container->getParameter('log.session_errors') && $this->data['user_id'] != ANONYMOUS)
 					{
 						if ($referer_valid)
 						{
-- 
cgit v1.2.1


From a702142a3483e0d80352e74d238938e6f01eaa2d Mon Sep 17 00:00:00 2001
From: rubencm <rubencm@gmail.com>
Date: Thu, 13 Sep 2018 14:53:01 +0000
Subject: [ticket/12636] Rename log.session_errors to session.log_errors

PHPBB3-12636
---
 phpBB/phpbb/session.php | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'phpBB/phpbb/session.php')

diff --git a/phpBB/phpbb/session.php b/phpBB/phpbb/session.php
index 719a5fb104..9e1548bf76 100644
--- a/phpBB/phpbb/session.php
+++ b/phpBB/phpbb/session.php
@@ -479,7 +479,7 @@ class session
 				else
 				{
 					// Added logging temporarily to help debug bugs...
-					if ($phpbb_container->getParameter('log.session_errors') && $this->data['user_id'] != ANONYMOUS)
+					if ($phpbb_container->getParameter('session.log_errors') && $this->data['user_id'] != ANONYMOUS)
 					{
 						if ($referer_valid)
 						{
-- 
cgit v1.2.1


From 3f33d8957288ee988e6e276ec8cb2042eb921627 Mon Sep 17 00:00:00 2001
From: Marc Alexander <admin@m-a-styles.de>
Date: Sat, 11 May 2019 13:40:47 +0200
Subject: [ticket/16052] Fix issues caused by merges

PHPBB3-16052
---
 phpBB/phpbb/session.php | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

(limited to 'phpBB/phpbb/session.php')

diff --git a/phpBB/phpbb/session.php b/phpBB/phpbb/session.php
index 9e1548bf76..38e40ba29e 100644
--- a/phpBB/phpbb/session.php
+++ b/phpBB/phpbb/session.php
@@ -99,8 +99,8 @@ class session
 		else
 		{
 			// current directory within the phpBB root (for example: adm)
-			$root_dirs = explode('/', str_replace('\\', '/', filesystem_helper::realpath($root_path)));
-			$page_dirs = explode('/', str_replace('\\', '/', filesystem_helper::realpath('./')));
+			$root_dirs = explode('/', str_replace('\\', '/', $phpbb_filesystem->realpath($root_path)));
+			$page_dirs = explode('/', str_replace('\\', '/', $phpbb_filesystem->realpath('./')));
 		}
 
 		$intersection = array_intersect_assoc($root_dirs, $page_dirs);
-- 
cgit v1.2.1


From 163aac74dbe500eac223ea2ebdbce0a76c1d6ab4 Mon Sep 17 00:00:00 2001
From: rubencm <rubencm@gmail.com>
Date: Thu, 31 Oct 2019 20:54:21 +0000
Subject: [ticket/16190] Deprecate phpbb's checkdnsrr wrapper

PHPBB3-16190
---
 phpBB/phpbb/session.php | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

(limited to 'phpBB/phpbb/session.php')

diff --git a/phpBB/phpbb/session.php b/phpBB/phpbb/session.php
index 7624a67b05..ba200f38df 100644
--- a/phpBB/phpbb/session.php
+++ b/phpBB/phpbb/session.php
@@ -1400,7 +1400,7 @@ class session
 
 			foreach ($dnsbl_check as $dnsbl => $lookup)
 			{
-				if (phpbb_checkdnsrr($reverse_ip . '.' . $dnsbl . '.', 'A') === true)
+				if (checkdnsrr($reverse_ip . '.' . $dnsbl . '.', 'A') === true)
 				{
 					$info = array($dnsbl, $lookup . $ip);
 				}
@@ -1444,7 +1444,7 @@ class session
 		{
 			// One problem here... the return parameter for the "windows" method is different from what
 			// we expect... this may render this check useless...
-			if (phpbb_checkdnsrr($uri . '.multi.uribl.com.', 'A') === true)
+			if (checkdnsrr($uri . '.multi.uribl.com.', 'A') === true)
 			{
 				return true;
 			}
-- 
cgit v1.2.1


From f09f5bc51c15eef526374105d6443270dc1172bf Mon Sep 17 00:00:00 2001
From: rubencm <rubencm@gmail.com>
Date: Thu, 24 Oct 2019 19:32:47 +0000
Subject: [ticket/16189] Deprecate inet_ntop and inet_pton wrappers

PHPBB3-16189
---
 phpBB/phpbb/session.php | 46 +++++++---------------------------------------
 1 file changed, 7 insertions(+), 39 deletions(-)

(limited to 'phpBB/phpbb/session.php')

diff --git a/phpBB/phpbb/session.php b/phpBB/phpbb/session.php
index ba200f38df..edd9e23c6b 100644
--- a/phpBB/phpbb/session.php
+++ b/phpBB/phpbb/session.php
@@ -311,49 +311,17 @@ class session
 
 		foreach ($ips as $ip)
 		{
-			if (function_exists('phpbb_ip_normalise'))
-			{
-				// Normalise IP address
-				$ip = phpbb_ip_normalise($ip);
-
-				if (empty($ip))
-				{
-					// IP address is invalid.
-					break;
-				}
-
-				// IP address is valid.
-				$this->ip = $ip;
+			// Normalise IP address
+			$ip = phpbb_ip_normalise($ip);
 
-				// Skip legacy code.
-				continue;
-			}
-
-			if (preg_match(get_preg_expression('ipv4'), $ip))
-			{
-				$this->ip = $ip;
-			}
-			else if (preg_match(get_preg_expression('ipv6'), $ip))
+			if ($ip === false)
 			{
-				// Quick check for IPv4-mapped address in IPv6
-				if (stripos($ip, '::ffff:') === 0)
-				{
-					$ipv4 = substr($ip, 7);
-
-					if (preg_match(get_preg_expression('ipv4'), $ipv4))
-					{
-						$ip = $ipv4;
-					}
-				}
-
-				$this->ip = $ip;
-			}
-			else
-			{
-				// We want to use the last valid address in the chain
-				// Leave foreach loop when address is invalid
+				// IP address is invalid.
 				break;
 			}
+
+			// IP address is valid.
+			$this->ip = $ip;
 		}
 
 		$this->load = false;
-- 
cgit v1.2.1


From b6a99e7dcad10836cd2277ba2bd9a6c19afe90f1 Mon Sep 17 00:00:00 2001
From: rubencm <rubencm@gmail.com>
Date: Thu, 24 Oct 2019 20:03:32 +0000
Subject: [ticket/16188] Remove some legacy code and fix bug

PHPBB3-16189
---
 phpBB/phpbb/session.php | 3 +--
 1 file changed, 1 insertion(+), 2 deletions(-)

(limited to 'phpBB/phpbb/session.php')

diff --git a/phpBB/phpbb/session.php b/phpBB/phpbb/session.php
index edd9e23c6b..7c76c08b73 100644
--- a/phpBB/phpbb/session.php
+++ b/phpBB/phpbb/session.php
@@ -250,8 +250,7 @@ class session
 			$ips = explode(' ', $this->forwarded_for);
 			foreach ($ips as $ip)
 			{
-				// check IPv4 first, the IPv6 is hopefully only going to be used very seldom
-				if (!empty($ip) && !preg_match(get_preg_expression('ipv4'), $ip) && !preg_match(get_preg_expression('ipv6'), $ip))
+				if (!filter_var($ip, FILTER_VALIDATE_IP))
 				{
 					// contains invalid data, don't use the forwarded for header
 					$this->forwarded_for = '';
-- 
cgit v1.2.1


From e9fc3ed22e879c7a33bf5b77e4fa51e88815e612 Mon Sep 17 00:00:00 2001
From: rxu <rxu@mail.ru>
Date: Sun, 3 Nov 2019 18:44:39 +0700
Subject: [ticket/15294] Fix session_gc() selecting expired sessions for unique
 users

Also remove limit of 10 as it does not allow to collect all the garbage.

PHPBB3-15294
---
 phpBB/phpbb/session.php | 56 ++++++++++++++++++++++++-------------------------
 1 file changed, 28 insertions(+), 28 deletions(-)

(limited to 'phpBB/phpbb/session.php')

diff --git a/phpBB/phpbb/session.php b/phpBB/phpbb/session.php
index 7c76c08b73..4552f3bd82 100644
--- a/phpBB/phpbb/session.php
+++ b/phpBB/phpbb/session.php
@@ -954,8 +954,6 @@ class session
 	{
 		global $db, $config, $phpbb_container, $phpbb_dispatcher;
 
-		$batch_size = 10;
-
 		if (!$this->time_now)
 		{
 			$this->time_now = time();
@@ -968,14 +966,21 @@ class session
 		$db->sql_query($sql);
 
 		// Get expired sessions, only most recent for each user
-		$sql = 'SELECT session_user_id, session_page, MAX(session_time) AS recent_time
-			FROM ' . SESSIONS_TABLE . '
-			WHERE session_time < ' . ($this->time_now - $config['session_length']) . '
-			GROUP BY session_user_id, session_page';
-		$result = $db->sql_query_limit($sql, $batch_size);
+		// Inner SELECT gets most recent expired sessions for unique session_user_id
+		// Outer SELECT gets session_page for them
+		$sql = 'SELECT s1.session_page, s1.session_user_id, s1.session_time AS recent_time
+			FROM ' . SESSIONS_TABLE . ' AS s1
+			INNER JOIN (
+				SELECT session_user_id, MAX(session_time) AS recent_time
+				FROM ' . SESSIONS_TABLE . '
+				WHERE session_time < ' . ($this->time_now - (int) $config['session_length']) . '
+				GROUP BY session_user_id
+			) AS s2
+			ON s1.session_user_id = s2.session_user_id
+				AND s1.session_time = s2.recent_time';
+		$result = $db->sql_query($sql);
 
 		$del_user_id = array();
-		$del_sessions = 0;
 
 		while ($row = $db->sql_fetchrow($result))
 		{
@@ -985,7 +990,6 @@ class session
 			$db->sql_query($sql);
 
 			$del_user_id[] = (int) $row['session_user_id'];
-			$del_sessions++;
 		}
 		$db->sql_freeresult($result);
 
@@ -998,29 +1002,25 @@ class session
 			$db->sql_query($sql);
 		}
 
-		if ($del_sessions < $batch_size)
-		{
-			// Less than 10 users, update gc timer ... else we want gc
-			// called again to delete other sessions
-			$config->set('session_last_gc', $this->time_now, false);
+		// Update gc timer
+		$config->set('session_last_gc', $this->time_now, false);
 
-			if ($config['max_autologin_time'])
-			{
-				$sql = 'DELETE FROM ' . SESSIONS_KEYS_TABLE . '
-					WHERE last_login < ' . (time() - (86400 * (int) $config['max_autologin_time']));
-				$db->sql_query($sql);
-			}
-
-			// only called from CRON; should be a safe workaround until the infrastructure gets going
-			/* @var $captcha_factory \phpbb\captcha\factory */
-			$captcha_factory = $phpbb_container->get('captcha.factory');
-			$captcha_factory->garbage_collect($config['captcha_plugin']);
-
-			$sql = 'DELETE FROM ' . LOGIN_ATTEMPT_TABLE . '
-				WHERE attempt_time < ' . (time() - (int) $config['ip_login_limit_time']);
+		if ($config['max_autologin_time'])
+		{
+			$sql = 'DELETE FROM ' . SESSIONS_KEYS_TABLE . '
+				WHERE last_login < ' . (time() - (86400 * (int) $config['max_autologin_time']));
 			$db->sql_query($sql);
 		}
 
+		// only called from CRON; should be a safe workaround until the infrastructure gets going
+		/* @var $captcha_factory \phpbb\captcha\factory */
+		$captcha_factory = $phpbb_container->get('captcha.factory');
+		$captcha_factory->garbage_collect($config['captcha_plugin']);
+
+		$sql = 'DELETE FROM ' . LOGIN_ATTEMPT_TABLE . '
+			WHERE attempt_time < ' . (time() - (int) $config['ip_login_limit_time']);
+		$db->sql_query($sql);
+
 		/**
 		* Event to trigger extension on session_gc
 		*
-- 
cgit v1.2.1


From 46a68d37ee858db19b3cb9100ff7f4be9e5e994e Mon Sep 17 00:00:00 2001
From: rxu <rxu@mail.ru>
Date: Sun, 3 Nov 2019 23:14:52 +0700
Subject: [ticket/15294] Another approach, optimize updating & purging expired
 sessions

PHPBB3-15294
---
 phpBB/phpbb/session.php | 63 ++++++++++++++++++++++++-------------------------
 1 file changed, 31 insertions(+), 32 deletions(-)

(limited to 'phpBB/phpbb/session.php')

diff --git a/phpBB/phpbb/session.php b/phpBB/phpbb/session.php
index 4552f3bd82..60551bfe99 100644
--- a/phpBB/phpbb/session.php
+++ b/phpBB/phpbb/session.php
@@ -959,48 +959,47 @@ class session
 			$this->time_now = time();
 		}
 
-		// Firstly, delete guest sessions
-		$sql = 'DELETE FROM ' . SESSIONS_TABLE . '
-			WHERE session_user_id = ' . ANONYMOUS . '
-				AND session_time < ' . (int) ($this->time_now - $config['session_length']);
-		$db->sql_query($sql);
-
-		// Get expired sessions, only most recent for each user
-		// Inner SELECT gets most recent expired sessions for unique session_user_id
-		// Outer SELECT gets session_page for them
-		$sql = 'SELECT s1.session_page, s1.session_user_id, s1.session_time AS recent_time
+		// Get expired sessions, only most recent for each registered user
+		// Inner SELECT gets most recent expired sessions data for unique session_user_id
+		// Outer SELECT gets also session_page for them
+		$sql_select = '(
+			SELECT s1.session_page, s1.session_user_id, s1.session_time AS recent_time
 			FROM ' . SESSIONS_TABLE . ' AS s1
 			INNER JOIN (
 				SELECT session_user_id, MAX(session_time) AS recent_time
 				FROM ' . SESSIONS_TABLE . '
 				WHERE session_time < ' . ($this->time_now - (int) $config['session_length']) . '
+					AND session_user_id <> ' . ANONYMOUS . '
 				GROUP BY session_user_id
 			) AS s2
 			ON s1.session_user_id = s2.session_user_id
-				AND s1.session_time = s2.recent_time';
-		$result = $db->sql_query($sql);
-
-		$del_user_id = array();
-
-		while ($row = $db->sql_fetchrow($result))
-		{
-			$sql = 'UPDATE ' . USERS_TABLE . '
-				SET user_lastvisit = ' . (int) $row['recent_time'] . ", user_lastpage = '" . $db->sql_escape($row['session_page']) . "'
-				WHERE user_id = " . (int) $row['session_user_id'];
-			$db->sql_query($sql);
-
-			$del_user_id[] = (int) $row['session_user_id'];
+				AND s1.session_time = s2.recent_time
+		) AS s3';
+
+		// Update user session data from above selected result
+		switch ($db->get_sql_layer())
+		{
+			case 'sqlite3':
+			case 'mysqli':
+				$sql = 'UPDATE ' . USERS_TABLE . " AS u,
+					$sql_select
+					SET u.user_lastvisit = s3.recent_time, u.user_lastpage = s3.session_page
+					WHERE u.user_id = s3.session_user_id";
+			break;
+
+			default:
+			$sql = 'UPDATE ' . USERS_TABLE . "
+					SET user_lastvisit = s3.recent_time, user_lastpage = s3.session_page
+					FROM $sql_select
+					WHERE user_id = s3.session_user_id";
+			break;
 		}
-		$db->sql_freeresult($result);
+		$db->sql_query($sql);
 
-		if (count($del_user_id))
-		{
-			// Delete expired sessions
-			$sql = 'DELETE FROM ' . SESSIONS_TABLE . '
-				WHERE ' . $db->sql_in_set('session_user_id', $del_user_id) . '
-					AND session_time < ' . ($this->time_now - $config['session_length']);
-			$db->sql_query($sql);
-		}
+		// Delete all expired sessions
+		$sql = 'DELETE FROM ' . SESSIONS_TABLE . '
+			WHERE session_time < ' . ($this->time_now - $config['session_length']);
+		$db->sql_query($sql);
 
 		// Update gc timer
 		$config->set('session_last_gc', $this->time_now, false);
-- 
cgit v1.2.1


From a340c362bcac64e115ddbb138bb90a8afa7faeb9 Mon Sep 17 00:00:00 2001
From: rxu <rxu@mail.ru>
Date: Mon, 4 Nov 2019 13:36:58 +0700
Subject: [ticket/15294] Back to the all DBMS compliant splitted queries

PHPBB3-15294
---
 phpBB/phpbb/session.php | 44 +++++++++++++++++---------------------------
 1 file changed, 17 insertions(+), 27 deletions(-)

(limited to 'phpBB/phpbb/session.php')

diff --git a/phpBB/phpbb/session.php b/phpBB/phpbb/session.php
index 60551bfe99..d8a0d4d708 100644
--- a/phpBB/phpbb/session.php
+++ b/phpBB/phpbb/session.php
@@ -959,11 +959,12 @@ class session
 			$this->time_now = time();
 		}
 
-		// Get expired sessions, only most recent for each registered user
-		// Inner SELECT gets most recent expired sessions data for unique session_user_id
-		// Outer SELECT gets also session_page for them
-		$sql_select = '(
-			SELECT s1.session_page, s1.session_user_id, s1.session_time AS recent_time
+		/**
+		* Get expired sessions for registered users, only most recent for each user
+		* Inner SELECT gets most recent expired sessions for unique session_user_id
+		* Outer SELECT gets data for them
+		*/
+		$sql = 'SELECT s1.session_page, s1.session_user_id, s1.session_time AS recent_time
 			FROM ' . SESSIONS_TABLE . ' AS s1
 			INNER JOIN (
 				SELECT session_user_id, MAX(session_time) AS recent_time
@@ -973,32 +974,21 @@ class session
 				GROUP BY session_user_id
 			) AS s2
 			ON s1.session_user_id = s2.session_user_id
-				AND s1.session_time = s2.recent_time
-		) AS s3';
-
-		// Update user session data from above selected result
-		switch ($db->get_sql_layer())
-		{
-			case 'sqlite3':
-			case 'mysqli':
-				$sql = 'UPDATE ' . USERS_TABLE . " AS u,
-					$sql_select
-					SET u.user_lastvisit = s3.recent_time, u.user_lastpage = s3.session_page
-					WHERE u.user_id = s3.session_user_id";
-			break;
-
-			default:
-			$sql = 'UPDATE ' . USERS_TABLE . "
-					SET user_lastvisit = s3.recent_time, user_lastpage = s3.session_page
-					FROM $sql_select
-					WHERE user_id = s3.session_user_id";
-			break;
+				AND s1.session_time = s2.recent_time';
+		$result = $db->sql_query($sql);
+
+		while ($row = $db->sql_fetchrow($result))
+		{
+			$sql = 'UPDATE ' . USERS_TABLE . '
+				SET user_lastvisit = ' . (int) $row['recent_time'] . ", user_lastpage = '" . $db->sql_escape($row['session_page']) . "'
+				WHERE user_id = " . (int) $row['session_user_id'];
+			$db->sql_query($sql);
 		}
-		$db->sql_query($sql);
+		$db->sql_freeresult($result);
 
 		// Delete all expired sessions
 		$sql = 'DELETE FROM ' . SESSIONS_TABLE . '
-			WHERE session_time < ' . ($this->time_now - $config['session_length']);
+			WHERE session_time < ' . ($this->time_now - (int) $config['session_length']);
 		$db->sql_query($sql);
 
 		// Update gc timer
-- 
cgit v1.2.1


From ba17f16dc87f7f584fa6a56e61ddd44a8aed7f2b Mon Sep 17 00:00:00 2001
From: rxu <rxu@mail.ru>
Date: Mon, 4 Nov 2019 21:48:20 +0700
Subject: [ticket/15294] Get rid of SQL query in a loop as far as possible

PHPBB3-15294
---
 phpBB/phpbb/session.php | 51 +++++++++++++++++++++++++++++++++++++++++--------
 1 file changed, 43 insertions(+), 8 deletions(-)

(limited to 'phpBB/phpbb/session.php')

diff --git a/phpBB/phpbb/session.php b/phpBB/phpbb/session.php
index d8a0d4d708..42ee509df4 100644
--- a/phpBB/phpbb/session.php
+++ b/phpBB/phpbb/session.php
@@ -964,7 +964,7 @@ class session
 		* Inner SELECT gets most recent expired sessions for unique session_user_id
 		* Outer SELECT gets data for them
 		*/
-		$sql = 'SELECT s1.session_page, s1.session_user_id, s1.session_time AS recent_time
+		$sql_select = 'SELECT s1.session_page, s1.session_user_id, s1.session_time AS recent_time
 			FROM ' . SESSIONS_TABLE . ' AS s1
 			INNER JOIN (
 				SELECT session_user_id, MAX(session_time) AS recent_time
@@ -975,16 +975,51 @@ class session
 			) AS s2
 			ON s1.session_user_id = s2.session_user_id
 				AND s1.session_time = s2.recent_time';
-		$result = $db->sql_query($sql);
 
-		while ($row = $db->sql_fetchrow($result))
+		switch ($db->get_sql_layer())
 		{
-			$sql = 'UPDATE ' . USERS_TABLE . '
-				SET user_lastvisit = ' . (int) $row['recent_time'] . ", user_lastpage = '" . $db->sql_escape($row['session_page']) . "'
-				WHERE user_id = " . (int) $row['session_user_id'];
-			$db->sql_query($sql);
+			case 'sqlite3':
+			case 'oracle':
+				if ($db->get_sql_layer() === 'sqlite3' && phpbb_version_compare($db->sql_server_info(true), '3.8.3', '>='))
+				{
+					// For SQLite versions 3.8.3+ which support Common Table Expressions (CTE)
+					$sql = "WITH s3 (session_page, session_user_id, session_time) AS ($sql_select)
+						UPDATE " . USERS_TABLE . '
+						SET (user_lastpage, user_lastvisit) = (SELECT session_page, session_time FROM s3 WHERE session_user_id = user_id)
+						WHERE EXISTS (SELECT session_user_id FROM s3 WHERE session_user_id = user_id)';
+					$db->sql_query($sql);
+				}
+				else
+				{
+					// For SQLite versions prior to 3.8.3 and Oracle
+					$result = $db->sql_query($sql_select);
+					while ($row = $db->sql_fetchrow($result))
+					{
+						$sql = 'UPDATE ' . USERS_TABLE . '
+							SET user_lastvisit = ' . (int) $row['recent_time'] . ", user_lastpage = '" . $db->sql_escape($row['session_page']) . "'
+							WHERE user_id = " . (int) $row['session_user_id'];
+						$db->sql_query($sql);
+					}
+					$db->sql_freeresult($result);
+				}
+			break;
+
+			case 'mysqli':
+				$sql = 'UPDATE ' . USERS_TABLE . " u,
+					($sql_select) s3
+					SET u.user_lastvisit = s3.recent_time, u.user_lastpage = s3.session_page
+					WHERE u.user_id = s3.session_user_id";
+				$db->sql_query($sql);
+			break;
+
+			default:
+				$sql = 'UPDATE ' . USERS_TABLE . "
+					SET user_lastvisit = s3.recent_time, user_lastpage = s3.session_page
+					FROM ($sql_select) s3
+					WHERE user_id = s3.session_user_id";
+				$db->sql_query($sql);
+			break;
 		}
-		$db->sql_freeresult($result);
 
 		// Delete all expired sessions
 		$sql = 'DELETE FROM ' . SESSIONS_TABLE . '
-- 
cgit v1.2.1


From 7ea063100e23234bf0d6a79fd0411e956a1b6668 Mon Sep 17 00:00:00 2001
From: rxu <rxu@mail.ru>
Date: Tue, 5 Nov 2019 20:42:59 +0700
Subject: [ticket/15294] Minor code adjusting

PHPBB3-15294
---
 phpBB/phpbb/session.php | 36 ++++++++++++++++++------------------
 1 file changed, 18 insertions(+), 18 deletions(-)

(limited to 'phpBB/phpbb/session.php')

diff --git a/phpBB/phpbb/session.php b/phpBB/phpbb/session.php
index 42ee509df4..6851bc8188 100644
--- a/phpBB/phpbb/session.php
+++ b/phpBB/phpbb/session.php
@@ -960,10 +960,10 @@ class session
 		}
 
 		/**
-		* Get expired sessions for registered users, only most recent for each user
-		* Inner SELECT gets most recent expired sessions for unique session_user_id
-		* Outer SELECT gets data for them
-		*/
+		 * Get expired sessions for registered users, only most recent for each user
+		 * Inner SELECT gets most recent expired sessions for unique session_user_id
+		 * Outer SELECT gets data for them
+		 */
 		$sql_select = 'SELECT s1.session_page, s1.session_user_id, s1.session_time AS recent_time
 			FROM ' . SESSIONS_TABLE . ' AS s1
 			INNER JOIN (
@@ -979,8 +979,7 @@ class session
 		switch ($db->get_sql_layer())
 		{
 			case 'sqlite3':
-			case 'oracle':
-				if ($db->get_sql_layer() === 'sqlite3' && phpbb_version_compare($db->sql_server_info(true), '3.8.3', '>='))
+				if (phpbb_version_compare($db->sql_server_info(true), '3.8.3', '>='))
 				{
 					// For SQLite versions 3.8.3+ which support Common Table Expressions (CTE)
 					$sql = "WITH s3 (session_page, session_user_id, session_time) AS ($sql_select)
@@ -988,20 +987,21 @@ class session
 						SET (user_lastpage, user_lastvisit) = (SELECT session_page, session_time FROM s3 WHERE session_user_id = user_id)
 						WHERE EXISTS (SELECT session_user_id FROM s3 WHERE session_user_id = user_id)';
 					$db->sql_query($sql);
+
+					break;
 				}
-				else
+
+			// No break, for SQLite versions prior to 3.8.3 and Oracle
+			case 'oracle':
+				$result = $db->sql_query($sql_select);
+				while ($row = $db->sql_fetchrow($result))
 				{
-					// For SQLite versions prior to 3.8.3 and Oracle
-					$result = $db->sql_query($sql_select);
-					while ($row = $db->sql_fetchrow($result))
-					{
-						$sql = 'UPDATE ' . USERS_TABLE . '
-							SET user_lastvisit = ' . (int) $row['recent_time'] . ", user_lastpage = '" . $db->sql_escape($row['session_page']) . "'
-							WHERE user_id = " . (int) $row['session_user_id'];
-						$db->sql_query($sql);
-					}
-					$db->sql_freeresult($result);
+					$sql = 'UPDATE ' . USERS_TABLE . '
+						SET user_lastvisit = ' . (int) $row['recent_time'] . ", user_lastpage = '" . $db->sql_escape($row['session_page']) . "'
+						WHERE user_id = " . (int) $row['session_user_id'];
+					$db->sql_query($sql);
 				}
+				$db->sql_freeresult($result);
 			break;
 
 			case 'mysqli':
@@ -1037,7 +1037,7 @@ class session
 		}
 
 		// only called from CRON; should be a safe workaround until the infrastructure gets going
-		/* @var $captcha_factory \phpbb\captcha\factory */
+		/* @var \phpbb\captcha\factory $captcha_factory */
 		$captcha_factory = $phpbb_container->get('captcha.factory');
 		$captcha_factory->garbage_collect($config['captcha_plugin']);
 
-- 
cgit v1.2.1