path: root/database/postgresql/prepared-sql.c

/*  prepared-sql.c  -- PostgreSQL database driver for eurephia
 *
 *  GPLv2 only - Copyright (C) 2012
 *               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.
 *
 */

/**
 * @file   prepared-sql.c
 * @author David Sommerseth <dazo@users.sourceforge.net>
 * @date   2012-01-15
 *
 * @brief  eurephia database driver for the PostgreSQL database.
 *         The infrastructure needed for prepared SQL statements
 */

#include <stdlib.h>
#include <string.h>
#include <libpq-fe.h>

#include <eurephia_context.h>
#include <eurephia_log.h>
#include <eurephia_nullsafe.h>

#include "prepared-sql.h"
#include "pgsql-common.h"

typedef struct {
        const ePG_prepID prepid;
        const char *name;
        const unsigned int numargs;
        const char *sql;
} _ePGprepStatements_t;


/* All prepared statements are declared here, including the SQL part */
static const _ePGprepStatements_t _ePGprepStatementsPlugin[] = {
        {.prepid = PREPSQL_TLS_AUTH,
         .name = "eurephia_tls_auth", .numargs = 5,
         .sql  =
         "SELECT cert.certid, blid "
         "  FROM certificates cert"
         "  LEFT JOIN blacklist bl USING(digest)"
         " WHERE organisation=$1::VARCHAR"
         "      AND common_name=$2::VARCHAR"
         "      AND email=$3::VARCHAR AND depth=$4::INTEGER"
         "      AND lower(cert.digest)=lower($5::VARCHAR)"
        },

        {.prepid = PREPSQL_USERPWD_AUTH,
         .name = "eurephia_userpwd_auth", .numargs = 2,
         .sql =
         "SELECT uicid, ou.uid, activated, deactivated,"
         "bl1.blid, bl2.blid, password"
         "  FROM users ou"
         "  JOIN usercerts uc USING(uid)"
         "  LEFT JOIN blacklist bl1 ON( ou.username = bl1.username)"
         "  LEFT JOIN (SELECT blid, certid"
         "               FROM certificates"
         "               JOIN blacklist USING(digest)) bl2"
         "                   ON(uc.certid = bl2.certid)"
         " WHERE uc.certid = $1::INTEGER"
         "      AND ou.username = $2::VARCHAR"
        },

        {.prepid = PREPSQL_BLACKLIST_ATTEMPTUPD,
         .name ="eurephia_blacklist_attupd" , .numargs = 2,
         .sql =
         "SELECT uicid, ou.uid, activated, deactivated,"
         "bl1.blid, bl2.blid, password"
         "  FROM users ou"
         "  JOIN usercerts uc USING(uid)"
         "  LEFT JOIN blacklist bl1 ON( ou.username = bl1.username)"
         "  LEFT JOIN (SELECT blid, certid"
         "               FROM certificates"
         "               JOIN blacklist USING(digest)) bl2"
         "                   ON(uc.certid = bl2.certid)"
         " WHERE uc.certid = $1::INTEGER"
         "      AND ou.username = $2::VARCHAR"
        },

        {.prepid = PREPSQL_USERS_LASTACC_UPD,
         .name = "eurephia_users_lastacc_upd", .numargs = 1,
         .sql =
         "UPDATE users_last_access SET last_accessed = NULL"
         " WHERE uid = $1::INTEGER"
        },

        {.prepid = PREPSQL_USERS_GETUID,
         .name = "eurephia_getuid", .numargs = 2,
         .sql =
         "SELECT uid "
         "  FROM usercerts "
         "  JOIN users USING (uid) "
         " WHERE certid = $1::INTEGER AND username = $2::VARCHAR",
        },

        {.prepid = PREPSQL_ATTEMPTS_RESET,
         .name = "eurephia_attempts_reset", .numargs = 1,
         .sql =
         "UPDATE attempts"
         "   SET attempts = 0 "
         " WHERE atpid = $1::INTEGER"
        },

        {.prepid = PREPSQL_ATTEMPTS_INCR,
         .name = "eurephia_attempts_increment", .numargs = 1,
         .sql =
         "UPDATE attempts"
         "   SET last_attempt = CURRENT_TIMESTAMP, "
         "       attempts = attempts + 1"
         " WHERE atpid = $1::INTEGER"
        },

        {.prepid = PREPSQL_ATTEMPTS_REG_CERT,
         .name = "eurephia_attempts_reg_cert", .numargs = 1,
         .sql = "INSERT INTO attempts (digest, attempts) VALUES ($1::VARCHAR, 1)"
        },

        {.prepid = PREPSQL_ATTEMPTS_REG_USERNAME,
         .name = "eurephia_attempts_reg_username", .numargs = 0,
         .sql = "INSERT INTO attempts (username, attempts) VALUES ($1::VARCHAR, 1)"
        },

        {.prepid = PREPSQL_ATTEMPTS_REG_IPADDR,
         .name = "eurephia_attempts_reg_ipaddr", .numargs = 0,
         .sql = "INSERT INTO attempts (remoteip, attempts) VALUES ($1::VARCHAR, 1)"
        },

        {.prepid = PREPSQL_BLACKLIST_CHECK_CERT,
         .name = "eurephia_blacklist_check_cert", .numargs = 2,
         .sql =
         "SELECT atpid, attempts >= $2::INTEGER, blid, attempts "
         "  FROM attempts "
         "  LEFT JOIN blacklist USING(digest)"
         " WHERE lower(digest) = lower($1::VARCHAR)"
        },

        {.prepid = PREPSQL_BLACKLIST_CHECK_USERNAME,
         .name = "eurephia_blacklist_check_username", .numargs = 2,
         .sql =
         "SELECT atpid, attempts >= $2::INTEGER, blid, attempts "
         "  FROM attempts "
         "  LEFT JOIN blacklist USING(username)"
         " WHERE username = $1::VARCHAR"
        },

        {.prepid = PREPSQL_BLACKLIST_CHECK_IPADDR,
         .name = "eurephia_blacklist_check_cert_ipaddr", .numargs = 2,
         .sql =
         "SELECT atpid, attempts >= $2::INTEGER, blid, attempts "
         "  FROM attempts "
         "  LEFT JOIN blacklist USING(remoteip)"
         " WHERE remoteip = $1::VARCHAR"
        },

        {.prepid = PREPSQL_BLACKLIST_REG_CERT,
         .name = "eurephia_blacklist_reg_cert", .numargs = 0,
         .sql = "INSERT INTO blacklist (digest) VALUES ($1::VARCHAR)"
        },

        {.prepid = PREPSQL_BLACKLIST_REG_USERNAME,
         .name = "eurephia_blacklist_reg_username", .numargs = 0,
         .sql = "INSERT INTO blacklist (username) VALUES ($1::VARCHAR)"
        },

        {.prepid = PREPSQL_BLACKLIST_REG_IPADDR,
         .name = "eurephia_blacklist_reg_ipaddr", .numargs = 0,
         .sql = "INSERT INTO blacklist (remoteip) VALUES ($1::VARCHAR)"
        },

        {.prepid = PREPSQL_SESSIONKEY_GETSEED_SESSION,
         .name = "eurephia_sessionkey_getseed_session", .numargs = 1,
         .sql =
         "SELECT sessionkey "
         "  FROM sessionkeys "
         "  JOIN lastlog USING (sessionkey)"
         " WHERE sessionstatus IN (1,2)"
         "       AND sessionseed = $1::VARCHAR"
        },

        {.prepid = PREPSQL_SESSIONKEY_GETSEED_AUTH,
         .name = "eurephia_sessionkey_getseed_auth", .numargs = 1,
         .sql =
         "SELECT sessionkey "
         "  FROM sessionkeys "
         "  LEFT JOIN lastlog USING (sessionkey)"
         " WHERE sessionstatus IS NULL"
         "       AND sessionseed = $1::VARCHAR"
        },

        {.prepid = PREPSQL_SESSIONKEY_GETMAC,
         .name = "eurephia_sessionkey_getmac", .numargs = 1,
         .sql =
         "SELECT sessionkey "
         "  FROM sessions "
         "  JOIN lastlog USING (sessionkey)"
         " WHERE sessionstatus = 3 "
         "       AND datakey = 'macaddr'"
         "       AND dataval = $1::VARCHAR"
        },

        {.prepid = PREPSQL_SESSIONKEY_UNIQ_ADMIN,
         .name = "eurephia_sessionkey_uniq_admin", .numargs = 1,
         .sql =
         "SELECT count(sessionkey) = 0 "
         "FROM eurephia_adminlog WHERE sessionkey = $1::VARCHAR"
        },

        {.prepid = PREPSQL_SESSIONKEY_UNIQ_PLAUTH,
         .name = "eurephia_sessionkey_uniq_plauth", .numargs = 1,
         .sql =
         "SELECT count(sessionkey) = 0 "
         "FROM lastlog WHERE sessionkey = $1::VARCHAR"
        },

        {.prepid = PREPSQL_SESSIONKEY_REGISTER,
         .name = "eurephia_sessionkey_register", .numargs = 2,
         .sql =
         "INSERT INTO sessionkeys (sessionseed, sessionkey) "
         "     VALUES($1::VARCHAR, $2::VARCHAR)"
        },

        {.prepid = PREPSQL_SESSIONKEY_REMOVE,
         .name = "eurephia_sessionkey_remove", .numargs = 1,
         .sql = "DELETE FROM sessionkeys WHERE sessionkey = $1::VARCHAR"
        },

        {.prepid = PREPSQL_SESSIONVARS_LOAD,
         .name = "eurephia_sessionvars_load", .numargs = 1,
         .sql =
         "SELECT datakey, dataval"
         "  FROM sessions"
         " WHERE sessionkey = $1::VARCHAR"
        },

        {.prepid = PREPSQL_SESSIONVARS_STORE_NEW,
         .name = "eurephia_sessionvars_store_new", .numargs = 3,
         .sql =
         "INSERT INTO sessions (sessionkey, datakey, dataval) "
         "VALUES ($1::VARCHAR,$2::VARCHAR,$3::VARCHAR)"
        },

        {.prepid = PREPSQL_SESSIONVARS_STORE_UPDATE,
         .name = "eurephia_sessionvars_store_upd", .numargs = 3,
         .sql =
        "UPDATE sessions SET dataval = $3::VARCHAR "
         " WHERE sessionkey = $1::VARCHAR"
         "       AND datakey = $2::VARCHAR"
        },

        {.prepid = PREPSQL_SESSIONVARS_STORE_DELETE,
         .name = "eurephia_sessionvars_store_del", .numargs = 2,
         .sql =
         "DELETE FROM sessions "
         " WHERE sessionkey = $1::VARCHAR"
         "       AND datakey = $2::VARCHAR"
        },

        {.prepid = PREPSQL_SESSIONS_DESTROY_LASTLOG,
         .name = "eurephia_sessions_destr_lastlogupd", .numargs = 1,
         .sql =
         "UPDATE lastlog_update "
         "   SET sessionstatus = 4,"
         "       session_deleted = CURRENT_TIMESTAMP "
         " WHERE sessionkey = $1::VARCHAR"
         "       AND sessionstatus = 3"
        },

        {.prepid = PREPSQL_SESSIONS_DESTROY_SESS,
         .name = "eurephia_sessions_destr_sessions", .numargs = 1,
         .sql = "DELETE FROM sessions WHERE sessionkey = $1::VARCHAR"
        },

        {.prepid = PREPSQL_REGISTER_LOGIN,
         .name = "eurephia_register_login", .numargs = 8,
         .sql = "INSERT INTO lastlog (uid, certid, "
         "                     protocol, remotehost, remoteport,"
         "                     vpnipaddr, vpnipmask,"
         "                     sessionstatus, sessionkey, login) "
         "VALUES ($1::INTEGER, $2::INTEGER, "
         "        $3::VARCHAR, $4::VARCHAR, $5::INTEGER,"
         "        $6::VARCHAR, $7::VARCHAR,"
         "        1, $8::VARCHAR, CURRENT_TIMESTAMP)"
        },

        {.prepid = PREPSQL_REGISTER_LOGOUT,
         .name = "eurephia_register_logout", .numargs = 4,
         .sql =
         "UPDATE lastlog_update "
         "   SET sessionstatus = 3, logout = CURRENT_TIMESTAMP, "
         "       bytes_sent=$2::INTEGER, bytes_received=$3::INTEGER,"
         "       session_duration = $4::INTERVAL "
         " WHERE sessionkey = $1::VARCHAR AND sessionstatus = 2"
        },

        {.prepid = PREPSQL_MACHISTORY_REGISTER,
         .name = "eurephia_machistory_reg", .numargs = 2,
         .sql =
         "INSERT INTO macaddr_history (sessionkey, macaddr)"
         "     VALUES ($1::VARCHAR, $2::VARCHAR)"
        },

        {.prepid = PREPSQL_MACHISTORY_LASTLOG,
         .name = "eurephia_machistory_lastlog_upd", .numargs = 2,
         .sql =
         "UPDATE lastlog_update "
         "   SET sessionstatus = 2,macaddr = $2::VARCHAR"
         " WHERE sessionkey = $1::VARCHAR AND sessionstatus = 1"
        },

        {.prepid = PREPSQL_FIREWALL_GETPROFILE,
         .name = "eurephia_firewall_getprofile", .numargs = 0,
         .sql =
         "SELECT fw_profile "
         "  FROM lastlog "
         "  JOIN usercerts USING(certid, uid)"
         "  JOIN accesses USING(accessprofile)"
         " WHERE sessionkey = $1::VARCHAR"
        },

        {.prepid = PREPSQL_NONE,
         .name = NULL, .numargs = 0, .sql = ""}
};


