path: root/database/sqlite/administration.c

/* administration.c  --  Functions needed for administration tasks
 *
 *  GPLv2 only - Copyright (C) 2008, 2009
 *               David Sommerseth <dazo@users.sourceforge.net>
 *
 *  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; version 2
 *  of the License.
 *
 *  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, write to the Free Software
 *  Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 *
 */

#include <string.h>
#include <unistd.h>
#include <assert.h>

#include <libxml/tree.h>

#ifndef DRIVERAPIVERSION
# define DRIVERAPIVERSION 2
#endif

#include <sqlite3.h>

#include <eurephia_nullsafe.h>
#include <eurephia_context.h>
#include <eurephia_admin_struct.h>
#include <eurephia_log.h>
#include <eurephia_xml.h>
#include <eurephia_values.h>
#include <eurephiadb_session_struct.h>
#include <eurephiadb_mapping.h>
#include <passwd.h>

#ifndef DRIVER_MODE
#define DRIVER_MODE
#endif
#include <eurephiadb_driver.h>

#include "sqlite.h"

#define FMAP_USERS
#define FMAP_CERTS
#define FMAP_USERCERTS
#define FMAP_ADMINACCESS
#define FMAP_LASTLOG
#include "fieldmapping.h"

#if DRIVERAPIVERSION > 1
/*
 *  API Version 2 functions
 *
 */

// local, internal function
void xmlReplaceChars(xmlChar *str, char s, char r) {
        if( str != NULL ) {
                xmlChar *ptr = str;

                while( *ptr != '\0' ) {
                        if( *ptr == s ) {
                                *ptr = r;
                        }
                        ptr++;
                }
        }
}


// Authenticate admin user against user database
int eDBadminAuth(eurephiaCTX *ctx, const char *req_access, const char *uname, const char *pwd) {
        dbresult *res = NULL;
        char *crpwd = NULL, *dbpwd = NULL;
        char *activated = NULL, *deactivated = NULL, *blid = NULL;
        int uid = -1, access = 0;
        char interface;

        DEBUG(ctx, 20, "Function call: eDBadminAuth(ctx, '%s, '%s', 'xxxxxxxx')", req_access, uname);

        assert(ctx != NULL);

        switch( ctx->context_type ) {
        case ECTX_ADMIN_CONSOLE:
                interface = 'C';
                break;
        case ECTX_ADMIN_WEB:
                interface = 'W';
                break;
        default:
                eurephia_log(ctx, LOG_ERROR, 0, "Wrong eurephia context type (0x%04x)", ctx->context_type);
                return 0;
        }

        if( (strlen_nullsafe(uname) < 4) || (strlen_nullsafe(pwd) < 4) ) {
                eurephia_log(ctx, LOG_WARNING, 0, "User name and/or password is either null or less than 4 bytes");
                return 0;
        }

        //
        // Authenticate user and password
        //
        res = sqlite_query(ctx,
                           "SELECT activated, deactivated, bl.blid, "
                           "       password, uid "
                           "  FROM openvpn_users ou"
                           "  LEFT JOIN openvpn_blacklist bl USING (username)"
                           " WHERE ou.username = '%q'",
                           uname);

        if( res == NULL ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not authenticate user against the database");
                return 0;
        }

        if( sqlite_get_numtuples(res) == 1 ) {
                activated   = sqlite_get_value(res, 0, 0);
                deactivated = sqlite_get_value(res, 0, 1);
                blid        = sqlite_get_value(res, 0, 2);
                dbpwd       = sqlite_get_value(res, 0, 3);
                uid         = atoi_nullsafe(sqlite_get_value(res, 0, 4));

                if( blid != NULL ) {
                        eurephia_log(ctx, LOG_WARNING, 0,
                                     "Your user account is BLACKLISTED.  You have no access.");
                        sqlite_free_results(res);
                        return 0;
                }

                if( activated == NULL ) {
                        eurephia_log(ctx, LOG_WARNING, 0, "Your user account is not yet activated.");
                        sqlite_free_results(res);
                        return 0;
                }

                if( deactivated != NULL ) {
                        eurephia_log(ctx, LOG_WARNING, 0, "Your user account is deactivated.");
                        sqlite_free_results(res);
                        return 0;
                }

                if( dbpwd == NULL ) {
                        eurephia_log(ctx, LOG_WARNING, 0, "Authentication failed. DB error.");
                        sqlite_free_results(res);
                        return 0;
                } else {
                        int pwdok = 0;
                        // Verify the password
                        crpwd = eurephia_pwd_crypt(ctx, pwd, dbpwd);
                        pwdok = ((crpwd != NULL) && (strcmp(crpwd, dbpwd) == 0) ? 1 : 0);
                        memset(crpwd, 0, strlen_nullsafe(crpwd));
                        memset(dbpwd, 0, strlen_nullsafe(dbpwd));
                        free_nullsafe(crpwd);
                        if( pwdok == 0 ) {
                                eurephia_log(ctx, LOG_WARNING, 0, "Authentication failed.");
                                sleep(2);
                                sqlite_free_results(res);
                                return 0;
                        }
                }
                sqlite_free_results(res);

                // Check if access level is granted
                // (SQLite do not handle advanced joins so well, so we need to
                //  do this check with an extra query)
                res = sqlite_query(ctx,
                                   "SELECT (count(*) = 1) AS access "
                                   "  FROM eurephia_adminaccess"
                                   " WHERE uid = '%i' AND interface = '%c' AND access = '%q'",
                                   uid, interface, req_access);
                if( res == NULL ) {
                        eurephia_log(ctx, LOG_FATAL, 0, "Could not check access level");
                        return 0;
                }
                access = atoi_nullsafe(sqlite_get_value(res, 0, 0));
                sqlite_free_results(res);

                if( access == 0 ) {
                        eurephia_log(ctx, LOG_WARNING, 0, "Your account is lacking privileges for this operation");
                        return 0;
                }
        } else {
                eurephia_log(ctx, LOG_WARNING, 0, "Authentication failed. No unique records found.");
                sqlite_free_results(res);
                sleep(2);
                return 0;
        }

        // If we reach this place, authentication was successful.  Return users uid
        return uid;
}

int eDBadminValidateSession(eurephiaCTX *ctx, const char *sesskey, const char *req_access) {
        dbresult *res = NULL;
        int valid = 0, access = 0, expire_time = 0;
        char interface;

        DEBUG(ctx, 20, "Function call: eDBadminValidateSession(ctx, '%s, '%s')", sesskey, req_access);
        assert( (ctx != NULL) && (sesskey != NULL) );

        switch( ctx->context_type ) {
        case ECTX_ADMIN_CONSOLE:
                interface = 'C';
                break;
        case ECTX_ADMIN_WEB:
                interface = 'W';
                break;
        default:
                eurephia_log(ctx, LOG_ERROR, 0, "Wrong eurephia context type (0x%04x)", ctx->context_type);
                return 0;
        }

        // Check if the session is still valid (not expired) and that this session are allowed to access
        // the requested access level.
        expire_time = (60 * atoi_nullsafe(defaultValue(eGet_value(ctx->dbc->config, "eurephiadmin_autologout"),
                                                       "10")
                                          )
                       );
        res = sqlite_query(ctx,
                           "SELECT (strftime('%%s',CURRENT_TIMESTAMP)-strftime('%%s',last_action)) > %i AS exp,"
                           "       (access IS NOT NULL) AS access"
                           "  FROM eurephia_adminlog"
                           "  LEFT JOIN eurephia_adminaccess USING(uid,interface)"
                           " WHERE status IN (1,2)"
                           "       AND sessionkey = '%q'"
                           "       AND access = '%q'",
                           expire_time, sesskey, req_access);

        if( (res == NULL) ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not validate session");
                return 0;
        }

        valid  = (atoi_nullsafe(sqlite_get_value(res, 0, 0)) == 0);
        access = (atoi_nullsafe(sqlite_get_value(res, 0, 1)) == 1);
        sqlite_free_results(res);

        // If still valid, update last_action
        if( valid && access ) {
                res = sqlite_query(ctx,
                                   "UPDATE eurephia_adminlog"
                                   "   SET last_action = CURRENT_TIMESTAMP, status = 2"
                                   " WHERE sessionkey = '%q'", sesskey);
                if( res == NULL ) {
                        eurephia_log(ctx, LOG_ERROR, 0, "Could not register session activity");
                }
                sqlite_free_results(res);

        } else {
                // If not valid, register session as auto-logged out

                res = sqlite_query(ctx,
                                   "UPDATE eurephia_adminlog"
                                   "   SET logout = CURRENT_TIMESTAMP, status = %i"
                                   " WHERE sessionkey = '%q'",
                                   (access ? 4 : 5), sesskey);
                if( res == NULL ) {
                        eurephia_log(ctx, LOG_ERROR, 0, "Could not register old session as logged out");
                }
                sqlite_free_results(res);

                // Delete session variables
                res = sqlite_query(ctx, "DELETE FROM openvpn_sessions WHERE sessionkey = '%q'",
                                   sesskey);
                if( res == NULL ) {
                        eurephia_log(ctx, LOG_ERROR, 0,
                                     "Could not delete session variables (%s))", sesskey);
                        return 0;
                }
                sqlite_free_results(res);

                if( !access ) {
                        eurephia_log(ctx, LOG_WARNING, 0, "Your user account is lacking privileges");
                }

        }
        return (valid && access);
}

