dbConn = $dbConn; $this->cache = new DbStatementCache($dbConn); } public const NAME_MIN_LENGTH = 3; public const NAME_MAX_LENGTH = 16; public const PASSWORD_ALGO = PASSWORD_ARGON2ID; public const PASSWORD_OPTS = []; public const PASSWORD_UNIQUE = 6; public const PROFILE_ABOUT_MAX_LENGTH = 50000; public const FORUM_SIGNATURE_MAX_LENGTH = 2000; public static function passwordHash(string $password): string { return password_hash($password, self::PASSWORD_ALGO, self::PASSWORD_OPTS); } public static function passwordNeedsRehash(string $passwordHash): bool { return password_needs_rehash($passwordHash, self::PASSWORD_ALGO, self::PASSWORD_OPTS); } public function countUsers( RoleInfo|string|null $roleInfo = null, UserInfo|string|null $after = null, ?int $lastActiveInMinutes = null, ?int $newerThanDays = null, ?DateTime $birthdate = null, ?bool $deleted = null ): int { if($roleInfo instanceof RoleInfo) $roleInfo = $roleInfo->getId(); if($after instanceof UserInfo) $after = $after->getId(); $hasRoleInfo = $roleInfo !== null; $hasAfter = $after !== null; $hasLastActiveInMinutes = $lastActiveInMinutes !== null; $hasNewerThanDays = $newerThanDays !== null; $hasBirthdate = $birthdate !== null; $hasDeleted = $deleted !== null; $args = 0; $query = 'SELECT COUNT(*) FROM msz_users'; if($hasRoleInfo) { ++$args; $query .= ' WHERE user_id IN (SELECT user_id FROM msz_users_roles WHERE role_id = ?)'; } if($hasAfter) $query .= sprintf(' %s user_id > ?', ++$args > 1 ? 'AND' : 'WHERE'); if($hasLastActiveInMinutes) $query .= sprintf(' %s user_active > NOW() - INTERVAL ? MINUTE', ++$args > 1 ? 'AND' : 'WHERE'); if($hasNewerThanDays) $query .= sprintf(' %s user_created > NOW() - INTERVAL ? DAY', ++$args > 1 ? 'AND' : 'WHERE'); if($hasDeleted) $query .= sprintf(' %s user_deleted %s NULL', ++$args > 1 ? 'AND' : 'WHERE', $deleted ? 'IS NOT' : 'IS'); $args = 0; $stmt = $this->cache->get($query); if($hasRoleInfo) $stmt->addParameter(++$args, $roleInfo); if($hasAfter) $stmt->addParameter(++$args, $after); if($hasLastActiveInMinutes) $stmt->addParameter(++$args, $lastActiveInMinutes); if($hasNewerThanDays) $stmt->addParameter(++$args, $newerThanDays); $stmt->execute(); $result = $stmt->getResult(); return $result->next() ? $result->getInteger(0) : 0; } private const GET_USERS_SORT = [ 'id' => ['user_id', false], 'name' => ['username', false], 'country' => ['user_country', false], 'created' => ['user_created', true], 'active' => ['user_active', true], 'random' => ['RAND()', null], ]; public function getUsers( RoleInfo|string|null $roleInfo = null, UserInfo|string|null $after = null, ?int $lastActiveInMinutes = null, ?int $newerThanDays = null, ?DateTime $birthdate = null, ?bool $deleted = null, ?string $orderBy = null, ?bool $reverseOrder = null, ?array $searchQuery = null, ?Pagination $pagination = null ): iterable { // remove this hack when search server $hasSearchQuery = $searchQuery !== null; $searchLimitResults = false; if($hasSearchQuery) { if(!empty($searchQuery['type']) && $searchQuery['type'] !== 'member') return []; $roleInfo = null; $after = null; $lastActiveInMinutes = null; $newerThanDays = null; $birthdate = null; $deleted = false; $orderBy = 'id'; $reverseOrder = false; $pagination = null; $searchLimitResults = true; if(!empty($searchQuery['after'])) $after = $searchQuery['after']; $searchQuery = $searchQuery['query_string']; $hasSearchQuery = !empty($searchQuery); } if($roleInfo instanceof RoleInfo) $roleInfo = $roleInfo->getId(); if($after instanceof UserInfo) $after = $after->getId(); $hasRoleInfo = $roleInfo !== null; $hasAfter = $after !== null; $hasLastActiveInMinutes = $lastActiveInMinutes !== null; $hasNewerThanDays = $newerThanDays !== null; $hasBirthdate = $birthdate !== null; $hasDeleted = $deleted !== null; $hasOrderBy = $orderBy !== null; $hasReverseOrder = $reverseOrder !== null; $hasPagination = $pagination !== null; if($hasOrderBy) { if(!array_key_exists($orderBy, self::GET_USERS_SORT)) throw new InvalidArgumentException('Invalid sort specified.'); $orderBy = self::GET_USERS_SORT[$orderBy]; if($hasReverseOrder && $reverseOrder && $orderBy[1] !== null) $orderBy[1] = !$orderBy[1]; } $args = 0; $query = 'SELECT user_id, username, password, email, INET6_NTOA(register_ip), INET6_NTOA(last_ip), user_super, user_country, user_colour, UNIX_TIMESTAMP(user_created), UNIX_TIMESTAMP(user_active), UNIX_TIMESTAMP(user_deleted), display_role, user_totp_key, user_about_content, user_about_parser, user_signature_content, user_signature_parser, user_birthdate, user_background_settings, user_title FROM msz_users'; if($hasRoleInfo) { ++$args; $query .= ' WHERE user_id IN (SELECT user_id FROM msz_users_roles WHERE role_id = ?)'; } if($hasAfter) $query .= sprintf(' %s user_id > ?', ++$args > 1 ? 'AND' : 'WHERE'); if($hasLastActiveInMinutes) $query .= sprintf(' %s user_active > NOW() - INTERVAL ? MINUTE', ++$args > 1 ? 'AND' : 'WHERE'); if($hasNewerThanDays) $query .= sprintf(' %s user_created > NOW() - INTERVAL ? DAY', ++$args > 1 ? 'AND' : 'WHERE'); if($hasDeleted) $query .= sprintf(' %s user_deleted %s NULL', ++$args > 1 ? 'AND' : 'WHERE', $deleted ? 'IS NOT' : 'IS'); if($hasBirthdate) $query .= sprintf(' %s user_birthdate LIKE ?', ++$args > 1 ? 'AND' : 'WHERE'); if($hasSearchQuery) $query .= sprintf(' %s username LIKE CONCAT("%%", ?, "%%")', ++$args > 1 ? 'AND' : 'WHERE'); if($hasOrderBy) { $query .= sprintf(' ORDER BY %s', $orderBy[0]); if($orderBy[1] !== null) $query .= ' ' . ($orderBy[1] ? 'DESC' : 'ASC'); } if($searchLimitResults) $query .= ' LIMIT 20'; elseif($hasPagination) $query .= ' LIMIT ? OFFSET ?'; $args = 0; $stmt = $this->cache->get($query); if($hasRoleInfo) $stmt->addParameter(++$args, $roleInfo); if($hasAfter) $stmt->addParameter(++$args, $after); if($hasLastActiveInMinutes) $stmt->addParameter(++$args, $lastActiveInMinutes); if($hasNewerThanDays) $stmt->addParameter(++$args, $newerThanDays); if($hasBirthdate) $stmt->addParameter(++$args, $birthdate->format('%-m-d')); if($hasSearchQuery) $stmt->addParameter(++$args, $searchQuery); if($hasPagination) { $stmt->addParameter(++$args, $pagination->getRange()); $stmt->addParameter(++$args, $pagination->getOffset()); } $stmt->execute(); return $stmt->getResult()->getIterator(UserInfo::fromResult(...)); } public const GET_USER_ID = 0x01; public const GET_USER_NAME = 0x02; public const GET_USER_MAIL = 0x04; private const GET_USER_SELECT_ALIASES = [ 'id' => self::GET_USER_ID, 'name' => self::GET_USER_NAME, 'email' => self::GET_USER_MAIL, 'profile' => self::GET_USER_ID | self::GET_USER_NAME, 'search' => self::GET_USER_ID | self::GET_USER_NAME, 'messaging' => self::GET_USER_ID | self::GET_USER_NAME, 'login' => self::GET_USER_NAME | self::GET_USER_MAIL, 'recovery' => self::GET_USER_MAIL, ]; public static function resolveGetUserSelectAlias(int|string $select): int { if(is_string($select)) { if(!array_key_exists($select, self::GET_USER_SELECT_ALIASES)) throw new InvalidArgumentException('Invalid $select alias.'); $select = self::GET_USER_SELECT_ALIASES[$select]; } elseif($select === 0) throw new InvalidArgumentException('$select may not be zero.'); return $select; } public function getUser(string $value, int|string $select = self::GET_USER_ID): UserInfo { if($value === '') throw new InvalidArgumentException('$value may not be empty.'); $select = self::resolveGetUserSelectAlias($select); $selectId = ($select & self::GET_USER_ID) > 0; $selectName = ($select & self::GET_USER_NAME) > 0; $selectMail = ($select & self::GET_USER_MAIL) > 0; if(!$selectId && !$selectName && !$selectMail) throw new InvalidArgumentException('$select flagset is invalid.'); $args = 0; $query = 'SELECT user_id, username, password, email, INET6_NTOA(register_ip), INET6_NTOA(last_ip), user_super, user_country, user_colour, UNIX_TIMESTAMP(user_created), UNIX_TIMESTAMP(user_active), UNIX_TIMESTAMP(user_deleted), display_role, user_totp_key, user_about_content, user_about_parser, user_signature_content, user_signature_parser, user_birthdate, user_background_settings, user_title FROM msz_users'; if($selectId) { ++$args; $query .= ' WHERE user_id = ?'; } if($selectName) $query .= sprintf(' %s username = ?', ++$args > 1 ? 'OR' : 'WHERE'); if($selectMail) $query .= sprintf(' %s email = ?', ++$args > 1 ? 'OR' : 'WHERE'); $args = 0; $stmt = $this->cache->get($query); if($selectId) $stmt->addParameter(++$args, $value); if($selectName) $stmt->addParameter(++$args, $value); if($selectMail) $stmt->addParameter(++$args, $value); $stmt->execute(); $result = $stmt->getResult(); if(!$result->next()) throw new RuntimeException('User not found.'); return UserInfo::fromResult($result); } public function createUser( string $name, string $password, string $email, IPAddress|string $remoteAddr, string $countryCode, RoleInfo|string|null $displayRoleInfo = null ): UserInfo { if($remoteAddr instanceof IPAddress) $remoteAddr = (string)$remoteAddr; if($displayRoleInfo instanceof RoleInfo) $displayRoleInfo = $displayRoleInfo->getId(); elseif($displayRoleInfo === null) $displayRoleInfo = Roles::DEFAULT_ROLE; $password = self::passwordHash($password); if(self::validateName($name, true) !== '') throw new InvalidArgumentException('$name is not a valid user name.'); if(self::validateEMailAddress($email, true) !== '') throw new InvalidArgumentException('$email is not a valid e-mail address.'); $stmt = $this->cache->get('INSERT INTO msz_users (username, password, email, register_ip, last_ip, user_country, display_role) VALUES (?, ?, ?, INET6_ATON(?), INET6_ATON(?), ?, ?)'); $stmt->addParameter(1, $name); $stmt->addParameter(2, $password); $stmt->addParameter(3, $email); $stmt->addParameter(4, $remoteAddr); $stmt->addParameter(5, $remoteAddr); $stmt->addParameter(6, $countryCode); $stmt->addParameter(7, $displayRoleInfo); $stmt->execute(); return $this->getUser((string)$this->dbConn->getLastInsertId(), self::GET_USER_ID); } public function updateUser( UserInfo|string $userInfo, ?string $name = null, ?string $emailAddr = null, ?string $password = null, ?string $countryCode = null, ?Colour $colour = null, RoleInfo|string|null $displayRoleInfo = null, ?string $totpKey = null, ?string $aboutContent = null, ?int $aboutParser = null, ?string $signatureContent = null, ?int $signatureParser = null, ?int $birthYear = null, ?int $birthMonth = null, ?int $birthDay = null, ?int $backgroundSettings = null, ?string $title = null ): void { if($userInfo instanceof UserInfo) $userInfo = $userInfo->getId(); if($displayRoleInfo instanceof RoleInfo) $displayRoleInfo = $displayRoleInfo->getId(); $fields = []; $values = []; if($name !== null) { if(self::validateName($name, true) !== '') throw new InvalidArgumentException('$name is not valid.'); $fields[] = 'username = ?'; $values[] = $name; } if($emailAddr !== null) { if(self::validateEMailAddress($emailAddr, true) !== '') throw new InvalidArgumentException('$emailAddr is not valid.'); $fields[] = 'email = ?'; $values[] = $emailAddr; } if($password !== null) { $fields[] = 'password = ?'; $values[] = $password === '' ? null : self::passwordHash($password); } if($countryCode !== null) { $fields[] = 'user_country = ?'; $values[] = $countryCode; } if($colour !== null) { $fields[] = 'user_colour = ?'; $values[] = $colour->shouldInherit() ? null : Colour::toMisuzu($colour); } if($displayRoleInfo !== null) { $fields[] = 'display_role = ?'; $values[] = $displayRoleInfo; } if($totpKey !== null) { $fields[] = 'user_totp_key = ?'; $values[] = $totpKey === '' ? null : $totpKey; } if($aboutContent !== null && $aboutParser !== null) { if(self::validateProfileAbout($aboutParser, $aboutContent) !== '') throw new InvalidArgumentException('$aboutContent and $aboutParser contain invalid data!'); $fields[] = 'user_about_content = ?'; $values[] = $aboutContent; $fields[] = 'user_about_parser = ?'; $values[] = $aboutParser; } if($signatureContent !== null && $signatureParser !== null) { if(self::validateForumSignature($signatureParser, $signatureContent) !== '') throw new InvalidArgumentException('$signatureContent and $signatureParser contain invalid data!'); $fields[] = 'user_signature_content = ?'; $values[] = $signatureContent; $fields[] = 'user_signature_parser = ?'; $values[] = $signatureParser; } if($birthMonth !== null && $birthDay !== null) { if(self::validateBirthdate($birthYear, $birthMonth, $birthDay) !== '') throw new InvalidArgumentException('$birthYear, $birthMonth and $birthDay contain invalid data!'); // lowest leap year MariaDB accepts, used a 'no year' value if($birthYear < 1004) $birthYear = 1004; $fields[] = 'user_birthdate = ?'; $values[] = $birthMonth < 1 || $birthDay < 1 ? null : sprintf('%04d-%02d-%02d', $birthYear, $birthMonth, $birthDay); } if($backgroundSettings !== null) { $fields[] = 'user_background_settings = ?'; $values[] = $backgroundSettings; } if($title !== null) { $fields[] = 'user_title = ?'; $values[] = $title; } if(empty($fields)) return; $args = 0; $stmt = $this->cache->get(sprintf('UPDATE msz_users SET %s WHERE user_id = ?', implode(', ', $fields))); foreach($values as $value) $stmt->addParameter(++$args, $value); $stmt->addParameter(++$args, $userInfo); $stmt->execute(); } public function recordUserActivity( UserInfo|string $userInfo, IPAddress|string $remoteAddr ): void { if($userInfo instanceof UserInfo) $userInfo = $userInfo->getId(); if($remoteAddr instanceof IPAddress) $remoteAddr = (string)$remoteAddr; $stmt = $this->cache->get('UPDATE msz_users SET user_active = NOW(), last_ip = INET6_ATON(?) WHERE user_id = ?'); $stmt->addParameter(1, $remoteAddr); $stmt->addParameter(2, $userInfo); $stmt->execute(); } public function hasRole( UserInfo|string $userInfo, RoleInfo|string $roleInfo ): bool { if($userInfo instanceof UserInfo) $userInfo = $userInfo->getId(); if($roleInfo instanceof RoleInfo) $roleInfo = $roleInfo->getId(); return in_array($roleInfo, $this->hasRoles($userInfo, $roleInfo)); } public function hasRoles( UserInfo|string $userInfo, RoleInfo|string|array $roleInfos ): array { if($userInfo instanceof UserInfo) $userInfo = $userInfo->getId(); if(!is_array($roleInfos)) $roleInfos = [$roleInfos]; elseif(empty($roleInfos)) return []; $args = 0; $stmt = $this->cache->get(sprintf( 'SELECT role_id FROM msz_users_roles WHERE user_id = ? AND role_id IN (%s)', DbTools::prepareListString($roleInfos) )); $stmt->addParameter(++$args, $userInfo); foreach($roleInfos as $roleInfo) { if($roleInfo instanceof RoleInfo) $roleInfo = $roleInfo->getId(); elseif(!is_string($roleInfo)) throw new InvalidArgumentException('$roleInfos must be strings of instances of RoleInfo.'); $stmt->addParameter(++$args, $roleInfo); } $stmt->execute(); $roleIds = []; $result = $stmt->getResult(); while($result->next()) $roleIds[] = (string)$result->getInteger(0); return $roleIds; } public function addRoles( UserInfo|string $userInfo, RoleInfo|string|array $roleInfos ): void { if($userInfo instanceof UserInfo) $userInfo = $userInfo->getId(); if(!is_array($roleInfos)) $roleInfos = [$roleInfos]; elseif(empty($roleInfos)) return; $stmt = $this->cache->get(sprintf( 'REPLACE INTO msz_users_roles (user_id, role_id) VALUES %s', DbTools::prepareListString($roleInfos, '(?, ?)') )); $args = 0; foreach($roleInfos as $roleInfo) { if($roleInfo instanceof RoleInfo) $roleInfo = $roleInfo->getId(); elseif(!is_string($roleInfo)) throw new InvalidArgumentException('$roleInfos must be strings of instances of RoleInfo.'); $stmt->addParameter(++$args, $userInfo); $stmt->addParameter(++$args, $roleInfo); } $stmt->execute(); } public function removeRoles( UserInfo|string $userInfo, RoleInfo|string|array $roleInfos ): void { if($userInfo instanceof UserInfo) $userInfo = $userInfo->getId(); if(!is_array($roleInfos)) $roleInfos = [$roleInfos]; elseif(empty($roleInfos)) return; $args = 0; $stmt = $this->cache->get(sprintf( 'DELETE FROM msz_users_roles WHERE user_id = ? AND role_id IN (%s)', DbTools::prepareListString($roleInfos) )); $stmt->addParameter(++$args, $userInfo); foreach($roleInfos as $roleInfo) { if($roleInfo instanceof RoleInfo) $roleInfo = $roleInfo->getId(); elseif(!is_string($roleInfo)) throw new InvalidArgumentException('$roleInfos must be strings of instances of RoleInfo.'); $stmt->addParameter(++$args, $roleInfo); } $stmt->execute(); } // the below two funcs should probably be moved to a higher level location so caching can be introduced // without cluttering the data source interface <-- real words i just wrote public function getUserColour(UserInfo|string $userInfo): Colour { if($userInfo instanceof UserInfo) { if($userInfo->hasColour()) return $userInfo->getColour(); $query = '?'; $value = $userInfo->getDisplayRoleId(); } else { $query = '(SELECT display_role FROM msz_users WHERE user_id = ?)'; $value = $userInfo; } $stmt = $this->cache->get(sprintf('SELECT role_colour FROM msz_roles WHERE role_id = %s', $query)); $stmt->addParameter(1, $value); $stmt->execute(); $result = $stmt->getResult(); return $result->next() ? Colour::fromMisuzu($result->getInteger(0)) : Colour::none(); } public function getUserRank(UserInfo|string $userInfo): int { if($userInfo instanceof UserInfo) $userInfo = $userInfo->getId(); $stmt = $this->cache->get('SELECT MAX(role_hierarchy) FROM msz_roles WHERE role_id IN (SELECT role_id FROM msz_users_roles WHERE user_id = ?)'); $stmt->addParameter(1, $userInfo); $stmt->execute(); $result = $stmt->getResult(); return $result->next() ? $result->getInteger(0) : 0; } public function checkNameInUse(string $name): bool { $stmt = $this->cache->get('SELECT COUNT(*) FROM msz_users WHERE username = ?'); $stmt->addParameter(1, $name); $stmt->execute(); $result = $stmt->getResult(); if(!$result->next()) throw new RuntimeException('Was not able to check if name is already in use.'); return $result->getInteger(0) > 0; } public function validateName(string $name, bool $skipInUse = false): string { if($name !== trim($name)) return 'trim'; if(str_starts_with(mb_strtolower($name), 'flappyzor')) return 'flapp'; $length = mb_strlen($name); if($length < self::NAME_MIN_LENGTH) return 'short'; if($length > self::NAME_MAX_LENGTH) return 'long'; if(!preg_match('#^[A-Za-z0-9-_]+$#u', $name)) return 'invalid'; if(!$skipInUse && $this->checkNameInUse($name)) return 'used'; return ''; } public static function validateNameText(string $error): string { return match($error) { 'trim' => 'Your username may not start or end with spaces.', 'short' => sprintf('Your username is too short, it has to be at least %d characters.', self::NAME_MIN_LENGTH), 'long' => sprintf("Your username is too long, it can't be longer than %d characters.", self::NAME_MAX_LENGTH), 'invalid' => 'Your username contains invalid characters.', 'used' => 'That username is already taken.', 'flapp' => 'Your username may not start with Flappyzor.', '' => 'Your username is correctly formatted, why are you seeing this?', default => 'This username is incorrectly formatted.', }; } public function checkEMailAddressInUse(string $address): bool { $stmt = $this->cache->get('SELECT COUNT(*) FROM msz_users WHERE email = ?'); $stmt->addParameter(1, $address); $stmt->execute(); $result = $stmt->getResult(); if(!$result->next()) throw new RuntimeException('Was not able to check if e-mail address is already in use.'); return $result->getInteger(0) > 0; } public function validateEMailAddress(string $address, bool $skipInUse = false): string { if(filter_var($address, FILTER_VALIDATE_EMAIL) === false) return 'invalid'; if(!checkdnsrr(mb_substr(mb_strstr($address, '@'), 1), 'MX')) return 'dns'; if(!$skipInUse && $this->checkEMailAddressInUse($address)) return 'used'; return ''; } public static function validateEMailAddressText(string $error): string { return match($error) { 'dns' => 'Was unable to find a mail server running on the domain in your e-mail address.', '' => 'Your e-mail address is correctly formatted, why are you seeing this?', default => 'Your e-mail address is not correctly formatted.', }; } public static function validatePassword(string $password): string { if(XString::countUnique($password) < self::PASSWORD_UNIQUE) return 'weak'; return ''; } public static function validatePasswordText(string $error): string { return match($error) { 'weak' => sprintf("Your password is too weak, it must contain at least %d unique characters.", self::PASSWORD_UNIQUE), '' => 'Your password is strong enough, why are you seeing this?', default => 'Your password is not acceptable.', }; } public static function validateBirthdate(?int $year, int $month, int $day, int $yearRange = 100): string { $year ??= 0; if($day !== 0 && $month !== 0) { $currentYear = (int)date('Y'); if($year > 0 && ($year < $currentYear - $yearRange || $year > $currentYear)) return 'year'; if(!Tools::isValidDate($year, $month, $day)) return 'date'; } return ''; } public static function validateBirthdateText(string $error): string { return match($error) { 'year' => 'The year in your birthdate is too ridiculous.', 'date' => 'The birthdate you attempted to set is not a valid date.', '' => 'Your birthdate is fine, why are you seeing this?', default => 'Your birthdate is not acceptable.', }; } public static function validateProfileAbout(int $parser, string $text): string { if(!Parser::isValid($parser)) return 'parser'; $length = strlen($text); if($length > self::PROFILE_ABOUT_MAX_LENGTH) return 'long'; return ''; } public static function validateProfileAboutText(string $error): string { return match($error) { 'parser' => 'You attempted to select an invalid parser for your profile about section.', 'long' => sprintf('Please keep the length of your profile about section below %d characters.', self::PROFILE_ABOUT_MAX_LENGTH), '' => 'Your profile about section is fine, why are you seeing this?', default => 'Your profile about section is not acceptable.', }; } public static function validateForumSignature(int $parser, string $text): string { if(!Parser::isValid($parser)) return 'parser'; $length = strlen($text); if($length > self::FORUM_SIGNATURE_MAX_LENGTH) return 'long'; return ''; } public static function validateForumSignatureText(string $error): string { return match($error) { 'parser' => 'You attempted to select an invalid parser for your forum signature.', 'long' => sprintf('Please keep the length of your forum signature below %d characters.', self::FORUM_SIGNATURE_MAX_LENGTH), '' => 'Your forum signature is fine, why are you seeing this?', default => 'Your forum signature is not acceptable.', }; } }