KaiRo bug 413 - Set up doctrine DBAL to drive the DB and do schema migration

[authserver.git] / app / authutils.php-class

<?php
/* This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this file,
 * You can obtain one at http://mozilla.org/MPL/2.0/. */

class AuthUtils {
  // KaiRo.at authentication utilities PHP class
  // This class contains helper functions for the authentication system.
  //
  // function __construct()
  //   CONSTRUCTOR
  //
  // public $settings
  //   An array of settings for the auth server website.
  //
  // public $db
  //   A PDO database object for interaction.
  //
  // public $running_on_localhost
  //   A boolean telling if the system is running on localhost (where https is not required).
  //
  // public $client_reg_email_whitelist
  //   An array of emails that are whitelisted for registering clients.
  //
  // private $pwd_cost
  //   The cost parameter for use with PHP password_hash function.
  //
  // private $pwd_nonces
  //   The array of nonces to use for "peppering" passwords. For new hashes, the last one of those will be used.
  //     Generate a nonce with this command: |openssl rand -base64 48|
  //
  // function log($code, $additional_info)
  //   Log an entry for admin purposes, with a code and some additional info.
  //
  // function checkForSecureConnection()
  //   Check is the connection is secure and return an array of error messages (empty if it's secure).
  //
  // function sendSecurityHeaders()
  //   Rend HTTP headers for improving security.
  //
  // function initSession()
  //   Initialize a session. Returns an associative array of all the DB fields of the session.
  //
  // function getLoginSession($user)
  //   Return an associative array of a session with the given user logged in (new if user changed compared to given previous session, otherwise updated variant of that previous session).
  //
  // function setRedirect($session, $redirect)
  //   Set a redirect on the session for performing later. Returns true if a redirect was saved, otherwise false.
  //
  // function doRedirectIfSet($session)
  //   If the session has a redirect set, perform it. Returns true if a redirect was performed, otherwise false.
  //
  // function resetRedirect($session)
  //   If the session has a redirect set, remove it. Returns true if a redirect was removed, otherwise false.
  //
  // function getDomainBaseURL()
  //   Get the base URL of the current domain, e.g. 'https://example.com'.
  //
  // function checkPasswordConstraints($new_password, $user_email)
  //   Check password constraints and return an array of error messages (empty if all constraints are met).
  //
  // function createSessionKey()
  //   Return a random session key.
  //
  // function createVerificationCode()
  //   Return a random acount/email verification code.
  //
  // function createClientSecret()
  //   Return a random client secret.
  //
  // function createTimeCode($session, [$offset], [$validity_minutes])
  //   Return a time-based code based on the key and ID of the given session.
  //     An offset can be given to create a specific code for verification, otherwise and offset will be generated.
  //     Also, an amount of minutes for the code to stay valid can be handed over, by default 10 minutes will be used.
  //
  // function verifyTimeCode($timecode_to_verify, $session, [$validity_minutes])
  //   Verify a given time-based code and return true if it's valid or false if it's not.
  //     See createTimeCode() documentation for the session and validity paramerters.
  //
  // function pwdHash($new_password)
  //   Return a hash for the given password.
  //
  // function pwdVerify($password_to_verify, $user)
  //   Return true if the password verifies against the pwdhash field of the user, false if not.
  //
  // function pwdNeedsRehash($user)
  //   Return true if the pwdhash field of the user uses an outdated standard and needs to be rehashed.
  //
  // function setUpL10n()
  //   Set up the localization stack (gettext).
  //
  // function negotiateLocale($supportedLanguages)
  //   Return the language to use out of the given array of supported locales, via netotiation based on the HTTP Accept-Language header.
  //
  // function getGroupedEmails($group_id, [$exclude_email])
  //   Return all emails grouped in the specified group ID, optionally exclude a specific email (e.g. because you only want non-current entries)
  //
  // function getOAuthServer()
  //   Return an OAuth2 server object to use for all our actual OAuth2 interaction.
  //
  // function initHTMLDocument($titletext, [$headlinetext]) {
  //   initialize the HTML document for the auth system, with some elements we always use, esp. all the scripts and stylesheet.
  //     Sets the title of the document to the given title, the main headline will be the same as the title if not set explicitly.
  //     Returns an associative array with the following elements: 'document', 'html', 'head', 'title', 'body'.
  //
  // function appendLoginForm($dom_element, $session, $user, [$addfields])
  //   Append a login form for the given session to the given DOM element, possibly prefilling the email from the given user info array.
  //     The optional $addfields parameter is an array of name=>value pairs of hidden fields to add to the form.
  //
  // function updateDBSchema()
  //   update DB Schema to current requirements as specified by getDBSchema().
  //
  // function getDBSchema()
  //   Return a DB schema with all tables, fields, etc. that the app requires.