static const _ePGprepStatements_t _ePGprepStatementsAdmin[] = {
        {.prepid = PREPSQL_NONE,
         .name = NULL, .numargs = 0, .sql = ""}
};

static int _ePGprepStatement(eurephiaCTX *ctx, const _ePGprepStatements_t *prepst)
{
        PGresult *dbr = NULL;

        /* statement found in lookup table, but not prepared in database */
        dbr = PQprepare(ctx->dbc->dbhandle,
                        prepst->name, prepst->sql, prepst->numargs, NULL);
        if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) {
                ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, prepst->prepid,
                                "Failed to prepare SQL query: %s", prepst->sql);
                return 0;
        }
        PQclear(dbr);
        return 1;
};


/**
 *  Retrieves a particular prepared statement, based on the context type
 *
 *  @param eurephiaCTX   eurephia context which contains the context type
 *  @param ePG_prepID    The prepared statement which is requested
 *
 *  @return Returns a pointer to the statement record if it is found, otherwise NULL.
 */
const _ePGprepStatements_t * ePGprepGetStatement(eurephiaCTX *ctx, ePG_prepID prepid)
{
        const _ePGprepStatements_t * prep = NULL;

        switch( ctx->context_type ) {
        case ECTX_PLUGIN_AUTH:
                prep = &_ePGprepStatementsPlugin[0];
                break;

        case ECTX_ADMIN_CONSOLE:
        case ECTX_ADMIN_WEB:
                prep = &_ePGprepStatementsAdmin[0];
                break;

        default:
                eurephia_log(ctx, LOG_FATAL, 0, "ePGprepLoadStatements: Invalid context type");
                return NULL;
        }

        for( ; prep->prepid != PREPSQL_NONE; prep++ ) {
                if( prep->prepid == prepid ) {
                        return prep;
                }
        }
        return NULL;
}