int eDBadminRegisterLogin(eurephiaCTX *ctx, eurephiaSESSION *session) {
        dbresult *res = NULL;
        char interface;
        int uid;

        DEBUG(ctx, 20, "Function call: eDBadminRegisterLogin(ctx, {session}'%s')", session->sessionkey);
        assert((ctx != NULL) && (session != NULL));

        switch( ctx->context_type ) {
        case ECTX_ADMIN_CONSOLE:
                interface = 'C'; break;
        case ECTX_ADMIN_WEB:
                interface = 'W'; break;
        default:
                eurephia_log(ctx, LOG_ERROR, 0, "Wrong eurephia context type (0x%04x)", ctx->context_type);
                return 0;
        }

        // Register login into eurephia_adminlog ... uid, login, interface, sessionkey
        uid = atoi_nullsafe(eGet_value(session->sessvals, "uid"));
        res = sqlite_query(ctx,
                           "INSERT INTO eurephia_adminlog "
                           "       (uid, interface, status, login, last_action, sessionkey) "
                           "VALUES ('%i','%c',1,CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '%q')",
                           uid, interface, session->sessionkey);
        if( !res ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not manage to register the session in the database");
                return 0;
        }
        sqlite_free_results(res);
        return 1;
}

int eDBadminLogout(eurephiaCTX *ctx, const char *sessionkey) {
        dbresult *res = NULL;

        DEBUG(ctx, 20, "Function call: eDBadminLogout(ctx, '%s')", sessionkey);
        assert((ctx != NULL) && (sessionkey != NULL));

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return 0;
        }

        // Update session as logged out
        res = sqlite_query(ctx,
                           "UPDATE eurephia_adminlog "
                           "   SET logout = CURRENT_TIMESTAMP, status = 3"
                           " WHERE sessionkey = '%q'",
                           sessionkey);
        if( !res ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not manage to register the session as logged out");
                return 0;
        }
        sqlite_free_results(res);

        // Delete session variables
        res = sqlite_query(ctx, "DELETE FROM openvpn_sessions WHERE sessionkey = '%q'", sessionkey);
        if( res == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0,
                             "Could not delete session variables (%s))", sessionkey);
                return 0;
        }
        sqlite_free_results(res);

        return 1;
}

int eDBadminConfigSet(eurephiaCTX *ctx, const char *key, const char *val) {
        dbresult *res = NULL;
        int found = 0;

        DEBUG(ctx, 20, "Function call: eDBadminConfigSet(ctx, '%s', '%s')", key, val);
        assert((ctx != NULL) && (ctx->dbc != NULL));

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return 0;
        }

        res = sqlite_query(ctx, "SELECT count(*) FROM openvpn_config WHERE datakey = '%q'", key);
        if( !res ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not query configuration table");
                return 0;
        }
        found = atoi_nullsafe(sqlite_get_value(res, 0, 0));
        sqlite_free_results(res);

        if( found == 0 ) {
                res = sqlite_query(ctx,
                                   "INSERT INTO openvpn_config (datakey, dataval) VALUES ('%q','%q')",
                                   key, val);
        } else {
                res = sqlite_query(ctx, "UPDATE openvpn_config SET dataval = '%q' WHERE datakey = '%q'",
                                   val, key);
        }

        if( res == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not register configuration entry (%s = '%s'", key, val);
                return 0;
        }
        sqlite_free_results(res);
        eAdd_value(ctx, ctx->dbc->config, key, val);
        return 1;
}

int eDBadminConfigDelete(eurephiaCTX *ctx, const char *key) {
        dbresult *res = NULL;

        DEBUG(ctx, 20, "Function call: eDBadminConfigDelete(ctx, '%s') ", key);
        assert((ctx != NULL) && (ctx->dbc != NULL));

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return 0;
        }

        res = sqlite_query(ctx, "DELETE FROM openvpn_config WHERE datakey = '%q'", key);
        if( !res ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could delete config configuration entry (%s)", key);
                return 0;
        }
        sqlite_free_results(res);
        return 1;
}

xmlDoc *eDBadminGetUserList(eurephiaCTX *ctx, const char *sortkeys) {
        xmlDoc *userlist = NULL;
        xmlNode *root_n = NULL, *user_n = NULL;
        dbresult *res = NULL;
        char *dbsort = NULL, tmp[34];
        int i = 0;

        DEBUG(ctx, 20, "Function call: eDBadminGetUserList(ctx, '%s')", sortkeys);
        assert((ctx != NULL) && (ctx->dbc != 0));

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return NULL;
        }

        // Convert the input sort keys to the proper database field names
        dbsort = eDBmkSortKeyString(tbl_sqlite_users, sortkeys);

        // Query database for all users
        res = sqlite_query(ctx,
                           "SELECT username, activated, deactivated, last_accessed, uid"
                           "  FROM openvpn_users "
                           "ORDER BY %s", (sortkeys != NULL ? dbsort : "uid"));
        if( res == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Error querying the user database");
                return NULL;
        }

        // Prepare a list with all users
        memset(&tmp, 0, 34);
        eurephiaXML_CreateDoc(ctx, 1, "userlist", &userlist, &root_n);
        snprintf(tmp, 32, "%i", sqlite_get_numtuples(res));
        xmlNewProp(root_n, (xmlChar *)"usercount", (xmlChar *)tmp);

        // Register all records
        for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
                user_n = xmlNewChild(root_n, NULL, (xmlChar *)"user", NULL);
                sqlite_xml_value(user_n, XML_ATTR, "uid",           res, i, 4);
                sqlite_xml_value(user_n, XML_NODE, "username",      res, i, 0);
                sqlite_xml_value(user_n, XML_NODE, "activated",     res, i, 1);
                sqlite_xml_value(user_n, XML_NODE, "deactivated",   res, i, 2);
                sqlite_xml_value(user_n, XML_NODE, "last_accessed", res, i, 3);
        }
        sqlite_free_results(res);

        // Return a user list
        return userlist;
}

inline int xml_set_flag(xmlNode *node, char *flagname, int flagged) {
        if( flagged ) {
                xmlNewChild(node, NULL, (xmlChar *) "flag", (xmlChar *) flagname);
        }
        return flagged;
}


