diff options
| -rw-r--r-- | database/postgresql/edb-pgsql.c | 1386 |
1 files changed, 1043 insertions, 343 deletions
diff --git a/database/postgresql/edb-pgsql.c b/database/postgresql/edb-pgsql.c index e1330b5..eabe4fe 100644 --- a/database/postgresql/edb-pgsql.c +++ b/database/postgresql/edb-pgsql.c @@ -1,6 +1,6 @@ /* edb-pgsql.c -- PostgreSQL database driver for eurephia * - * GPLv2 only - Copyright (C) 2011 + * GPLv2 only - Copyright (C) 2011, 2012 * David Sommerseth <dazo@users.sourceforge.net> * * This program is free software; you can redistribute it and/or @@ -49,29 +49,155 @@ #include <eurephiadb_session_struct.h> #include <passwd.h> +typedef enum { PREPSQL_NONE = 0, PREPSQL_TLS_AUTH, PREPSQL_USERPWD_AUTH, + PREPSQL_BLACKLIST_ATTEMPTUPD, PREPSQL_USERS_LASTACC_UPD, + PREPSQL_USERS_GETUID, PREPSQL_ATTEMPTS_RESET, PREPSQL_ATTEMPTS_INCR, + PREPSQL_ATTEMPTS_REG_CERT, PREPSQL_ATTEMPTS_REG_USERNAME, + PREPSQL_ATTEMPTS_REG_IPADDR, + PREPSQL_BLACKLIST_CHECK_CERT, PREPSQL_BLACKLIST_CHECK_USERNAME, + PREPSQL_BLACKLIST_CHECK_IPADDR, + PREPSQL_BLACKLIST_REG_CERT, PREPSQL_BLACKLIST_REG_USERNAME, + PREPSQL_BLACKLIST_REG_IPADDR, + PREPSQL_SESSIONKEY_GETSEED_SESSION, PREPSQL_SESSIONKEY_GETSEED_AUTH, + PREPSQL_SESSIONKEY_GETMAC, + PREPSQL_SESSIONKEY_UNIQ_ADMIN, PREPSQL_SESSIONKEY_UNIQ_PLAUTH, + PREPSQL_SESSIONKEY_REGISTER, PREPSQL_SESSIONKEY_REMOVE, + PREPSQL_SESSIONVARS_LOAD, PREPSQL_SESSIONVARS_STORE_NEW, + PREPSQL_SESSIONVARS_STORE_UPDATE, PREPSQL_SESSIONVARS_STORE_DELETE, + PREPSQL_SESSIONS_DESTROY_LASTLOG, PREPSQL_SESSIONS_DESTROY_SESS, + PREPSQL_REGISTER_LOGIN, PREPSQL_REGISTER_LOGOUT, + PREPSQL_MACHISTORY_REGISTER, PREPSQL_MACHISTORY_LASTLOG, + PREPSQL_FIREWALL_GETPROFILE +} _ePG_prepID; + +typedef struct { + _ePG_prepID prepid; + const char *name; + unsigned int numargs; + unsigned int prepared; +} _ePGprepStatements_t; + +static _ePGprepStatements_t _ePGprepStatements[] = { + {.prepid = PREPSQL_TLS_AUTH, + .name = "eurephia_tls_auth", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_USERPWD_AUTH, + .name = "eurephia_userpwd_auth", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_BLACKLIST_ATTEMPTUPD, + .name ="eurephia_blacklist_attupd" , .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_USERS_LASTACC_UPD, + .name = "eurephia_users_lastacc_upd", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_USERS_GETUID, + .name = "eurephia_getuid", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_ATTEMPTS_RESET, + .name = "eurephia_attempts_reset", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_ATTEMPTS_INCR, + .name = "eurephia_attempts_increment", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_ATTEMPTS_REG_CERT, + .name = "eurephia_attempts_reg_cert", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_ATTEMPTS_REG_USERNAME, + .name = "eurephia_attempts_reg_username", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_ATTEMPTS_REG_IPADDR, + .name = "eurephia_attempts_reg_ipaddr", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_BLACKLIST_CHECK_CERT, + .name = "eurephia_blacklist_check_cert", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_BLACKLIST_CHECK_USERNAME, + .name = "eurephia_blacklist_check_username", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_BLACKLIST_CHECK_IPADDR, + .name = "eurephia_blacklist_check_cert_ipaddr", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_BLACKLIST_REG_CERT, + .name = "eurephia_blacklist_reg_cert", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_BLACKLIST_REG_USERNAME, + .name = "eurephia_blacklist_reg_username", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_BLACKLIST_REG_IPADDR, + .name = "eurephia_blacklist_reg_ipaddr", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_SESSIONKEY_GETSEED_SESSION, + .name = "eurephia_sessionkey_getseed_session", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_SESSIONKEY_GETSEED_AUTH, + .name = "eurephia_sessionkey_getseed_auth", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_SESSIONKEY_GETMAC, + .name = "eurephia_sessionkey_getmac", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_SESSIONKEY_UNIQ_ADMIN, + .name = "eurephia_sessionkey_uniq_admin", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_SESSIONKEY_UNIQ_PLAUTH, + .name = "eurephia_sessionkey_uniq_plauth", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_SESSIONKEY_REGISTER, + .name = "eurephia_sessionkey_register", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_SESSIONKEY_REMOVE, + .name = "eurephia_sessionkey_remove", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_SESSIONVARS_LOAD, + .name = "eurephia_sessionvars_load", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_SESSIONVARS_STORE_NEW, + .name = "eurephia_sessionvars_store_new", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_SESSIONVARS_STORE_UPDATE, + .name = "eurephia_sessionvars_store_upd", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_SESSIONVARS_STORE_DELETE, + .name = "eurephia_sessionvars_store_del", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_SESSIONS_DESTROY_LASTLOG, + .name = "eurephia_sessions_destr_lastlogupd", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_SESSIONS_DESTROY_SESS, + .name = "eurephia_sessions_destr_sessions", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_REGISTER_LOGIN, + .name = "eurephia_register_login", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_REGISTER_LOGOUT, + .name = "eurephia_register_logout", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_MACHISTORY_REGISTER, + .name = "eurephia_machistory_reg", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_MACHISTORY_LASTLOG, + .name = "eurephia_machistory_lastlog_upd", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_FIREWALL_GETPROFILE, + .name = "eurephia_firewall_getprofile", .numargs = 0, .prepared = 0}, + {.prepid = PREPSQL_NONE, + .name = NULL, .numargs = 0, .prepared = 0}, +}; + +/* FIXME: Go through the code and place all ePGprepStatement() in a function called from eDBconnect() + * Let the SQL be declared in _ePGprepStatements_t ... the prep-function will just loop _ePGprepStatements[] + */ + /** * Mapping struct - maps attempt types (attempt_IPADDR, attempt_CERTIFICATE, attempt_USERNAME) * to database field names, configuration options (with default values) and description */ typedef struct { - char *colname; /**< Field when doing look up in blacklist and attempts tables */ - char *colname_where; /**< Field when using column name in WHERE section of SQL queries */ - char *allow_cfg; /**< Configure parameter for the attempt limits */ - char *descr; /**< Description, used to give more readable output for users */ - char *default_value; /**< Default value, if config option is not found */ - char *value_func; /**< If not NULL, the value will be passed through the given SQL function */ + char *colname; /** Field when doing look up in blacklist and attempts tables */ + char *colname_where; /** Field when using column name in WHERE section of SQL queries */ + char *allow_cfg; /** Configure parameter for the attempt limits */ + char *descr; /** Description, used to give more readable output for users */ + char *default_value; /** Default value, if config option is not found */ + char *value_func; /** If not NULL, value will be passed through given SQL function */ + _ePG_prepID sql_blacklist_check; /** Prepared SQL ID reference for blacklist check queries */ + _ePG_prepID sql_blacklist_reg; /** Prepared SQL ID reference for blacklist register queries */ + _ePG_prepID sql_attempts_reg; /** Prepared SQL ID reference for attempts register queries */ } eDBattempt_types_t; - + /** * Static mapping table with the needed values. Uses the eDBattempt_types_t struct. */ static const eDBattempt_types_t eDBattempt_types[] = { - {NULL, NULL, NULL, NULL}, - {"remoteip\0", "remoteip\0", "allow_ipaddr_attempts\0", "IP Address\0", "10\0", NULL}, - {"digest\0", "lower(digest)\0", "allow_cert_attempts\0", "Certificate\0", "5\0", "lower\0"}, - {"username\0", "username\0", "allow_username_attempts\0", "Username\0", "5\0", NULL}, - {NULL, NULL, NULL, NULL} + {NULL}, + {.colname = "remoteip", .colname_where = "remoteip", + .allow_cfg = "allow_ipaddr_attempts", .descr = "IP Address", + .default_value = "10", .value_func = NULL, + .sql_blacklist_check = PREPSQL_BLACKLIST_CHECK_IPADDR, + .sql_blacklist_reg = PREPSQL_BLACKLIST_REG_IPADDR, + .sql_attempts_reg = PREPSQL_ATTEMPTS_REG_IPADDR + }, + {.colname = "digest", .colname_where = "lower(digest)", + .allow_cfg = "allow_cert_attempts", .descr = "Certificate", + .default_value = "5", .value_func = "lower", + .sql_blacklist_check = PREPSQL_BLACKLIST_CHECK_CERT, + .sql_blacklist_reg = PREPSQL_BLACKLIST_REG_CERT, + .sql_attempts_reg = PREPSQL_ATTEMPTS_REG_CERT + }, + {.colname = "username", .colname_where = "username", + .allow_cfg = "allow_username_attempts", .descr = "Username", + .default_value = "3", .value_func = NULL, + .sql_blacklist_check = PREPSQL_BLACKLIST_CHECK_USERNAME, + .sql_blacklist_reg = PREPSQL_BLACKLIST_REG_USERNAME, + .sql_attempts_reg = PREPSQL_ATTEMPTS_REG_USERNAME + }, + {NULL} }; @@ -84,43 +210,122 @@ static inline char *ePGgetValue(PGresult *res, int row, int col) { } -static void ePGerrorMessage(eurephiaCTX *ctx, PGresult *dbr, int logdst, int loglvl, - const char *prepsql, const char *fmt, ...) +int ePGprepStatementGetID(_ePG_prepID prepid) +{ + int i; + for( i = 0; _ePGprepStatements[i].prepid != PREPSQL_NONE; i++ ) { + if( _ePGprepStatements[i].prepid == prepid ) { + return i; + } + } + return -1; +} + + +#define ePGerrorMessage(ctx, dbr, logdst, loglvl, prepid, fmt...) __ePGerrorMessage(ctx, dbr, logdst,loglvl, prepid, __FILE__, __LINE__, ## fmt) +static void __ePGerrorMessage(eurephiaCTX *ctx, PGresult *dbr, int logdst, int loglvl, + _ePG_prepID prepid, const char *errfile, const long errline, + const char *fmt, ...) { char msgfmt[514]; va_list ap; - + int id = -1; + + if( prepid != PREPSQL_NONE ) { + id = ePGprepStatementGetID(prepid); + if( id < 0 ) { + eurephia_log(ctx, LOG_ERROR, 0, + "Failed to map prepared statement ID %i to " + "a valid statement", prepid); + return; + } + } memset(&msgfmt, 0, 514); va_start(ap, fmt); - snprintf(msgfmt, 512, "[%s] SQL query failed: %s: %s", - prepsql, fmt, - (dbr != NULL ? PQresultErrorMessage(dbr) - : (ctx->dbc != NULL ? PQerrorMessage(ctx->dbc->dbhandle) : "[unknown error]") - )); + if( prepid != PREPSQL_NONE ) { + snprintf(msgfmt, 512, "[%s] SQL query failed: %s %s: %s", + _ePGprepStatements[id].name, + (dbr != NULL ? PQresStatus(PQresultStatus(dbr)) : ""), fmt, + (dbr != NULL ? PQresultErrorMessage(dbr) + : (ctx->dbc != NULL ? PQerrorMessage(ctx->dbc->dbhandle) + : "[unknown error]") + )); + } else { + snprintf(msgfmt, 512, "SQL query failed: %s %s: %s", + (dbr != NULL ? PQresStatus(PQresultStatus(dbr)) : ""), + fmt, + (dbr != NULL ? PQresultErrorMessage(dbr) + : (ctx->dbc != NULL ? PQerrorMessage(ctx->dbc->dbhandle) + : "[unknown error]") + )); + } +#ifdef ENABLE_DEBUG + _veurephia_log_func(ctx, logdst, loglvl, errfile, errline, ap, msgfmt); +#else veurephia_log(ctx, logdst, loglvl, ap, msgfmt); +#endif va_end(ap); memset(&msgfmt, 0, 514); - PQclear(dbr); - if( (loglvl == LOG_EMERG) && (ctx->dbc != NULL) ) { + if( dbr ) { + PQclear(dbr); + } + if( (loglvl == LOG_EMERG) && (ctx->dbc != NULL) + && (PQstatus(ctx->dbc->dbhandle) != CONNECTION_OK) ) { PQfinish(ctx->dbc->dbhandle); } } -#define PREPSQL_TLS_AUTH "eurephia_tls_auth" -#define PREPSQL_USERPWD_AUTH "eurephia_userpwd_auth" -#define PREPSQL_BLACKLIST_ATTEMPTUPD "eurephia_blacklist_attupd" -#define PREPSQL_USERS_LASTACC_UPD "eurephia_users_lastacc_upd" -#define PREPSQL_USERS_GETUID "eurephia_getuid" -#define PREPSQL_BLACKLIST_CHECK "eurephia_blacklist_check" -#define PREPSQL_ATTEMPTS_CHECK "eurephia_attempts_check" -#define PREPSQL_BLACKLIST_REGISTER "eurephia_blacklist_reg" +int ePGprepStatement(eurephiaCTX *ctx, _ePG_prepID prepid, const char *sql, int argc) +{ + int id; + + /* Check if this statement is already prepared */ + id = ePGprepStatementGetID(prepid); + if( _ePGprepStatements[id].prepid == prepid ) { + PGresult *dbr = NULL; + _ePGprepStatements_t *prep = &_ePGprepStatements[id]; + + if( prep->prepared == 1 ) { + /* statement found, and was already prepared */ + return id; + } + + /* statement found in lookup table, but not prepared in database */ + dbr = PQprepare(ctx->dbc->dbhandle, + prep->name, sql, argc, NULL); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, prepid, + "Failed to prepare SQL query: %s", sql); + return -2; + } + PQclear(dbr); + _ePGprepStatements[id].numargs = argc; + _ePGprepStatements[id].prepared = 1; /* Flag statement as prepared */ + return id; + } + return -1; +}; + + +PGresult * ePGprepExec(eurephiaCTX *ctx, int prepid, const char **qry_args) { + if( prepid < 0 ) { + eurephia_log(ctx, LOG_ERROR, 0, + "Invalid prepared statement ID %i", prepid); + return NULL; + } + return PQexecPrepared(ctx->dbc->dbhandle, + _ePGprepStatements[prepid].name, + _ePGprepStatements[prepid].numargs, + qry_args, NULL, NULL, 0); +} + /** * @copydoc eDB_DriverVersion() */ const char *eDB_DriverVersion(void) { - return "edb-pgsql (v"DRIVERVERSION") David Sommerseth 2011 (C) GPLv2"; + return "edb-pgsql (v"DRIVERVERSION") David Sommerseth 2012 (C) GPLv2"; } @@ -132,7 +337,6 @@ int eDB_DriverAPIVersion() { } - /** * Internal driver function for simplifying update of the blacklist table. It will simply just * update the 'last_accessed' field in the blacklist table. @@ -143,23 +347,23 @@ int eDB_DriverAPIVersion() { static inline void update_blacklist_attempt(eurephiaCTX *ctx, const char *blid) { if( blid != NULL ) { PGresult *dbr = NULL; + int prepid = -1; const char ** qry_args = NULL; - dbr = PQprepare(ctx->dbc->dbhandle, PREPSQL_BLACKLIST_ATTEMPTUPD, - "UPDATE blacklist " - " SET last_accessed = CURRENT_TIMESTAMP WHERE blid = $1", 1, NULL); - if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + prepid = ePGprepStatement(ctx, PREPSQL_BLACKLIST_ATTEMPTUPD, + "UPDATE blacklist" + " SET last_accessed = CURRENT_TIMESTAMP" + " WHERE blid = $1", 1); + if( prepid < 0 ) { ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, PREPSQL_BLACKLIST_ATTEMPTUPD, "Preparing last attempt query update failed"); return; } - PQclear(dbr); qry_args = calloc(1, sizeof(char *)); qry_args[0] = blid; - dbr = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_BLACKLIST_ATTEMPTUPD, 1, - qry_args, NULL, NULL, 0); + dbr = ePGprepExec(ctx, prepid, qry_args); free_nullsafe(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, PREPSQL_BLACKLIST_ATTEMPTUPD, @@ -172,7 +376,6 @@ static inline void update_blacklist_attempt(eurephiaCTX *ctx, const char *blid) } - /** * @copydoc eDBconnect() * Connect to the database ... connection is stored in the eurephiaCTX context @@ -223,8 +426,8 @@ int eDBconnect(eurephiaCTX *ctx, const int argc, const char **argv) } if( PQstatus(dbc->dbhandle) != CONNECTION_OK ) { - ePGerrorMessage(ctx, NULL, LOG_EMERG, 0, "DB-INIT", - "Failed to connect to the PostgreSQL database '%s'", + ePGerrorMessage(ctx, NULL, LOG_EMERG, 0, PREPSQL_NONE, + "[DB-INIT] Failed to connect to the PostgreSQL database '%s'", dbname); ctx->dbc = NULL; free_nullsafe(ctx, dbc); @@ -254,8 +457,8 @@ int eDBconnect(eurephiaCTX *ctx, const int argc, const char **argv) dbc->config = cfg; PQclear(res); } else { - ePGerrorMessage(ctx, res, LOG_EMERG, 0, "DB-INIT", - "Failed to the configuration from the database"); + ePGerrorMessage(ctx, res, LOG_EMERG, 0, PREPSQL_NONE, + "[DB-INIT] Failed to the configuration from the database"); eFree_values(ctx, dbc->dbparams); free_nullsafe(ctx, dbc); return 0; @@ -264,6 +467,9 @@ int eDBconnect(eurephiaCTX *ctx, const int argc, const char **argv) } +/** + * @copydoc eDBdisconnect() + */ void eDBdisconnect(eurephiaCTX *ctx) { eDBconn *dbc = NULL; @@ -292,12 +498,27 @@ void eDBdisconnect(eurephiaCTX *ctx) /** + * @copydoc eDBdisconnect_firewall() + */ +void eDBdisconnect_firewall(eurephiaCTX *ctx) { + DEBUG(ctx, 20, "Function call: eDBdisconnect_firewall(ctx)"); + DEBUG(ctx, 21, "Closing PostgreSQL file descriptor (%ld) for firewall thread", + PQsocket(ctx->dbc->dbhandle)); + /* Is there a better way how to avoid the firewall thread to close the inherited + * PostgreSQL connection? ... this is pretty brute force, but seems to work + */ + close(PQsocket(ctx->dbc->dbhandle)); + ctx->dbc->dbhandle = NULL; +} + + +/** * @copydoc eDBauth_TLS() */ int eDBauth_TLS(eurephiaCTX *ctx, const char *org, const char *cname, const char *email, const char *digest, const unsigned int depth) { - int certid = 0; + int certid = 0, prepid = -1; PGresult *dbr = NULL; char *blid = NULL; char depth_str[5]; @@ -311,19 +532,20 @@ int eDBauth_TLS(eurephiaCTX *ctx, const char *org, const char *cname, const char return 0; } - dbr = PQprepare(ctx->dbc->dbhandle, PREPSQL_TLS_AUTH, - "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::varchar " - " AND lower(cert.digest)=lower($5::varchar)\0", - 5, NULL); - if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { - ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_TLS_AUTH, "Failed to prepare SQL query"); + prepid = ePGprepStatement(ctx, PREPSQL_TLS_AUTH, + "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)\0", + 5); + if( prepid < 0 ) { + ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_TLS_AUTH, + "Failed to prepare SQL query"); return 0; } - PQclear(dbr); // Check if certificate is valid, and not too many attempts has been tried with // the given certificate @@ -334,7 +556,7 @@ int eDBauth_TLS(eurephiaCTX *ctx, const char *org, const char *cname, const char qry_args[2] = email; qry_args[3] = depth_str; qry_args[4] = digest; - dbr = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_TLS_AUTH, 5, qry_args, NULL, NULL, 0); + dbr = ePGprepExec(ctx, prepid, qry_args); free_nullsafe(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_TLS_AUTH, @@ -369,7 +591,7 @@ int eDBauth_TLS(eurephiaCTX *ctx, const char *org, const char *cname, const char */ int eDBauth_user(eurephiaCTX *ctx, const int certid, const char *username, const char *passwd) { - int uicid = 0, pwdok = 0; + int uicid = 0, pwdok = 0, prepid = -1; PGresult *dbr = NULL; char certid_str[10]; char *crpwd = NULL, *activated = NULL, *deactivated = NULL; @@ -384,35 +606,37 @@ int eDBauth_user(eurephiaCTX *ctx, const int certid, const char *username, const return 0; } - dbr = PQprepare(ctx->dbc->dbhandle, PREPSQL_USERPWD_AUTH, - "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", 2, NULL); - if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + prepid = ePGprepStatement(ctx, PREPSQL_USERPWD_AUTH, + "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", 2); + if( prepid < 0 ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_USERPWD_AUTH, "Failed to prepare SQL query"); uicid = 0; goto exit; } - PQclear(dbr); - // Look up the user in the database snprintf(certid_str, 9, "%8i%c", certid, 0); qry_args = calloc(2, sizeof(char *)); qry_args[0] = certid_str; qry_args[1] = username; - dbr = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_USERPWD_AUTH, 2, qry_args, NULL, NULL, 0); + dbr = ePGprepExec(ctx, prepid, qry_args); free_nullsafe(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { - ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, - "Failed to lookup user account information [username=%s, certid=%s]", - username, certid_str); + ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_NONE, + "Failed to lookup user account information " + "[username=%s, certid=%s]", + username, certid_str); uicid = 0; goto exit; } @@ -470,30 +694,33 @@ int eDBauth_user(eurephiaCTX *ctx, const int certid, const char *username, const uicid = -1; } else { PGresult *upd = NULL; + int prepid = -1; uicid = atoi_nullsafe(ePGgetValue(dbr, 0, 0)); // Update last accessed status - upd = PQprepare(ctx->dbc->dbhandle, PREPSQL_USERS_LASTACC_UPD, - "UPDATE users SET last_accessed = CURRENT_TIMESTAMP" - " WHERE uid = $1::INTEGER", 1, NULL); - if( !dbr || (PQresultStatus(upd) != PGRES_COMMAND_OK) ) { - ePGerrorMessage(ctx, upd, LOG_FATAL, 0, PREPSQL_USERS_LASTACC_UPD, - "Failed to prepare SQL query"); - } else if( dbr ) { + prepid = ePGprepStatement(ctx, PREPSQL_USERS_LASTACC_UPD, + "UPDATE users_last_access SET last_accessed = NULL" + " WHERE uid = $1::INTEGER", 1); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_USERS_LASTACC_UPD, + "Failed to prepare SQL query"); + } else { PQclear(upd); qry_args = calloc(1, sizeof(char *)); qry_args[0] = uid; - upd = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_USERS_LASTACC_UPD, - 1, qry_args, NULL, NULL, 0); + upd = ePGprepExec(ctx, prepid, qry_args); free_nullsafe(ctx, qry_args); - if( !upd || (PQresultStatus(upd) != PGRES_TUPLES_OK) ) { - ePGerrorMessage(ctx, upd, LOG_FATAL, 0, PREPSQL_USERS_LASTACC_UPD - "Failed to update last access status for uid %s", uid); - } - if( upd ) { - PQclear(upd); + if( !upd || (PQresultStatus(upd) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, upd, LOG_FATAL, 0, + PREPSQL_USERS_LASTACC_UPD, + "Failed to update last access status " + "for uid %s", uid); + } else { + if( upd ) { + PQclear(upd); + } } } } @@ -514,7 +741,7 @@ int eDBauth_user(eurephiaCTX *ctx, const int certid, const char *username, const */ int eDBget_uid(eurephiaCTX *ctx, const int certid, const char *username) { - int ret = 0; + int ret = 0, prepid = -1; PGresult *dbr = NULL; const char **qry_args = NULL; char certid_str[10]; @@ -526,34 +753,33 @@ int eDBget_uid(eurephiaCTX *ctx, const int certid, const char *username) return 0; } - dbr = PQprepare(ctx->dbc->dbhandle, PREPSQL_USERS_GETUID, - "SELECT uid " - " FROM usercerts " - " JOIN users USING (uid) " - " WHERE certid = $1::INTEGER AND username = $1::VARCHAR", - 2, NULL); - if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + prepid = ePGprepStatement(ctx, PREPSQL_USERS_GETUID, + "SELECT uid " + " FROM usercerts " + " JOIN users USING (uid) " + " WHERE certid = $1::INTEGER AND username = $2::VARCHAR", + 2); + if( prepid < 0 ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_USERS_GETUID, - "Failed to prepare SQL query"); + "Failed to prepare SQL query"); ret = -1; goto exit; } - PQclear(dbr); snprintf(certid_str, 9, "%8i%c", certid, 0); qry_args = calloc(2, sizeof(char *)); qry_args[0] = certid_str; qry_args[1] = username; - dbr = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_USERPWD_AUTH, 2, qry_args, NULL, NULL, 0); + dbr = ePGprepExec(ctx, prepid, qry_args); free_nullsafe(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { - ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_USERPWD_AUTH, - "Failed to lookup userid for user '%s'", username); + ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_USERS_GETUID, + "Failed to lookup userid for user '%s'", username); ret = -1; goto exit; } - switch( PQntuples(dbr) > 1 ) { + switch(PQntuples(dbr)) { case 0: eurephia_log(ctx, LOG_WARNING,0, "Failed to find userid for certid %i and username '%s'", certid, username); @@ -584,135 +810,102 @@ int eDBget_uid(eurephiaCTX *ctx, const int certid, const char *username) */ int eDBblacklist_check(eurephiaCTX *ctx, const int type, const char *val) { - int blacklisted = 0; + int blacklisted = 0, prepid = -1; char sql[4098], *blid = NULL; const char **qry_args = NULL; PGresult *dbr = NULL; + const eDBattempt_types_t *atmptype = &eDBattempt_types[type]; DEBUG(ctx, 20, "Function call: eDBblacklist_check(ctx, '%s', '%s')", - eDBattempt_types[type].descr, val); + atmptype->descr, val); - qry_args = calloc(1, sizeof(char *)); - qry_args[0] = val; + qry_args = calloc(1, sizeof(char *)); + qry_args[0] = val; snprintf(sql, 4096, - "SELECT blid" - " FROM blacklist" - " WHERE %s = %s%s$1%s", - eDBattempt_types[type].colname_where, - defaultValue(eDBattempt_types[type].value_func, ""), - (eDBattempt_types[type].value_func ? "(" : ""), - (eDBattempt_types[type].value_func ? ")" : "")); - - dbr = PQprepare(ctx->dbc->dbhandle, PREPSQL_BLACKLIST_CHECK, sql, 1, NULL); - if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { - ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_BLACKLIST_CHECK, + "SELECT atpid, attempts >= %s, blid, attempts " + " FROM attempts " + " LEFT JOIN blacklist USING(%s)" + " WHERE %s = %s%s$1::VARCHAR%s", + defaultValue(eGet_value(ctx->dbc->config, atmptype->allow_cfg), + atmptype->default_value), + atmptype->colname, atmptype->colname_where, + defaultValue(atmptype->value_func, ""), + (atmptype->value_func ? "(" : ""), + (atmptype->value_func ? ")" : "")); + + prepid = ePGprepStatement(ctx, atmptype->sql_blacklist_check, sql, 1); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, atmptype->sql_blacklist_check, "Failed to prepare SQL query"); blacklisted = -1; - goto exit; + goto error; } - PQclear(dbr); - - dbr = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_BLACKLIST_CHECK, 2, qry_args, NULL, NULL, 0); + + dbr = ePGprepExec(ctx, prepid, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { - ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_BLACKLIST_CHECK, - "Failed to lookup %s in the blacklist for '%s'", - eDBattempt_types[type].descr, val); + ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, atmptype->sql_blacklist_check, + "Failed to lookup %s in the attempts/blacklist table for '%s'", + atmptype->descr, val); blacklisted = -1; - goto exit; + goto error; } if( dbr && PQntuples(dbr) > 0 ) { - blid = ePGgetValue(dbr, 0, 0); + char *atpid = NULL; + int atpexceed = -1, atpcount = 0; + + blid = ePGgetValue(dbr, 0, 2); if( blid != NULL ) { eurephia_log(ctx, LOG_WARNING, 0, "Attempt from blacklisted %s: %s", - eDBattempt_types[type].descr, val); + atmptype->descr, val); blacklisted = 1; // [type] is blacklisted - } - update_blacklist_attempt(ctx, blid); - PQclear(dbr); - } else { - ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_BLACKLIST_CHECK, - "Blacklist query for %s failed (%s)", - eDBattempt_types[type].descr, val); - }; - - // Check if this [type] have been tried before and consider if it should be blacklisted - if( blacklisted == 0) { - char *atpid = NULL; - int atpexceed = -1; - - snprintf(sql, 4096, - "SELECT atpid, attempts >= '%s' " - " FROM attempts " - " WHERE %s = $1", - defaultValue(eGet_value(ctx->dbc->config, eDBattempt_types[type].allow_cfg), - eDBattempt_types[type].default_value), - eDBattempt_types[type].colname_where); - dbr = PQprepare(ctx->dbc->dbhandle, PREPSQL_ATTEMPTS_CHECK, sql, 1, NULL); - if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { - ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_ATTEMPTS_CHECK, - "Failed to prepare SQL query"); - blacklisted = -1; - goto exit; - } - PQclear(dbr); - - dbr = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_BLACKLIST_CHECK, 1, qry_args, NULL, NULL, 0); - if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { - ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_BLACKLIST_CHECK, - "Failed look up attempts counter for %s in the blacklist check on '%s'", - eDBattempt_types[type].descr, val); - blacklisted = -1; + update_blacklist_attempt(ctx, blid); goto exit; } - if( dbr && (PQntuples(dbr) > 0) ) { - atpid = ePGgetValue(dbr, 0, 0); - atpexceed = atoi_nullsafe(ePGgetValue(dbr, 0, 1)); - - // If [type] has reached attempt limit and it is not black listed, black list it - if( (atpexceed > 0) && (blid == NULL) ) { - PGresult *blr = NULL; - - eurephia_log(ctx, LOG_WARNING, 0, - "%s got BLACKLISTED due to too many failed attempts: %s", - eDBattempt_types[type].descr, val); - - snprintf(sql, 4096, "INSERT INTO blacklist (%s) VALUES ($1)", - eDBattempt_types[type].colname); - blr = PQprepare(ctx->dbc->dbhandle, PREPSQL_BLACKLIST_REGISTER, sql, 1, NULL); - if( !blr || (PQresultStatus(blr) != PGRES_COMMAND_OK) ) { - ePGerrorMessage(ctx, blr, LOG_FATAL, 0, PREPSQL_BLACKLIST_REGISTER, - "Failed to prepare SQL query"); - } else { - PQclear(blr); - blr = PQexecPrepared(ctx->dbc->dbhandle, - PREPSQL_BLACKLIST_REGISTER, 1, qry_args, - NULL, NULL, 0); - if( !blr || (PQresultStatus(blr) != PGRES_COMMAND_OK) ) { - ePGerrorMessage(ctx, blr, LOG_FATAL, 0, - PREPSQL_BLACKLIST_REGISTER, - "Failed to register %s value '%s' in the " - "blacklist:", - eDBattempt_types[type].descr, val); - } + // Check if this [type] have been tried before and consider if it should be blacklisted + atpid = ePGgetValue(dbr, 0, 0); + atpexceed = atoi_nullsafe(ePGgetValue(dbr, 0, 1)); + atpcount = atoi_nullsafe(ePGgetValue(dbr, 0, 3)); + + // If [type] has reached attempt limit and it is not black listed, black list it + if( (atpexceed > 0) && (blid == NULL) ) { + PGresult *blr = NULL; + + eurephia_log(ctx, LOG_WARNING, 0, + "%s got BLACKLISTED due to too many (%i) failed attempts: %s", + atmptype->descr, atpcount, val); + + snprintf(sql, 4096, "INSERT INTO blacklist (%s) VALUES ($1)", + atmptype->colname); + prepid = ePGprepStatement(ctx, atmptype->sql_blacklist_reg, sql, 1); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, atmptype->sql_blacklist_reg, + "Failed to prepare SQL query"); + } else { + blr = ePGprepExec(ctx, prepid, qry_args); + if( !blr + || (PQresultStatus(blr) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, blr, LOG_FATAL, 0, + atmptype->sql_blacklist_reg, + "Failed to register %s value " + "'%s' in the blacklist:", + atmptype->descr, + val); } PQclear(blr); - blacklisted = 1; // [type] is blacklisted } - atpid = NULL; - PQclear(dbr); - } else { - ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_BLACKLIST_CHECK, - "Querying attempts counts for blacklisted %s failed (%s)", - eDBattempt_types[type].descr, val); + blacklisted = 1; // [type] is blacklisted } + atpid = NULL; + PQclear(dbr); } + exit: DEBUG(ctx, 20, "Result - function call: eDBblacklist_check(ctx, '%s', '%s') - %i", - eDBattempt_types[type].descr, val, blacklisted); + atmptype->descr, val, blacklisted); - exit: + error: blid = NULL; free_nullsafe(ctx, qry_args); @@ -725,26 +918,159 @@ int eDBblacklist_check(eurephiaCTX *ctx, const int type, const char *val) */ void eDBregister_attempt(eurephiaCTX *ctx, int type, int mode, const char *value) { + const eDBattempt_types_t *atmptype = &eDBattempt_types[type]; + const char **qry_args = NULL; + char sql[4098]; + char *id = NULL, *atmpt_block = NULL, *blid = NULL; + int prepid = -1, attempts = 0; + PGresult *dbr = NULL; + DEBUG(ctx, 20, "Function call: eDBregister_attempt(ctx, %s, %s, '%s')", - eDBattempt_types[type].colname, + atmptype->colname, (mode == ATTEMPT_RESET ? "ATTEMPT_RESET" : "ATTEMPT_REGISTER"), value); - /* - "SELECT atpid, attempts > %s, blid, attempts " - " FROM attempts " - " LEFT JOIN blacklist USING(%s)" - " WHERE %s = %s%s'%q'%s", - defaultValue(eGet_value(ctx->dbc->config, eDBattempt_types[type].allow_cfg), - eDBattempt_types[type].default_value), - eDBattempt_types[type].colname, - eDBattempt_types[type].colname_where, - defaultValue(eDBattempt_types[type].value_func, ""), - (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? "(" : ""), - value, - (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? ")" : "") - ); - */ + qry_args = calloc(1, sizeof(char *)); + qry_args[0] = value; + + snprintf(sql, 4096, + "SELECT atpid, attempts > %s, blid, attempts " + " FROM attempts " + " LEFT JOIN blacklist USING(%s)" + " WHERE %s = %s%s$1::VARCHAR%s", + defaultValue(eGet_value(ctx->dbc->config, atmptype->allow_cfg), + atmptype->default_value), + atmptype->colname, atmptype->colname_where, + defaultValue(atmptype->value_func, ""), + (atmptype->value_func ? "(" : ""), + (atmptype->value_func ? ")" : "")); + prepid = ePGprepStatement(ctx, atmptype->sql_blacklist_check, sql, 1); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, atmptype->sql_blacklist_check, + "Failed to prepare SQL query"); + } + + dbr = ePGprepExec(ctx, prepid, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { + ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, atmptype->sql_blacklist_check, + "Failed to lookup %s in the attempts/blacklist table for '%s'", + atmptype->descr, value); + return; + } + + attempts = atoi_nullsafe(ePGgetValue(dbr, 0, 3)); + // If we are asked to reset the attempt counter and we do not find any attempts, exit here + if( (mode == ATTEMPT_RESET) && ((PQntuples(dbr) == 0) || (attempts == 0))) { + PQclear(dbr); + return; + } + + id = strdup_nullsafe(ePGgetValue(dbr, 0, 0)); + atmpt_block = strdup_nullsafe(ePGgetValue(dbr, 0, 1)); + blid = strdup_nullsafe(ePGgetValue(dbr, 0, 2)); + PQclear(dbr); + + if( (id == NULL) && (mode == ATTEMPT_REGISTER) ) { + // Only insert record when we are in registering mode + snprintf(sql, 4096, "INSERT INTO attempts (%s, attempts) VALUES ($1::VARCHAR, 1)", + atmptype->colname); + prepid = ePGprepStatement(ctx, atmptype->sql_attempts_reg, sql, 1); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, atmptype->sql_attempts_reg, + "Failed to prepare SQL query"); + goto error; + } + + dbr = ePGprepExec(ctx, prepid, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, atmptype->sql_attempts_reg, + "Failed to register failed attempt for %s '%s'", + atmptype->descr, value); + goto error; + } + PQclear(dbr); + } else if( id != NULL ){ + // if a attempt record exists, update it according to mode + qry_args[0] = id; + switch( mode ) { + case ATTEMPT_RESET: + prepid = ePGprepStatement(ctx, PREPSQL_ATTEMPTS_RESET, + "UPDATE attempts" + " SET attempts = 0 " + " WHERE atpid = $1::INTEGER", 1); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_ATTEMPTS_RESET, + "Failed to prepare SQL query"); + goto error; + } + + dbr = ePGprepExec(ctx, prepid, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_ATTEMPTS_RESET, + "Failed to reset attempts counter for atpid '%s' (%s %s)", + id, atmptype->descr, value); + goto error; + } + PQclear(dbr); + break; + default: + prepid = ePGprepStatement(ctx, PREPSQL_ATTEMPTS_INCR, + "UPDATE attempts" + " SET last_attempt = CURRENT_TIMESTAMP, " + " attempts = attempts + 1" + " WHERE atpid = $1::INTEGER", 1); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_ATTEMPTS_INCR, + "Failed to prepare SQL query"); + goto error; + } + dbr = ePGprepExec(ctx, prepid, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_ATTEMPTS_INCR, + "Failed to increment attempts counter for atpid '%s' (%s %s)", + id, atmptype->descr, value); + goto error; + } + PQclear(dbr); + break; + } + } + + // If attempts have exceeded attempt limit, blacklist it immediately if not already registered + if( (mode == ATTEMPT_REGISTER) + && (blid == NULL) && (atmpt_block != NULL) && (atmpt_block[0] == 't') ) { + eurephia_log(ctx, LOG_WARNING, 0, "Blacklisting %s due to too many attempts: %s", + eDBattempt_types[type].descr, value); + + snprintf(sql, 4096, "INSERT INTO blacklist (%s) VALUES ($1)", + atmptype->colname); + prepid = ePGprepStatement(ctx, atmptype->sql_blacklist_reg, sql, 1); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, atmptype->sql_blacklist_reg, + "Failed to prepare SQL query"); + } else { + qry_args[0] = value; + + dbr = ePGprepExec(ctx, prepid, qry_args); + if( !dbr + || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, + atmptype->sql_blacklist_reg, + "Failed to blacklist %s value " + "'%s':", + atmptype->descr, + value); + goto error; + } + PQclear(dbr); + } + } + + error: + free_nullsafe(ctx, id); + free_nullsafe(ctx, atmpt_block); + free_nullsafe(ctx, blid); + } /** @@ -754,18 +1080,51 @@ int eDBregister_login(eurephiaCTX *ctx, eurephiaSESSION *skey, const int certid, const char *proto, const char *remipaddr, const char *remport, const char *vpnipaddr, const char *vpnipmask) { + char uidstr[10], certidstr[10]; + const char **qry_args = NULL; + int prepid = -1, ret = 0; + PGresult *dbr = NULL; + DEBUG(ctx, 20, "Function call: eDBregister_login(ctx, '%s', %i, %i, '%s','%s','%s','%s','%s')", skey->sessionkey, certid, uid, proto, remipaddr, remport, vpnipaddr, vpnipmask); - /* - "INSERT INTO lastlog (uid, certid, " - " protocol, remotehost, remoteport," - " vpnipaddr, vpnipmask," - " sessionstatus, sessionkey, login) " - "VALUES (%i, %i, '%q','%q','%q','%q','%q', 1,'%q', CURRENT_TIMESTAMP)", - uid, certid, proto, remipaddr, remport, vpnipaddr, vpnipmask, skey->sessionke - */ - return 0; + prepid = ePGprepStatement(ctx, PREPSQL_REGISTER_LOGIN, + "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)", 8); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_REGISTER_LOGIN, + "Failed to prepare query"); + return 0; + } + + qry_args = calloc(8, sizeof(char *)); + snprintf(uidstr, 8, "%i", uid); qry_args[0] = uidstr; + snprintf(certidstr, 8, "%i", certid); qry_args[1] = certidstr; + qry_args[2] = proto; + qry_args[3] = remipaddr; + qry_args[4] = remport; + qry_args[5] = vpnipaddr; + qry_args[6] = vpnipmask; + qry_args[7] = skey->sessionkey; + + dbr = ePGprepExec(ctx, prepid, qry_args); + free_nullsafe(ctx, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, PREPSQL_REGISTER_LOGIN, + "Failed to register session login for session key '%s'", + skey->sessionkey); + ret = 0; + } else { + ret = 1; + PQclear(dbr); + } + return ret; } @@ -774,17 +1133,63 @@ int eDBregister_login(eurephiaCTX *ctx, eurephiaSESSION *skey, const int certid, */ int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const char *macaddr) { + const char **qry_args = NULL; + int prepid_reg = -1, prepid_ll = -1, ret = 0; + PGresult *dbr = NULL; + DEBUG(ctx, 20, "Function call: eDBregister_vpnmacaddr(ctx, '%s', '%s')", session->sessionkey, macaddr); - /* - "INSERT INTO macaddr_history (sessionkey, macaddr) VALUES ('%q','%q')", - session->sessionkey, macaddr); + /* TODO: Use transaction */ + + prepid_reg = ePGprepStatement(ctx, PREPSQL_MACHISTORY_REGISTER, + "INSERT INTO macaddr_history (sessionkey, macaddr)" + " VALUES ($1::VARCHAR, $2::VARCHAR)", 2); + if( prepid_reg < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_MACHISTORY_REGISTER, + "Failed to prepare SQL query"); + return 0; + } + + prepid_ll = ePGprepStatement(ctx, PREPSQL_MACHISTORY_LASTLOG, + "UPDATE lastlog_update " + " SET sessionstatus = 2,macaddr = $2::VARCHAR" + " WHERE sessionkey = $1::VARCHAR AND sessionstatus = 1", 2); + if( prepid_ll < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_MACHISTORY_LASTLOG, + "Failed to prepare SQL query"); + return 0; + } - "UPDATE lastlog SET sessionstatus = 2, macaddr = '%q' " - " WHERE sessionkey = '%q' AND sessionstatus = 1", macaddr, session->sessionkey); + qry_args = calloc(2, sizeof(const char *)); + qry_args[0] = session->sessionkey; + qry_args[1] = macaddr; - */ + // Register the MAC address into the macaddr_history table + dbr = ePGprepExec(ctx, prepid_reg, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, + PREPSQL_MACHISTORY_REGISTER, + "eDBregister_vpnmacaddr: Failed to register VPN client " + "MAC address (%s/%s)", + session->sessionkey, macaddr); + ret = 0; + goto exit; + } + PQclear(dbr); + + // Update lastlog table with the latest used MAC address + dbr = ePGprepExec(ctx, prepid_ll, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, + PREPSQL_MACHISTORY_LASTLOG, + "eDBregister_vpnmacaddr: Failed to update " + "lastlog with MAC address info (%s/%s)", + session->sessionkey, macaddr); + ret = 0; + goto exit; + } + PQclear(dbr); // Save the MAC address in the session values register - needed for the destroy session if( eDBset_session_value(ctx, session, "macaddr", macaddr) == 0 ) { @@ -792,6 +1197,8 @@ int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const cha return 0; } + exit: + free_nullsafe(ctx, qry_args); return 1; } @@ -802,18 +1209,40 @@ int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const cha int eDBregister_logout(eurephiaCTX *ctx, eurephiaSESSION *skey, const char *bytes_sent, const char *bytes_received, const char *duration) { + const char **qry_args = NULL; + int prepid = -1; + PGresult *dbr = NULL; + DEBUG(ctx, 20, "Function call: eDBregister_logout(ctx, '%s', %s, %s)", skey->sessionkey, bytes_sent, bytes_received); - /* - "UPDATE lastlog " - " SET sessionstatus = 3, logout = CURRENT_TIMESTAMP, " - " bytes_sent = '%i', bytes_received = '%i', session_duration = '%i' " - " WHERE sessionkey = '%q' AND sessionstatus = 2", - atoi_nullsafe(bytes_sent), atoi_nullsafe(bytes_received), - atoi_nullsafe(duration), skey->sessionkey); - */ + prepid = ePGprepStatement(ctx, PREPSQL_REGISTER_LOGOUT, + "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", 4); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_REGISTER_LOGOUT, + "Failed to prepare SQL query"); + } + + qry_args = calloc(4, sizeof(char *)); + qry_args[0] = skey->sessionkey; + qry_args[1] = bytes_sent; + qry_args[2] = bytes_received; + qry_args[3] = duration; + dbr = ePGprepExec(ctx, prepid, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, PREPSQL_REGISTER_LOGOUT, + "Failed to lastlog with logout info (%s)", + skey->sessionkey); + free_nullsafe(ctx, qry_args); + return 0; + } + PQclear(dbr); + free_nullsafe(ctx, qry_args); skey->sessionstatus = SESSION_LOGGEDOUT; return 1; } @@ -824,7 +1253,11 @@ int eDBregister_logout(eurephiaCTX *ctx, eurephiaSESSION *skey, */ char *eDBget_sessionkey_seed(eurephiaCTX *ctx, sessionType type, const char *sessionseed) { + const char **qry_args = NULL; char *skey = NULL; + _ePG_prepID preptype = PREPSQL_NONE; + int prepid = -1; + PGresult *dbr = NULL; DEBUG(ctx, 20, "eDBget_sessionkey_seed(ctx, %i, '%s')", type, sessionseed); @@ -834,35 +1267,57 @@ char *eDBget_sessionkey_seed(eurephiaCTX *ctx, sessionType type, const char *ses return NULL; } + qry_args = calloc(1, sizeof(char *)); + qry_args[0] = sessionseed; + switch( type ) { case stSESSION: - /* - "SELECT sessionkey " - " FROM sessionkeys " - " JOIN lastlog USING (sessionkey)" - " WHERE sessionstatus IN (1,2)" - " AND sessionseed = '%q'", - sessionseed); - */ + preptype = PREPSQL_SESSIONKEY_GETSEED_SESSION; + prepid = ePGprepStatement(ctx, preptype, + "SELECT sessionkey " + " FROM sessionkeys " + " JOIN lastlog USING (sessionkey)" + " WHERE sessionstatus IN (1,2)" + " AND sessionseed = $1::VARCHAR", 1); break; case stAUTHENTICATION: - /* - "SELECT sessionkey" - " FROM sessionkeys" - " LEFT JOIN lastlog USING(sessionkey)" - " WHERE sessionstatus IS NULL" - " AND sessionseed = '%q'", - sessionseed); - */ + preptype = PREPSQL_SESSIONKEY_GETSEED_AUTH; + prepid = ePGprepStatement(ctx, preptype, + "SELECT sessionkey " + " FROM sessionkeys " + " LEFT JOIN lastlog USING (sessionkey)" + " WHERE sessionstatus IS NULL" + " AND sessionseed = $1::VARCHAR", 1); break; default: eurephia_log(ctx, LOG_ERROR, 0, "Invalid session type: %i", type); - return NULL; + goto exit; + } + + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, preptype, + "Failed to prepare SQL query"); + skey = NULL; + goto exit; + } + + dbr = ePGprepExec(ctx, prepid, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, preptype, + "Failed to lookup session key from session seed '%s'", + sessionseed); + skey = NULL; + goto exit; } + skey = (PQntuples(dbr) == 1 ? strdup_nullsafe(PQgetvalue(dbr, 0, 0)) : NULL); + PQclear(dbr); + exit: + free_nullsafe(ctx, qry_args); return skey; + } @@ -872,19 +1327,43 @@ char *eDBget_sessionkey_seed(eurephiaCTX *ctx, sessionType type, const char *ses char *eDBget_sessionkey_macaddr(eurephiaCTX *ctx, const char *macaddr) { char *skey = NULL; + const char **qry_args = NULL; + int prepid = -1; + PGresult *dbr = NULL; + DEBUG(ctx, 20, "eDBget_sessionkey_macaddr(ctx, '%s')", macaddr); // Find sessionkey from MAC address - /* - "SELECT sessionkey " - " FROM sessions " - " JOIN lastlog USING (sessionkey)" - " WHERE sessionstatus = 3 " - " AND datakey = 'macaddr'" - " AND dataval = '%q'", macaddr); - */ + prepid = ePGprepStatement(ctx, PREPSQL_SESSIONKEY_GETMAC, + "SELECT sessionkey " + " FROM sessions " + " JOIN lastlog USING (sessionkey)" + " WHERE sessionstatus = 3 " + " AND datakey = 'macaddr'" + " AND dataval = $1::VARCHAR", 1); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_SESSIONKEY_GETMAC, + "Failed to prepare SQL query"); + return NULL; + } + qry_args = calloc(1, sizeof(char *)); + qry_args[0] = macaddr; + + dbr = ePGprepExec(ctx, prepid, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_SESSIONKEY_GETMAC, + "Failed to lookup session key from MAC address '%s'", + macaddr); + skey = NULL; + goto exit; + } + skey = (PQntuples(dbr) == 1 ? strdup_nullsafe(PQgetvalue(dbr, 0, 0)) : NULL); + PQclear(dbr); + + exit: + free_nullsafe(ctx, qry_args); return skey; } @@ -895,8 +1374,13 @@ char *eDBget_sessionkey_macaddr(eurephiaCTX *ctx, const char *macaddr) int eDBcheck_sessionkey_uniqueness(eurephiaCTX *ctx, const char *seskey) { int uniq = 0; + const char **qry_args = NULL; + int prepid = -1; + _ePG_prepID preptype; + PGresult *dbr = NULL; DEBUG(ctx, 20, "eDBcheck_sessionkey_uniqueness(ctx, '%s')", seskey); + if( seskey == NULL ) { eurephia_log(ctx, LOG_FATAL, 1, "eDBcheck_sessionkey_uniqness: Invalid session key given"); @@ -910,22 +1394,46 @@ int eDBcheck_sessionkey_uniqueness(eurephiaCTX *ctx, const char *seskey) case ECTX_ADMIN_CONSOLE: case ECTX_ADMIN_WEB: - /* - "SELECT count(sessionkey) = 0 " - "FROM eurephia_adminlog WHERE sessionkey = '%q'", seskey); - */ + preptype = PREPSQL_SESSIONKEY_UNIQ_ADMIN; + prepid = ePGprepStatement(ctx, preptype, + "SELECT count(sessionkey) = 0 " + "FROM eurephia_adminlog WHERE sessionkey = $1::VARCHAR", 1); break; case ECTX_PLUGIN_AUTH: default: - /* - "SELECT count(sessionkey) = 0 " - "FROM lastlog WHERE sessionkey = '%q'", seskey); - */ + preptype = PREPSQL_SESSIONKEY_UNIQ_PLAUTH; + prepid = ePGprepStatement(ctx, preptype, + "SELECT count(sessionkey) = 0 " + "FROM lastlog WHERE sessionkey = $1::VARCHAR", 1); break; } - return uniq; + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, preptype, + "Failed to prepare SQL query"); + return 0; + } + + qry_args = calloc(1, sizeof(char *)); + qry_args[0] = seskey; + + dbr = ePGprepExec(ctx, prepid, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, preptype, + "Failed to check session key uniqueness for '%s'", + seskey); + uniq = 0; + goto exit; + } else if( PQntuples(dbr) == 1 ) { + char *res = PQgetvalue(dbr, 0, 0); + uniq = (*res == 't'); + } + PQclear(dbr); + + exit: + free_nullsafe(ctx, qry_args); + return uniq; } @@ -933,7 +1441,9 @@ int eDBcheck_sessionkey_uniqueness(eurephiaCTX *ctx, const char *seskey) * @copydoc eDBregister_sessionkey() */ int eDBregister_sessionkey(eurephiaCTX *ctx, const char *seed, const char *seskey) { - // dbresult *res; + const char **qry_args = NULL; + int prepid = -1, ret = 0; + PGresult *dbr = NULL; DEBUG(ctx, 20, "eDBregister_sessionkey(ctx, '%s', '%s')", seed, seskey); if( (seed == NULL) || (seskey == NULL) ) { @@ -942,16 +1452,32 @@ int eDBregister_sessionkey(eurephiaCTX *ctx, const char *seed, const char *seske return 0; } - /* - * "INSERT INTO openvpn_sessionkeys (sessionseed, sessionkey) VALUES('%q','%q')", - - if( res == NULL ) { - eurephia_log(ctx, LOG_FATAL, 0, - "eDBregister_sessionkey: Error registering sessionkey into 'sessionkeys'"); + prepid = ePGprepStatement(ctx, PREPSQL_SESSIONKEY_REGISTER, + "INSERT INTO sessionkeys (sessionseed, sessionkey) " + " VALUES($1::VARCHAR, $2::VARCHAR)", 2); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_SESSIONKEY_REGISTER, + "Failed to prepare SQL query"); return 0; } - */ - return 1; + + qry_args = calloc(2, sizeof(char *)); + qry_args[0] = seed; + qry_args[1] = seskey; + + dbr = ePGprepExec(ctx, prepid, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_SESSIONKEY_REGISTER, + "eDBregister_sessionkey: Error registering sessionkey" + " into 'sessionkeys'"); + ret = 0; + goto exit; + } + PQclear(dbr); + ret = 1; + exit: + free_nullsafe(ctx, qry_args); + return ret; } /** @@ -964,7 +1490,9 @@ int eDBregister_sessionkey(eurephiaCTX *ctx, const char *seed, const char *seske * */ int eDBremove_sessionkey(eurephiaCTX *ctx, const char *seskey) { - // dbresult *res; + const char **qry_args = NULL; + int prepid = -1, ret = 0; + PGresult *dbr = NULL; DEBUG(ctx, 20, "eDBremove_sessionkey(ctx, '%s')", seskey); if( seskey == NULL ) { @@ -973,35 +1501,84 @@ int eDBremove_sessionkey(eurephiaCTX *ctx, const char *seskey) { return 0; } - /* - * "DELETE FROM openvpn_sessionkeys WHERE sessionkey = '%q'", seskey); - if( res == NULL ) { - eurephia_log(ctx, LOG_FATAL, 0, - "eDBremove_sessionkey: Error removing sessionkey from openvpn_sessionkeys"); + prepid = ePGprepStatement(ctx, PREPSQL_SESSIONKEY_REMOVE, + "DELETE FROM sessionkeys WHERE sessionkey = $1::VARCHAR", 1); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_SESSIONKEY_REMOVE, + "Failed to prepare SQL query"); return 0; } - */ - return 1; + + qry_args = calloc(1, sizeof(char *)); + qry_args[0] = seskey; + + dbr = ePGprepExec(ctx, prepid, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_SESSIONKEY_REMOVE, + "eDBremove_sessionkey: Error removing sessionkey '%s'", + seskey); + ret = 0; + goto exit; + } + PQclear(dbr); + ret = 1; + exit: + free_nullsafe(ctx, qry_args); + return ret; } /** * @copydoc eDBload_sessiondata() */ -eurephiaVALUES *eDBload_sessiondata(eurephiaCTX *ctx, const char *sesskey) +eurephiaVALUES *eDBload_sessiondata(eurephiaCTX *ctx, const char *seskey) { eurephiaVALUES *sessvals = NULL; + const char **qry_args = NULL; + int prepid = -1, i = 0; + PGresult *dbr = NULL; + DEBUG(ctx, 20, "Function call: eDBload_sessiondata(ctx, '%s')", seskey); - DEBUG(ctx, 20, "Function call: eDBload_sessiondata(ctx, '%s')", sesskey); + prepid = ePGprepStatement(ctx, PREPSQL_SESSIONVARS_LOAD, + "SELECT datakey, dataval" + " FROM sessions" + " WHERE sessionkey = $1::VARCHAR", 1); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_SESSIONVARS_LOAD, + "Failed to prepare SQL query"); + return NULL; + } + + qry_args = calloc(1, sizeof(char *)); + qry_args[0] = seskey; + + dbr = ePGprepExec(ctx, prepid, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { + ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_SESSIONVARS_LOAD, + "eDBload_sessiondata: Failed to load session variables for session '%s'", + seskey); + sessvals = NULL; + goto exit; + } sessvals = eCreate_value_space(ctx, 10); + if( !sessvals ) { + eurephia_log(ctx, LOG_CRITICAL, 0, + "Failed to create value storage for session variables for session '%s'", + seskey); + goto exit; + } - /* - "SELECT datakey, dataval FROM sessions WHERE sessionkey = '%q'", - sesskey - */ + for( i = 0; i < PQntuples(dbr); i++ ) { + eAdd_value(ctx, sessvals, + ePGgetValue(dbr, i, 0), + ePGgetValue(dbr, i, 1)); + } + PQclear(dbr); + exit: + free_nullsafe(ctx, qry_args); return sessvals; } @@ -1012,6 +1589,11 @@ eurephiaVALUES *eDBload_sessiondata(eurephiaCTX *ctx, const char *sesskey) int eDBstore_session_value(eurephiaCTX *ctx, eurephiaSESSION *session, int mode, const char *key, const char *val) { + const char **qry_args = NULL; + int prepid = -1, ret = 0; + _ePG_prepID preptype = PREPSQL_NONE; + PGresult *dbr = NULL; + if( session == NULL ) { DEBUG(ctx, 20, "Function call failed to eDBstore_session_value(ctx, ...): Non-existing session key"); @@ -1021,34 +1603,62 @@ int eDBstore_session_value(eurephiaCTX *ctx, eurephiaSESSION *session, int mode, DEBUG(ctx, 20, "Function call: eDBstore_session_value(ctx, '%s', %i, '%s', '%s')", session->sessionkey, mode, key, val); + /* TODO: Need testing */ + switch( mode ) { case SESSVAL_NEW: - /* - "INSERT INTO sessions (sessionkey, datakey, dataval) " - "VALUES ('%q','%q','%q')", session->sessionkey, key, val); - */ + preptype = PREPSQL_SESSIONVARS_STORE_NEW; + prepid = ePGprepStatement(ctx, preptype, + "INSERT INTO sessions (sessionkey, datakey, dataval) " + "VALUES ($1::VARCHAR,$2::VARCHAR,$3::VARCHAR)", 3); break; case SESSVAL_UPDATE: - /* - "UPDATE sessions SET dataval = '%q' " - " WHERE sessionkey = '%q' AND datakey = '%q'", - val, session->sessionkey, key); - */ + preptype = PREPSQL_SESSIONVARS_STORE_UPDATE; + prepid = ePGprepStatement(ctx, preptype, + "UPDATE sessions SET dataval = $3::VARCHAR " + " WHERE sessionkey = $1::VARCHAR" + " AND datakey = $2::VARCHAR", 2); + break; case SESSVAL_DELETE: - /* - "DELETE FROM sessions " - " WHERE sessionkey = '%q' AND datakey = '%q'", - session->sessionkey, key); - */ + preptype = PREPSQL_SESSIONVARS_STORE_DELETE; + prepid = ePGprepStatement(ctx, preptype, + "DELETE FROM sessions " + " WHERE sessionkey = $1::VARCHAR" + " AND datakey = $2::VARCHAR", 2); break; default: eurephia_log(ctx, LOG_FATAL, 0, "Unknown eDBstore_session_value mode '%i'", mode); return 0; } - return 1; + + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, preptype, + "Failed to prepare SQL query"); + return 0; + } + + qry_args = calloc(3, sizeof(char *)); + qry_args[0] = session->sessionkey; + qry_args[1] = key; + qry_args[2] = val; + + dbr = ePGprepExec(ctx, prepid, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, preptype, + "eDBstore_session_value: Failed to update session value [%s] %s", + session->sessionkey, key); + ret = 0; + goto exit; + } + PQclear(dbr); + ret = 1; + + exit: + free_nullsafe(ctx, qry_args); + return ret; } @@ -1057,6 +1667,10 @@ int eDBstore_session_value(eurephiaCTX *ctx, eurephiaSESSION *session, int mode, */ int eDBdestroy_session(eurephiaCTX *ctx, eurephiaSESSION *session) { + const char **qry_args = NULL; + int prepid = -1, ret = 0; + PGresult *dbr = NULL; + DEBUG(ctx, 20, "Function call: eDBdestroy_session(ctx, '%s')", session->sessionkey); if( (session == NULL) || (session->sessionkey == NULL) ) { @@ -1064,25 +1678,73 @@ int eDBdestroy_session(eurephiaCTX *ctx, eurephiaSESSION *session) return 1; } + qry_args = calloc(1, sizeof(char *)); + qry_args[0] = session->sessionkey; + // Update session status - if we have a "real" session (not auth-session) if( session->type == stSESSION ) { - /* - "UPDATE lastlog " - " SET sessionstatus = 4, session_deleted = CURRENT_TIMESTAMP " - " WHERE sessionkey = '%q' AND sessionstatus = 3", session->sessionkey); - */ - } + prepid = ePGprepStatement(ctx, PREPSQL_SESSIONS_DESTROY_LASTLOG, + "UPDATE lastlog_update " + " SET sessionstatus = 4," + " session_deleted = CURRENT_TIMESTAMP " + " WHERE sessionkey = $1::VARCHAR" + " AND sessionstatus = 3", 1); + if( prepid < 0 ) { + ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_SESSIONS_DESTROY_LASTLOG, + "Failed to prepare SQL query"); + ret = 0; + goto exit; + } + + dbr = ePGprepExec(ctx, prepid, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, + PREPSQL_SESSIONS_DESTROY_LASTLOG, + "eDBdestroy_session: Failed to update " + "update lastlog with session deleted status (%s)", + session->sessionkey); + ret = 0; + goto exit; + } + PQclear(dbr); + } // Delete session variables - /* - "DELETE FROM sessions WHERE sessionkey = '%q'", session->sessionkey); - */ - - // Delete the session key - /* - "DELETE FROM sessionkeys WHERE sessionkey = '%q'", seskey); - */ - return 0; + prepid = ePGprepStatement(ctx, PREPSQL_SESSIONS_DESTROY_SESS, + "DELETE FROM sessions WHERE sessionkey = $1::VARCHAR", 1); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, + PREPSQL_SESSIONS_DESTROY_SESS, + "Failed to prepare SQL query"); + ret = 0; + goto exit; + } + + dbr = ePGprepExec(ctx, prepid, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, + PREPSQL_SESSIONS_DESTROY_SESS, + "eDBdestroy_session: Failed to delete " + "session variables (%s)", + session->sessionkey); + ret = 0; + goto exit; + } + PQclear(dbr); + + // Delete the session seed/session key relation + if( eDBremove_sessionkey(ctx, session->sessionkey) == 0) { + eurephia_log(ctx, LOG_CRITICAL, 0, + "Failed to delete sessionkey/sessionseed relation (%s)", + session->sessionkey); + ret = 0; + } else { + ret = 1; + } + + exit: + free_nullsafe(ctx, qry_args); + return ret; } @@ -1091,19 +1753,41 @@ int eDBdestroy_session(eurephiaCTX *ctx, eurephiaSESSION *session) */ char *eDBget_firewall_profile(eurephiaCTX *ctx, eurephiaSESSION *session) { + const char **qry_args = NULL; + int prepid = -1; char *ret = NULL; - + PGresult *dbr = NULL; DEBUG(ctx, 20, "Function call: eDBget_firewall_profile(ctx, {session}'%s')", session->sessionkey); - /* - "SELECT fw_profile " - " FROM lastlog " - " JOIN usercerts USING(certid, uid)" - " JOIN accesses USING(accessprofile)" - " WHERE sessionkey = '%q'", session->sessionkey); - */ + /* TODO: Need testing */ + + prepid = ePGprepStatement(ctx, PREPSQL_FIREWALL_GETPROFILE, + "SELECT fw_profile " + " FROM lastlog " + " JOIN usercerts USING(certid, uid)" + " JOIN accesses USING(accessprofile)" + " WHERE sessionkey = $1::VARCHAR", 1); + if( prepid < 0 ) { + ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_FIREWALL_GETPROFILE, + "Failed to prepare SQL query"); + return NULL; + } + + qry_args = calloc(1, sizeof(char *)); + qry_args[0] = session->sessionkey; + + dbr = ePGprepExec(ctx, prepid, qry_args); + free_nullsafe(ctx,qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) || (PQntuples(dbr) > 1) ) { + ePGerrorMessage(ctx, dbr, LOG_WARNING, 0, PREPSQL_FIREWALL_GETPROFILE, + "Failed to retrieve the firewall profile for session '%s'", + session->sessionkey); + return NULL; + } + ret = (PQntuples(dbr) == 1 ? strdup_nullsafe(ePGgetValue(dbr, 0, 0)) : NULL); + PQclear(dbr); return ret; } @@ -1114,17 +1798,33 @@ char *eDBget_firewall_profile(eurephiaCTX *ctx, eurephiaSESSION *session) */ eurephiaVALUES *eDBget_blacklisted_ip(eurephiaCTX *ctx) { + int i = 0; + PGresult *dbr = NULL; eurephiaVALUES *ret = NULL; DEBUG(ctx, 20, "Function call: eDBget_blacklisted_ip(ctx)"); - /* - "SELECT remoteip FROM blacklist WHERE remoteip IS NOT NULL"); - */ + if( !ctx || !ctx->dbc || !ctx->dbc->dbhandle ) { + eurephia_log(ctx, LOG_FATAL, 0, "System error in eDBget_blacklisted_ip()"); + return NULL; + } + + dbr = PQexec(ctx->dbc->dbhandle, "SELECT remoteip FROM blacklist WHERE remoteip IS NOT NULL"); + if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { + ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, PREPSQL_NONE, + "eDBget_blaclisted_ip: Failed to query IP blacklist"); + return NULL; + } + // Copy blacklisted IP addresses into a eurephiaVALUES chain without key names ret = eCreate_value_space(ctx, 21); - // copy SQL result into eurephiaVALUES chain + for( i = 0; i < PQntuples(dbr); i++ ) { + char *ip = ePGgetValue(dbr, i, 0); + if( (ip != NULL) && (*ip != '\0') ) { + eAdd_value(ctx, ret, NULL, ip); + } + } + PQclear(dbr); return ret; - } |