<?php
/**
* SeekQuarry/Yioop --
* Open Source Pure PHP Search Engine, Crawler, and Indexer
*
* Copyright (C) 2009 - 2026 Chris Pollett chris@pollett.org
*
* LICENSE:
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <https://www.gnu.org/licenses/>.
*
* END LICENSE
*
* @author Chris Pollett chris@pollett.org
* @license https://www.gnu.org/licenses/ GPL3
* @link https://www.seekquarry.com/
* @copyright 2009 - 2026
* @filesource
*/
namespace seekquarry\yioop\models;
use seekquarry\yioop\configs as C;
use seekquarry\yioop\library as L;
use seekquarry\yioop\library\processors\ImageProcessor;
/** For getLocaleTag*/
require_once __DIR__.'/../library/LocaleFunctions.php';
/**
* UserModel this class is used to handle database statements related to User
* Administration
* @author Chris Pollett
*/
class UserModel extends Model
{
/**
* search_table_column_map stores associations of the form name of field for
* web forms => database column names/abbreviations In this case, things
* will in general map to the USERS table in the Yioop data base
* @var array
*/
public $search_table_column_map = ["first"=>"FIRST_NAME",
"last" => "LAST_NAME", "user" => "USER_NAME", "email"=>"EMAIL",
"status"=>"STATUS"];
/**
* any_fields stores these fields if present in $search_array (used by @see
* getRows() ), but with value "-1", will be skipped as part of the where
* clause but will be used for order by clause
* @var array
*/
public $any_fields = ["status"];
/**
* selectCallback {@inheritDoc}
* @param mixed $args any additional arguments which should be used to
* determine these tables (in this case none)
*/
public function selectCallback($args = null)
{
return "USER_ID, USER_NAME, FIRST_NAME, LAST_NAME, EMAIL, STATUS";
}
/**
* fromCallback {@inheritDoc}
* @param mixed $args any additional arguments which should be used to
* determine these tables (in this case none)
*/
public function fromCallback($args = null)
{
return "USERS";
}
/**
* whereCallback {@inheritDoc}
* @param mixed $args any additional arguments which should be used to
* determine these tables (in this case none)
*/
public function whereCallback($args = null)
{
return "USER_ID != '" . C\PUBLIC_USER_ID . "'";
}
/**
* getUserActivities get a list of admin activities that a user is allowed
* to perform. This includes their name and their associated method.
* @param string $user_id id of user to get activities fors
* @return array list of admin-activity rows the user is permitted to run
* (each row contains the activity's METHOD_NAME and localized name);
* empty array when the user is suspended or inactive
*/
public function getUserActivities($user_id)
{
$db = $this->db;
$activities = [];
$status = $this->getUserStatus($user_id);
if (!$status || in_array($status,
[C\SUSPENDED_STATUS, C\INACTIVE_STATUS])) {
return [];
}
$locale_tag = L\getLocaleTag();
$limit_offset = $db->limitOffset(1);
$sql = "SELECT LOCALE_ID FROM LOCALE ".
"WHERE LOCALE_TAG = ? $limit_offset";
$result = $db->execute($sql, [$locale_tag]);
$row = $db->fetchArray($result);
$locale_id = $row['LOCALE_ID'];
$sql = "SELECT DISTINCT A.ACTIVITY_ID AS ACTIVITY_ID, ".
"T.TRANSLATION_ID AS TRANSLATION_ID, A.METHOD_NAME AS METHOD_NAME,".
" RA.ALLOWED_ARGUMENTS AS ALLOWED_ARGUMENTS," .
" T.IDENTIFIER_STRING AS IDENTIFIER_STRING FROM ACTIVITY A, ".
" USER_ROLE UR, ROLE_ACTIVITY RA, TRANSLATION T ".
" WHERE UR.USER_ID = ? " . (" AND (UR.EXPIRES = " . C\FOREVER .
" OR UR.EXPIRES > " . time() . ") ") .
" AND UR.ROLE_ID=RA.ROLE_ID AND T.TRANSLATION_ID=A.TRANSLATION_ID ".
" AND RA.ACTIVITY_ID = A.ACTIVITY_ID ORDER BY A.ACTIVITY_ID ASC";
$result = $db->execute($sql, [$user_id]);
$sub_sql = "SELECT TRANSLATION AS ACTIVITY_NAME ".
"FROM TRANSLATION_LOCALE ".
"WHERE TRANSLATION_ID=? AND LOCALE_ID=? $limit_offset";
// maybe do left join at some point
while ($row = $this->db->fetchArray($result)) {
$id = $row['TRANSLATION_ID'];
if (empty($activities[$id])) {
$activities[$id] = $row;
} else {
if (empty($activities[$id]["ALLOWED_ARGUMENTS"]) ||
$activities[$id]["ALLOWED_ARGUMENTS"] == "all") {
$activities[$id]["ALLOWED_ARGUMENTS"] = "all";
continue;
} else if (empty($row["ALLOWED_ARGUMENTS"]) ||
trim($row["ALLOWED_ARGUMENTS"]) == "all") {
$activities[$id]["ALLOWED_ARGUMENTS"] = "all";
}
$activities[$id]["ALLOWED_ARGUMENTS"] .= ", " .
$row["ALLOWED_ARGUMENTS"];
}
$result_sub = $db->execute($sub_sql, [$id, $locale_id]);
$translate = $db->fetchArray($result_sub);
if ($translate) {
$activities[$id]['ACTIVITY_NAME'] = $translate['ACTIVITY_NAME'];
}
if (!isset($activities[$id]['ACTIVITY_NAME']) ||
$activities[$id]['ACTIVITY_NAME'] == "") {
$activities[$id]['ACTIVITY_NAME'] = $this->translateDb(
$activities[$id]['IDENTIFIER_STRING'],
C\p('DEFAULT_LOCALE'));
}
}
$activities = array_values($activities);
return $activities;
}
/**
* isAllowedUserActivity checks if a user is allowed to perform the activity
* given by method name
* @param string $user_id id of user to check
* @param string $method_name to see if user allowed to do
* @param bool $get_roles when true, return the list of roles that grant the
* activity instead of a boolean (used by the role-audit UI)
* @return mixed bool whether the user is allowed; or, when $get_roles is
* true, the array of granting role ids
*/
public function isAllowedUserActivity($user_id, $method_name,
$get_roles = false)
{
$db = $this->db;
$select = "COUNT(*)";
$sql = "SELECT $select AS ALLOWED FROM ACTIVITY A, ".
"USER_ROLE UR, ROLE_ACTIVITY RA WHERE UR.USER_ID = ? " .
(" AND (UR.EXPIRES = " . C\FOREVER . " OR UR.EXPIRES > " .
time() . ") ") .
"AND UR.ROLE_ID=RA.ROLE_ID AND A.METHOD_NAME = ? ".
"AND RA.ACTIVITY_ID = A.ACTIVITY_ID";
$result = $db->execute($sql, [$user_id, $method_name]);
$role_ids = [];
if ($result) {
while($row = $db->fetchArray($result)) {
if (intval($row['ALLOWED']) > 0) {
return true;
}
}
}
return false;
}
/**
* setSessionUser records which signed-in user a session id belongs to, so
* the session can be restored later if the server no longer has it in
* memory (for example after it was dropped under load or the server
* restarted). Any earlier mapping for the same session id is replaced.
* Nothing is stored for the public (not signed-in) user.
* @param string $session_id the session id from the visitor's cookie
* @param int $user_id id of the signed-in user
* @param int $expires unix time after which this mapping should no longer
* be honored
*/
public function setSessionUser($session_id, $user_id, $expires)
{
if ($user_id == C\PUBLIC_USER_ID) {
return;
}
$sql = "DELETE FROM AUTH_SESSION WHERE SESSION_ID = ?";
$this->db->execute($sql, [$session_id]);
$sql = "INSERT INTO AUTH_SESSION VALUES (?, ?, ?)";
$this->db->execute($sql, [$session_id, $user_id, $expires]);
}
/**
* getSessionUser looks up which signed-in user a session id belongs to,
* ignoring mappings that have passed their expiry time. Used to restore a
* session that is no longer in the server's memory.
* @param string $session_id the session id from the visitor's cookie
* @return int the user id, or 0 when there is no usable mapping
*/
public function getSessionUser($session_id)
{
$db = $this->db;
$sql = "SELECT USER_ID FROM AUTH_SESSION " .
"WHERE SESSION_ID = ? AND EXPIRES > ? " . $db->limitOffset(1);
$result = $db->execute($sql, [$session_id, time()]);
if (!empty($result)) {
$row = $db->fetchArray($result);
if (!empty($row["USER_ID"])) {
return $row["USER_ID"];
}
}
return 0;
}
/**
* deleteSessionUser removes the mapping from a session id to its user, for
* example when the person signs out so the session can no longer be
* restored.
* @param string $session_id the session id from the visitor's cookie
*/
public function deleteSessionUser($session_id)
{
$sql = "DELETE FROM AUTH_SESSION WHERE SESSION_ID = ?";
$this->db->execute($sql, [$session_id]);
}
/**
* getUserSession returns $_SESSION variable of given user from the last
* time logged in.
* @param int $user_id id of user to get session for
* @return array user's session data
*/
public function getUserSession($user_id)
{
$db = $this->db;
$sql = "SELECT SESSION FROM USER_SESSION ".
"WHERE USER_ID = ? " . $db->limitOffset(1);
$result = $db->execute($sql, [$user_id]);
if (!empty($result)) {
$row = $db->fetchArray($result);
if (!empty($row["SESSION"])) {
$session = unserialize($row["SESSION"]);
if (!empty($session)) {
return $session;
}
}
}
return [];
}
/**
* setUserSession stores into DB the $session associative array of given
* user
* @param int $user_id id of user to store session for
* @param array $session session data for the given user
* @return bool true on a successful insert, false when the session exceeds
* MAX_USER_SESSION_SIZE, the user is PUBLIC_USER_ID, or the insert
* itself failed
*/
public function setUserSession($user_id, $session)
{
$session_string = serialize($session);
if (strlen($session_string) >= C\MAX_USER_SESSION_SIZE ||
$user_id == C\PUBLIC_USER_ID) {
return false;
}
$sql = "DELETE FROM USER_SESSION WHERE USER_ID = ?";
$this->db->execute($sql, [$user_id]);
$sql = "INSERT INTO USER_SESSION VALUES (?, ?)";
if(!$this->db->execute($sql, [$user_id, $session_string])) {
return false;
}
return true;
}
/**
* getUsername get a username by user_id
* @param string $user_id id of the user
* @return string the name of the user corresponding to that id
*/
public function getUsername($user_id)
{
$db = $this->db;
if (intval($user_id) != $user_id) {
return false; //keep postgres error log cleaner by doing check
}
$sql = "SELECT USER_NAME FROM USERS WHERE USER_ID = ?";
$result = $db->execute($sql, [$user_id]);
$row = $db->fetchArray($result);
return ($row['USER_NAME'] ?? false);
}
/**
* getUserStatus get the status of user by user_id
* @param string $user_id id of the user
* @return int the status flag of the user: ACTIVE, INACTIVE, INVITED,
* SUSPENDED
*/
public function getUserStatus($user_id)
{
$db = $this->db;
if (intval($user_id) != $user_id) {
return false; //keep postgres error log cleaner by doing check
}
$sql = "SELECT STATUS FROM USERS WHERE USER_ID = ?";
$result = $db->execute($sql, [$user_id]);
$row = $db->fetchArray($result);
return $row['STATUS'] ?? false;
}
/**
* usersStartingWith gives the users whose name begins with the
* letters somebody has typed, in alphabetical order. The messages
* screen and the manage users screen both offer names as a person
* types, and this is where those names come from. The letters are
* matched from the start of a name rather than anywhere in it, so
* what is offered always extends what was typed. A name is matched
* without regard to upper or lower case.
*
* @param string $beginning the letters typed so far, already trimmed
* @param int $how_many the most names to give back
* @param array $leave_out user ids to keep out of the answer, such as
* the person doing the typing
* @return array rows holding USER_ID and USER_NAME, alphabetical by
* name, empty where nothing begins with those letters
*/
public function usersStartingWith($beginning, $how_many,
$leave_out = [])
{
$db = $this->db;
$beginning = trim((string)$beginning);
if ($beginning === "") {
return [];
}
/* The letters go into a pattern that matches from the start, and
anything in them that the pattern language reads as a wildcard
is made ordinary first, so a typed underscore or percent looks
for itself. */
$pattern = str_replace(["\\", "%", "_"],
["\\\\", "\\%", "\\_"], $beginning) . "%";
$sql = "SELECT USER_ID, USER_NAME FROM USERS " .
"WHERE LOWER(USER_NAME) LIKE LOWER(?) " .
"AND STATUS = ? ORDER BY USER_NAME " .
$db->limitOffset(0, $how_many);
$result = $db->execute($sql, [$pattern, C\ACTIVE_STATUS]);
if (!$result) {
return [];
}
$found = [];
while ($row = $db->fetchArray($result)) {
if (in_array($row['USER_ID'], $leave_out)) {
continue;
}
$found[] = $row;
}
return $found;
}
/**
* getUser returns a row from the USERS table based on a username
* (case-insensitive)
* @param string $username user login to be used for look up
* @return array corresponds to the row of that user in the USERS
* table
*/
public function getUser($username)
{
$db = $this->db;
$sql = "SELECT * FROM USERS WHERE LOWER(USER_NAME) = LOWER(?) " .
$db->limitOffset(1);
$result = $db->execute($sql, [$username]);
if (!$result) {
return false;
}
$row = $db->fetchArray($result);
if (isset($row['USER_ID'])) {
$row['USER_ICON'] = $this->getUserIconUrl($row['USER_ID']);
$row['IS_BOT_USER'] = $this->isBotUser($row['USER_ID']);
}
if (isset($row['IS_BOT_USER'])) {
$sql = "SELECT BOT_TOKEN, CALLBACK_URL
FROM CHAT_BOT WHERE USER_ID = ?";
$result = $db->execute($sql,[$row['USER_ID']]);
$bot_row = $db->fetchArray($result);
if (!empty($bot_row)) {
$row['BOT_TOKEN'] = $bot_row['BOT_TOKEN'];
$row['CALLBACK_URL'] = $bot_row['CALLBACK_URL'];
} else {
$row['BOT_TOKEN'] = "";
$row['CALLBACK_URL'] = "";
}
}
return $row;
}
/**
* getUserByEmail looks up the account, if any, that uses a given email
* address. Used to stop the site's own bot address from being pointed at an
* address some member already signs up with. The match ignores letter case.
* @param string $email the email address to look up
* @return array|bool the matching user row, or false when no account uses
* that address
*/
public function getUserByEmail($email)
{
$db = $this->db;
$sql = "SELECT * FROM USERS WHERE LOWER(EMAIL) = LOWER(?) " .
$db->limitOffset(1);
$result = $db->execute($sql, [$email]);
if (!$result) {
return false;
}
$row = $db->fetchArray($result);
if (isset($row['USER_ID'])) {
$row['USER_ICON'] = $this->getUserIconUrl($row['USER_ID']);
}
return $row;
}
/**
* setRecoveryQuestion saves the personal recovery question a member typed
* and, when an answer is given, the hash of that answer, used when the site
* recovers accounts by question rather than by email. The question is kept
* as plain text so it can be shown back to the member, while only a one-way
* hash of the answer is stored, the same way a password is. Passing null
* for the answer leaves the stored answer untouched, which lets a member
* reword their question without retyping the answer.
* @param int $user_id numeric id of the member
* @param string $question the recovery question the member typed
* @param string $answer the member's answer, hashed before storage, or null
* to keep the answer already on file
*/
public function setRecoveryQuestion($user_id, $question, $answer = null)
{
if ($answer === null) {
$sql = "UPDATE USERS SET RECOVERY_QUESTION = ? WHERE USER_ID = ?";
$this->db->execute($sql, [$question, $user_id]);
return;
}
$sql = "UPDATE USERS SET RECOVERY_QUESTION = ?, " .
"RECOVERY_ANSWER = ? WHERE USER_ID = ?";
$this->db->execute($sql,
[$question, L\crawlCrypt($answer), $user_id]);
}
/**
* checkRecoveryAnswer checks whether a typed answer matches the stored hash
* of a member's recovery answer, using a constant-time comparison so a
* wrong answer and a right one take the same time to reject.
* @param array $user a USERS row holding the stored RECOVERY_ANSWER hash
* @param string $answer the answer the person typed during recovery
* @return bool true only when the answer matches the stored hash
*/
public function checkRecoveryAnswer($user, $answer)
{
if (empty($user['RECOVERY_ANSWER'])) {
return false;
}
return hash_equals($user['RECOVERY_ANSWER'],
L\crawlCrypt($answer, $user['RECOVERY_ANSWER']));
}
/**
* getUserById looks up a user by their numeric user id and returns the
* whole USERS row, or false when there is no such user. Used by the
* account-activation link, which names the new member by an opaque signed
* token holding only this id, so the email address never travels in the
* link.
* @param int $user_id the id of the user to look up
* @return array|bool the user's database row as an associative array, or
* false when no user has that id
*/
public function getUserById($user_id)
{
$db = $this->db;
$sql = "SELECT * FROM USERS WHERE USER_ID = ? " .
$db->limitOffset(1);
$result = $db->execute($sql, [$user_id]);
if (!$result) {
return false;
}
$row = $db->fetchArray($result);
if (isset($row['USER_ID'])) {
$row['USER_ICON'] = $this->getUserIconUrl($row['USER_ID']);
}
return $row;
}
/**
* userFolderAndPrefix works out the on-disk folder name and its short
* prefix for a user's private files. The name is a crawlHash of the user id
* keyed by the site AUTH_KEY, so it cannot be guessed from the id alone,
* and the prefix is its first few characters, used to spread users across
* subdirectories rather than piling them into one folder. The hash is left
* as crawlHash on purpose: it names an existing folder on disk, so changing
* it would orphan every user's current files.
* @param int $user_id id of the user whose folder is wanted
* @return array the folder name followed by its prefix
*/
public function userFolderAndPrefix($user_id)
{
$user_folder = L\crawlHash("user" . $user_id . C\p('AUTH_KEY'));
$user_prefix = substr($user_folder, 0, C\FOLDER_PREFIX_LEN);
return [$user_folder, $user_prefix];
}
/**
* getUserIconUrl returns the relative url needed to request the given users
* avatar icon
* @param int $user_id user to look up path for
* @return string path to icon
*/
public function getUserIconUrl($user_id)
{
$user_icon = C\SHORT_BASE_URL . "resources/anonymous.png";
list($user_folder, $user_prefix) =
$this->userFolderAndPrefix($user_id);
$icon_path = C\APP_DIR .
"/resources/$user_prefix/$user_folder/user_icon.webp";
if (file_exists($icon_path)) {
if (C\REDIRECTS_ON) {
$user_icon = C\SHORT_BASE_URL .
"wd/users/$user_folder/user_icon.webp";
} else {
$user_icon = C\SHORT_BASE_URL .
"?c=resource&a=get&f=resources&" .
"s=$user_folder&n=user_icon.webp";
}
}
return $user_icon;
}
/**
* getUserIconFolder returns the path to a user's resource folder (where
* uploaded files will be stored). It creates the folder if it does not
* exist
* @param int $user_id user id of user to get path for
* @return string|bool absolute filesystem path to the user's resource
* folder on success; false if any of the parent directories could not
* be created
*/
public function getUserIconFolder($user_id)
{
list($user_folder, $user_prefix) =
$this->userFolderAndPrefix($user_id);
$resource_path = C\APP_DIR . "/resources";
$prefix_path = $resource_path."/$user_prefix";
$user_path = "$prefix_path/$user_folder";
if (file_exists($user_path)) {
return $user_path;
}
if (!file_exists(C\APP_DIR) && !mkdir(C\APP_DIR)) {
return false;
}
if (!file_exists($resource_path) && !mkdir($resource_path)) {
return false;
}
if (!file_exists($prefix_path) && !mkdir($prefix_path)) {
return false;
}
if (mkdir($user_path)) {
return $user_path;
}
return false;
}
/**
* updateUserStatus set status of user by user_id
* @param string $user_id id of the user
* @param int $status one of ACTIVE_STATUS, INACTIVE_STATUS, or
* SUSPENDED_STATUS
*/
public function updateUserStatus($user_id, $status)
{
$db = $this->db;
if (!in_array($status, [C\ACTIVE_STATUS, C\INACTIVE_STATUS,
C\SUSPENDED_STATUS])) {
return;
}
$sql = "UPDATE USERS SET STATUS=? WHERE USER_ID=?";
$db->execute($sql, [$status, $user_id]);
}
/**
* addUser add a user with a given username and password to the list of
* users that can login to the admin panel
* @param string $username the username of the user to be added
* @param string $password the password in plaintext of the user to be
* added
* @param string $firstname the firstname of the user to be added
* @param string $lastname the lastname of the user to be added
* @param string $email the email of the user to be added
* @param int $status one of ACTIVE_STATUS, INACTIVE_STATUS, or
* SUSPENDED_STATUS
* @return mixed false if operation not successful, user_id otherwise
*/
public function addUser($username, $password, $firstname = '', $lastname='',
$email = '', $status = C\ACTIVE_STATUS)
{
$creation_time = L\microTimestamp();
$db = $this->db;
$sql = "INSERT INTO USERS(FIRST_NAME, LAST_NAME,
USER_NAME, EMAIL, PASSWORD, STATUS, HASH,
CREATION_TIME) VALUES (
?, ?, ?, ?, ?, ?, ?, ?)";
$username = mb_strtolower($username);
$result = $db->execute($sql, [$firstname, $lastname,
$username, $email, L\crawlCrypt($password), $status,
L\crawlCrypt($username . C\p('AUTH_KEY') . $creation_time),
$creation_time]);
if (!$user_id = $this->getUserId($username)) {
return false;
}
$now = time();
$user_id = $db->escapeString($user_id);
$sql = "INSERT INTO USER_GROUP (USER_ID, GROUP_ID, STATUS,
JOIN_DATE) VALUES(?, ?, ?, ?)";
$result = $db->execute($sql, [$user_id, C\PUBLIC_GROUP_ID,
C\ACTIVE_STATUS, $now]);
/* Naming the columns rather than relying on their order lets the
two columns a role grant has gained since, when it lapses and
whether it renews, take the defaults they were added with; a
row given only two values in order is refused outright once a
table has more columns than that. */
$sql = "INSERT INTO USER_ROLE (USER_ID, ROLE_ID) VALUES (?, ?)";
$result_id = $db->execute($sql, [$user_id, C\USER_ROLE]);
return $user_id;
}
/**
* deleteUser deletes a user by username from the list of users that can
* login to the admin panel
* @param string $username the login name of the user to delete
*/
public function deleteUser($username)
{
$db = $this->db;
$user_id = $this->getUserId($username);
if ($user_id) {
$sql = "DELETE FROM USER_ROLE WHERE USER_ID=?";
$result = $db->execute($sql, [$user_id]);
$sql = "DELETE FROM USER_GROUP WHERE USER_ID=?";
$result = $db->execute($sql, [$user_id]);
$sql = "DELETE FROM USER_SESSION WHERE USER_ID=?";
$result = $db->execute($sql, [$user_id]);
}
$sql = "DELETE FROM USERS WHERE USER_ID=?";
$result = $db->execute($sql, [$user_id]);
}
/**
* updateUser used to update the fields stored in a USERS row according to
* an array holding new values
* @param array $user updated values for a USERS row
*/
public function updateUser($user)
{
$user_id = $user['USER_ID'];
if (isset($user['IMAGE_STRING'])) {
$folder = $this->getUserIconFolder($user_id);
$image = @imagecreatefromstring($user['IMAGE_STRING']);
$thumb_string = ImageProcessor::createThumb($image);
if (!empty($thumb_string)) {
file_put_contents($folder . "/user_icon.webp",
$thumb_string);
clearstatcache($folder . "/user_icon.webp");
}
}
unset($user['USER_ID'], $user['IMAGE_STRING'], $user['USER_ICON'],
$user['IS_BOT_USER'], $user['BOT_TOKEN'], $user['CALLBACK_URL']);
$user['USER_NAME'] = mb_strtolower($this->getUserName(
$user_id));
if (empty($user['USER_NAME'])) {
unset($user['USER_NAME']);
}
/* A caller that read a user's record, changed a field, and
handed the record back is not setting a password: what it
holds is the stored password, already scrambled. Scrambling
it again would leave the account's owner unable to sign in
with the password they have, which is what happened to
anybody whose name or email was edited. */
if (isset($user['PASSWORD']) &&
$user['PASSWORD'] === $this->storedPassword($user_id)) {
unset($user['PASSWORD']);
}
$sql = "UPDATE USERS SET ";
$comma ="";
$params = [];
if ($user == []) {
return;
}
foreach ($user as $field => $value) {
$sql .= "$comma $field=? ";
$comma = ",";
if ($field == "PASSWORD") {
$params[] = L\crawlCrypt($value);
} else {
$params[] = $value;
}
}
$sql .= " WHERE USER_ID=?";
$params[] = $user_id;
$this->db->execute($sql, $params);
}
/**
* storedPassword gives what is stored as a user's password, which is the
* scrambled form rather than anything anybody typed. Used to tell a caller
* setting a new password from one handing back the record it read.
* @param int $user_id which user
* @return string what is stored, or the empty string where there is no such
* user
*/
public function storedPassword($user_id)
{
$sql = "SELECT PASSWORD FROM USERS WHERE USER_ID=?";
$result = $this->db->execute($sql, [$user_id]);
if (!$result) {
return "";
}
$row = $this->db->fetchArray($result);
return $row['PASSWORD'] ?? "";
}
/**
* isBotUser checks if a user is bot
* @param string $user_id id of user to check
* @return bool whether or not the user is bot
*/
public function isBotUser($user_id)
{
$db = $this->db;
$sql = "SELECT COUNT(*) AS NUM FROM ".
"USER_ROLE UR WHERE UR.USER_ID = ? ".
"AND UR.ROLE_ID = ?";
$is_bot = false;
$bot_id = RoleModel::getRoleIdWithDb("Bot User", $db);
if ($bot_id) {
$result = $db->execute($sql, [$user_id, $bot_id]);
if ($result) {
while ($row = $db->fetchArray($result)) {
if ($row["NUM"] > 0) {
$is_bot = true;
} else {
$is_bot = false;
}
}
}
}
return $is_bot;
}
/**
* updateBot used to update the fields stored in a CHAT_BOT row according to
* an array holding new values
* @param array $user updated values for a CHAT_BOT row
*/
public function updateBot($user)
{
if ($user == []) {
return;
}
$db = $this->db;
$sql = "SELECT COUNT(*) AS NUM FROM ".
"CHAT_BOT CB WHERE CB.USER_ID = ? ";
$result = $db->execute($sql, [$user['USER_ID']]);
$is_new_bot = false;
if ($result) {
while ($row = $db->fetchArray($result)) {
if ($row["NUM"] > 0) {
$is_new_bot = false;
} else {
$is_new_bot = true;
}
}
}
if (!$this->isBotUser($user["USER_ID"])) {
if (!$is_new_bot) {
$sql = "DELETE FROM CHAT_BOT WHERE USER_ID = ?";
$db->execute($sql, [$user['USER_ID']]);
}
return;
}
if ($is_new_bot) {
$user['BOT_TOKEN'] = (empty($user['BOT_TOKEN'])) ? "" :
$user['BOT_TOKEN'];
$user['CALLBACK_URL'] = (empty($user['CALLBACK_URL'])) ? "" :
$user['CALLBACK_URL'];
$sql = "INSERT INTO CHAT_BOT(USER_ID, BOT_TOKEN,
CALLBACK_URL) VALUES (?, ?, ?)";
$result = $db->execute($sql, [$user['USER_ID'], $user['BOT_TOKEN'],
$user['CALLBACK_URL']]);
} else {
$sql = "UPDATE CHAT_BOT SET ";
$comma = "";
$params = [];
foreach ($user as $field => $value) {
if ($field == "USER_ID") {
$sql .= "$comma $field = ? ";
$comma = ",";
$params[] = $value;
} else if ($field == "BOT_TOKEN") {
$sql .= "$comma $field = ? ";
$comma = ",";
$params[] = $value;
} else if ($field == "CALLBACK_URL") {
$sql .= "$comma $field = ? ";
$comma = ",";
$params[] = $value;
}
}
$sql .= " WHERE USER_ID = ?";
$params[] = $user['USER_ID'];
$this->db->execute($sql, $params);
}
}
/**
* getRecommendations gets $num many user recommendations of the given type.
* @param int $timestamp of the recommendation media job calculation of
* recommendations (available using cron model
* item_group_recommendations)
* @param int $user_id to user to get recommendations for
* @param int $type = one of C\TRENDING_RECOMMENDATION,
* C\THREAD_RECOMMENDATION, or C\GROUP_RECOMMENDATION
* @param int $num the number of recommendations to return
* @return array list of recommendation rows (each having ID and NAME
* columns from GROUP_ITEM or SOCIAL_GROUPS depending on $type), ordered
* by SCORE DESC and capped at $num entries
*/
public function getRecommendations($timestamp, $user_id, $type, $num = 3)
{
$db = $this->db;
$name_table = "GROUP_ITEM";
$name_column = "TITLE";
$name_id = "ID";
if ($type == C\GROUP_RECOMMENDATION) {
$name_table = "SOCIAL_GROUPS";
$name_column = "GROUP_NAME";
$name_id = "GROUP_ID";
}
$sql = "SELECT IR.ITEM_ID AS ID, NT.$name_column AS NAME ".
"FROM GROUP_ITEM_RECOMMENDATION IR, $name_table NT ".
"WHERE IR.ITEM_ID = NT.$name_id AND IR.USER_ID = ? AND " .
"ITEM_TYPE = ? AND TIMESTAMP = $timestamp " .
"ORDER BY SCORE DESC " . $db->limitOffset($num);
$result = $db->execute($sql, [$user_id, $type]);
$recommendations = [];
while($row = $db->fetchArray($result)) {
$recommendations[$row['ID']] = $row['NAME'];
}
return $recommendations;
}
/**
* getResourceRecommendations returns the top wiki-resource recommendations
* for the given user, resolved from GROUP_RESOURCE_RECOMMENDATION back into
* a list of [group_id, page_id, page_title, resource_name, sub_path] tuples
* the UI can link to.
* @param int $user_id user whose recommendations to load
* @param int $num max number of recommendations to return (default 3)
* @return array list of resource-recommendation tuples ordered by SCORE
* DESC, capped at $num entries
*/
public function getResourceRecommendations($user_id, $num = 3)
{
$db = $this->db;
$sql = "SELECT * FROM GROUP_RESOURCE_RECOMMENDATION WHERE" .
" USER_ID = ? ORDER BY SCORE DESC " . $db->limitOffset($num);
$results = $db->execute($sql, [$user_id]);
$recommendations = [];
while($row = $db->fetchArray($results)) {
$group_id = $row['GROUP_ID'];
$page_id = $row['PAGE_ID'];
$page_sql = "SELECT TITLE FROM GROUP_PAGE WHERE ID = ?";
$result = $db->execute($page_sql, [$page_id]);
while ($sub_row = $db->fetchArray($result)) {
$page_title = $sub_row['TITLE'];
}
$index = strrpos($row['RESOURCE_PATH'], "/");
$name = substr($row['RESOURCE_PATH'], $index + 1);
$resource_index = strrpos($row['RESOURCE_PATH'], "/resources/");
$sub_path = substr($row['RESOURCE_PATH'], $resource_index + 28,
$index - $resource_index - 28);
$recommendations[] = [$group_id, $page_id, $page_title ?? "",
$name ?? "", $sub_path ?? ""];
}
return $recommendations;
}
}
(c) 2026 Seekquarry - Open Source Search Engine Software