// This function will search up a user, based on information given in a fieldMapping structure.
// It will return an XML document containing the user information requested, controlled by the
// getInfo flag. These flags are defined in eurephiadb_driver.h
//
// The search XML document format is:
//    <eurephia format="1">
//        <fieldMapping table="users">
//           <{search field}>{search value}</{search field}>
//        </fieldMapping>
//    </eurehpia>
//
//  It can be several search field tags to limit the search even more.
//
xmlDoc *eDBadminGetUserInfo(eurephiaCTX *ctx, int getInfo, xmlDoc *srch) {
        dbresult *uinf = NULL, *qres = NULL;
        eDBfieldMap *uinfo_map = NULL;
        int flag = 0, uid = 0;
        char *username = NULL;

        xmlDoc *doc = NULL;
        xmlNode *root_n = NULL, *info_n = NULL, *fieldmap = NULL;

        DEBUG(ctx, 20, "Function call: eDBadminGetUserUserInfo(ctx, %i, {xmlDoc})", getInfo);
        assert( (ctx != NULL) && (srch != NULL) );

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return NULL;
        }

        fieldmap = eurephiaXML_getRoot(ctx, srch, "fieldMapping", 1);
        uinfo_map = eDBxmlMapping(ctx, tbl_sqlite_users, "u", fieldmap);

        // Query the database, find the user defined in the user map
        uinf = sqlite_query_mapped(ctx, SQL_SELECT,
                                   "SELECT u.username, u.activated, u.deactivated, u.last_accessed, u.uid,"
                                   "       (bl.username IS NOT NULL), opensess, logincount,"
                                   "       (at.attempts > 0)"
                                   "  FROM openvpn_users u"
                                   "  LEFT JOIN openvpn_blacklist bl USING(username)"
                                   "  LEFT JOIN openvpn_attempts at ON(at.username = u.username)"
                                   "  LEFT JOIN (SELECT uid, count(*) AS logincount "
                                   "               FROM openvpn_lastlog"
                                   "              GROUP BY uid) lc"
                                   "         ON (lc.uid = u.uid)"
                                   "  LEFT JOIN (SELECT uid, count(*) > 0 AS opensess"
                                   "               FROM openvpn_lastlog"
                                   "              WHERE sessionstatus = 2"
                                   "              GROUP BY uid) os"
                                   "         ON (os.uid = u.uid)",
                                   NULL, uinfo_map, NULL);

        if( uinf == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Error querying the database for a user");
                return 0;
        }
        eDBfreeMapping(uinfo_map);

        switch( sqlite_get_numtuples(uinf) ) {
        case 0:
                sqlite_free_results(uinf);
                return 0; // No user found

        case 1:
                uid = atoi_nullsafe(sqlite_get_value(uinf, 0, 4));
                username = sqlite_get_value(uinf, 0, 0);

                eurephiaXML_CreateDoc(ctx, 1, "user", &doc, &root_n);
                sqlite_xml_value(root_n, XML_NODE, "username", uinf, 0, 0);
                sqlite_xml_value(root_n, XML_ATTR, "uid", uinf, 0, 4);

                if( (getInfo & USERINFO_user) == USERINFO_user ) {
                        info_n = xmlNewChild(root_n, NULL, (xmlChar *) "flags", NULL);

                        // set DEACTIVATED flag, if deactivated field is not NULL
                        xml_set_flag(info_n, "DEACTIVATED", (sqlite_get_value(uinf, 0, 2) != NULL));

                        // set BLACKLISTED flag, if username is found in blacklist table
                        xml_set_flag(info_n, "BLACKLISTED", (atoi_nullsafe(sqlite_get_value(uinf, 0, 5))==1));

                        // set OPENSESSION flag, if user has a lastlog entry with sessionstatus == 2
                        xml_set_flag(info_n, "OPENSESSION", (atoi_nullsafe(sqlite_get_value(uinf, 0, 6))==1));

                        // set ERRATTEMPT flag, if user has an entry in attempts log with attemtps > 0
                        xml_set_flag(info_n, "ERRATTEMPT", (atoi_nullsafe(sqlite_get_value(uinf, 0, 8))==1));

                        // set NEVERUSED flag, if login count == 0 and last_accessed == NULL
                        flag = xml_set_flag(info_n, "NEVERUSED", ((atoi_nullsafe(sqlite_get_value(uinf,0, 7))==0)
                                                                  && (sqlite_get_value(uinf, 0, 3) == NULL)));

                        // set RSETLASTUSED flag, if login count == 0 and last_accessed == NULL
                        xml_set_flag(info_n, "RSETLASTUSED", !flag && (sqlite_get_value(uinf,0,3)) == NULL);

                        // set RSETLOGINCNT flag, if login count == 0 and last_accessed != NULL
                        xml_set_flag(info_n, "RSETLOGINCNT", ((atoi_nullsafe(sqlite_get_value(uinf,0, 7))==0)
                                                              && (sqlite_get_value(uinf,0,3)) != NULL));

                        sqlite_xml_value(root_n, XML_NODE, "activated", uinf, 0, 1);
                        sqlite_xml_value(root_n, XML_NODE, "deactivated", uinf, 0, 2);
                        info_n = sqlite_xml_value(root_n, XML_NODE, "last_accessed", uinf, 0, 3);
                        sqlite_xml_value(info_n, XML_ATTR, "logincount", uinf, 0, 7);
                }

                if( (getInfo & USERINFO_certs) == USERINFO_certs ) {
                        // Extract certificate info
                        qres = sqlite_query(ctx,
                                            "SELECT depth, digest, common_name, organisation, email, "
                                            "       c.registered, c.certid,  uc.accessprofile, access_descr,"
                                            "       fw_profile"
                                            "  FROM openvpn_certificates c"
                                            "  LEFT JOIN openvpn_usercerts uc ON (c.certid = uc.certid)"
                                            "  LEFT JOIN openvpn_accesses a "
                                            "         ON (uc.accessprofile = a.accessprofile)"
                                            " WHERE uid = '%i' ORDER BY c.certid DESC", uid);

                        info_n = xmlNewChild(root_n, NULL, (xmlChar *) "certificates", NULL);
                        if( (qres != NULL) && (sqlite_get_numtuples(qres) > 0) ) {
                                int i;
                                xmlNode *cert, *acpr;
                                xmlChar *tmp = NULL;

                                for( i = 0; i < sqlite_get_numtuples(qres); i++ ) {
                                        cert = xmlNewChild(info_n, NULL, (xmlChar *) "certificate", NULL);

                                        sqlite_xml_value(cert, XML_ATTR, "certid",        qres, 0, 6);
                                        sqlite_xml_value(cert, XML_ATTR, "depth",         qres, 0, 0);
                                        sqlite_xml_value(cert, XML_ATTR, "registered",    qres, 0, 5);
                                        sqlite_xml_value(cert, XML_NODE, "digest",        qres, 0, 1);

                                        tmp = (xmlChar *)sqlite_get_value(qres, 0, 2);
                                        xmlReplaceChars(tmp, '_', ' ');
                                        xmlNewChild(cert, NULL, (xmlChar *) "common_name", tmp);

                                        tmp = (xmlChar *)sqlite_get_value(qres, 0, 3);
                                        xmlReplaceChars(tmp, '_', ' ');
                                        xmlNewChild(cert, NULL, (xmlChar *) "organisation", tmp);

                                        sqlite_xml_value(cert, XML_NODE, "email",         qres, 0, 4);

                                        acpr = sqlite_xml_value(cert, XML_NODE, "access_profile", qres, 0, 8);
                                        sqlite_xml_value(acpr, XML_ATTR, "accessprofile",         qres, 0, 7);
                                        sqlite_xml_value(acpr, XML_ATTR, "fwdestination",         qres, 0, 9);
                                }
                        }

                        if( qres != NULL ) {
                                sqlite_free_results(qres);
                        }
                }

                if( (getInfo & USERINFO_lastlog) == USERINFO_lastlog ) {
                        int i = 0;
                        xmlNode *lastl = NULL, *sess = NULL, *tmp1 = NULL, *tmp2 = NULL;
                        xmlChar *tmp = NULL;

                        qres = sqlite_query(ctx,
                                            "SELECT llid, ll.certid, protocol, remotehost, remoteport, macaddr,"
                                            "       vpnipaddr, vpnipmask, sessionstatus, sessionkey,"
                                            "       login, logout, session_duration, session_deleted,"
                                            "       bytes_sent, bytes_received, uicid, accessprofile,"
                                            "       access_descr, fw_profile, depth, digest,"
                                            "       common_name, organisation, email"
                                            "  FROM openvpn_lastlog ll"
                                            "  LEFT JOIN openvpn_usercerts USING (uid, certid)"
                                            "  LEFT JOIN openvpn_accesses USING (accessprofile)"
                                            "  LEFT JOIN openvpn_certificates cert ON (ll.certid = cert.certid)"
                                            " WHERE uid = '%i' ORDER BY login, logout", uid);

                        if( qres == NULL ) {
                                eurephia_log(ctx, LOG_ERROR, 0, "Quering the lastlog failed");
                                xmlFreeDoc(doc);
                                return NULL;
                        }

                        lastl = xmlNewChild(root_n, NULL, (xmlChar *) "lastlog", NULL);
                        for( i = 0; i < sqlite_get_numtuples(qres); i++ ) {

                                sess = xmlNewChild(lastl, NULL, (xmlChar*) "session", NULL);
                                sqlite_xml_value(sess, XML_ATTR, "llid",                  qres, i, 0);
                                xmlNewProp(sess, (xmlChar *) "session_status",
                                           (xmlChar *)SESSION_STATUS[atoi_nullsafe(sqlite_get_value(qres, i, 8))]);
                                sqlite_xml_value(sess, XML_ATTR, "session_duration",      qres, i, 12);
                                sqlite_xml_value(sess, XML_NODE, "sessionkey",            qres, i, 9);
                                sqlite_xml_value(sess, XML_NODE, "login",                 qres, i, 10);
                                sqlite_xml_value(sess, XML_NODE, "logout",                qres, i, 11);
                                sqlite_xml_value(sess, XML_NODE, "session_closed",        qres, i, 13);

                                tmp1 = xmlNewChild(sess, NULL, (xmlChar *) "connection", NULL);
                                sqlite_xml_value(tmp1, XML_ATTR, "bytes_sent",            qres, i, 14);
                                sqlite_xml_value(tmp1, XML_ATTR, "bytes_received",        qres, i, 15);
                                sqlite_xml_value(tmp1, XML_NODE, "protocol",              qres, i, 2);
                                sqlite_xml_value(tmp1, XML_NODE, "remote_host",           qres, i, 3);
                                sqlite_xml_value(tmp1, XML_NODE, "remote_port",           qres, i, 4);
                                sqlite_xml_value(tmp1, XML_NODE, "vpn_macaddr",           qres, i, 5);
                                sqlite_xml_value(tmp1, XML_NODE, "vpn_ipaddr" ,           qres, i, 6);
                                sqlite_xml_value(tmp1, XML_NODE, "vpn_netmask",           qres, i, 7);

                                tmp1 = xmlNewChild(sess, NULL, (xmlChar *) "certificate", NULL);
                                sqlite_xml_value(tmp1, XML_ATTR, "certid",                qres, i, 1);
                                sqlite_xml_value(tmp1, XML_ATTR, "uicid",                 qres, i, 16);
                                sqlite_xml_value(tmp1, XML_ATTR, "depth",                 qres, i, 20);
                                sqlite_xml_value(tmp1, XML_NODE, "digest",                qres, i, 21);

                                tmp = (xmlChar *)sqlite_get_value(qres, 0, 22);
                                xmlReplaceChars(tmp, '_', ' ');
                                xmlNewChild(tmp1, NULL, (xmlChar *) "common_name", tmp);

                                tmp = (xmlChar *)sqlite_get_value(qres, 0, 23);
                                xmlReplaceChars(tmp, '_', ' ');
                                xmlNewChild(tmp1, NULL, (xmlChar *) "organisation", tmp);

                                sqlite_xml_value(tmp1, XML_NODE, "email",                 qres, i, 24);

                                tmp2 = sqlite_xml_value(tmp1, XML_NODE, "access_profile", qres, i, 18);
                                sqlite_xml_value(tmp2, XML_ATTR, "accessprofile",         qres, i, 17);
                                sqlite_xml_value(tmp2, XML_ATTR, "fwdestination",         qres, i, 19);
                        }
                        sqlite_free_results(qres);
                }

                if( (getInfo & USERINFO_attempts) == USERINFO_attempts ) {
                        xmlNode *atmpt = NULL;

                        qres = sqlite_query(ctx,
                                            "SELECT attempts, registered, last_attempt, atpid"
                                            "  FROM openvpn_attempts "
                                            " WHERE username = '%q'", username);

                        if( (qres == NULL) || (sqlite_get_numtuples(qres) > 1) ) {
                                eurephia_log(ctx, LOG_ERROR, 0, "Quering for login attempts failed");
                                sqlite_free_results(qres);
                                xmlFreeDoc(doc);
                                return NULL;
                        }

                        atmpt = xmlNewChild(root_n, NULL, (xmlChar *) "attempts", NULL);
                        if( sqlite_get_numtuples(qres) == 1 ) {
                                sqlite_xml_value(atmpt, XML_ATTR, "atpid", qres, 0, 3);
                                sqlite_xml_value(atmpt, XML_ATTR, "attempts", qres, 0, 0);
                                sqlite_xml_value(atmpt, XML_NODE, "first_attempt", qres, 0, 1);
                                sqlite_xml_value(atmpt, XML_NODE, "last_attempt", qres, 0, 2);
                        }
                        sqlite_free_results(qres);
                }

                if( (getInfo & USERINFO_blacklist) == USERINFO_blacklist ) {
                        xmlNode *atmpt = NULL;

                        qres = sqlite_query(ctx,
                                            "SELECT registered, last_accessed, blid"
                                            "  FROM openvpn_blacklist "
                                            " WHERE username = '%q'", username);

                        if( (qres == NULL) || (sqlite_get_numtuples(qres) > 1) ) {
                                eurephia_log(ctx, LOG_ERROR, 0, "Quering blacklist log failed");
                                sqlite_free_results(qres);
                                xmlFreeDoc(doc);
                                return NULL;
                        }

                        atmpt = xmlNewChild(root_n, NULL, (xmlChar *) "blacklist", NULL);
                        if( sqlite_get_numtuples(qres) == 1 ) {
                                sqlite_xml_value(atmpt, XML_ATTR, "blid", qres, 0, 2);
                                sqlite_xml_value(atmpt, XML_NODE, "blacklisted", qres, 0, 0);
                                sqlite_xml_value(atmpt, XML_NODE, "last_accessed", qres, 0, 1);
                        }
                        sqlite_free_results(qres);
                }

                sqlite_free_results(uinf);
                return doc;
        default:
                sqlite_free_results(uinf);
                eurephia_log(ctx, LOG_ERROR, 0, "Too many user records was found.");
                return NULL;
        }
}


