module.py

#!/usr/bin/python
# -*- coding: UTF-8 -*-

from cStringIO import StringIO
from datetime import datetime, timedelta
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from threading import Thread
from uuid import uuid4
import base64
import cPickle
import functools
import hashlib
import json
import os
import smtplib

from PIL import Image, ImageDraw, ImageFont
from flask import Flask
from flask import jsonify
from flask import render_template, send_from_directory 
from flask import request
from flask import send_file
from flask import session
from flask import url_for
from flask_compress import Compress
from flask_session import Session
from werkzeug import abort, redirect
import gnupg
import pyotp
import pytz
import time
import qrcode
import webauthn

from NIST.fingerprint import NISTf_auto
from PiAnoS import caseExistsInDB

from const import pfsp
from functions import float_or_null
from functions import pbkdf2, AESCipher
from functions import pil2buffer
from functions import random_data
from functions import render_jinja_html
from functions import rotate_image_upon_exif
import config

################################################################################

from version import __version__, __branch__, __commit__, __commiturl__, __treeurl__

################################################################################

Image.MAX_IMAGE_PIXELS = 1 * 1024 * 1024 * 1024

################################################################################

app = Flask( __name__ )
app.config.from_pyfile( "config.py" )

Compress( app )
Session( app )

baseurl = os.environ.get( "BASEURL", "" )
envtype = os.environ.get( "ENVTYPE", "" )

################################################################################

gnupg._parsers.Verify.TRUST_LEVELS[ "ENCRYPTION_COMPLIANCE_MODE" ] = 23

################################################################################
#    Decorators

def session_field_required( field, value ):
    def decorator( func ):
        @functools.wraps( func )
        def wrapper_login_required( *args, **kwargs ):
            if not field in session:
                return redirect( url_for( "login" ) )
            
            elif not session.get( field ) == value:
                return redirect( url_for( "login" ) )
            
            return func( *args, **kwargs )
    
        return wrapper_login_required
    
    return decorator

def login_required( func ):
    @functools.wraps( func )
    def wrapper_login_required( *args, **kwargs ):
        if not session.get( "logged", False ) :
            return redirect( url_for( "login" ) )
        
        return func( *args, **kwargs )

    return wrapper_login_required

def referer_required( func ):
    @functools.wraps( func )
    def wrapper_login_required( *args, **kwargs ):
        if not request.headers.get( "Referer", False ):
            return "referrer needed", 404
        
        return func( *args, **kwargs )

    return wrapper_login_required

def admin_required( func ):
    @functools.wraps( func )
    def wrapper_login_required( *args, **kwargs ):
        if not session.get( "logged", False ) or not session.get( "account_type", None ) == 1:
            return redirect( url_for( "login" ) )
        
        return func( *args, **kwargs )

    return wrapper_login_required

def submitter_required( func ):
    @functools.wraps( func )
    def wrapper_login_required( *args, **kwargs ):
        if not session.get( "logged", False ) or not session.get( "account_type", None ) == 3:
            return redirect( url_for( "login" ) )
        
        return func( *args, **kwargs )

    return wrapper_login_required

def redis_cache( ttl = 3600 ):
    def decorator( func ):
        @functools.wraps( func )
        def wrapper_cache( *args, **kwargs ):
            lst = []
            lst.append( func.__name__ )
            lst.extend( args )
            index = "_".join( lst )
            index = hashlib.sha256( index ).hexdigest()
            
            d = config.redis_shared.get( index )
            
            if d != None:
                buff = StringIO()
                buff.write( base64.b64decode( d ) )
                buff.seek( 0 )
                
                return cPickle.load( buff )
            
            else:
                d = func( *args, **kwargs )
                
                buff = StringIO()
                cPickle.dump( d, buff )
                buff.seek( 0 )
                d_cached = base64.b64encode( buff.getvalue() )
                
                config.redis_shared.set( index, d_cached, ex = ttl )
                
                return d
    
        return wrapper_cache
    return decorator

################################################################################
#    Generic routing

@app.route( "/ping" )
@app.route( baseurl + "/ping" )
def ping():
    """
        Ping function to check if the web application is healthy.
        This function need to check all important element of the web application, i.e. the flask app and the postgresql database.

        If the application report a error 500, the healthcheck done by the container orchestrator will fail, hence re-scheduling the container as needed.
    """
    if not config.db.check():
        config.db.connect() # Try to re-connect to the database to try to serve the requests before the rescheduling
        return abort( 500 )
    else:
        return "pong"

