diff options
author | David Sommerseth <dazo@users.sourceforge.net> | 2008-09-19 19:19:47 +0200 |
---|---|---|
committer | David Sommerseth <dazo@users.sourceforge.net> | 2008-09-19 19:19:47 +0200 |
commit | 655ac53af5e76eccc30cc0e3b0a463d72b5c9f12 (patch) | |
tree | abc9b2f5e4ab731a7ee35a706c0ecf6adb9116af /database/eurephiadb-driver_template.c | |
parent | 587028ddd4136637e3b94a877ecceae4217dfe72 (diff) | |
download | eurephia-655ac53af5e76eccc30cc0e3b0a463d72b5c9f12.tar.gz eurephia-655ac53af5e76eccc30cc0e3b0a463d72b5c9f12.tar.xz eurephia-655ac53af5e76eccc30cc0e3b0a463d72b5c9f12.zip |
Updated database driver template to match the most recent needed API
Diffstat (limited to 'database/eurephiadb-driver_template.c')
-rw-r--r-- | database/eurephiadb-driver_template.c | 669 |
1 files changed, 388 insertions, 281 deletions
diff --git a/database/eurephiadb-driver_template.c b/database/eurephiadb-driver_template.c index b44c833..5bcea91 100644 --- a/database/eurephiadb-driver_template.c +++ b/database/eurephiadb-driver_template.c @@ -1,4 +1,14 @@ -/* eurephiadb-driver_template.c -- Skeleton for a eurephiaDB driver +/* eurephia-template.c -- Main driver for eurephia authentication plugin for OpenVPN + * + * + * // THIS IS A TEMPLATE FOR A DATABASE DRIVER - IT WILL NOT COMPILE // + * + * Go through the code and you will find examples for SQL queries and + * hints how to implement the database specific code + * + * This template is based on the sqlite/eurephia-sqlite.c + * + * * * GPLv2 - Copyright (C) 2008 David Sommerseth <dazo@users.sourceforge.net> * @@ -23,22 +33,17 @@ #include <string.h> #include <dlfcn.h> + #define EUREPHIADB_DRIVER 1 #include <eurephiadb_driver.h> #include <eurephia_nullsafe.h> #include <eurephia_log.h> +#include <eurephia_values.h> #include <eurephiadb_session.h> #include <passwd.h> -typedef struct dblconfig_s { - char *key; - char *val; - struct dblconfig_s *next; -} dblconfig; - - -// Mapping table - mapping attempt types from .... to sqlite table fields +// Mapping table - mapping attempt types from .... to database table fields typedef struct { char *colname; char *allow_cfg; @@ -59,7 +64,8 @@ static const eDBattempt_types_t eDBattempt_types[] = { */ const char *eDB_DriverVersion() { - return "eurephiadb-driver_template (v"DRIVERVERSION") David Sommerseth 2008 (C) GPLv2"; + /* WORK TO DO --- UPDATE INFO */ + return "eurephiadb-template (v"DRIVERVERSION") David Sommerseth 2008 (C) GPLv2"; } @@ -72,32 +78,20 @@ int eDB_DriverAPIVersion() { * local functions */ -// Function for retrieving config parameters. Parameters are loaded during eDBconnect(...) -char *get_config(eurephiaCTX *ctx, const char *key) { - dblconfig *cfg = ctx->dbc->config; - - while( cfg != NULL ) { - if( strcmp(key, cfg->key) == 0 ) { - return cfg->val; - } - cfg = cfg->next; - } - return NULL; -} - // Function for simplifying update of openvpn_blacklist void update_attempts(eurephiaCTX *ctx, const char *blid) { - dbresult *res = NULL; if( blid != NULL ) { - res = do_sql_query( // pseudo-code - "UPDATE openvpn_blacklist " - " SET last_accessed = CURRENT_TIMESTAMP WHERE blid = %s", blid); - if( res == NULL ) { + /* WORK TO DO -- DO SQL: + "UPDATE openvpn_blacklist " + " SET last_accessed = CURRENT_TIMESTAMP WHERE blid = %s", blid + */ + if( /* SQL COMMAND FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not update openvpn_blacklist.last_accessed for blid=%s", blid); } + /* FREE SQL RESULT */ } } @@ -109,50 +103,59 @@ void update_attempts(eurephiaCTX *ctx, const char *blid) { int eDBconnect(eurephiaCTX *ctx, const int argc, const char **argv) { eDBconn *dbc = NULL; - dbresult *res = NULL; int rc; - eurephia_log(ctx, LOG_DEBUG, 10, "Function call: eDBconnect(ctx, %i, '%s')", argc, argv[1]); + DEBUG(ctx, 10, "Function call: eDBconnect(ctx, %i, '%s')", argc, argv[1]); - // Validate input parameters + if( (argc != 1) || (argv[0] == NULL) || (strlen(argv[0]) < 1) ) { + eurephia_log(ctx, LOG_PANIC, 0, + "Wrong parameters to eurephia-auth (eDBconnect). Cannot open database."); + return 0; + } - // Create a memory buffer for our database context + // Connect to the database dbc = (eDBconn *) malloc(sizeof(eDBconn)+2); memset(dbc, 1, sizeof(eDBconn)+2); - dbc->dbname = strdup("put a sensible database name here from input arguments"); + dbc->dbname = strdup(argv[1]); - - // Connect to the database eurephia_log(ctx, LOG_INFO, 1, "Opening database '%s'", dbc->dbname); - if( connection_status == ERROR ) { // pseudo-code - catch if error connection fails + /* WORK TO DO -- Open a database connection, and save the handle in dbc->handle */ + + if( /* IF CONNECTION FAILED */ ) { eurephia_log(ctx, LOG_FATAL, 0, "Could not open database '%s'", dbc->dbname); free_nullsafe(dbc->dbname); free_nullsafe(dbc); return 0; } - // When connection is ready ... + dbc->config = NULL; ctx->dbc = dbc; // Load configuration parameters into memory eurephia_log(ctx, LOG_INFO, 1, "Reading config from database (openvpn_config)"); - res = do_sql_query("SELECT datakey, dataval FROM openvpn_config"); // pseudo-code - catch config from DB - if( res != NULL ) { + + /* WORK TO DO - DO SQL: + "SELECT datakey, dataval FROM openvpn_config" + */ + if( /* IF WE GOT SOME RECORDS */ ) { int i = 0; - dblconfig *cfg = NULL; + eurephiaVALUES *cfg = NULL; - for( i = 0; i < sqlite_get_numtuples(res); i++ ) { - cfg = (dblconfig *) malloc(sizeof(dblconfig) + 2); - memset(cfg, 0, sizeof(dblconfig) + 2); - - // copy data from SQL query into this structure - cfg->key = strdup_nullsafe(...); - cfg->val = strdup_nullsafe(...); - eurephia_log(ctx, LOG_DEBUG, 12, "Config: %s = %s", cfg->key, cfg->val); - cfg->next = ctx->dbc->config; - ctx->dbc->config = cfg; + cfg = eCreate_value_space(ctx, 11); + if( cfg == NULL ) { + eurephia_log(ctx, LOG_FATAL, 0, "Could not allocate memory for config variables"); + /* FREE SQL RESULT */ + return 0; + } + + for( /* LOOP THROUGH ALL RECORDS, AND ADD THEM TO THE cfg VARIABLE */ ) { + eAdd_value(ctx, cfg, + /* GET datakey FROM SQL RESULT */, + /* GET dataval FROM SQL RESULT */); } + /* FREE SQL RESULT */ + ctx->dbc->config = cfg; } return 1; } @@ -161,9 +164,8 @@ int eDBconnect(eurephiaCTX *ctx, const int argc, const char **argv) void eDBdisconnect(eurephiaCTX *ctx) { eDBconn *dbc = NULL; - dblconfig *cfg = NULL, *ptr = NULL; - eurephia_log(ctx, LOG_DEBUG, 10, "Function call: eDBdisconnect(ctx)"); + DEBUG(ctx, 10, "Function call: eDBdisconnect(ctx)"); if( ctx->dbc == NULL ) { eurephia_log(ctx, LOG_WARNING, 0, "Database not open, cannot close database."); @@ -173,22 +175,14 @@ void eDBdisconnect(eurephiaCTX *ctx) dbc = ctx->dbc; eurephia_log(ctx, LOG_INFO, 2, "Closing database '%s'", dbc->dbname); - // Close database connection - - close_database(dbc->dbhandle); // pseudo-code ... close database connection + /* WORK TO DO: Close database connection using dbc->dbhandle */ + free_nullsafe(dbc->dbname); dbc->dbhandle = NULL; // Free up config memory - cfg = dbc->config; - while( cfg != NULL ) { - free_nullsafe(cfg->key); - free_nullsafe(cfg->val); - ptr = cfg; - cfg = cfg->next; - free_nullsafe(ptr); - } + eFree_values(ctx, dbc->config); free_nullsafe(dbc); ctx->dbc = NULL; } @@ -199,24 +193,26 @@ void eDBdisconnect(eurephiaCTX *ctx) int eDBauth_TLS(eurephiaCTX *ctx, const char *org, const char *cname, const char *email, const char *digest, const char *depth) { - dbresult *res = NULL; int certid = 0; char *blid = NULL; - eurephia_log(ctx, LOG_DEBUG, 10, "Function call: eDBauth_TLS(ctx, '%s', '%s', '%s', '%s', %s)", + DEBUG(ctx, 10, "Function call: eDBauth_TLS(ctx, '%s', '%s', '%s', '%s', %s)", org, cname, email, digest, depth); // Check if certificate is valid, and not too many attempts has been tried with the given certificate - res = do_sql_query( // pseudo-code + /* WORK TO DO -- DO SQL: "SELECT cert.certid, blid " " FROM openvpn_certificates cert" " LEFT JOIN openvpn_blacklist bl USING(digest)" - " WHERE organisation='%s' AND common_name='%s' " - " AND email='%s' AND depth='%s' AND cert.digest='%s'%c", - org, cname, email, depth, digest, 0); + " WHERE organisation='%q' AND common_name='%q' " + " AND email='%q' AND depth='%q' AND cert.digest='%q'%c", + org, cname, email, depth, digest + */ - if( res != NULL ) { - // retrieve certid and blid from database result + if( /*IF WE GOT A RESULT */ ) { + certid = atoi_nullsafe(/* GET cert.certid FROM SQL RESULT */); + blid = atoi_nullsafe(/* GET blid FROM SQL RESULT */); + /* FREE SQL RESULT */ // Check if we found certificate to be blacklisted or not. blid == NULL when NOT blacklisted if( blid == NULL ) { @@ -242,9 +238,8 @@ int eDBauth_TLS(eurephiaCTX *ctx, const char *org, const char *cname, const char eurephia_log(ctx, LOG_FATAL, 0, "Could not look up certificate information"); } - eurephia_log(ctx, LOG_DEBUG, 10, - "Result function call: eDBauth_TLS(ctx, '%s', '%s', '%s', '%s', %s) - %i", - org, cname, email, digest, depth, certid); + DEBUG(ctx, 10, "Result function call: eDBauth_TLS(ctx, '%s', '%s', '%s', '%s', %s) - %i", + org, cname, email, digest, depth, certid); return certid; } @@ -253,18 +248,16 @@ int eDBauth_TLS(eurephiaCTX *ctx, const char *org, const char *cname, const char // returns -1 if authentication fails. Returns 0 if user account is not found. int eDBauth_user(eurephiaCTX *ctx, const int certid, const char *username, const char *passwd) { - dbresult *res = NULL; char *crpwd = NULL, *activated = NULL, *deactivated = NULL, *blid_uname = NULL, *blid_cert; int uicid = 0, uid = 0, pwdok = 0; - eurephia_log(ctx, LOG_DEBUG, 10, "Function call: eDBauth_user(ctx, %i, '%s','xxxxxxxx')", - certid, username); + DEBUG(ctx, 10, "Function call: eDBauth_user(ctx, %i, '%s','xxxxxxxx')", certid, username); // Generate SHA1 hash of password, used for password auth crpwd = passwdhash(passwd); - res = do_sql_query( // pseudo-code + /* WORK TO DO -- DO SQL: "SELECT uicid, ou.uid, activated, deactivated, bl1.blid, bl2.blid, " " (password = '%s') AS pwdok" " FROM openvpn_users ou" @@ -273,24 +266,23 @@ int eDBauth_user(eurephiaCTX *ctx, const int certid, const char *username, const " LEFT JOIN (SELECT blid, certid " " FROM openvpn_certificates " " JOIN openvpn_blacklist USING(digest)) bl2 ON(uc.certid = bl2.certid)" - " WHERE uc.certid = '%i' AND ou.username = '%s'", - crpwd, certid, username); + " WHERE uc.certid = '%i' AND ou.username = '%q'", + crpwd, certid, username + */ free_nullsafe(crpwd); - if( res == NULL ) { + if( /* IF NO RESULT WAS RETURNED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not lookup user in database (certid %i, username '%s'", certid, username); return 0; } - if( get_numtuples(res) == 1 ) { // pseudo-code - // fetch uid, activated, deactivated, blid_uname (bl1.blid), blid_cert (bl2.blid) and pwdok - // in the SQL results - uid = atoi_nullsafe(...); - activated = ...; - deactivated = ...; - blid_uname = ...; - blid_cert = ...; - pwdok = atoi_nullsafe(...); + if( /* CHECK THAT WE ONLY GOT 1 RECORD*/ ) { + uid = atoi_nullsafe(/* RETRIEVE uid FROM SQL*/); + activated = ;/* GET activated FIELD FROM SQL RESULT */ + deactivated = ;/* GET deactivated FIELD FROM SQL RESULT */ + blid_uname = ;/* GET bl1.blid FIELD FROM SQL RESULT */ + blid_cert = ;/* GET bl2.blid FIELD FROM SQL RESULT */ + pwdok = atoi_nullsafe(/* GET pwdok FIELD FROM SQL RESULT */); if( blid_uname != NULL ) { eurephia_log(ctx, LOG_WARNING, 0, "User account is BLACKLISTED (uid: %i, %s)", @@ -316,41 +308,65 @@ int eDBauth_user(eurephiaCTX *ctx, const int certid, const char *username, const uicid = -1; } else { - // fetch uicid from the SQL results - uicid = atoi_nullsafe(...); + uicid = atoi_nullsafe(/*GET uicid FIELD FROM SQL RESULT */); } } else { eurephia_log(ctx, LOG_WARNING, 0, "Authentication failed for user '%s'. " "Could not find user or user-certificate link.", username); uicid = 0; } + /* FREE SQL RESULT */ - eurephia_log(ctx, LOG_DEBUG, 10, "Result function call: eDBauth_user(ctx, %i, '%s','xxxxxxxx') - %i", - certid, username, uicid); + DEBUG(ctx, 10, "Result function call: eDBauth_user(ctx, %i, '%s','xxxxxxxx') - %i", + certid, username, uicid); return uicid; } +// Retrieve the user id from openvpn_usercerts, based on certid and username +int eDBget_uid(eurephiaCTX *ctx, const int certid, const char *username) +{ + int ret = 0; + + DEBUG(ctx, 10, "Function call: eDBget_uid(ctx, %i, '%s')", certid, username); + + /* WORK TO DO -- DO SQL: + "SELECT uid " + " FROM openvpn_usercerts " + " JOIN openvpn_users USING (uid) " + " WHERE certid = '%i' AND username = '%q'", + certid, username + */ + if( /* IF NO RESULT FOUND */ || /* OR WE GOT MORE THAN 1 RECORD */ ) { + eurephia_log(ctx, LOG_CRITICAL, 0, "Could not lookup userid for user '%s'", username); + ret = -1; + } else { + ret = atoi_nullsafe(/* GET uid FIELD FROM SQL RESULT */); + } + /* FREE SQL RESULT */ + + return ret; +} + // If function returns true(1) this control marks it as blacklisted int eDBblacklist_check(eurephiaCTX *ctx, const int type, const char *val) { - dbresult *blr = NULL, *atpr = NULL; int atpexceed = -1, blacklisted = 0; char *atpid = NULL, *blid = NULL; - eurephia_log(ctx, LOG_DEBUG, 10, "Function call: eDBblacklist_check(ctx, '%s', '%s')", - eDBattempt_types[type].descr, val); + DEBUG(ctx, 10, "Function call: eDBblacklist_check(ctx, '%s', '%s')", + eDBattempt_types[type].descr, val); - blr = do_sql_query("SELECT blid FROM openvpn_blacklist WHERE %s = '%s'", // pseudo-code - eDBattempt_types[type].colname, val); - if( blr != NULL ) { - // fetch blid from SQL result - blid = strdup_nullsafe(...); - sqlite_free_results(blr); - blr = NULL; + /* WORK TO DO -- DO SQL: + "SELECT blid FROM openvpn_blacklist WHERE %s = '%q'", + eDBattempt_types[type].colname, val + */ + if( /* IF RECORDS WAS FOUND */ ) { + blid = strdup_nullsafe(/* GET blid FIELD FROM SQL RESULT */); + /* FREE SQL RESULT */ - if( blid != NULL ) { + if( blid != NULL ) { // If we found a blacklisted record eurephia_log(ctx, LOG_WARNING, 1, "Attempt from blacklisted %s: %s", eDBattempt_types[type].descr, val); blacklisted = 1; // [type] is blacklisted @@ -365,29 +381,32 @@ int eDBblacklist_check(eurephiaCTX *ctx, const int type, const char *val) if( blacklisted == 0 ) { // Check if this [type] has been attempted earlier - if it has reaced the maximum // attempt limit, blacklist it - atpr = do_sql_query( // pseudo-code - "SELECT atpid, attempts >= %s FROM openvpn_attempts WHERE %s= '%s'", - get_config(ctx, eDBattempt_types[type].allow_cfg), - eDBattempt_types[type].colname, val); - if( atpr != NULL ) { - // fetch atpid, atpexceeded (attempts >= %s) from SQL query - atpid = strdup_nullsafe(...); - atpexceed = atoi_nullsafe(...); - atpr = NULL; + + /* WORK TO DO -- DO SQL: + "SELECT atpid, attempts >= %q FROM openvpn_attempts WHERE %s = '%q'", + eGet_value(ctx->dbc->config, eDBattempt_types[type].allow_cfg), + eDBattempt_types[type].colname, val + */ + if( /* IF WE FOUND RECORDS */ ) { + atpid = strdup_nullsafe(/* GET atpid FROM SQL RESULT */); + atpexceed = atoi_nullsafe(/* GET "attempts >= %q" FROM SQL RESULT */); + /* FREE SQL RESULT */ // If [type] has reached attempt limit and it is not black listed, black list it if( (atpexceed > 0) && (blid == NULL) ) { eurephia_log(ctx, LOG_WARNING, 0, "%s got BLACKLISTED due to too many failed attempts: %s", eDBattempt_types[type].descr, val); - blr = do_sql_query( // pseudo-code - "INSERT INTO openvpn_blacklist (%s) VALUES ('%s')", - eDBattempt_types[type].colname, val); - if( blr == NULL ) { + /* WORK TO DO -- DO SQL + "INSERT INTO openvpn_blacklist (%s) VALUES ('%q')", + eDBattempt_types[type].colname, val + */ + if( /* IF SQL QUERY FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not blacklist %s (%s)", eDBattempt_types[type].descr, val); } + /* FREE SQL RESULT */ blacklisted = 1; // [type] is blacklisted } free_nullsafe(atpid); @@ -399,72 +418,108 @@ int eDBblacklist_check(eurephiaCTX *ctx, const int type, const char *val) } free_nullsafe(blid); - eurephia_log(ctx, LOG_DEBUG, 10, "Result - function call: eDBblacklist_check(ctx, '%s', '%s') - %i", - eDBattempt_types[type].descr, val, blacklisted); + DEBUG(ctx, 10, "Result - function call: eDBblacklist_check(ctx, '%s', '%s') - %i", + eDBattempt_types[type].descr, val, blacklisted); return blacklisted; } // Register a failed attempt of authentication or IP address has been tried to many times -void eDBregister_attempt(eurephiaCTX *ctx, int type, const char *value) { - dbresult *res; +void eDBregister_attempt(eurephiaCTX *ctx, int type, int mode, const char *value) +{ char *id = NULL, *atmpt_block = NULL, *blid = NULL; + int attempts = 0; - eurephia_log(ctx, LOG_DEBUG, 10, "Function call: eDBregister_attempt(ctx, %s, '%s')", - eDBattempt_types[type].colname, value); + DEBUG(ctx, 10, "Function call: eDBregister_attempt(ctx, %s, %s, '%s')", + eDBattempt_types[type].colname, + (mode == ATTEMPT_RESET ? "ATTEMPT_RESET" : "ATTEMPT_REGISTER"), + value); // // openvpn_attempts // - res = do_sql_query( // pseudo-code - "SELECT atpid, attempts > %s, blid " + /* WORK TO DO -- DO SQL + "SELECT atpid, attempts > %s, blid, attempts " " FROM openvpn_attempts " " LEFT JOIN openvpn_blacklist USING(%s)" - " WHERE %s = '%s'", - get_config(ctx, eDBattempt_types[type].allow_cfg), + " WHERE %s = '%q'", + eGet_value(ctx->dbc->config, eDBattempt_types[type].allow_cfg), eDBattempt_types[type].colname, - eDBattempt_types[type].colname, value); - if( res == NULL ) { + eDBattempt_types[type].colname, value + */ + if( /* IF SQL QUERY FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not look up atpid in openvpn_attempts"); return; } - // fetch atpid, atmpt_block (attempts > %s) and blid from SQL query - id = strdup_nullsafe(...); - atmpt_block = strdup_nullsafe(...); - blid = strdup_nullsafe(...); + attempts = atoi_nullsafe(/* GET attempts FROM SQL RESULT */); + // If we are asked to reset the attempt counter and we do not find any attempts, exit here + if( (mode == ATTEMPT_RESET) && (/* IF NO RECORDS WHERE FOUND OR attempts == 0 */) ) { + /* FREE SQL RESULT */ + return; + } + id = strdup_nullsafe(/* GET atpid FROM SQL RESULT */); + atmpt_block = strdup_nullsafe(/* GET "attempts > %s" FROM SQL RESULT */); + blid = strdup_nullsafe(/* GET blid FROM SQL RESULT */); + + /* FREE SQL RESULT */ - if( id == NULL ) { - // pseudo-code - register the attempt in the openvpn_attempts table if not already registered - res = do_sql_query("INSERT INTO openvpn_attempts (%s, attempts) VALUES ('%s', 1)", - eDBattempt_types[type].colname, value); - } else { - // pseudo-code - update the openvpn_attempts record if it already exists - res = do_sql_query( - "UPDATE openvpn_attempts " - " SET last_attempt = CURRENT_TIMESTAMP, attempts = attempts + 1" - " WHERE atpid = %s", id); + if( (id == NULL) && (mode == ATTEMPT_REGISTER) ) { + // Only insert record when we are in registering mode + + /* WORK TO DO -- DO SQL: + "INSERT INTO openvpn_attempts (%s, attempts) VALUES ('%q', 1)", + eDBattempt_types[type].colname, value); + + Result check comes later ... + */ + } else if( id != NULL ){ + // if a attempt record exists, update it according to mode + switch( mode ) { + case ATTEMPT_RESET: + /* WORK TO DO -- DO SQL: + "UPDATE openvpn_attempts " + " SET attempts = 0 " + " WHERE atpid = '%q'", + id + Result check comes later ... + */ + break; + default: + /* WORK TO DO -- DO SQL: + "UPDATE openvpn_attempts " + " SET last_attempt = CURRENT_TIMESTAMP, attempts = attempts + 1" + " WHERE atpid = '%q'", + id + Result check comes later ... + */ + break; + } } - if( res == NULL ) { + if( /* IF SQL QUERY FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not update openvpn_attempts for %s = %s", eDBattempt_types[type].colname, value); } + /* FREE SQL RESULT */ // If attempts have exceeded attempt limit, blacklist it immediately if not already registered - if( (blid == NULL) && (atmpt_block != NULL) && (atoi_nullsafe(atmpt_block) > 0) ) { + if( (mode == ATTEMPT_REGISTER) + && (blid == NULL) && (atmpt_block != NULL) && (atoi_nullsafe(atmpt_block) > 0) ) { eurephia_log(ctx, LOG_WARNING, 0, "Blacklisting %s due to too many attempts: %s", eDBattempt_types[type].descr, value); - // if we have too many attempts, blacklist it - res = do_sql_query("INSERT INTO openvpn_blacklist (%s) VALUES ('%s')", // pseudo-code - eDBattempt_types[type].colname, value); - if( res == NULL ) { + /* WORK TO DO -- DO SQL: + "INSERT INTO openvpn_blacklist (%s) VALUES ('%q')", + eDBattempt_types[type].colname, value + */ + if( /* IF SQL FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not blacklist %s: %s", eDBattempt_types[type].descr, value); } + /* FREE SQL RESULT */ } free_nullsafe(id); free_nullsafe(atmpt_block); @@ -477,57 +532,67 @@ 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) { - dbresult *res = NULL; - - eurephia_log(ctx, LOG_DEBUG, 10, - "Function call: eDBregister_login(ctx, '%s', %i, %i, '%s','%s','%s','%s','%s')", - skey->sessionkey, certid, uid, proto, remipaddr, remport, vpnipaddr, vpnipmask); + DEBUG(ctx, 10, "Function call: eDBregister_login(ctx, '%s', %i, %i, '%s','%s','%s','%s','%s')", + skey->sessionkey, certid, uid, proto, remipaddr, remport, vpnipaddr, vpnipmask); if( skey->sessionstatus != SESSION_NEW ) { eurephia_log(ctx, LOG_WARNING, 10, "Not a new session, will not register it again"); return 1; } - res = do_sql_query( // pseudo-code - register information into openvpn_lastlog + /* WORK TO DO -- DO SQL: "INSERT INTO openvpn_lastlog (uid, certid, " " protocol, remotehost, remoteport," " vpnipaddr, vpnipmask," - " sessionstatus, sesskey, login) " - "VALUES (%i, %i, '%s','%s','%s','%s','%s', 1,'%s', CURRENT_TIMESTAMP)", - uid, certid, proto, remipaddr, remport, vpnipaddr, vpnipmask, skey->sessionkey); - if( res == NULL ) { + " sessionstatus, sessionkey, login) " + "VALUES (%i, %i, '%q','%q','%q','%q','%q', 1,'%q', CURRENT_TIMESTAMP)", + uid, certid, proto, remipaddr, remport, vpnipaddr, vpnipmask, skey->sessionkey + */ + if( /* IF SQL QUERY FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not insert new session into openvpn_lastlog"); return 0; } + /* FREE SQL RESULT */ skey->sessionstatus = SESSION_REGISTERED; return 1; } // Register the MAC address of the VPN adapter of the user. -int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *skey, const char *macaddr) +int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const char *macaddr) { - dbresult *res = NULL; - eurephia_log(ctx, LOG_DEBUG, 10, - "Function call: eDBregister_vpnmacaddr(ctx, '%s', '%s')", - skey->sessionkey, macaddr); + DEBUG(ctx, 10, "Function call: eDBregister_vpnmacaddr(ctx, '%s', '%s')", + session->sessionkey, macaddr); if( macaddr == NULL ) { eurephia_log(ctx, LOG_CRITICAL, 0, "No MAC address was given to save"); return 0; } - res = do_sql_query(// pseudo-code ... update openvpn_lastlog.sessionstatus and register MAC address - "UPDATE openvpn_lastlog SET sessionstatus = 2, macaddr = '%s' " - " WHERE sesskey = '%s' AND sessionstatus = 1", macaddr, skey->sessionkey); - if( res == NULL ) { + // Register MAC address into history table + /* WORK TO DO -- DO SQL: + "INSERT INTO openvpn_macaddr_history (sessionkey, macaddr) VALUES ('%q','%q')", + session->sessionkey, macaddr + */ + if( /* IF SQL QUERY FAILED */ ) { + eurephia_log(ctx, LOG_CRITICAL, 0, "Failed to log new MAC address for session"); + return 0; + } + /* FREE SQL RESULT */ + + // Update lastlog to reflect last used MAC address for the session + /* WORK TO DO -- DO SQL: + "UPDATE openvpn_lastlog SET sessionstatus = 2, macaddr = '%q' " + " WHERE sessionkey = '%q' AND sessionstatus = 1", macaddr, session->sessionkey); + */ + if( /* IF SQL QUERY FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not update lastlog with new MAC address for session"); return 0; - } + /* FREE SQL RESULT */ // Save the MAC address in the session values register - needed for the destroy session - if( eDBset_session_value(ctx, skey, "macaddr", macaddr) == 0 ) { + if( eDBset_session_value(ctx, session, "macaddr", macaddr) == 0 ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not save MAC address into session variables"); return 0; } @@ -535,38 +600,39 @@ int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *skey, const char * return 1; } + // Register the user as logged out int eDBregister_logout(eurephiaCTX *ctx, eurephiaSESSION *skey, - const char *bytes_sent, const char *bytes_received) + const char *bytes_sent, const char *bytes_received, const char *duration) { - dbresult *res = NULL; + DEBUG(ctx, 10, "Function call: eDBregister_logout(ctx, '%s', %s, %s)", + skey->sessionkey, bytes_sent, bytes_received); - eurephia_log(ctx, LOG_DEBUG, 10, - "Function call: eDBregister_logout(ctx, '%s', %s, %s)", - skey->sessionkey, bytes_sent, bytes_received); - - res = do_sql_query( // pseudo-code ... update openvpn_lastlog + /* WORK TO DO -- DO SQL: "UPDATE openvpn_lastlog " " SET sessionstatus = 3, logout = CURRENT_TIMESTAMP, " - " bytes_sent = '%i', bytes_received = '%i' " - " WHERE sesskey = '%s' AND sessionstatus = 2", - atoi_nullsafe(bytes_sent), atoi_nullsafe(bytes_received), skey->sessionkey); - if( res == NULL ) { + " 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->sessionke + */ + if( /* IF SQL QUERY FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not update lastlog with logout information (%s)", skey->sessionkey); return 0; } + /* FREE SQL RESULT */ skey->sessionstatus = SESSION_LOGGEDOUT; return 1; } + // Retrieve a session key from openvpn_sessionkeys if it is a current session. Session seed is used // as criteria -char *eDBget_sessionkey(eurephiaCTX *ctx, const char *sessionseed) { - dbresult *res = NULL; +char *eDBget_sessionkey_seed(eurephiaCTX *ctx, const char *sessionseed) { char *skey = NULL; - eurephia_log(ctx, LOG_DEBUG, 10, "eDBget_sessionkey(ctx, '%s')", sessionseed); + DEBUG(ctx, 10, "eDBget_sessionkey(ctx, '%s')", sessionseed); if( sessionseed == NULL ) { eurephia_log(ctx, LOG_CRITICAL, 1, @@ -574,96 +640,120 @@ char *eDBget_sessionkey(eurephiaCTX *ctx, const char *sessionseed) { return NULL; } - // pseudo-code - retrieve the sessionkey from the openvpn_sessionkeys based on sessionseed - res = do_sql_query("SELECT sessionkey FROM openvpn_sessionkeys WHERE sessionseed = '%s'", - sessionseed); - if( res == NULL ) { + /* WORK TO DO -- DO SQL: + "SELECT sessionkey FROM openvpn_sessionkeys WHERE sessionseed = '%q'", + sessionseed + */ + if( /* IF SQL QUERY FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0,"Could not retrieve sessionkey from openvpn_sessionkeys (%s)", sessionseed); return NULL; } - if( get_numtuples(res) == 1 ) { // pseudo-code - retrieve the sessionkey from the SQL query - skey = strdup_nullsafe(...); + if( /* CHECK THAT WE ONLY GOT 1 RECORD */ ) { + skey = strdup_nullsafe(/* GET sessionkey FIELD FROM SQL RESULT */); } else { skey = NULL; } + /* FREE SQL RESULT */ + return skey; +} + +char *eDBget_sessionkey_macaddr(eurephiaCTX *ctx, const char *macaddr) { + char *skey = NULL; + + // Find sessionkey from MAC address + /* WORK TO DO -- DO SQL: + "SELECT sessionkey " + " FROM openvpn_sessions " + " WHERE datakey = 'macaddr' AND dataval = '%q'", + macaddr + */ + if( /* IF SQL QUERY FAILED */ ) { + eurephia_log(ctx, LOG_CRITICAL, 0, + "Could not remove session from database (MAC addr: %s)", macaddr); + return 0; + } + skey = strdup_nullsafe(/* GET sessionkey FROM SQL RESULT */); + /* FREE SQL RESULT */ + return skey; } // Function returns true(1) if session key is unique int eDBcheck_sessionkey_uniqueness(eurephiaCTX *ctx, const char *seskey) { - dbresult *res; int uniq = 0; - eurephia_log(ctx, LOG_DEBUG, 10, "eDBcheck_sessionkey_uniqueness(ctx, '%s')", seskey); + DEBUG(ctx, 10, "eDBcheck_sessionkey_uniqueness(ctx, '%s')", seskey); if( seskey == NULL ) { eurephia_log(ctx, LOG_CRITICAL, 1, "eDBcheck_sessionkey_uniqness: Invalid session key given"); return 0; } - - // pseudo-code - check if this sessionkey already exists or not - res = do_sql_query("SELECT count(sesskey) = 0 FROM openvpn_lastlog WHERE sesskey = '%s'", seskey); - if( res == NULL ) { + + /* WORK TO DO -- DO SQL: + "SELECT count(sessionkey) = 0 FROM openvpn_lastlog WHERE sessionkey = '%q'", + seskey + */ + if( /* IF SQL QUERY FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, "eDBcheck_sessionkey_uniqness: Could not check uniqueness of sessionkey"); return 0; } - - // fetch the result from the SQL query - uniq = atoi_nullsafe(...); + uniq = atoi_nullsafe(/* GET "count(sessionkey) == 0" (as integer bool - 1 == true) FROM SQL RESULT */); + /* FREE SQL RESULT */ return uniq; } // register a link between a short-term session seed and a long-term session key int eDBregister_sessionkey(eurephiaCTX *ctx, const char *seed, const char *seskey) { - dbresult *res; - eurephia_log(ctx, LOG_DEBUG, 10, "eDBregister_sessionkey(ctx, '%s', '%s')", seed, seskey); + DEBUG(ctx, 10, "eDBregister_sessionkey(ctx, '%s', '%s')", seed, seskey); if( (seed == NULL) || (seskey == NULL) ) { eurephia_log(ctx, LOG_CRITICAL, 1, "eDBregister_sessionkey: Invalid session seed or session key given"); return 0; } - res = do_sql_query( // pseudo-code - register the session key and seed in openvpn_sessionkeys - "INSERT INTO openvpn_sessionkeys (sessionseed, sessionkey) VALUES('%s','%s')", - seed, seskey); - if( res == NULL ) { + /* WORK TO DO -- DO SQL: + "INSERT INTO openvpn_sessionkeys (sessionseed, sessionkey) VALUES('%q','%q')", + seed, seskey + */ + if( /* IF SQL QUERY FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, "eDBregister_sessionkey: Error registering sessionkey into openvpn_sessionkeys"); return 0; } - + /* FREE SQL RESULT */ return 1; } // remove a session seed/session key link from openvpn_sessionkeys int eDBremove_sessionkey(eurephiaCTX *ctx, const char *seskey) { - dbresult *res; - eurephia_log(ctx, LOG_DEBUG, 10, "eDBremove_sessionkey(ctx, '%s')", seskey); + DEBUG(ctx, 10, "eDBremove_sessionkey(ctx, '%s')", seskey); if( seskey == NULL ) { eurephia_log(ctx, LOG_CRITICAL, 1, "eDBremove_sessionkey: Invalid session key given"); return 0; } - res = sqlite_query(ctx, "DELETE FROM openvpn_sessionkeys WHERE sessionkey = '%s'", seskey); - if( res == NULL ) { + /* WORK TO DO -- DO SQL: + "DELETE FROM openvpn_sessionkeys WHERE sessionkey = '%q'", + seskey + */ + if( /* IF SQL QUERY FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, "eDBremove_sessionkey: Error removing sessionkey from openvpn_sessionkeys"); return 0; } - sqlite_free_results(res); + /* FREE SQL RESULT */ return 1; } // Load session values stored in the database into a eurephiaVALUES struct (session values) eurephiaVALUES *eDBload_sessiondata(eurephiaCTX *ctx, const char *sesskey) { - dbresult *res = NULL; eurephiaVALUES *sessvals = NULL; int i; @@ -671,17 +761,21 @@ eurephiaVALUES *eDBload_sessiondata(eurephiaCTX *ctx, const char *sesskey) { return NULL; } - eurephia_log(ctx, LOG_DEBUG, 10, "Function call: eDBload_sessiondata(ctx, '%s')", sesskey); + DEBUG(ctx, 10, "Function call: eDBload_sessiondata(ctx, '%s')", sesskey); + + sessvals = eCreate_value_space(ctx, 10); - res = sqlite_query(ctx, "SELECT datakey, dataval FROM openvpn_sessions WHERE sesskey = '%s'", - sesskey); - if( (res != NULL) || (sqlite_get_numtuples(res) > 0) ) { - for( i = 0; i < sqlite_get_numtuples(res); i++ ) { - sessvals = eDBadd_session_value(sessvals, - sqlite_get_value(res, i, 0), - sqlite_get_value(res, i, 1)); + /* WORK TO DO -- DO SQL: + "SELECT datakey, dataval FROM openvpn_sessions WHERE sessionkey = '%q'", + sesskey + */ + if( /* IF WE GOT RECORDS IN THE QUERY */ ) { + for( /* LOOP THROUGH ALL RECORDS */ ) { + eAdd_value(ctx, sessvals, + /* GET datakey FROM SQL RESULT */, + /* GET dataval FROM SQL RESULT */); } - sqlite_free_results(res); + /* FREE SQL RESULT */ } else { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not load session values for session '%s'", sesskey); @@ -692,49 +786,54 @@ eurephiaVALUES *eDBload_sessiondata(eurephiaCTX *ctx, const char *sesskey) { // Store a new, update or delete a sessionvalue in the database -int eDBstore_session_value(eurephiaCTX *ctx, eurephiaSESSION *skey, int mode, const char *key, const char *val) +int eDBstore_session_value(eurephiaCTX *ctx, eurephiaSESSION *session, int mode, const char *key, const char *val) { - dbresult *res = NULL; - if( skey == NULL ) { - eurephia_log(ctx, LOG_DEBUG, 10, - "Function call failed to eDBstore_session_value(ctx, ...): Non-existing session key"); + if( session == NULL ) { + DEBUG(ctx, 10, + "Function call failed to eDBstore_session_value(ctx, ...): Non-existing session key"); return 0; } - eurephia_log(ctx, LOG_DEBUG, 10, "Function call: eDBstore_session_value(ctx, '%s', %i, '%s', '%s')", - skey->sessionkey, mode, key, val); + DEBUG(ctx, 10, "Function call: eDBstore_session_value(ctx, '%s', %i, '%s', '%s')", + session->sessionkey, mode, key, val); switch( mode ) { case SESSVAL_NEW: - res = sqlite_query(ctx, - "INSERT INTO openvpn_sessions (sesskey, datakey, dataval) " - "VALUES ('%s','%s','%s')", skey->sessionkey, key, val); - if( res == NULL ) { + /* WORK TO DO -- DO SQL: + "INSERT INTO openvpn_sessions (sessionkey, datakey, dataval) " + "VALUES ('%q','%q','%q')", + session->sessionkey, key, val + */ + if( /* IF SQL QUERY FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not register new session variable into database: [%s] %s = %s", - skey->sessionkey, key, val); + session->sessionkey, key, val); return 0; } break; case SESSVAL_UPDATE: - res = sqlite_query(ctx, - "UPDATE openvpn_sessions SET dataval = '%s' " - " WHERE sesskey = '%s' AND datakey = '%s'", val, skey->sessionkey, key); - if( res == NULL ) { + /* WORK TO DO -- DO SQL: + "UPDATE openvpn_sessions SET dataval = '%q' " + " WHERE sessionkey = '%q' AND datakey = '%q'", + val, session->sessionkey, key + */ + if( /* IF SQL QUERY FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not update session variable: [%s] %s = %s ", - skey->sessionkey, key, val); + session->sessionkey, key, val); return 0; } break; case SESSVAL_DELETE: - res = sqlite_query(ctx, "DELETE FROM openvpn_sessions WHERE sesskey = '%s' AND datakey = '%s'", - skey->sessionkey, key); - if( res == NULL ) { + /* WORK TO DO -- DO SQL: + "DELETE FROM openvpn_sessions WHERE sessionkey = '%q' AND datakey = '%q'", + session->sessionkey, key + */ + if( /* IF SQL QUERY FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not delete session variable: [%s] %s", - skey->sessionkey, key); + session->sessionkey, key); return 0; } break; @@ -743,69 +842,77 @@ int eDBstore_session_value(eurephiaCTX *ctx, eurephiaSESSION *skey, int mode, co eurephia_log(ctx, LOG_FATAL, 0, "Unknown eDBstore_session_value mode '%i'", mode); return 1; } - sqlite_free_results(res); + /* FREE SQL RESULT */ return 1; } // Delete session information from openvpn_sessions and update openvpn_lastlog with status -int eDBdestroy_session(eurephiaCTX *ctx, const char *macaddr) -{ - dbresult *res = NULL; - char *skey = NULL; - - eurephia_log(ctx, LOG_DEBUG, 10, "Function call: eDBdestroy_session(ctx, '%s')", macaddr); - - // Find sessionkey from MAC address - res = sqlite_query(ctx, - "SELECT sesskey " - " FROM openvpn_sessions " - " WHERE datakey = 'macaddr' AND dataval = '%'s'", macaddr); - if( res == NULL ) { - eurephia_log(ctx, LOG_CRITICAL, 0, - "Could not remove session from database (MAC addr: %s)", macaddr); - return 0; - } +int eDBdestroy_session(eurephiaCTX *ctx, eurephiaSESSION *session) { - skey = strdup_nullsafe(sqlite_get_value(res, 0, 0)); - sqlite_free_results(res); + DEBUG(ctx, 10, "Function call: eDBdestroy_session(ctx, '%s')", session->sessionkey); - if( skey == NULL ) { - eurephia_log(ctx, LOG_INFO, 0, - "Could not find any sessions connected to MAC addr '%s')", macaddr); + if( (session == NULL) || (session->sessionkey == NULL) ) { + eurephia_log(ctx, LOG_ERROR, 1, "No active session given to be destroyed"); return 1; } // Update session status - res = sqlite_query(ctx, + /* WORK TO DO -- SQL "UPDATE openvpn_lastlog " " SET sessionstatus = 4, session_deleted = CURRENT_TIMESTAMP " - " WHERE sesskey = '%s' AND sessionstatus = 3", skey); - if( res == NULL ) { + " WHERE sessionkey = '%q' AND sessionstatus = 3", + session->sessionkey + */ + if( /* IF SQL QUERY FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, - "Could not update session status in lastlog (%s/%s))", skey, macaddr); - free_nullsafe(skey); + "Could not update session status in lastlog (%s))", session->sessionkey); return 0; } - sqlite_free_results(res); + /* FREE SQL RESULT */ // Delete session variables - res = sqlite_query(ctx, "DELETE FROM openvpn_sessions WHERE sesskey = '%s'", skey); - if( res == NULL ) { + /* WORK TO DO -- DO SQL: + "DELETE FROM openvpn_sessions WHERE sessionkey = '%q'", + session->sessionkey + */ + if( /* IF SQL QUERY FAILED */ ) { eurephia_log(ctx, LOG_CRITICAL, 0, - "Could not delete session variables (%s/%s))", skey, macaddr); - free_nullsafe(skey); + "Could not delete session variables (%s))", session->sessionkey); return 0; } - sqlite_free_results(res); + /* FREE SQL RESULT */ // Remove the sessionkey from openvpn_sessions - if( eDBremove_sessionkey(ctx, skey) == 0 ) { - free_nullsafe(skey); + if( eDBremove_sessionkey(ctx, session->sessionkey) == 0 ) { return 0; } - - free_nullsafe(skey); return 1; } + +char *eDBget_firewall_profile(eurephiaCTX *ctx, eurephiaSESSION *session) +{ + char *ret = NULL; + + DEBUG(ctx, 10, "Function call: eDBget_firewall_profile(ctx, {session}'%s')", + session->sessionkey); + + /* WORK TO DO -- DO SQL: + "SELECT fw_profile " + " FROM openvpn_lastlog " + " JOIN openvpn_usercerts USING(certid, uid)" + " JOIN openvpn_accesses USING(accessprofile)" + " WHERE sessionkey = '%q'", + session->sessionkey + */ + if( /* IF SQL QUERY FAILED */ ) { + eurephia_log(ctx, LOG_CRITICAL, 0, "Could not retrieve firewall profile for session '%s'", + session->sessionkey); + return NULL; + } + ret = strdup_nullsafe(/* GET fw_profile FROM SQL RESULT */); + /* FREE SQL RESULT */ + return ret; +} + |