// This function will add a user to the openvpn_users table, based on the
// XML document given.  The function returns the UID of the new user. On
// failure, the function returns -1
//
// XML format:
//    <eurephia format="1">
//       <add_user>
//           <fieldMapping table="users">
//               <username>{user name}</username>
//               <password pwhash="{none|sha512}">{password}</password>"
//           </fieldMapping>
//       </add_user>
//    </eurephia>
//
int eDBadminAddUser(eurephiaCTX *ctx, xmlDoc *usrinf) {
        dbresult *res = NULL;
        xmlNode *usrinf_n = NULL;
        eDBfieldMap *usrinf_map = NULL;
        int uid = 0;

        DEBUG(ctx, 20, "Function call: eDBadminAddUser(ctx, xmlDoc)");
        assert( (ctx != NULL) && (usrinf != NULL) );

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return 0;
        }

        // Get the add_user node, and then find the fieldMapping node
        usrinf_n = eurephiaXML_getRoot(ctx, usrinf, "add_user", 1);
        if( usrinf_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not find proper add user XML document");
                return 0;
        }
        usrinf_n = xmlFindNode(usrinf_n, "fieldMapping");
        if( usrinf_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not find proper add user XML document");
                return 0;
        }

        // Get a proper field mapping to be used by the database
        usrinf_map = eDBxmlMapping(ctx, tbl_sqlite_users, NULL, usrinf_n);
        assert( usrinf_map != NULL );

        // Register the user
        res = sqlite_query_mapped(ctx, SQL_INSERT, "INSERT INTO openvpn_users", usrinf_map, NULL, NULL);
        if( res == NULL ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not register the new user account");
                uid = -1;
        } else {
                uid = res->last_insert_id;
        }
        sqlite_free_results(res);
        eDBfreeMapping(usrinf_map);

        return uid;
}


