Multiple user testing bugs

[pjc09h]

Thanks wgm! Actions items based on an email conversation:

• [done] Inconsistent email database encryption. User email addresses should either be encrypted or not (preferably encrypted). Causes bugs in the user settings page and likely elsewhere (invites).
• [done] Email change confirmation was never fully implemented. The callback that sends the email with a selector and token is commented out. It also needs to use the library function directly, or a custom function to handle encrypted email addresses needs to use the same signature.
• [mitigated] I need to double check the usability and flow of the user settings page. Requiring the current passphrase to change anything may be hostile to the UX and introduce bugs.
• [done] Figure out why logging out works different across browsers (no in Chrome, yes in Firefox).
• [done] Ensure that all variables in the User class are defined as $foo ??= null before trying to use them (500 error on user stats).
• [!!] Go through the user-related database queries and ensure that they're using the most recent schema definition, e.g., select where users.id = ? but join users_main on users_main.userId.
• [done] Ensure that account recovery works (it didn't).
• [done] Implement the "resend confirmation email" feature in such a way that the link appears if the user is unconfirmed on login or if a valid confirmation is expired. This should provide a sensible recovery from email errors or slowness, without enabling an adversary to spam a target with my mail server.
• [done] Increase the usability and security of the registration and passphrase reset forms by describing complexity requirements up front and exposing the "suggest a secure passphrase" functionality to public forms.
• [done] Update the privacy policy to include WebAuthn.

[!!] Actually, there's a better way to do this for now. The codebase references users_main 355 times in 140 files. Many of these are legacy "glitch art" queries, either massive or programmatically determined. I need to remove the auto_increment and primary key properties of users_main.id and manually set it to users.id. Then the existing queries for users_main.id should work long enough for them to be rewritten.

[pjc09h]

A good example of "user query glitch art":

$app->dbOld->prepared_query("
  (SELECT
    SQL_CALC_FOUND_ROWS
    s.`Page`,
    s.`PageID`,
    lr.`PostID`,
    null AS ForumID,
    null AS ForumName,
    IF(s.`Page` = 'artist', a.`Name`, co.`Name`) AS Name,
    c.`ID` AS LastPost,
    c.`AddedTime` AS LastPostTime,
    c_lr.`Body` AS LastReadBody,
    c_lr.`EditedTime` AS LastReadEditedTime,
    um.`ID` AS LastReadUserID,
    um.`Username` AS LastReadUsername,
    ui.`Avatar` AS LastReadAvatar,
    c_lr.`EditedUserID` AS LastReadEditedUserID
  FROM `users_subscriptions_comments` AS s
    LEFT JOIN `users_comments_last_read` AS lr ON lr.`UserID` = {$app->user->core['id']} AND lr.`Page` = s.`Page` AND lr.`PageID` = s.`PageID`
    LEFT JOIN `artists_group` AS a ON s.`Page` = 'artist' AND a.`ArtistID` = s.`PageID`
    LEFT JOIN `collages` AS co ON s.`Page` = 'collages' AND co.`ID` = s.`PageID`
    LEFT JOIN `comments` AS c ON c.`ID` = (
          SELECT MAX(`ID`)
          FROM `comments`
          WHERE `Page` = s.`Page`
            AND `PageID` = s.`PageID`
        )
    LEFT JOIN `comments` AS c_lr ON c_lr.`ID` = lr.`PostID`
    LEFT JOIN `users_main` AS um ON um.`ID` = c_lr.`AuthorID`
    LEFT JOIN `users_info` AS ui ON ui.`UserID` = um.`ID`
  WHERE s.`UserID` = {$app->user->core['id']} AND s.`Page` IN ('artist', 'collages', 'requests', 'torrents') AND (s.`Page` != 'collages' OR co.`Deleted` = '0')" . ($ShowUnread ? ' AND c.`ID` > IF(lr.`PostID` IS NULL, 0, lr.`PostID`)' : '') . "
  GROUP BY s.`PageID`)
  UNION ALL
  (SELECT 'forums', s.`TopicID`, lr.`PostID`, f.`ID`, f.`Name`, t.`Title`, p.`ID`, p.`AddedTime`, p_lr.`Body`, p_lr.`EditedTime`, um.`ID`, um.`Username`, ui.`Avatar`, p_lr.`EditedUserID`
  FROM `users_subscriptions` AS s
    LEFT JOIN `forums_last_read_topics` AS lr ON lr.`UserID` = {$app->user->core['id']} AND s.`TopicID` = lr.`TopicID`
    LEFT JOIN `forums_topics` AS t ON t.`ID` = s.`TopicID`
    LEFT JOIN `forums` AS f ON f.`ID` = t.`ForumID`
    LEFT JOIN `forums_posts` AS p ON p.`ID` = (
          SELECT MAX(`ID`)
          FROM `forums_posts`
          WHERE `TopicID` = s.`TopicID`
        )
    LEFT JOIN `forums_posts` AS p_lr ON p_lr.`ID` = lr.`PostID`
    LEFT JOIN `users_main` AS um ON um.`ID` = p_lr.`AuthorID`
    LEFT JOIN `users_info` AS ui ON ui.`UserID` = um.`ID`
  WHERE s.`UserID` = {$app->user->core['id']}" .
    ($ShowUnread ? " AND p.`ID` > IF(t.`IsLocked` = '1' AND t.`IsSticky` = '0'" . ", p.`ID`, IF(lr.`PostID` IS NULL, 0, lr.`PostID`))" : '') .
    ' AND ' . Forums::user_forums_sql() . "
  GROUP BY t.`ID`)
  ORDER BY `LastPostTime` DESC
  LIMIT $Limit");

[pjc09h]

All that remains is to do the necessary database surgery (quicker without a migration) and do a quick end-to-end test of the features on a new account.