@app.route( baseurl + "/version" )
def version():
    """
        Function to report the version of the web app.
        The version.py file is re-generated by the CI/CD for production.
    """
    try:
        return jsonify( {
            "error": False,
            "version": __version__,
            "branch": __branch__,
            "commit": __commit__,
            "commiturl": __commiturl__,
            "treeurl": __treeurl__
        } )

    except:
        return jsonify( {
            "error": True
        } )

################################################################################
#   CDN serving

@app.route( baseurl + "/cdn/<path:subpath>" )
def send_cdn_files( subpath ):
    """
        Serve the files from the cdn directory.
    """
    return send_from_directory( "cdn", subpath )

################################################################################
#    App serving

@app.route( baseurl + "/app/<path:subpath>" )
def send_app_files( subpath ):
    """
        Serve the file from the app directory (all files related to the ICNML application).
    """
    return send_from_directory( "app", subpath )

@app.route( baseurl + "/static/<path:subpath>" )
def send_static_files( subpath ):
    """
        Serve static files from the static directory.
    """
    return send_from_directory( "static", subpath )

################################################################################
#    Sessions

@app.before_request
def renew_session():
    """
        Reset the timer before the automatic-logout.
        This function is called before every HTTP call.
    """
    session.permanent = True
    app.permanent_session_lifetime = timedelta( seconds = config.session_timeout )

@app.route( baseurl + "/is_logged" )
def is_logged():
    """
        App route to know if the user is logged in the ICNML main application.
        This route is used by nginx to protect some other locations, for example
        the PiAnoS dedicated pages.
        The session countdown timer is resetted to allow the user to use the protected
        location for the rest of the timeout.
    """
    if session.get( "logged", False ):
        return "ok"
    
    else:
        return abort( 403 )

@app.route( baseurl + "/logout" )
def logout():
    """
        Logout the user, clear the session and redirect to the login page.
    """
    session_clear_and_prepare()
    return redirect( url_for( "home" ) )

def session_clear_and_prepare():
    """
        Clear the session related to the user and initialize the login related variables.
    """
    session.clear()
    
    session[ "process" ] = "login"
    session[ "need_to_check" ] = [ "password" ]
    session[ "logged" ] = False
    session[ "session_security_key" ] = str( uuid4() )

@app.route( baseurl + "/login" )
def login():
    """
        Route to serve the login.html page.
    """
    session_clear_and_prepare()
    
    return render_template( 
        "login.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        session_security_key = session.get( "session_security_key" ),
        envtype = envtype
    )

@app.route( baseurl + "/do/login", methods = [ "POST" ] )
def do_login():
    """
        Function to manadge the login workflow and check the username, password and TOTP data.
        This function is called multiple times because the check is done only for one data
        type at the time.
        If all the checks are OK, the user has provided all needed information, hence is logged in.
    """
    #TODO: double-check this function with someone external
    #TODO: combine the security key checks in this function

    need_to_check = session.get( "need_to_check", [ "password" ] )
    try:
        current_check = need_to_check[ 0 ]
    except:
        current_check = None
    
    session[ "need_to_check" ] = need_to_check
    
    ############################################################################
    #   Username and password check

    if current_check == "password":
        q = config.db.query( "SELECT * FROM users WHERE username = %s", ( request.form.get( "username" ), ) )
        user = q.fetchone()
        
        if user == None:
            session_clear_and_prepare()
            
            return jsonify( {
                "error": False,
                "logged": False
            } )
        
        form_password = request.form.get( "password", None )
        
        if form_password == None or not pbkdf2( form_password, user[ "password" ] ).verify():
            session_clear_and_prepare()
            
            return jsonify( {
                "error": False,
                "logged": False,
            } )
        
        elif not user[ "active" ]:
            session_clear_and_prepare()
            
            return jsonify( {
                "error": False,
                "logged": False,
                "message": "Your account is not activated. Please contact an administrator (icnml@unil.ch)."
            } )
        
        else:
            session[ "username" ] = user[ "username" ]
            session[ "user_id" ] = user[ "id" ]
            session[ "password_check" ] = True
            
            session[ "need_to_check" ].remove( current_check )
            session[ "password" ] = pbkdf2( form_password, "AES256", config.PASSWORD_NB_ITERATIONS ).hash()
            
            sql = "SELECT count( * ) FROM webauthn WHERE user_id = %s AND active = TRUE"
            security_keys_count = config.db.query_fetchone( sql, ( user[ "id" ], ) )[ "count" ]
            
            if security_keys_count > 0:
                session[ "need_to_check" ].append( "securitykey" )
            
            elif user[ "totp" ]:
                session[ "need_to_check" ].append( "totp" )
            
            else:
                session_clear_and_prepare()
                
                return jsonify( {
                    "error": False,
                    "logged": False,
                    "message": "Second factor missing. Contact the ICNML administrator (icnml@unil.ch)."
                } )
    
    ############################################################################
    #   Time-based One Time Password check

    elif current_check == "totp":
        q = config.db.query( "SELECT username, totp FROM users WHERE username = %s", ( session[ "username" ], ) )
        user = q.fetchone()
        
        if not pyotp.TOTP( user[ "totp" ] ).verify( request.form[ "totp" ], valid_window = 2 ):
            session[ "logged" ] = False
            return jsonify( {
                "error": False,
                "logged": False,
                "message": "Wrong TOTP",
                "time": time.time()
            } )
        
        else:
            session[ "need_to_check" ].remove( current_check )
    
    
    ############################################################################
    #   Check if all the data has been provided; login if ok

    if len( session[ "need_to_check" ] ) == 0 and session.get( "password_check", False ):
        for key in [ "process", "need_to_check", "password_check" ]:
            if key in session:
                session.pop( key )
            
        session[ "logged" ] = True
        
        q = config.db.query( "SELECT type FROM users WHERE username = %s", ( session[ "username" ], ) )
        user = q.fetchone()
        session[ "account_type" ] = int( user[ "type" ] )
        
        return jsonify( {
            "error": False,
            "logged": True,
        } )
    
    else:
        return jsonify( {
            "error": False,
            "next_step": session[ "need_to_check" ][ 0 ]
        } )