// This function will update a user account based on the XML document sent in as a parameter.
// The function will double check that the uid in the argument list and the uid in the XML
// document is coherent.
//
// The format of the input XML is:
//   <eurephia format="1">
//       <update_user uid="{uid}">
//           <fieldMapping table="users">
//              <{field name}>{new value}</{field name}>
//           </fieldMapping>
//       </update_user>
//   </eurephia>
//
int eDBadminUpdateUser(eurephiaCTX *ctx, const int uid, xmlDoc *usrinf) {
        dbresult *uinf = NULL;
        xmlDoc *srch_xml = NULL;
        xmlNode *root_n = NULL, *srch_n = NULL, *values_n = NULL;
        eDBfieldMap *value_map = NULL, *srch_map = NULL;
        xmlChar *xmluid = 0;

        DEBUG(ctx, 20, "Function call: eDBadminUpdateUser(ctx, %i, xmlDoc)", uid);
        assert( (ctx != NULL) && (usrinf != NULL) );

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return 0;
        }

        // Get the update_user node
        root_n = eurephiaXML_getRoot(ctx, usrinf, "update_user", 1);
        if( root_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not find proper XML element for user update");
                return 0;
        }

        // Double check that we are going to update the right user
        xmluid = (xmlChar *)xmlGetAttrValue(root_n->properties, "uid");
        if( atoi_nullsafe((char *)xmluid) != uid ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Mismatch between uid given as parameter and uid in XML");
                return 0;
        }

        // Grab the fieldMapping node and create a eDBfieldMap structure for it
        values_n = xmlFindNode(root_n, "fieldMapping");
        value_map = eDBxmlMapping(ctx, tbl_sqlite_users, NULL, values_n);

        // Create an eDBfieldMap structure for the srch_map (used for WHERE clause)
        eurephiaXML_CreateDoc(ctx, 1, "fieldMapping", &srch_xml, &srch_n);
        xmlNewProp(srch_n, (xmlChar *) "table", (xmlChar *) "users");
        xmlNewChild(srch_n, NULL, (xmlChar *) "uid", xmluid);  // Add uid as the only criteria
        srch_map = eDBxmlMapping(ctx, tbl_sqlite_users, NULL, srch_n);
        assert( srch_map != NULL );

        // UPDATE the database
        uinf = sqlite_query_mapped(ctx, SQL_UPDATE, "UPDATE openvpn_users", value_map, srch_map, NULL);

        if( uinf == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Error querying the database for a user");
                return 0;
        }
        sqlite_free_results(uinf);

        eDBfreeMapping(srch_map);
        eDBfreeMapping(value_map);
        xmlFreeDoc(srch_xml);

        return 1;
}

// This function will delete a user to the openvpn_users table, based on the
// XML document given.  The uid of the account to be deleted must also be sent
// as a separate parameter, as a security feature
//
// XML format:
//   <eurephia format="1">
//       <delete_user uid="{uid}"/>
//   </eurephia>
//
int eDBadminDeleteUser(eurephiaCTX *ctx, const int uid, xmlDoc *usrinf) {
        dbresult *res = NULL;
        xmlNode *usrinf_n = NULL;
        char *uid_str = NULL;
        int rc = 0;

        DEBUG(ctx, 20, "Function call: eDBadminDeleteUser(ctx, %i, xmlDoc)", uid);
        assert( (ctx != NULL) && (usrinf != NULL) );

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return 0;
        }

        // Get the delete_user node
        usrinf_n = eurephiaXML_getRoot(ctx, usrinf, "delete_user", 1);
        if( usrinf_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not find proper delete user XML document");
                return 0;
        }

        // Get the uid from the XML and compare it with the uid in the function argument
        uid_str = xmlGetAttrValue(usrinf_n->properties, "uid");
        if( (uid_str == NULL) || (atoi_nullsafe(uid_str) != uid) ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not find proper delete user XML document. (uid mismatch)");
                return 0;
        }

        // Delete the user
        res = sqlite_query(ctx, "DELETE FROM openvpn_users WHERE uid = '%i'", uid);
        if( res == NULL ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not delete the user account");
                rc = 0;
        } else {
                rc = 1;
        }
        sqlite_free_results(res);
        return rc;
}


xmlDoc *eDBadminGetCertificateList(eurephiaCTX *ctx, const char *sortkeys) {
        return NULL;
}