const char const * ePGprepStatementGetName(eurephiaCTX *ctx, ePG_prepID prepid)
{
        const _ePGprepStatements_t * prep = NULL;

        if( prepid == PREPSQL_NONE ) {
                return NULL;
        }

        if( (prep = ePGprepGetStatement(ctx, prepid)) == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0,
                             "Failed to map prepared statement ID %i to "
                             "a valid statement", prepid);
                return NULL;
        }
        return prep->name;
}


/**
 *  Load the provided statements.
 *
 *  @param eurephiaCTX  Pointer to the eurephia context with the database connection
 *  @param _ePGprepStatements_t Pointer to the prepared statements to be loaded
 *
 *  @return Returns 1 on success, otherwise 0.
 */

static int _ePGprepDoStatementLoad(eurephiaCTX *ctx, const _ePGprepStatements_t *prep) {
        const _ePGprepStatements_t * ptr = NULL;

        ptr = prep;
        do {
                eurephia_log(ctx, LOG_INFO, 3, "Preparing SQL query '%s'", ptr->name);
                DEBUG(ctx, 22, "SQL Query: %s", ptr->sql);
                if( !_ePGprepStatement(ctx, ptr) ) {
                        return 0;
                }
                ptr++;
        } while (ptr->prepid != PREPSQL_NONE);
        return 1;
}