  function __construct() {
    // *** constructor ***
    $this->settings = json_decode(@file_get_contents('/etc/kairo/auth_settings.json'), true);
    if (!is_array($this->settings)) { throw new ErrorException('Authentication system settings not found', 0); }
    $config = new \Doctrine\DBAL\Configuration();
    $connectionParams = array(
        'dbname' => $this->settings['db_name'],
        'user' => $this->settings['db_username'],
        'password' => $this->settings['db_password'],
        'host' => $this->settings['db_host'],
        'driver' => 'pdo_mysql',
    );
    $this->db = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, $config);
    $this->db->executeQuery('SET time_zone = "+00:00";'); // Make sure the DB runs on UTC for this connection.
    try {
      $last_log = $this->db->fetchColumn('SELECT time_logged FROM fs_log WHERE code = \'db_checked\' ORDER BY id DESC LIMIT 1', array(1), 0);
      $utils_mtime = filemtime(__FILE__);
    }
    catch (Exception $e) {
      $last_log = false;
    }
    if (!$last_log || strtotime($last_log) < $utils_mtime) {
      $this->updateDBSchema();
    }
    // For debugging, potentially add |robert\.box\.kairo\.at to that regex temporarily.
    $this->running_on_localhost = preg_match('/^((.+\.)?localhost|127\.0\.0\.\d+)$/', $_SERVER['SERVER_NAME']);
    if (array_key_exists('pwd_cost', $this->settings)) {
      $this->pwd_cost = $this->settings['pwd_cost'];
    }
    if (array_key_exists('pwd_nonces', $this->settings)) {
      $this->pwd_nonces = $this->settings['pwd_nonces'];
    }
  }

  public $settings = null;
  public $db = null;
  public $running_on_localhost = false;
  public $client_reg_email_whitelist = array('kairo@kairo.at', 'com@kairo.at');
  private $pwd_cost = 10;
  private $pwd_nonces = array();

  function log($code, $info) {
    $result = $this->db->prepare('INSERT INTO `auth_log` (`code`, `info`, `ip_addr`) VALUES (:code, :info, :ipaddr);');
    if (!$result->execute(array(':code' => $code, ':info' => $info, ':ipaddr' => $_SERVER['REMOTE_ADDR']))) {
      // print($result->errorInfo()[2]);
    }
  }

  function checkForSecureConnection() {
    $errors = array();
    if (($_SERVER['SERVER_PORT'] != 443) && !$this->running_on_localhost) {
      $errors[] = _('You are not accessing this site on a secure connection, so authentication doesn\'t work.');
    }
    return $errors;
  }

  function sendSecurityHeaders() {
    // Send various headers that we want to have for security resons, mostly as recommended by https://observatory.mozilla.org/

    // CSP - see https://wiki.mozilla.org/Security/Guidelines/Web_Security#Content_Security_Policy
    // Disable unsafe inline/eval, only allow loading of resources (images, fonts, scripts, etc.) from ourselves; also disable framing.
    header('Content-Security-Policy: default-src \'none\';img-src \'self\'; script-src \'self\'; style-src \'self\'; frame-ancestors \'none\'');

    // X-Content-Type-Options - see https://wiki.mozilla.org/Security/Guidelines/Web_Security#X-Content-Type-Options
    // Prevent browsers from incorrectly detecting non-scripts as scripts
    header('X-Content-Type-Options: nosniff');

    // X-Frame-Options (for older browsers) - see https://wiki.mozilla.org/Security/Guidelines/Web_Security#X-Frame-Options
    // Block site from being framed
    header('X-Frame-Options: DENY');

    // X-XSS-Protection (for older browsers) - see https://wiki.mozilla.org/Security/Guidelines/Web_Security#X-XSS-Protection
    // Block pages from loading when they detect reflected XSS attacks
    header('X-XSS-Protection: 1; mode=block');
  }

  function initSession() {
    $session = null;
    if (strlen(@$_COOKIE['sessionkey'])) {
      // Fetch the session - or at least try to.
      $result = $this->db->prepare('SELECT * FROM `auth_sessions` WHERE `sesskey` = :sesskey AND `time_expire` > :expire;');
      $result->execute(array(':sesskey' => $_COOKIE['sessionkey'], ':expire' => gmdate('Y-m-d H:i:s')));
      $row = $result->fetch(PDO::FETCH_ASSOC);
      if ($row) {
        $session = $row;
      }
    }
    if (is_null($session)) {
      // Create new session and set cookie.
      $sesskey = $this->createSessionKey();
      setcookie('sessionkey', $sesskey, 0, "", "", !$this->running_on_localhost, true); // Last two params are secure and httponly, secure is not set on localhost.
      $result = $this->db->prepare('INSERT INTO `auth_sessions` (`sesskey`, `time_expire`) VALUES (:sesskey, :expire);');
      $result->execute(array(':sesskey' => $sesskey, ':expire' => gmdate('Y-m-d H:i:s', strtotime('+5 minutes'))));
      // After insert, actually fetch the session row from the DB so we have all values.
      $result = $this->db->prepare('SELECT * FROM auth_sessions WHERE `sesskey` = :sesskey AND `time_expire` > :expire;');
      $result->execute(array(':sesskey' => $sesskey, ':expire' => gmdate('Y-m-d H:i:s')));
      $row = $result->fetch(PDO::FETCH_ASSOC);
      if ($row) {
        $session = $row;
      }
      else {
        $this->log('session_create_failure', 'key: '.$sesskey);
      }
    }
    return $session;
  }

  function getLoginSession($userid, $prev_session) {
    $session = $prev_session;
    $sesskey = $this->createSessionKey();
    setcookie('sessionkey', $sesskey, 0, "", "", !$this->running_on_localhost, true); // Last two params are secure and httponly, secure is not set on localhost.
    // If the previous session has a user set, create a new one - otherwise take existing session entry.
    if (intval($session['user'])) {
      $result = $this->db->prepare('INSERT INTO `auth_sessions` (`sesskey`, `time_expire`, `user`, `logged_in`) VALUES (:sesskey, :expire, :userid, TRUE);');
      $result->execute(array(':sesskey' => $sesskey, ':userid' => $userid, ':expire' => gmdate('Y-m-d H:i:s', strtotime('+1 day'))));
      // After insert, actually fetch the session row from the DB so we have all values.
      $result = $this->db->prepare('SELECT * FROM auth_sessions WHERE `sesskey` = :sesskey AND `time_expire` > :expire;');
      $result->execute(array(':sesskey' => $sesskey, ':expire' => gmdate('Y-m-d H:i:s')));
      $row = $result->fetch(PDO::FETCH_ASSOC);
      if ($row) {
        $session = $row;
      }
      else {
        $utils->log('create_session_failure', 'at login, prev session: '.$session['id'].', new user: '.$userid);
        $errors[] = _('The session system is not working.').' '._('Please <a href="https://www.kairo.at/contact">contact KaiRo.at</a> and tell the team about this.');
      }
    }
    else {
      $result = $this->db->prepare('UPDATE `auth_sessions` SET `sesskey` = :sesskey, `user` = :userid, `logged_in` = TRUE, `time_expire` = :expire WHERE `id` = :sessid;');
      if (!$result->execute(array(':sesskey' => $sesskey, ':userid' => $userid, ':expire' => gmdate('Y-m-d H:i:s', strtotime('+1 day')), ':sessid' => $session['id']))) {
        $utils->log('login_failure', 'session: '.$session['id'].', user: '.$userid);
        $errors[] = _('Login failed unexpectedly.').' '._('Please <a href="https://www.kairo.at/contact">contact KaiRo.at</a> and tell the team about this.');
      }
      else {
        // After update, actually fetch the session row from the DB so we have all values.
        $result = $this->db->prepare('SELECT * FROM auth_sessions WHERE `sesskey` = :sesskey AND `time_expire` > :expire;');
        $result->execute(array(':sesskey' => $sesskey, ':expire' => gmdate('Y-m-d H:i:s')));
        $row = $result->fetch(PDO::FETCH_ASSOC);
        if ($row) {
          $session = $row;
        }
      }
    }
    return $session;
  }

  function setRedirect($session, $redirect) {
    $success = false;
    // Save the request in the session so we can get back to fulfilling it if one of the links is clicked.
    $result = $this->db->prepare('UPDATE `auth_sessions` SET `saved_redirect` = :redir WHERE `id` = :sessid;');
    if (!$result->execute(array(':redir' => $redirect, ':sessid' => $session['id']))) {
      $this->log('redir_save_failure', 'session: '.$session['id'].', redirect: '.$redirect);
    }
    else {
      $success = true;
    }
    return $success;
  }

  function doRedirectIfSet($session) {
    $success = false;
    // If the session has a redirect set, make sure it's performed.
    if (strlen(@$session['saved_redirect'])) {
      // Remove redirect.
      $result = $this->db->prepare('UPDATE `auth_sessions` SET `saved_redirect` = :redir WHERE `id` = :sessid;');
      if (!$result->execute(array(':redir' => '', ':sessid' => $session['id']))) {
        $this->log('redir_save_failure', 'session: '.$session['id'].', redirect: (empty)');
      }
      else {
        $success = true;
      }
      header('Location: '.$this->getDomainBaseURL().$session['saved_redirect']);
    }
    return $success;
  }

  function resetRedirect($session) {
    $success = false;
    // If the session has a redirect set, remove it.
    if (strlen(@$session['saved_redirect'])) {
      $result = $this->db->prepare('UPDATE `auth_sessions` SET `saved_redirect` = :redir WHERE `id` = :sessid;');
      if (!$result->execute(array(':redir' => '', ':sessid' => $session['id']))) {
        $this->log('redir_save_failure', 'session: '.$session['id'].', redirect: (empty)');
      }
      else {
        $success = true;
      }
    }
    return $success;
  }

  function getDomainBaseURL() {
    return ($this->running_on_localhost?'http':'https').'://'.$_SERVER['SERVER_NAME'];
  }

  function checkPasswordConstraints($new_password, $user_email) {
    $errors = array();
    if ($new_password != trim($new_password)) {
      $errors[] = _('Password must not start or end with a whitespace character like a space.');
    }
    if (strlen($new_password) < 8) { $errors[] = sprintf(_('Password too short (min. %s characters).'), 8); }
    if (strlen($new_password) > 70) { $errors[] = sprintf(_('Password too long (max. %s characters).'), 70); }
    if ((strtolower($new_password) == strtolower($user_email)) ||
        in_array(strtolower($new_password), preg_split("/[@\.]+/", strtolower($user_email)))) {
      $errors[] = _('The passwort can not be equal to your email or any part of it.');
    }
    if ((strlen($new_password) < 15) && (preg_match('/^[a-zA-Z]+$/', $new_password))) {
      $errors[] = sprintf(_('Your password must use characters other than normal letters or contain least %s characters.'), 15);
    }
    if (preg_match('/^\d+$/', $new_password)) {
      $errors[] = sprintf(_('Your password cannot consist only of numbers.'), 15);
    }
    if (strlen(count_chars($new_password, 3)) < 5) {
      $errors[] = sprintf(_('Password does have to contain at least %s different characters.'), 5);
    }
    return $errors;
  }

  function createSessionKey() {
    return bin2hex(openssl_random_pseudo_bytes(512 / 8)); // Get 512 bits of randomness (128 byte hex string).
  }

  function createVerificationCode() {
    return bin2hex(openssl_random_pseudo_bytes(512 / 8)); // Get 512 bits of randomness (128 byte hex string).
  }

  function createClientSecret() {
    return bin2hex(openssl_random_pseudo_bytes(160 / 8)); // Get 160 bits of randomness (40 byte hex string).
  }

  function createTimeCode($session, $offset = null, $validity_minutes = 10) {
    // Matches TOTP algorithms, see https://en.wikipedia.org/wiki/Time-based_One-time_Password_Algorithm
    $valid_seconds = intval($validity_minutes) * 60;
    if ($valid_seconds < 60) { $valid_seconds = 60; }
    $code_digits = 8;
    $time = time();
    $rest = is_null($offset)?($time % $valid_seconds):intval($offset); // T0, will be sent as part of code to make it valid for the full duration.
    $counter = floor(($time - $rest) / $valid_seconds);
    $hmac = mhash(MHASH_SHA1, $counter, $session['id'].$session['sesskey']);
    $offset = hexdec(substr(bin2hex(substr($hmac, -1)), -1)); // Get the last 4 bits as a number.
    $totp = hexdec(bin2hex(substr($hmac, $offset, 4))) & 0x7FFFFFFF; // Take 4 bytes at the offset, discard highest bit.
    $totp_value = sprintf('%0'.$code_digits.'d', substr($totp, -$code_digits));
    return $rest.'.'.$totp_value;
  }

  function verifyTimeCode($timecode_to_verify, $session, $validity_minutes = 10) {
    if (preg_match('/^(\d+)\.\d+$/', $timecode_to_verify, $regs)) {
      return ($timecode_to_verify === $this->createTimeCode($session, $regs[1], $validity_minutes));
    }
    return false;
  }

  function pwdHash($new_password) {
    $hash_prefix = '';
    if (count($this->pwd_nonces)) {
      $new_password .= $this->pwd_nonces[count($this->pwd_nonces) - 1];
      $hash_prefix = (count($this->pwd_nonces) - 1).'|';
    }
    return $hash_prefix.password_hash($new_password, PASSWORD_DEFAULT, array('cost' => $this->pwd_cost));
  }

  function pwdVerify($password_to_verify, $userdata) {
    $pwdhash = $userdata['pwdhash'];
    if (preg_match('/^(\d+)\|(.+)$/', $userdata['pwdhash'], $regs)) {
      $password_to_verify .= $this->pwd_nonces[$regs[1]];
      $pwdhash = $regs[2];
    }
    return password_verify($password_to_verify, $pwdhash);
  }

  function pwdNeedsRehash($userdata) {
    $nonceid = -1;
    $pwdhash = $userdata['pwdhash'];
    if (preg_match('/^(\d+)\|(.+)$/', $userdata['pwdhash'], $regs)) {
      $nonceid = $regs[1];
      $pwdhash = $regs[2];
    }
    if ($nonceid == count($this->pwd_nonces) - 1) {
      return password_needs_rehash($pwdhash, PASSWORD_DEFAULT, array('cost' => $this->pwd_cost));
    }
    else {
      return true;
    }
  }

  function setUpL10n() {
    // This is an array of locale tags in browser style mapping to unix system locale codes to use with gettext.
    $supported_locales = array(
        'en-US' => 'en_US',
        'de' => 'de_DE',
    );

    $textdomain = 'kairo_auth';
    $textlocale = $this->negotiateLocale(array_keys($supported_locales));
    putenv('LC_ALL='.$supported_locales[$textlocale]);
    $selectedlocale = setlocale(LC_ALL, $supported_locales[$textlocale]);
    bindtextdomain($textdomain, '../locale');
    bind_textdomain_codeset($textdomain, 'utf-8');
    textdomain($textdomain);
  }

  function negotiateLocale($supportedLanguages) {
    $nlocale = $supportedLanguages[0];
    $headers = getAllHeaders();
    $accLcomp = explode(',', @$headers['Accept-Language']);
    $accLang = array();
    foreach ($accLcomp as $lcomp) {
      if (strlen($lcomp)) {
        $ldef = explode(';', $lcomp);
        $accLang[$ldef[0]] = (float)((strpos(@$ldef[1],'q=')===0)?substr($ldef[1],2):1);
      }
    }
    if (count($accLang)) {
      $pLang = ''; $pLang_q = 0;
      foreach ($supportedLanguages as $wantedLang) {
        if (isset($accLang[$wantedLang]) && ($accLang[$wantedLang] > $pLang_q)) {
          $pLang = $wantedLang;
          $pLang_q = $accLang[$wantedLang];
        }
      }
      if (strlen($pLang)) { $nlocale = $pLang; }
    }
    return $nlocale;
  }

  function getGroupedEmails($group_id, $exclude_email = '') {
    $emails = array();
    if (intval($group_id)) {
      $result = $this->db->prepare('SELECT `email` FROM `auth_users` WHERE `group_id` = :groupid AND `status` = \'ok\' AND `email` != :excludemail ORDER BY `email` ASC;');
      $result->execute(array(':groupid' => $group_id, ':excludemail' => $exclude_email));
      foreach ($result->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $emails[] = $row['email'];
      }
    }
    return $emails;
  }

  function getOAuthServer() {
    // Simple server based on https://bshaffer.github.io/oauth2-server-php-docs/cookbook
    $dbdata = array('dsn' => 'mysql:dbname='.$this->settings['db_name'].';host='.$this->settings['db_host'],
                    'username' => $this->settings['db_username'],
                    'password' => $this->settings['db_password']);
    $oauth2_storage = new OAuth2\Storage\Pdo($dbdata);

    // Set configuration
    $oauth2_config = array(
      'require_exact_redirect_uri' => false,
      'always_issue_new_refresh_token' => true, // Needs to be handed below as well as there it's not constructed from within the server object.
      'refresh_token_lifetime' => 90*24*3600,
    );

    // Pass a storage object or array of storage objects to the OAuth2 server class
    $server = new OAuth2\Server($oauth2_storage, $oauth2_config);

    // Add the "Client Credentials" grant type (it is the simplest of the grant types)
    //$server->addGrantType(new OAuth2\GrantType\ClientCredentials($storage));

    // Add the "Authorization Code" grant type (this is where the oauth magic happens)
    $server->addGrantType(new OAuth2\GrantType\AuthorizationCode($oauth2_storage));

    // Add the "Refresh Token" grant type (required to get longer-living resource access by generating new access tokens)
    $server->addGrantType(new OAuth2\GrantType\RefreshToken($oauth2_storage, array('always_issue_new_refresh_token' => true)));

    return $server;
  }

  function initHTMLDocument($titletext, $headlinetext = null) {
    global $settings;
    if (is_null($headlinetext)) { $headlinetext = $titletext; }
    // Start HTML document as a DOM object.
    extract(ExtendedDocument::initHTML5()); // sets $document, $html, $head, $title, $body
    $document->formatOutput = true; // we want a nice output

    $style = $head->appendElement('link');
    $style->setAttribute('rel', 'stylesheet');
    $style->setAttribute('href', 'authsystem.css');
    $head->appendJSFile('authsystem.js');
    if ($settings['piwik_enabled']) {
      $head->setAttribute('data-piwiksite', $settings['piwik_site_id']);
      $head->setAttribute('data-piwikurl', $settings['piwik_url']);
      $head->appendJSFile('piwik.js', true, true);
    }
    $title->appendText($titletext);
    $h1 = $body->appendElement('h1', $headlinetext);

    if ($settings['piwik_enabled']) {
      // Piwik noscript element
      $noscript = $body->appendElement('noscript');
      $para = $noscript->appendElement('p');
      $img = $para->appendImage($settings['piwik_url'].'piwik.php?idsite='.$settings['piwik_site_id']);
      $img->setAttribute('style', 'border:0;');
    }

    // Make the document not be scaled on mobile devices.
    $vpmeta = $head->appendElement('meta');
    $vpmeta->setAttribute('name', 'viewport');
    $vpmeta->setAttribute('content', 'width=device-width, height=device-height');

    $para = $body->appendElement('p', _('This login system does not work without JavaScript. Please activate JavaScript for this site to log in.'));
    $para->setAttribute('id', 'jswarning');
    $para->setAttribute('class', 'warn');

    return array('document' => $document,
                 'html' => $html,
                 'head' => $head,
                 'title' => $title,
                 'body' => $body);
  }

  function appendLoginForm($dom_element, $session, $user, $addfields = array()) {
    $form = $dom_element->appendForm('./', 'POST', 'loginform');
    $form->setAttribute('id', 'loginform');
    $form->setAttribute('class', 'loginarea hidden');
    $ulist = $form->appendElement('ul');
    $ulist->setAttribute('class', 'flat login');
    $litem = $ulist->appendElement('li');
    $inptxt = $litem->appendInputEmail('email', 30, 20, 'login_email', (intval(@$user['id'])?$user['email']:''));
    $inptxt->setAttribute('autocomplete', 'email');
    $inptxt->setAttribute('required', '');
    $inptxt->setAttribute('placeholder', _('Email'));
    $inptxt->setAttribute('class', 'login');
    $litem = $ulist->appendElement('li');
    $inptxt = $litem->appendInputPassword('pwd', 20, 20, 'login_pwd', '');
    $inptxt->setAttribute('required', '');
    $inptxt->setAttribute('placeholder', _('Password'));
    $inptxt->setAttribute('class', 'login');
    $litem = $ulist->appendElement('li');
    $litem->appendLink('./?reset', _('Forgot password?'));
    /*
    $litem = $ulist->appendElement('li');
    $cbox = $litem->appendInputCheckbox('remember', 'login_remember', 'true', false);
    $cbox->setAttribute('class', 'logincheck');
    $label = $litem->appendLabel('login_remember', _('Remember me'));
    $label->setAttribute('id', 'rememprompt');
    $label->setAttribute('class', 'loginprompt');
    */
    $litem = $ulist->appendElement('li');
    $litem->appendInputHidden('tcode', $this->createTimeCode($session));
    foreach ($addfields as $fname => $fvalue) {
      $litem->appendInputHidden($fname, $fvalue);
    }
    $submit = $litem->appendInputSubmit(_('Log in / Register'));
    $submit->setAttribute('class', 'loginbutton');
  }

  function updateDBSchema() {
    $newSchema = $this->getDBSchema();
    $synchronizer = new \Doctrine\DBAL\Schema\Synchronizer\SingleDatabaseSynchronizer($this->db);
    $up_sql = $synchronizer->getUpdateSchema($newSchema); // for logging only
    $synchronizer->updateSchema($newSchema);
    $this->log('db_checked', implode("\n", $up_sql));
  }

  function getDBSchema() {
    $schema = new \Doctrine\DBAL\Schema\Schema();

    $table = $schema->createTable('auth_sessions');
    $table->addColumn('id', 'bigint', array('unsigned' => true, 'notnull' => true, 'autoincrement' => true));
    $table->addColumn('sesskey', 'string', array('length' => 150, 'notnull' => true, 'default' => ''));
    $table->addColumn('user', 'integer', array('unsigned' => true, 'notnull' => false, 'default' => null));
    $table->addColumn('logged_in', 'boolean', array('notnull' => true, 'default' => false));
    $table->addColumn('time_created', 'datetime', array('notnull' => true, 'default' => 'CURRENT_TIMESTAMP'));
    $table->addColumn('time_expire', 'datetime', array('notnull' => true, 'default' => 'CURRENT_TIMESTAMP'));
    $table->addColumn('saved_redirect', 'string', array('length' => 255, 'notnull' => true, 'default' => ''));
    $table->setPrimaryKey(array('id'), 'id');
    $table->addIndex(array('sesskey'), 'sesskey');
    $table->addIndex(array('time_expire'), 'time_expire');

    $table = $schema->createTable('auth_users');
    $table->addColumn('id', 'integer', array('unsigned' => true, 'notnull' => true, 'autoincrement' => true));
    $table->addColumn('email', 'string', array('length' => 255, 'notnull' => true, 'default' => ''));
    $table->addColumn('pwdhash', 'string', array('length' => 255, 'notnull' => true, 'default' => ''));
    $table->addColumn('status', 'string', array('length' => 20, 'notnull' => true, 'default' => 'unverified'));
    $table->addColumn('verify_hash', 'string', array('length' => 150, 'notnull' => false, 'default' => null));
    $table->addColumn('group_id', 'integer', array('unsigned' => true, 'notnull' => true, 'default' => 0));
    $table->setPrimaryKey(array('id'), 'id');
    $table->addUniqueIndex(array('email'), 'email');

    $table = $schema->createTable('auth_log');
    $table->addColumn('id', 'bigint', array('unsigned' => true, 'notnull' => true, 'autoincrement' => true));
    $table->addColumn('code', 'string', array('length' => 100, 'notnull' => true, 'default' => ''));
    $table->addColumn('info', 'text', array('notnull' => false, 'default' => null));
    $table->addColumn('ip_addr', 'string', array('length' => 50, 'notnull' => false, 'default' => null));
    $table->addColumn('time_logged', 'datetime', array('notnull' => true, 'default' => 'CURRENT_TIMESTAMP'));
    $table->setPrimaryKey(array('id'), 'id');
    $table->addIndex(array('time_logged'), 'time_logged');

    /* Doctrine DBAL variant of http://bshaffer.github.io/oauth2-server-php-docs/cookbook/#define-your-schema */
    $table = $schema->createTable('oauth_clients');
    $table->addColumn('client_id', 'string', array('length' => 80, 'notnull' => true));
    $table->addColumn('client_secret', 'string', array('length' => 80, 'notnull' => false));
    $table->addColumn('redirect_uri', 'string', array('length' => 2000, 'notnull' => true));
    $table->addColumn('grant_types', 'string', array('length' => 80, 'notnull' => false));
    $table->addColumn('scope', 'string', array('length' => 100, 'notnull' => false));
    $table->addColumn('user_id', 'string', array('length' => 80, 'notnull' => false));
    $table->setPrimaryKey(array('client_id'), 'clients_client_id_pk');

    $table = $schema->createTable('oauth_access_tokens');
    $table->addColumn('access_token', 'string', array('length' => 40, 'notnull' => true));
    $table->addColumn('client_id', 'string', array('length' => 80, 'notnull' => true));
    $table->addColumn('user_id', 'string', array('length' => 80, 'notnull' => false));
    $table->addColumn('expires', 'datetime', array('notnull' => true));
    $table->addColumn('scope', 'string', array('length' => 2000, 'notnull' => false));
    $table->setPrimaryKey(array('access_token'), 'access_token_pk');

    $table = $schema->createTable('oauth_authorization_codes');
    $table->addColumn('authorization_code', 'string', array('length' => 40, 'notnull' => true));
    $table->addColumn('client_id', 'string', array('length' => 80, 'notnull' => true));
    $table->addColumn('user_id', 'string', array('length' => 80, 'notnull' => false));
    $table->addColumn('redirect_uri', 'string', array('length' => 2000, 'notnull' => false));
    $table->addColumn('expires', 'datetime', array('notnull' => true));
    $table->addColumn('scope', 'string', array('length' => 2000, 'notnull' => false));
    $table->setPrimaryKey(array('authorization_code'), 'auth_code_pk');

    $table = $schema->createTable('oauth_refresh_tokens');
    $table->addColumn('refresh_token', 'string', array('length' => 40, 'notnull' => true));
    $table->addColumn('client_id', 'string', array('length' => 80, 'notnull' => true));
    $table->addColumn('user_id', 'string', array('length' => 80, 'notnull' => false));
    $table->addColumn('expires', 'datetime', array('notnull' => true));
    $table->addColumn('scope', 'string', array('length' => 2000, 'notnull' => false));
    $table->setPrimaryKey(array('refresh_token'), 'refresh_token_pk');

    $table = $schema->createTable('oauth_users');
    $table->addColumn('username', 'string', array('length' => 255, 'notnull' => true));
    $table->addColumn('password', 'string', array('length' => 2000, 'notnull' => false));
    $table->addColumn('first_name', 'string', array('length' => 255, 'notnull' => false));
    $table->addColumn('last_name', 'string', array('length' => 255, 'notnull' => false));
    $table->setPrimaryKey(array('username'), 'username_pk');

    $table = $schema->createTable('oauth_scopes');
    $table->addColumn('scope', 'text', array('notnull' => false));
    $table->addColumn('is_default', 'boolean', array('notnull' => false));

    $table = $schema->createTable('oauth_jwt');
    $table->addColumn('client_id', 'string', array('length' => 80, 'notnull' => true));
    $table->addColumn('subject', 'string', array('length' => 80, 'notnull' => false));
    $table->addColumn('public_key', 'string', array('length' => 2000, 'notnull' => false));
    $table->setPrimaryKey(array('client_id'), 'client_id_pk');

    return $schema;
  }
}
?>