################################################################################
#    Reset

@app.route( baseurl + "/reset_password" )
def password_reset():
    """
        Serve the password_reset.html page.
    """
    session.clear()
    session[ "process" ] = "request_password_reset"
    
    return render_template( 
        "users/password_reset.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        envtype = envtype
    )

@app.route( baseurl + "/do/reset_password", methods = [ "POST" ] )
def do_password_reset():
    """
        Start the check of the username for a password reset.
        The check is done in a thread to allow a fast "OK" response, even if the username
        does not exists. This prevent data extraction (presence or not of a username).
    """
    email = request.form.get( "email", None )
    
    Thread( target = do_password_reset_thread, args = ( email, ) ).start()
    
    return jsonify( {
        "error": False,
        "message": "OK"
    } )

def do_password_reset_thread( email ):
    """
        Search in the database if the provided email is present, and send the password reset email if so.
    """
    if email == None:
        return False

    else:
        q = config.db.query( "SELECT id, username, email FROM users" )
        users = q.fetchall()
        
        for user in users:
            if not user[ "email" ].startswith( "pbkdf2$" ):
                continue
            
            elif pbkdf2( email, user[ "email" ] ).verify():
                id = hashlib.sha512( random_data( 100 ) ).hexdigest()
                
                ####################################################################
                
                data = {
                    "process": "password_reset",
                    "process_id": id,
                    "user_id": user[ "id" ]
                }
                data = json.dumps( data )
                data = base64.b64encode( data )
                
                reset_id = "reset_{}".format( id )
                config.redis_shared.set( reset_id, data, ex = 24 * 3600 )
                
                ####################################################################
                
                email_content = render_jinja_html( 
                    "templates/email", "reset.html",
                    id = id,
                    url = config.domain + baseurl + "/reset_password_stage2",
                    username = user[ "username" ]
                )
                
                msg = MIMEText( email_content, "html" )
                
                msg[ "Subject" ] = "ICNML - User password reset"
                msg[ "From" ] = config.sender
                msg[ "To" ] = email
                
                s = smtplib.SMTP( config.smtpserver )
                s.sendmail( config.sender, [ email ], msg.as_string() )
                s.quit()