/**
 * Loads and registers all the prepared statements
 * for the current eurephia context
 *
 * @param ctx eurephiaCTX
 *
 * @return Returns 1 on success, otherwise 0.
 */
int ePGprepLoadStatements(eurephiaCTX *ctx)
{
        int ret = 0;

        switch( ctx->context_type ) {
        case ECTX_PLUGIN_AUTH:
                ret = _ePGprepDoStatementLoad(ctx, _ePGprepStatementsPlugin);
                break;

        case ECTX_ADMIN_CONSOLE:
        case ECTX_ADMIN_WEB:
                ret = _ePGprepDoStatementLoad(ctx, _ePGprepStatementsAdmin);

        default:
                eurephia_log(ctx, LOG_FATAL, 0, "ePGprepLoadStatements: Invalid context type");
                ret = 0;
        }
        return ret;
}


ePGprepParams * ePGprepParamsAlloc(eurephiaCTX *ctx, ePG_prepID prepid)
{
        ePGprepParams *ret = NULL;
        const _ePGprepStatements_t * prep = NULL;

        if( (prep = ePGprepGetStatement(ctx, prepid)) == NULL ) {
                eurephia_log(ctx, LOG_FATAL, 0,
                             "Invalid prepared statement ID %i", prepid);
                return NULL;
        }

        ret = malloc_nullsafe(ctx, sizeof(ePGprepParams));
        if( !ret ) {
                return NULL;
        }

        ret->prepid = prepid;
        ret->index = 0;
        if( prep->numargs > 0 ) {
                ret->params = calloc(prep->numargs, sizeof(char *));
                if( !ret->params ) {
                        eurephia_log(ctx, LOG_FATAL, 0,
                                     "Failed to allocate memory for SQL parameters (%s, %i args)",
                                     prep->name, prep->numargs);
                        free_nullsafe(ctx, ret);
                }
        }
        return ret;
}