// This function will search up all matching certificates from openvpn_certificates and return
// the result as an XML document.
//
// Search criterias are set by sending the following XML document:
//
// <eurephia format="1">
//    <certificate_info>
//        <fieldMapping table="certificates">
//            <{field name}>{field value}</{field name}>
//            ...
//            <{field name}>{field value}</{field name}>
//        </fieldMapping>
//    </certificate_info>
// </eurephia>
//
// The found certificates will be sent in an XML like this:
//
// <eurephia format="1">
//    <certificates certificates={number of found certs}">
//        <certificate certid="{int}" depth="{int}" registered="{date+time}">
//            <digest>{SHA1 digest of cert}</digest>
//            <common_name>{(CN) common name of cert}</common_name>
//            <organisation>{(O) organisation name of cert}</organisation>
//            <email>{(emailAddr) e-mail address found in cert}</email>
//        </certificate>
//    </certificates>
// </eurephia>
//
xmlDoc *eDBadminGetCertificateInfo(eurephiaCTX *ctx, xmlDoc *srchxml, const char *sortkeys) {
        xmlDoc *certlist = NULL;
        xmlNode *srch_n = NULL, *cert_n = NULL, *tmp_n = NULL;
        eDBfieldMap *srch_map = NULL, *ptr = NULL;
        dbresult *res = NULL;
        xmlChar tmp[2050];
        char *dbsort = NULL;
        int i;

        DEBUG(ctx, 20, "Function call: eDBadminGetCertificateInfo(ctx, xmlDoc, '%s')", sortkeys);
        assert( (ctx != NULL) && (srchxml != NULL) );

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return NULL;
        }

        if( sortkeys != NULL ) {
                dbsort = eDBmkSortKeyString(tbl_sqlite_certs, sortkeys);
        }

        srch_n = eurephiaXML_getRoot(ctx, srchxml, "certificate_info", 1);
        if( srch_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not find a valid XML for looking up certificates");
                return NULL;
        }

        srch_n = xmlFindNode(srch_n, "fieldMapping");
        if( srch_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not find a valid XML for looking up certificates");
                return NULL;
        }

        srch_map = eDBxmlMapping(ctx, tbl_sqlite_certs, NULL, srch_n);
        assert( srch_map != NULL );

        // Replace spaces with underscore in common name and
        // in organisation fields, to comply with OpenVPN standards
        for( ptr = srch_map; ptr != NULL; ptr = ptr->next ) {
                if( ptr->field_id & (FIELD_CNAME | FIELD_ORG) ) {
                        xmlReplaceChars((xmlChar *) ptr->value, ' ', '_');
                }
        }

        res = sqlite_query_mapped(ctx, SQL_SELECT,
                                  "SELECT depth, digest, common_name, organisation, email, registered, certid"
                                  "  FROM openvpn_certificates", NULL, srch_map, dbsort);
        if( res == NULL ) {
                eDBfreeMapping(srch_map);
                eurephia_log(ctx, LOG_ERROR, 0, "Could not query the certificate table");
                return NULL;
        }

        memset(&tmp, 0, 2050);
        eurephiaXML_CreateDoc(ctx, 1, "certificates", &certlist, &cert_n);
        xmlStrPrintf(tmp, 64, (xmlChar *) "%i", sqlite_get_numtuples(res));
        xmlNewProp(cert_n, (xmlChar *) "certificates", (xmlChar *) tmp);

        for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
                tmp_n = xmlNewChild(cert_n, NULL, (xmlChar *) "certificate", NULL);

                sqlite_xml_value(tmp_n, XML_ATTR, "certid", res, i, 6);
                sqlite_xml_value(tmp_n, XML_ATTR, "depth", res, i, 0);
                sqlite_xml_value(tmp_n, XML_ATTR, "registered", res, i, 5);
                sqlite_xml_value(tmp_n, XML_NODE, "digest", res, i, 1);

                xmlStrPrintf(tmp, 2048, (xmlChar *) "%.2048s", sqlite_get_value(res, i, 2));
                xmlReplaceChars(tmp, '_', ' ');
                xmlNewChild(tmp_n, NULL, (xmlChar *) "common_name", tmp);

                xmlStrPrintf(tmp, 2048, (xmlChar *) "%.2048s", sqlite_get_value(res, i, 3));
                xmlReplaceChars(tmp, '_', ' ');
                xmlNewChild(tmp_n, NULL, (xmlChar *) "organisation", tmp);

                sqlite_xml_value(tmp_n, XML_NODE, "email", res, i, 4);
        }
        sqlite_free_results(res);
        eDBfreeMapping(srch_map);

        return certlist;
}


// This functions register a certificate into openvpn_certificates, based on the
// following XML document:
//
// <eurephia format="1">
//      <register_certificate>
//          <fieldMapping table="certificates">
//              <depth>{cert.depth}</depth>
//              <digest>{SHA1 digest}</digest>
//              <common_name>{common_name}</common_name>
//              <organisation>{org.}</organisation>
//              <email>{email addr}</email>
//          </fieldMapping>
//      </register_certificate>
// </eurephia>
//
// The function returns certid of the newly registered certificate on success,
// and -1 on failure
int eDBadminAddCertificate(eurephiaCTX *ctx, xmlDoc *certxml) {
        xmlNode *crtinf_n = NULL;
        eDBfieldMap *crtinf_map = NULL, *ptr = NULL;
        dbresult *res = NULL;
        int certid = 0;

        DEBUG(ctx, 20, "Function call: eDBadminAddCertificate(ctx, xmlDoc)");
        assert( (ctx != NULL) && (certxml != NULL) );

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return 0;
        }

        crtinf_n = eurephiaXML_getRoot(ctx, certxml, "register_certificate", 1);
        if( crtinf_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not find a valid XML for registering certificate");
                return 0;
        }

        crtinf_n = xmlFindNode(crtinf_n, "fieldMapping");
        if( crtinf_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not find a valid XML for registering certificate");
                return 0;
        }

        crtinf_map = eDBxmlMapping(ctx, tbl_sqlite_certs, NULL, crtinf_n);
        assert( crtinf_map != NULL );

        // Replace spaces with underscore in common name and
        // in organisation fields, to comply with OpenVPN standards
        for( ptr = crtinf_map; ptr != NULL; ptr = ptr->next ) {
                if( ptr->field_id & (FIELD_CNAME | FIELD_ORG) ) {
                        xmlReplaceChars((xmlChar *) ptr->value, ' ', '_');
                }
        }

        // Register the certificate
        res = sqlite_query_mapped(ctx, SQL_INSERT, "INSERT INTO openvpn_certificates", crtinf_map, NULL, NULL);
        if( res == NULL ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not register the certificate");
                certid = -1;
        } else {
                certid = res->last_insert_id;
        }
        sqlite_free_results(res);
        eDBfreeMapping(crtinf_map);

        return certid;
}


// This functions deletes certificates from openvpn_certificates, based on the
// following XML document:
//
// <eurephia format="1">
//      <delete_certificate>
//          <fieldMapping table="certificates">
//              <digest>{SHA1 digest}</digest>
//              <common_name>{common_name}</common_name>
//              <organisation>{org.}</organisation>
//              <email>{email addr}</email>
//          </fieldMapping>
//      </register_certificate>
// </eurephia>
//
// Not all fieldMapping fields are needed, as you can do bulk removal certificates
//
int eDBadminDeleteCertificate(eurephiaCTX *ctx, xmlDoc *certxml) {
        int rc = 0;
        xmlNode *crtinf_n = NULL;
        eDBfieldMap *crtinf_map = NULL, *ptr = NULL;
        dbresult *res = NULL;

        DEBUG(ctx, 20, "Function call: eDBadminDeleteCertificate(ctx, xmlDoc)");
        assert( (ctx != NULL) && (certxml != NULL) );

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return 0;
        }

        crtinf_n = eurephiaXML_getRoot(ctx, certxml, "delete_certificate", 1);
        if( crtinf_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not find a valid XML for the delete certificate request");
                return 0;
        }

        crtinf_n = xmlFindNode(crtinf_n, "fieldMapping");
        if( crtinf_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not find a valid XML for the delete certificate request");
                return 0;
        }

        crtinf_map = eDBxmlMapping(ctx, tbl_sqlite_certs, NULL, crtinf_n);
        assert( crtinf_map != NULL );

        // Replace spaces with underscore in common name and
        // in organisation fields, to comply with OpenVPN standards
        for( ptr = crtinf_map; ptr != NULL; ptr = ptr->next ) {
                if( ptr->field_id & (FIELD_CNAME | FIELD_ORG) ) {
                        xmlReplaceChars((xmlChar *) ptr->value, ' ', '_');
                }
        }

        // Register the certificate
        res = sqlite_query_mapped(ctx, SQL_DELETE, "DELETE FROM openvpn_certificates", NULL, crtinf_map, NULL);
        if( res == NULL ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not complete the delete certificate request");
                rc = 0;
        } else {
                rc = 1;
        }
        sqlite_free_results(res);
        eDBfreeMapping(crtinf_map);

        return rc;
}