@app.route( baseurl + "/reset_password_stage2/<id>", methods = [ "GET", "POST" ] )
def password_reset_stage2( id ):
    """
        Serve the reset password, second stage (password edit fields) page,
        and set the data in the database if provided.
    """
    #TODO: double-check this function with someone external

    reset_id = "reset_{}".format( id )
    data = config.redis_shared.get( reset_id )
    
    if data != None:
        data = base64.b64decode( data )
        data = json.loads( data )
        
        password = request.form.get( "password", None )
        
        userid = data.get( "user_id", None )
        
        if password != None:
            password = pbkdf2( password, random_data( 50 ), config.EMAIL_NB_ITERATIONS ).hash()
            config.db.query( "UPDATE users SET password = %s WHERE id = %s", ( password, userid ) )
            config.db.commit()
            
            reset_id = "reset_{}".format( id )
            config.redis_shared.delete( reset_id )
            
            return jsonify( {
                "error": False,
                "password_updated": True
            } )
            
        else:
            return render_template( 
                "users/password_reset_stage2.html",
                baseurl = baseurl,
                id = id,
                js = config.cdnjs,
                css = config.cdncss,
                envtype = envtype
            ) 
        
    else:
        return jsonify( {
            "error": True,
            "message": "Reset procedure not found/expired"
        } )

################################################################################
#    U2F keys

@app.route( baseurl + "/webauthn/admin" )
@login_required
def webauthn_admin():
    """
        Serve the administartion page for the FIDO2 keys.
    """
    return render_template( 
        "webauthn/admin.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        account_type = session.get( "account_type", None ),
        keys = do_webauthn_get_list_of_keys( all = True ),
        envtype = envtype
    )

def do_webauthn_get_list_of_keys( uid = None, all = False ):
    """
        Get the list of keys for a particular user.
        Can be filtered by active keys only with the `all` parameter.
        If the user id (uid) variable is not passed in parameter, the id of the currently logged user will be used (via the session).
    """
    user_id = session.get( "user_id", uid )
    
    sql = """
        SELECT
            id, key_name as name,
            created_on, last_usage, usage_counter,
            active
        FROM webauthn
        WHERE user_id = %s
    """
    if not all:
        sql += " AND active = true"
    sql += " ORDER BY key_name ASC"
    
    q = config.db.query( sql, ( user_id, ) )
    keys = q.fetchall()
    
    data = []
    for key in keys:
        data.append( dict( key ) )
    
    return data

@app.route( baseurl + "/webauthn/begin_activate", methods = [ "POST" ] )
@login_required
def webauthn_begin_activate():
    """
        Start the registering process for a new security key.
        The json returned will be used by the javascript navigator.credentials.create() function.
    """
    session[ "key_name" ] = request.form.get( "key_name", None )
    
    username = session.get( "username" )
    
    challenge = pyotp.random_base32( 64 )
    ukey = pyotp.random_base32( 64 )
    
    session[ "challenge" ] = challenge
    session[ "register_ukey" ] = ukey

    make_credential_options = webauthn.WebAuthnMakeCredentialOptions( 
        challenge, config.rp_name, config.RP_ID,
        ukey, username, username,
        None
    )
    
    registration_dict = make_credential_options.registration_dict
    registration_dict[ "authenticatorSelection" ] = {
        "authenticatorAttachment": "cross-platform",
        "requireResidentKey": False,
        "userVerification": "discouraged"
    }
    
    return jsonify( registration_dict )

@app.route( baseurl + "/webauthn/verify", methods = [ "POST" ] )
@login_required
def webauthn_verify():
    """
        Verify the data produced by the security key while registring
        (with the navigator.credentials.create() function).
    """
    challenge = session[ "challenge" ]
    user_id = session[ "user_id" ]
    key_name = session.get( "key_name", None )
    ukey = session[ "register_ukey" ]
    
    webauthn_registration_response = webauthn.WebAuthnRegistrationResponse( 
        config.RP_ID,
        config.ORIGIN,
        request.form,
        challenge
    )
    
    try:
        webauthn_credential = webauthn_registration_response.verify()
    
    except Exception as e:
        return jsonify( {
            "error": True,
            "message": "Registration failed. Error: {}".format( e )
        } )
    
    try:
        config.db.query(
            """
                INSERT INTO webauthn
                ( user_id, key_name, ukey, credential_id, pub_key, sign_count )
                VALUES ( %s, %s, %s, %s, %s, %s )
            """,
            (
                user_id, key_name,
                ukey, webauthn_credential.credential_id,
                webauthn_credential.public_key, webauthn_credential.sign_count,
            )
        )
        config.db.commit()
        
        return jsonify( {
            "success": "User successfully registered."
        } )
    
    except:
        return jsonify( {
            "error": True,
            "message": "Database error"
        } )

################################################################################

