diff mbox series

[3/3] rewrite query to support both mysql/sqlite

Message ID 20210217032854.245535-3-eschwartz@archlinux.org
State New
Headers show
Series [1/3] fix broken SQL query that always failed | expand

Commit Message

Eli Schwartz Feb. 17, 2021, 3:28 a.m. UTC 
Signed-off-by: Eli Schwartz <eschwartz@archlinux.org>
---

I don't know nearly enough SQL to know any kind of tradeoffs between one
or the other, but this one should theoretically *work* everywhere, and
certainly runs to success here on my sqlite development environment.

 web/lib/acctfuncs.inc.php | 14 +++++---------
 1 file changed, 5 insertions(+), 9 deletions(-)
diff mbox series

Patch

diff --git a/web/lib/acctfuncs.inc.php b/web/lib/acctfuncs.inc.php
index 30c4cfe0..752abe97 100644
--- a/web/lib/acctfuncs.inc.php
+++ b/web/lib/acctfuncs.inc.php
@@ -597,21 +597,17 @@  function try_login() {
 	/* Generate a session ID and store it. */
 	while (!$logged_in && $num_tries < 5) {
 		$session_limit = config_get_int('options', 'max_sessions_per_user');
-		# FIXME: this does not work for sqlite (JOIN in a DELETE clause)
-		# hence non-prod instances can have a naughty amount of simultaneous logins
-		if ($backend == "mysql" && $session_limit) {
+		if ($session_limit) {
 			/*
 			 * Delete all user sessions except the
 			 * last ($session_limit - 1).
 			 */
-			$q = "DELETE s.* FROM Sessions s ";
-			$q.= "LEFT JOIN (SELECT SessionID FROM Sessions ";
+			$q = "DELETE FROM Sessions ";
 			$q.= "WHERE UsersId = " . $userID . " ";
+			$q.= "AND SessionID NOT IN (SELECT SessionID FROM Sessions ";
+			$q.= "WHERE UsersID = " . $userID . " ";
 			$q.= "ORDER BY LastUpdateTS DESC ";
-			$q.= "LIMIT " . ($session_limit - 1) . ") q ";
-			$q.= "ON s.SessionID = q.SessionID ";
-			$q.= "WHERE s.UsersId = " . $userID . " ";
-			$q.= "AND q.SessionID IS NULL;";
+			$q.= "LIMIT " . ($session_limit - 1) . ")";
 			$dbh->query($q);
 		}