xmlDoc *eDBadminGetUserCertsList(eurephiaCTX *ctx, const char *sortkeys) {
        xmlDoc *list_xml = NULL;
        xmlNode *link_root_n = NULL, *link_n = NULL, *tmp_n = NULL;
        dbresult *res = NULL;
        xmlChar tmp[2050];
        char *dbsort = NULL;
        int i;

        DEBUG(ctx, 20, "Function call: eDBadminGetUserCertsList(ctx, '%s')", sortkeys);
        assert( ctx != NULL );

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return NULL;
        }

        if( sortkeys != NULL ) {
                dbsort = eDBmkSortKeyString(tbl_sqlite_usercerts, sortkeys);
        }

        res = sqlite_query(ctx,
                           "SELECT uicid, ucs.uid AS uid, certid, ucs.registered AS registered,"
                           "       ucs.accessprofile AS accessprofile, access_descr,"
                           "       username, "
                           "       common_name, organisation, email, digest, depth "
                           "  FROM openvpn_usercerts ucs"
                           "  LEFT JOIN openvpn_certificates USING(certid)"
                           "  LEFT JOIN openvpn_accesses acc ON(ucs.accessprofile = acc.accessprofile)"
                           "  LEFT JOIN openvpn_users u ON(u.uid = ucs.uid)%s%s",
                           (dbsort != NULL ? " ORDER BY ":""),
                           (dbsort != NULL ? dbsort : ""));

        if( res == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not query the certificate table");
                return NULL;
        }

        memset(&tmp, 0, 2050);
        eurephiaXML_CreateDoc(ctx, 1, "usercerts_links", &list_xml, &link_root_n);
        xmlStrPrintf(tmp, 64, (xmlChar *) "%i", sqlite_get_numtuples(res));
        xmlNewProp(link_root_n, (xmlChar *) "link_count", (xmlChar *) tmp);

        for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
                link_n = xmlNewChild(link_root_n, NULL, (xmlChar *) "usercert_link", NULL);

                sqlite_xml_value(link_n, XML_ATTR, "uicid", res, i, 0);
                sqlite_xml_value(link_n, XML_ATTR, "registered", res, i, 3);

                tmp_n = sqlite_xml_value(link_n, XML_NODE, "username", res, i, 6);
                sqlite_xml_value(tmp_n, XML_ATTR, "uid", res, i, 1);

                tmp_n = xmlNewChild(link_n, NULL, (xmlChar *) "certificate", NULL);
                sqlite_xml_value(tmp_n, XML_ATTR, "certid", res, i, 2);
                sqlite_xml_value(tmp_n, XML_ATTR, "depth", res, i, 11);

                xmlStrPrintf(tmp, 2048, (xmlChar *) "%.2048s", sqlite_get_value(res, i, 7));
                xmlReplaceChars(tmp, '_', ' ');
                xmlNewChild(tmp_n, NULL, (xmlChar *) "common_name", tmp);

                xmlStrPrintf(tmp, 2048, (xmlChar *) "%.2048s", sqlite_get_value(res, i, 8));
                xmlReplaceChars(tmp, '_', ' ');
                xmlNewChild(tmp_n, NULL, (xmlChar *) "organisation", tmp);

                sqlite_xml_value(tmp_n, XML_NODE, "email", res, i, 9);
                sqlite_xml_value(tmp_n, XML_NODE, "digest", res, i, 10);

                tmp_n = sqlite_xml_value(link_n, XML_NODE, "access_profile", res, i, 5);
                sqlite_xml_value(tmp_n, XML_ATTR, "accessprofile", res, i, 4);
        }
        sqlite_free_results(res);

        return list_xml;
}


int eDBadminUpdateUserCertLink(eurephiaCTX *ctx, xmlDoc *usrcrt_xml) {
        dbresult *res = NULL;
        xmlNode *usrcrt_n = NULL, *fmap_n = NULL;
        eDBfieldMap *usrcrt_m = NULL;
        char *mode = NULL;
        int rc = 0;

        DEBUG(ctx, 20, "Function call: eDBadminUpdateUserCertLink(ctx, xmlDoc)");
        assert( (ctx != NULL) && (usrcrt_xml != NULL) );

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return 0;
        }

        usrcrt_n = eurephiaXML_getRoot(ctx, usrcrt_xml, "usercerts_link", 1);
        if( usrcrt_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not find a valid XML for the user-certs link request");
                return 0;
        }
        mode = xmlGetAttrValue(usrcrt_n->properties, "mode");
        if( mode == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Invalid user-cert link request (1).");
                return 0;
        }

        fmap_n = xmlFindNode(usrcrt_n, "fieldMapping");
        if( fmap_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Invalid user-cert link request (2).");
                return 0;
        }
        usrcrt_m = eDBxmlMapping(ctx, tbl_sqlite_usercerts, NULL, fmap_n);
        assert(usrcrt_m != NULL);

        if( strcmp(mode, "register") == 0 ) {
                res = sqlite_query_mapped(ctx, SQL_INSERT, "INSERT INTO openvpn_usercerts", usrcrt_m, NULL, NULL);
                rc = res->last_insert_id;
        } else if( strcmp(mode, "remove") == 0 ) {
                res = sqlite_query_mapped(ctx, SQL_DELETE, "DELETE FROM openvpn_usercerts", NULL, usrcrt_m, NULL);
                rc = 1;
        }

        if( res == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Failed to register user account / certificate");
                rc = -1;
        } else {
                sqlite_free_results(res);
        }

        eDBfreeMapping(usrcrt_m);
        return rc;
}


// The search XML document format is:
//    <eurephia format="1">
//        <admin_access>
//            <fieldMapping table="eurephia_adminaccess">
//               <{search field}>{search value}</{search field}>
//            </fieldMapping>
//        </admin_access>
//    </eurehpia>
//
//  It can be several search field tags to limit the search even more.
//
xmlDoc *eDBadminGetAdminAccess(eurephiaCTX *ctx, xmlDoc *srch) {
        dbresult *res = NULL;
        eDBfieldMap *fmap = NULL;
        int last_uid = -1, i = 0;

        xmlDoc *doc = NULL;
        xmlNode *root_n = NULL, *fieldmap_n = NULL, *rec_n = NULL, *acl_n = NULL, *tmp_n;

        DEBUG(ctx, 20, "Function call: eDBadminGetAdminAccess(ctx, {xmlDoc})");
        assert( (ctx != NULL) && (srch != NULL) );

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return 0;
        }

        tmp_n = eurephiaXML_getRoot(ctx, srch, "admin_access", 1);
        fieldmap_n = xmlFindNode(tmp_n, "fieldMapping");
        fmap = eDBxmlMapping(ctx, tbl_sqlite_eurephiaadmacc, "eac", fieldmap_n);

        // Query the database, find the user defined in the user map
        res = sqlite_query_mapped(ctx, SQL_SELECT,
                                  "SELECT eac.uid, username, interface, access"
                                  "  FROM eurephia_adminaccess eac"
                                  "  LEFT JOIN openvpn_users USING(uid)",
                                  NULL, fmap, "uid, interface, access");
        if( res == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Error querying the database for a access levels");
                return 0;
        }
        eDBfreeMapping(fmap);

        eurephiaXML_CreateDoc(ctx, 1, "admin_access_list", &doc, &root_n);

        for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
                if( last_uid != atoi_nullsafe(sqlite_get_value(res, i, 0)) ) {
                        // Create a new block element when we get a new uid
                        rec_n = xmlNewChild(root_n, NULL, (xmlChar *) "user_access", NULL);
                        last_uid = atoi_nullsafe(sqlite_get_value(res, i, 0));

                        tmp_n = sqlite_xml_value(rec_n, XML_NODE, "username", res, i, 1);
                        sqlite_xml_value(tmp_n, XML_ATTR, "uid", res, i, 0);

                        acl_n = xmlNewChild(rec_n, NULL, (xmlChar *) "access_levels", NULL);
                }

                tmp_n = sqlite_xml_value(acl_n, XML_NODE, "access", res, i, 3);
                sqlite_xml_value(tmp_n, XML_ATTR, "interface", res, i, 2);
        }

        sqlite_free_results(res);
        return doc;
}