@app.route( baseurl + "/webauthn/delete", methods = [ "POST" ] )
@login_required
def webauthn_delete_key():
    """
        Delete a key based upon the key id and name for the currently logged user.
    """
    key_id = request.form.get( "key_id", False )
    userid = session[ "user_id" ]
    
    try:
        config.db.query( "DELETE FROM webauthn WHERE id = %s AND user_id = %s", ( key_id, userid, ) )
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
        
    except Exception as e:
        return jsonify( {
            "error": True
        } );

@app.route( baseurl + "/webauthn/disable", methods = [ "POST" ] )
@login_required
def webauthn_disable_key():
    """
        Disable a particular security key for the current user.
    """
    key_id = request.form.get( "key_id", False )
    userid = session[ "user_id" ]
    
    try:
        config.db.query( "UPDATE webauthn SET active = False WHERE id = %s AND user_id = %s", ( key_id, userid, ) )
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
        
    except Exception as e:
        return jsonify( {
            "error": True
        } );

@app.route( baseurl + "/webauthn/enable", methods = [ "POST" ] )
@login_required
def webauthn_enable_key():
    """
        Activation of a security key for the current user.
    """
    key_id = request.form.get( "key_id", False )
    userid = session[ "user_id" ]
    
    try:
        config.db.query( "UPDATE webauthn SET active = True WHERE id = %s AND user_id = %s", ( key_id, userid, ) )
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
        
    except Exception as e:
        return jsonify( {
            "error": True
        } );

@app.route( baseurl + "/webauthn/rename", methods = [ "POST" ] )
@login_required
def webauthn_rename_key():
    """
        Rename a security key for the current user.
    """
    key_id = request.form.get( "key_id", False )
    key_name = request.form.get( "key_name", False )
    userid = session[ "user_id" ]
    
    try:
        config.db.query( "UPDATE webauthn SET key_name = %s WHERE id = %s AND user_id = %s", ( key_name, key_id, userid, ) )
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
        
    except Exception as e:
        return jsonify( {
            "error": True
        } );

@app.route( baseurl + "/webauthn/begin_assertion" )
def webauthn_begin_assertion():
    """
        Get the data to start the login process with all actives keys for a user.
    """
    user_id = session.get( "user_id" )
    
    if "challenge" in session:
        del session[ "challenge" ]
    
    challenge = pyotp.random_base32( 64 )
    session[ "challenge" ] = challenge
    
    q = config.db.query( "SELECT * FROM webauthn WHERE user_id = %s AND active = true", ( user_id, ) )
    key_list = q.fetchall()
    
    credential_id_list = []
    for key in key_list:
        credential_id_list.append( {
            "type": "public-key",
            "id": key[ "credential_id" ],
            "transports": [ "usb", "nfc", "ble", "internal" ]
        } )
     
    assertion_dict = {
        "challenge": challenge,
        "timeout": 60000,
        "allowCredentials": credential_id_list,
        "rpId": config.RP_ID,
    }
    
    return jsonify( {
        "error": False,
        "data": assertion_dict
    } )

@app.route( baseurl + "/webauthn/verify_assertion", methods = [ "POST" ] )
def webauthn_verify_assertion():
    """
        Check the signed challenge provided to the user for the login process.
    """
    challenge = session.get( "challenge" )
    assertion_response = request.form
    credential_id = assertion_response.get( "id" )
    
    q = config.db.query( "SELECT * FROM webauthn WHERE credential_id = %s", ( credential_id, ) )
    user = q.fetchone()
    
    webauthn_user = webauthn.WebAuthnUser( 
        None, session[ "username" ], None, None,
        user[ "credential_id" ], user[ "pub_key" ], user[ "sign_count" ], config.RP_ID
    )

    webauthn_assertion_response = webauthn.WebAuthnAssertionResponse( 
        webauthn_user,
        assertion_response,
        challenge,
        config.ORIGIN,
        uv_required = False
    )
    
    try:
        sign_count = webauthn_assertion_response.verify()
        
    except Exception as e:
        return jsonify( {
            "error": True,
            "message": "Assertion failed. Error: {}".format( e )
        } )
    
    else:
        dt = datetime.now( pytz.timezone( "Europe/Zurich" ) )
        q = config.db.query( "UPDATE webauthn SET sign_count = %s, last_usage = %s, usage_counter = usage_counter + 1 WHERE credential_id = %s", ( sign_count, dt, credential_id, ) )
        config.db.commit()
        
        session[ "need_to_check" ].remove( "securitykey" )
        do_login()
        
        return jsonify( {
            "error": False
        } )