int ePGprepParamsAddArgument(eurephiaCTX *ctx, ePGprepParams *prms, const char *arg)
{
        const _ePGprepStatements_t * prep = NULL;

        if( !prms ) {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "Not a valid parameter buffer");
                return 0;
        }

        // TODO: Should a separate error indicator be set in ePGprepParams, to block further invocations?

        if( (prep = ePGprepGetStatement(ctx, prms->prepid)) == NULL ) {
                eurephia_log(ctx, LOG_FATAL, 0,
                             "Invalid prepared statement ID %i", prms->prepid);
                return 0;
        }

        if( prms->index+1 > prep->numargs ) {
                eurephia_log(ctx, LOG_FATAL, 0,
                             "Too many arguments provided to %s",
                             prep->name);
                return 0;
        }
        prms->params[prms->index] = strdup_nullsafe(arg);
        prms->index++;

        return 1;
}


int ePGprepParamsAddArgumentInt(eurephiaCTX *ctx, ePGprepParams *prms, const int arg) {
        char argstr[66];

        memset(&argstr, 0, 66);
        snprintf(argstr, 64, "%i", arg);
        return ePGprepParamsAddArgument(ctx, prms, argstr);
}


PGresult * ePGprepExec(eurephiaCTX *ctx, ePGprepParams *qry_args)
{
        int i = 0;
        PGresult *res = NULL;
        const _ePGprepStatements_t * prep = NULL;

        if( !qry_args ) {
                eurephia_log(ctx, LOG_FATAL, 0,
                             "Missing query arguments (cannot be NULL)");
        }

        if( (prep = ePGprepGetStatement(ctx, qry_args->prepid)) == NULL ) {
                eurephia_log(ctx, LOG_FATAL, 0,
                             "Invalid prepared statement ID %i", qry_args->prepid);
                return NULL;
        }

        if( qry_args->index != prep->numargs ) {
                eurephia_log(ctx, LOG_FATAL, 0,
                             "Invalid number of arguments registered to %s query",
                             prep->name);
                return NULL;
        }

        // TODO: Should a separate error indicator in ePGprepParams block further invocations?

        res = PQexecPrepared(ctx->dbc->dbhandle,
                             prep->name,
                             prep->numargs,
                             ((qry_args && (qry_args->index > 0)) ? (const char **) qry_args->params : NULL),
                              NULL, NULL, 0);

        // Free the memory used by the query arguments (ePGprepParams)
        for( i = 0; i < qry_args->index; i++ ) {
                free_nullsafe(ctx, qry_args->params[i]);
        }
        free_nullsafe(ctx, qry_args->params);
        qry_args->index = -1;
        free_nullsafe(ctx, qry_args);

        return res;
}