// This functions updates (INSERT/DELETE) records in the eurephia_adminaccess table
// based on information from the following XML document:
//
// <eurephia format="1">
//      <edit_admin_access mode="{grant|revoke}">
//          <fieldMapping table="eurephia_adminaccess">
//              <uid>{user id}</uid>
//              <interface>{C|W}</uid>
//              <accesslevel>{access level string}</accesslevel>
//          </fieldMapping>
//      </edit_admin_access>
// </eurephia>
//
// To grant access, all fields are needed.  For bulk revokes, some fields can be skipped
//
int eDBadminEditAdminAccess(eurephiaCTX *ctx, xmlDoc *grant_xml) {
        dbresult *res = NULL;
        xmlNode *grant_n = NULL, *fmap_n = NULL;
        eDBfieldMap *grant_m = NULL;
        char *mode = NULL;
        int rc = 0;

        DEBUG(ctx, 20, "Function call: eDBadminEditAdminAccess(ctx, xmlDoc)");
        assert( (ctx != NULL) && (grant_xml != NULL) );

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return 0;
        }

        grant_n = eurephiaXML_getRoot(ctx, grant_xml, "edit_admin_access", 1);
        if( grant_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not find a valid XML for the user-certs link request");
                return 0;
        }
        mode = xmlGetAttrValue(grant_n->properties, "mode");
        if( mode == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Invalid edit admin access request (1).");
                return 0;
        }

        fmap_n = xmlFindNode(grant_n, "fieldMapping");
        if( fmap_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Invalid edit admin access request (2).");
                return 0;
        }

        grant_m = eDBxmlMapping(ctx, tbl_sqlite_eurephiaadmacc, NULL, fmap_n);
        assert(grant_m != NULL);

        if( strcmp(mode, "grant") == 0 ) {
                res = sqlite_query_mapped(ctx, SQL_INSERT, "INSERT INTO eurephia_adminaccess",
                                          grant_m, NULL, NULL);
                rc = res->last_insert_id;
        } else if( strcmp(mode, "revoke") == 0 ) {
                res = sqlite_query_mapped(ctx, SQL_DELETE, "DELETE FROM eurephia_adminaccess",
                                          NULL, grant_m, NULL);
                rc = 1;
        }

        if( res == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Failed to update admin access");
                rc = -1;
        } else {
                sqlite_free_results(res);
        }
        eDBfreeMapping(grant_m);

        return rc;
}


xmlDoc *eDBadminGetLastlog(eurephiaCTX *ctx, xmlDoc *srch, const char *sortkeys)
{
        dbresult *res = NULL;
        eDBfieldMap *fmap = NULL, *fptr = NULL;
        int i = 0;

        xmlDoc *doc = NULL;
        xmlNode *fieldmap_n = NULL, *lastl = NULL, *sess = NULL, *tmp1 = NULL, *tmp2 = NULL;

        DEBUG(ctx, 20, "Function call: eDBadminGetLastLog(ctx, {xmlDoc})");
        assert( (ctx != NULL) && (srch != NULL) );

        if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "eurephia admin function call attempted with wrong context type");
                return NULL;
        }

        tmp1 = eurephiaXML_getRoot(ctx, srch, "lastlog_query", 1);
        fieldmap_n = xmlFindNode(tmp1, "fieldMapping");
        fmap = eDBxmlMapping(ctx, tbl_sqlite_lastlog, "ll", fieldmap_n);

        // HACK: Remove table alias for some fields in the field mapping
        for( fptr = fmap; fptr != NULL; fptr = fptr->next) {
                switch( fptr->field_id ) {
                case FIELD_UNAME:
                        free_nullsafe(fptr->table_alias);
                default:
                        break;
                }
        }

        // Query the database, find the user defined in the user map
        res = sqlite_query_mapped(ctx, SQL_SELECT,
                                  "SELECT llid, ll.certid, protocol, remotehost, remoteport, macaddr,"
                                  "       vpnipaddr, vpnipmask, sessionstatus, sessionkey,"
                                  "       login, logout, session_duration, session_deleted,"
                                  "       bytes_sent, bytes_received, uicid, accessprofile,"
                                  "       access_descr, fw_profile, depth, digest,"
                                  "       common_name, organisation, email, username, ll.uid"
                                  "  FROM openvpn_lastlog ll"
                                  "  LEFT JOIN openvpn_usercerts USING (uid, certid)"
                                  "  LEFT JOIN openvpn_accesses USING (accessprofile)"
                                  "  LEFT JOIN openvpn_users users ON( ll.uid = users.uid)"
                                  "  LEFT JOIN openvpn_certificates cert ON (ll.certid = cert.certid)",
                                  NULL, fmap, sortkeys);
        eDBfreeMapping(fmap);
        xmlFreeDoc(doc);
        if( res == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Quering the lastlog failed");
                return NULL;
        }
        eurephiaXML_CreateDoc(ctx, 1, "lastlog", &doc, &lastl);
        assert( (doc != NULL) && (lastl != NULL) );
        for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
                xmlChar *tmp = NULL;
                sess = xmlNewChild(lastl, NULL, (xmlChar*) "session", NULL);
                sqlite_xml_value(sess, XML_ATTR, "llid",                  res, i, 0);
                xmlNewProp(sess, (xmlChar *) "session_status",
                           (xmlChar *)SESSION_STATUS[atoi_nullsafe(sqlite_get_value(res, i, 8))]);
                sqlite_xml_value(sess, XML_ATTR, "session_duration",      res, i, 12);
                sqlite_xml_value(sess, XML_NODE, "sessionkey",            res, i, 9);
                sqlite_xml_value(sess, XML_NODE, "login",                 res, i, 10);
                sqlite_xml_value(sess, XML_NODE, "logout",                res, i, 11);
                sqlite_xml_value(sess, XML_NODE, "session_closed",        res, i, 13);

                tmp1 = xmlNewChild(sess, NULL, (xmlChar *) "connection", NULL);
                sqlite_xml_value(tmp1, XML_ATTR, "bytes_sent",            res, i, 14);
                sqlite_xml_value(tmp1, XML_ATTR, "bytes_received",        res, i, 15);
                sqlite_xml_value(tmp1, XML_NODE, "protocol",              res, i, 2);
                sqlite_xml_value(tmp1, XML_NODE, "remote_host",           res, i, 3);
                sqlite_xml_value(tmp1, XML_NODE, "remote_port",           res, i, 4);
                sqlite_xml_value(tmp1, XML_NODE, "vpn_macaddr",           res, i, 5);
                sqlite_xml_value(tmp1, XML_NODE, "vpn_ipaddr" ,           res, i, 6);
                sqlite_xml_value(tmp1, XML_NODE, "vpn_netmask",           res, i, 7);

                tmp1 = sqlite_xml_value(sess, XML_NODE, "username",       res, i, 25);
                sqlite_xml_value(tmp1, XML_ATTR, "uid",                   res, i, 26);

                tmp1 = xmlNewChild(sess, NULL, (xmlChar *) "certificate", NULL);
                sqlite_xml_value(tmp1, XML_ATTR, "certid",                res, i, 1);
                sqlite_xml_value(tmp1, XML_ATTR, "uicid",                 res, i, 16);
                sqlite_xml_value(tmp1, XML_ATTR, "depth",                 res, i, 20);
                sqlite_xml_value(tmp1, XML_NODE, "digest",                res, i, 21);

                tmp = (xmlChar *)sqlite_get_value(res, i, 22);
                xmlReplaceChars(tmp, '_', ' ');
                xmlNewChild(tmp1, NULL, (xmlChar *) "common_name", tmp);

                tmp = (xmlChar *)sqlite_get_value(res, i, 23);
                xmlReplaceChars(tmp, '_', ' ');
                xmlNewChild(tmp1, NULL, (xmlChar *) "organisation", tmp);

                sqlite_xml_value(tmp1, XML_NODE, "email",                 res, i, 24);

                tmp2 = sqlite_xml_value(tmp1, XML_NODE, "access_profile", res, i, 18);
                sqlite_xml_value(tmp2, XML_ATTR, "accessprofile",         res, i, 17);
                sqlite_xml_value(tmp2, XML_ATTR, "fwdestination",         res, i, 19);
        }
        sqlite_free_results(res);
        return doc;
}

xmlDoc *eDBadminGetAttemptsLog(eurephiaCTX *ctx, xmlDoc *usersrch, xmlDoc *certsrch,
                                        const char *sortkeys)
{
        return NULL;
}

#endif