################################################################################
#    New user

@app.route( baseurl + "/signin" )
def new_user():
    """
        Serve the page to register to ICNML.
    """
    q = config.db.query( "SELECT id, name FROM account_type WHERE can_singin = true" )
    r = q.fetchall()
    
    account_type = []
    for rr in r:
        account_type.append( dict( rr ) )
    
    return render_template( 
        "users/signin.html",
        baseurl = baseurl,
        list_account_type = account_type,
        js = config.cdnjs,
        css = config.cdncss,
        envtype = envtype
    )

@app.route( baseurl + "/do/signin", methods = [ "POST" ] )
def add_account_request_to_db():
    """
        Add the new user request to the database.
    """
    try:
        first_name = request.form[ "first_name" ]
        last_name = request.form[ "last_name" ]
        email = request.form[ "email" ]
        account_type = int( request.form[ "account_type" ] )
        uuid = str( uuid4() )
        
        sql = "SELECT name FROM account_type WHERE id = %s"
        account_type_name = config.db.query_fetchone( sql, ( account_type, ) )[ "name" ]
        account_type_name = account_type_name.lower()

        sql = "SELECT nextval( 'username_{}_seq' ) as id".format( account_type_name ) 
        username_id = config.db.query_fetchone( sql )[ "id" ]
        
        config.db.query( 
            """
                INSERT INTO signin_requests
                ( first_name, last_name, email, account_type, uuid, username_id )
                VALUES ( %s, %s, %s, %s, %s, %s )
            """,
            ( first_name, last_name, email, account_type, uuid, username_id, )
        )
        config.db.commit()
        
        return jsonify( {
            "error": False,
            "uuid": uuid
        } )
        
    except:
        return jsonify( {
            "error": True
        } )

@app.route( baseurl + "/validate_signin" )
@admin_required
def validate_signin():
    """
        Serve the page to admins regarding the validation of new users.
    """
    q = config.db.query( """
        SELECT signin_requests.*, account_type.name as account_type 
        FROM signin_requests
        LEFT JOIN account_type ON signin_requests.account_type = account_type.id
        WHERE signin_requests.status = 'pending'
    """ )
    
    users = []
    
    try:
        r = q.fetchall()
        for rr in r:
            users.append( dict( rr ) )
    except:
        pass
    
    return render_template( 
        "users/validate_signin.html",
        baseurl = baseurl,
        users = users,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        envtype = envtype
    )

@app.route( baseurl + "/do/validate_signin", methods = [ "POST" ] )
@admin_required
def do_validate_signin():
    """
        Prepare the new user data to be signed by the admin, and serve the page.
    """
    request_id = request.form.get( "id" )
    
    q = config.db.query( "SELECT * FROM signin_requests WHERE id = %s", ( request_id, ) )
    s = q.fetchone()
    s = dict( s )
    
    r = {}
    
    r[ "user" ] = s
    r[ "user" ][ "request_time" ] = str( r[ "user" ][ "request_time" ] )
    r[ "user" ][ "validation_time" ] = str( r[ "user" ][ "validation_time" ] )
    
    r[ "acceptance" ] = {}
    r[ "acceptance" ][ "username" ] = session[ "username" ]
    r[ "acceptance" ][ "time" ] = str( datetime.now() )
    
    j = json.dumps( r )
    challenge = base64.b64encode( j )
    challenge = challenge.replace( "=", "" )
    session[ "validation_user_challenge" ] = challenge
    
    ############################################################################
    
    user_id = session[ "user_id" ]
    
    q = config.db.query( "SELECT * FROM webauthn WHERE user_id = %s AND usage_counter > 0 ORDER BY last_usage DESC LIMIT 1", ( user_id, ) )
    key = q.fetchone()
    
    webauthn_user = webauthn.WebAuthnUser( 
        key[ "ukey" ], session[ "username" ], session[ "username" ], None,
        key[ "credential_id" ], key[ "pub_key" ], key[ "sign_count" ], config.RP_ID
    )
    webauthn_assertion_options = webauthn.WebAuthnAssertionOptions( webauthn_user, challenge )
    
    return jsonify( {
        "error": False,
        "data": webauthn_assertion_options.assertion_dict
    } )

@app.route( baseurl + "/do/validate_signin_2", methods = [ "POST" ] )
@admin_required
def do_validate_signin_2():