summaryrefslogtreecommitdiffstats
path: root/database/sqlite/edb-sqlite.c
diff options
context:
space:
mode:
Diffstat (limited to 'database/sqlite/edb-sqlite.c')
-rw-r--r--database/sqlite/edb-sqlite.c900
1 files changed, 900 insertions, 0 deletions
diff --git a/database/sqlite/edb-sqlite.c b/database/sqlite/edb-sqlite.c
new file mode 100644
index 0000000..bcbf3ec
--- /dev/null
+++ b/database/sqlite/edb-sqlite.c
@@ -0,0 +1,900 @@
+/* eurephia-sqlite.c -- Main driver for eurephia authentication plugin for OpenVPN
+ * This is the SQLite database driver
+ *
+ * GPLv2 - Copyright (C) 2008 David Sommerseth <dazo@users.sourceforge.net>
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU General Public License
+ * as published by the Free Software Foundation; version 2
+ * of the License.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * GNU General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program; if not, write to the Free Software
+ * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
+ *
+ */
+
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+#include <dlfcn.h>
+
+#include <sqlite3.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>
+#include "sqlite.h"
+
+#ifdef MEMWATCH
+#include <memwatch.h>
+#endif
+
+
+#define DRIVERVERSION "1.0"
+#define DRIVERAPIVERSION 1
+
+// Mapping table - mapping attempt types from .... to sqlite table fields
+typedef struct {
+ char *colname;
+ char *allow_cfg;
+ char *descr;
+} eDBattempt_types_t;
+
+
+static const eDBattempt_types_t eDBattempt_types[] = {
+ {NULL, NULL, NULL},
+ {"remoteip\0", "allow_ipaddr_attempts\0", "IP Address\0"},
+ {"digest\0", "allow_cert_attempts\0", "Certificate\0"},
+ {"username\0", "allow_username_attempts\0", "Username\0"},
+ {NULL, NULL, NULL}
+};
+
+/*
+ * Driver info
+ */
+
+const char *eDB_DriverVersion() {
+ return "eurephiadb-sqlite (v"DRIVERVERSION") David Sommerseth 2008 (C) GPLv2";
+}
+
+
+int eDB_DriverAPIVersion() {
+ return DRIVERAPIVERSION;
+}
+
+
+/*
+ * local functions
+ */
+
+
+// Function for simplifying update of openvpn_blacklist
+void update_attempts(eurephiaCTX *ctx, const char *blid) {
+ dbresult *res = NULL;
+
+ if( blid != NULL ) {
+ res = sqlite_query(ctx,
+ "UPDATE openvpn_blacklist "
+ " SET last_accessed = CURRENT_TIMESTAMP WHERE blid = %q", blid);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_CRITICAL, 0,
+ "Could not update openvpn_blacklist.last_accessed for blid=%s", blid);
+ }
+ sqlite_free_results(res);
+ }
+}
+
+/*
+ * Public driver functions
+ */
+
+// Connect to the database ... connection is stored in the eurephiaCTX context
+int eDBconnect(eurephiaCTX *ctx, const int argc, const char **argv)
+{
+ eDBconn *dbc = NULL;
+ dbresult *res = NULL;
+ int rc;
+
+ DEBUG(ctx, 20, "Function call: eDBconnect(ctx, %i, '%s')", argc, argv[0]);
+
+ if( (argc != 1) || (argv[0] == NULL) || (strlen(argv[0]) < 1) ) {
+ eurephia_log(ctx, LOG_PANIC, 0, "Wrong parameters to eurephiadb-sqlite. Cannot open database.");
+ return 0;
+ }
+
+ // Connect to the database
+ dbc = (eDBconn *) malloc(sizeof(eDBconn)+2);
+ memset(dbc, 1, sizeof(eDBconn)+2);
+ dbc->dbname = strdup(argv[0]);
+
+ eurephia_log(ctx, LOG_INFO, 1, "Opening database '%s'", dbc->dbname);
+
+ rc = sqlite3_open(argv[0], (void *) &dbc->dbhandle);
+ if( rc ) {
+ eurephia_log(ctx, LOG_PANIC, 0, "Could not open database '%s'", dbc->dbname);
+ free_nullsafe(dbc->dbname);
+ free_nullsafe(dbc);
+ return 0;
+ }
+
+ dbc->config = NULL;
+ ctx->dbc = dbc;
+
+ // Load configuration parameters into memory
+ eurephia_log(ctx, LOG_INFO, 1, "Reading config from database (openvpn_config)");
+ res = sqlite_query(ctx, "SELECT datakey, dataval FROM openvpn_config");
+ if( res != NULL ) {
+ int i = 0;
+ eurephiaVALUES *cfg = NULL;
+
+ cfg = eCreate_value_space(ctx, 11);
+ if( cfg == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0, "Could not allocate memory for config variables");
+ sqlite_free_results(res);
+ return 0;
+ }
+ for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
+ eAdd_value(ctx, cfg, sqlite_get_value(res, i, 0), sqlite_get_value(res, i, 1));
+ }
+ sqlite_free_results(res);
+ ctx->dbc->config = cfg;
+ }
+ return 1;
+}
+
+// Disconnect from the database
+void eDBdisconnect(eurephiaCTX *ctx)
+{
+ eDBconn *dbc = NULL;
+
+ DEBUG(ctx, 20, "Function call: eDBdisconnect(ctx)");
+
+ if( ctx->dbc == NULL ) {
+ eurephia_log(ctx, LOG_WARNING, 0, "Database not open, cannot close database.");
+ return;
+ }
+
+ dbc = ctx->dbc;
+ eurephia_log(ctx, LOG_INFO, 1, "Closing database '%s'", dbc->dbname);
+
+ // Close database connection
+ sqlite3_close((sqlite3 *) dbc->dbhandle);
+ free_nullsafe(dbc->dbname);
+ dbc->dbhandle = NULL;
+
+ // Free up config memory
+ eFree_values(ctx, dbc->config);
+ free_nullsafe(dbc);
+ ctx->dbc = NULL;
+}
+
+
+// Authenticate certificate ... returns certid (certificate ID) on success,
+// 0 if not found or -1 if certificate is blacklisted
+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;
+
+ DEBUG(ctx, 20, "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 = sqlite_query(ctx,
+ "SELECT cert.certid, blid "
+ " FROM openvpn_certificates cert"
+ " LEFT JOIN openvpn_blacklist bl USING(digest)"
+ " WHERE organisation='%q' AND common_name='%q' "
+ " AND email='%q' AND depth='%q' AND cert.digest='%q'%c",
+ org, cname, email, depth, digest, 0);
+
+ if( res != NULL ) {
+ certid = atoi_nullsafe(sqlite_get_value(res, 0, 0));
+ blid = strdup_nullsafe(sqlite_get_value(res, 0, 1));
+ sqlite_free_results(res);
+
+ // Check if the certificate is blacklisted or not. blid != NULL when blacklisted
+ if( blid != NULL ) {
+ // If the certificate or IP is blacklisted, update status and deny access.
+ eurephia_log(ctx, LOG_WARNING, 0,
+ "Attempt with BLACKLISTED certificate (certid %i)", certid);
+ update_attempts(ctx, blid);
+ certid = -1;
+ }
+ free_nullsafe(blid);
+ } else {
+ eurephia_log(ctx, LOG_FATAL, 0, "Could not look up certificate information");
+ }
+
+ DEBUG(ctx, 20, "Result function call: eDBauth_TLS(ctx, '%s', '%s', '%s', '%s', %s) - %i",
+ org, cname, email, digest, depth, certid);
+
+ return certid;
+}
+
+// Authenticate user, using username, password and certid as authentication parameters
+// 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;
+
+ DEBUG(ctx, 20, "Function call: eDBauth_user(ctx, %i, '%s','xxxxxxxx')", certid, username);
+
+
+ // Generate SHA1 hash of password, used for password auth
+ crpwd = passwdhash(passwd);
+
+ res = sqlite_query(ctx,
+ "SELECT uicid, ou.uid, activated, deactivated, bl1.blid, bl2.blid, "
+ " (password = '%s') AS pwdok"
+ " FROM openvpn_users ou"
+ " JOIN openvpn_usercerts uc USING(uid) "
+ " LEFT JOIN openvpn_blacklist bl1 ON( ou.username = bl1.username) "
+ " 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 = '%q'",
+ crpwd, certid, username);
+ free_nullsafe(crpwd);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0,
+ "Could not lookup user in database (certid %i, username '%s'", certid, username);
+ return 0;
+ }
+
+ if( sqlite_get_numtuples(res) == 1 ) {
+ uid = atoi_nullsafe(sqlite_get_value(res, 0, 1));
+ activated = sqlite_get_value(res, 0, 2);
+ deactivated = sqlite_get_value(res, 0, 3);
+ blid_uname = sqlite_get_value(res, 0, 4);
+ blid_cert = sqlite_get_value(res, 0, 5);
+ pwdok = atoi_nullsafe(sqlite_get_value(res, 0, 6));
+
+ if( blid_uname != NULL ) {
+ eurephia_log(ctx, LOG_WARNING, 0, "User account is BLACKLISTED (uid: %i, %s)",
+ uid, username);
+ uicid = -1;
+ } else if( blid_cert != NULL ) {
+ eurephia_log(ctx, LOG_WARNING, 0,
+ "User account linked with a BLACKLISTED certificate "
+ "(uid: %i, %s) - certid: %s",
+ uid, username, certid);
+ uicid = -1;
+ } else if( activated == NULL ) {
+ eurephia_log(ctx, LOG_WARNING, 0, "User account is not activated (uid: %i, %s)",
+ uid, username);
+ uicid = -1;
+ } else if( deactivated != NULL ) {
+ eurephia_log(ctx, LOG_WARNING, 0, "User account is deactivated (uid: %i, %s)",
+ uid, username);
+ uicid = -1;
+ } else if( pwdok != 1 ) {
+ eurephia_log(ctx, LOG_WARNING, 0,"Authentication failed for user '%s'. Wrong password.",
+ username);
+ uicid = -1;
+
+ } else {
+ uicid = atoi_nullsafe(sqlite_get_value(res, 0, 0));
+ }
+ } else {
+ eurephia_log(ctx, LOG_WARNING, 0, "Authentication failed for user '%s'. "
+ "Could not find user or user-certificate link.", username);
+ uicid = 0;
+ }
+ sqlite_free_results(res);
+
+ DEBUG(ctx, 20, "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)
+{
+ dbresult *res = NULL;
+ int ret = 0;
+
+ DEBUG(ctx, 20, "Function call: eDBget_uid(ctx, %i, '%s')", certid, username);
+
+ res = sqlite_query(ctx,
+ "SELECT uid "
+ " FROM openvpn_usercerts "
+ " JOIN openvpn_users USING (uid) "
+ " WHERE certid = '%i' AND username = '%q'",
+ certid, username);
+ if( (res == NULL) || (sqlite_get_numtuples(res) != 1) ) {
+ eurephia_log(ctx, LOG_FATAL, 0, "Could not lookup userid for user '%s'", username);
+ ret = -1;
+ } else {
+ ret = atoi_nullsafe(sqlite_get_value(res, 0, 0));
+ }
+ sqlite_free_results(res);
+
+ 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;
+
+ DEBUG(ctx, 20, "Function call: eDBblacklist_check(ctx, '%s', '%s')",
+ eDBattempt_types[type].descr, val);
+
+ blr = sqlite_query(ctx, "SELECT blid FROM openvpn_blacklist WHERE %s = '%q'",
+ eDBattempt_types[type].colname, val);
+ if( blr != NULL ) {
+ blid = strdup_nullsafe(sqlite_get_value(blr, 0, 0));
+ sqlite_free_results(blr);
+ blr = NULL;
+
+ if( blid != NULL ) {
+ eurephia_log(ctx, LOG_WARNING, 0, "Attempt from blacklisted %s: %s",
+ eDBattempt_types[type].descr, val);
+ blacklisted = 1; // [type] is blacklisted
+ }
+ // Update attempt information
+ update_attempts(ctx, blid);
+ } else {
+ eurephia_log(ctx, LOG_FATAL, 0, "Quering openvpn_blacklist for blacklisted %s failed",
+ eDBattempt_types[type].descr);
+ }
+
+ if( blacklisted == 0 ) {
+ // Check if this [type] has been attempted earlier - if it has reaced the maximum
+ // attempt limit, blacklist it
+ atpr = sqlite_query(ctx,
+ "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( atpr != NULL ) {
+ atpid = strdup_nullsafe(sqlite_get_value(atpr, 0, 0));
+ atpexceed = atoi_nullsafe(sqlite_get_value(atpr, 0, 1));
+ sqlite_free_results(atpr);
+ atpr = NULL;
+
+ // 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 = sqlite_query(ctx,
+ "INSERT INTO openvpn_blacklist (%s) VALUES ('%q')",
+ eDBattempt_types[type].colname, val);
+ if( blr == NULL ) {
+ eurephia_log(ctx, LOG_CRITICAL, 0,
+ "Could not blacklist %s (%s)",
+ eDBattempt_types[type].descr, val);
+ }
+ sqlite_free_results(blr);
+ blacklisted = 1; // [type] is blacklisted
+ }
+ free_nullsafe(atpid);
+ } else {
+ eurephia_log(ctx, LOG_CRITICAL, 0, "Quering openvpn_attempts for blacklisted %s failed",
+ eDBattempt_types[type].descr);
+ }
+ free_nullsafe(atpr);
+ }
+ free_nullsafe(blid);
+
+ DEBUG(ctx, 20, "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, int mode, const char *value) {
+ dbresult *res;
+ char *id = NULL, *atmpt_block = NULL, *blid = NULL;
+ int attempts = 0;
+
+ DEBUG(ctx, 20, "Function call: eDBregister_attempt(ctx, %s, %s, '%s')",
+ eDBattempt_types[type].colname,
+ (mode == ATTEMPT_RESET ? "ATTEMPT_RESET" : "ATTEMPT_REGISTER"),
+ value);
+
+ //
+ // openvpn_attempts
+ //
+ res = sqlite_query(ctx,
+ "SELECT atpid, attempts > %s, blid, attempts "
+ " FROM openvpn_attempts "
+ " LEFT JOIN openvpn_blacklist USING(%s)"
+ " 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 ) {
+ eurephia_log(ctx, LOG_FATAL, 0, "Could not look up atpid in openvpn_attempts");
+ return;
+ }
+
+ attempts = atoi_nullsafe(sqlite_get_value(res, 0, 3));
+ // If we are asked to reset the attempt counter and we do not find any attempts, exit here
+ if( (mode == ATTEMPT_RESET) && ((sqlite_get_numtuples(res) == 0) || (attempts == 0))) {
+ sqlite_free_results(res);
+ return;
+ }
+
+ id = strdup_nullsafe(sqlite_get_value(res, 0, 0));
+ atmpt_block = strdup_nullsafe(sqlite_get_value(res, 0, 1));
+ blid = strdup_nullsafe(sqlite_get_value(res, 0, 2));
+
+ sqlite_free_results(res);
+
+ if( (id == NULL) && (mode == ATTEMPT_REGISTER) ) {
+ // Only insert record when we are in registering mode
+ res = sqlite_query(ctx, "INSERT INTO openvpn_attempts (%s, attempts) VALUES ('%q', 1)",
+ eDBattempt_types[type].colname, value);
+ } else if( id != NULL ){
+ // if a attempt record exists, update it according to mode
+ switch( mode ) {
+ case ATTEMPT_RESET:
+ res = sqlite_query(ctx,
+ "UPDATE openvpn_attempts "
+ " SET attempts = 0 "
+ " WHERE atpid = '%q'", id);
+ break;
+ default:
+ res = sqlite_query(ctx,
+ "UPDATE openvpn_attempts "
+ " SET last_attempt = CURRENT_TIMESTAMP, attempts = attempts + 1"
+ " WHERE atpid = '%q'", id);
+ break;
+ }
+ }
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_CRITICAL, 0,
+ "Could not update openvpn_attempts for %s = %s",
+ eDBattempt_types[type].colname, value);
+ }
+ sqlite_free_results(res);
+
+ // If attempts have exceeded attempt limit, blacklist it immediately if not already registered
+ 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);
+ res = sqlite_query(ctx, "INSERT INTO openvpn_blacklist (%s) VALUES ('%q')",
+ eDBattempt_types[type].colname, value);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_CRITICAL, 0,
+ "Could not blacklist %s: %s", eDBattempt_types[type].descr, value);
+ }
+ sqlite_free_results(res);
+ }
+ free_nullsafe(id);
+ free_nullsafe(atmpt_block);
+ free_nullsafe(blid);
+}
+
+
+// Register a successful authentication
+int eDBregister_login(eurephiaCTX *ctx, eurephiaSESSION *skey, const int certid, const int uid,
+ const char *proto, const char *remipaddr, const char *remport,
+ const char *vpnipaddr, const char *vpnipmask)
+{
+ dbresult *res = 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);
+
+ if( skey->sessionstatus != SESSION_NEW ) {
+ eurephia_log(ctx, LOG_ERROR, 5, "Not a new session, will not register it again");
+ return 1;
+ }
+
+ res = sqlite_query(ctx,
+ "INSERT INTO openvpn_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->sessionkey);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0, "Could not insert new session into openvpn_lastlog");
+ return 0;
+ }
+ sqlite_free_results(res);
+ skey->sessionstatus = SESSION_REGISTERED;
+ return 1;
+}
+
+// Register the MAC address of the VPN adapter of the user.
+int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const char *macaddr)
+{
+ dbresult *res = NULL;
+
+ DEBUG(ctx, 20, "Function call: eDBregister_vpnmacaddr(ctx, '%s', '%s')",
+ session->sessionkey, macaddr);
+
+ if( macaddr == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0, "No MAC address was given to save");
+ return 0;
+ }
+
+ // Register MAC address into history table
+ res = sqlite_query(ctx, "INSERT INTO openvpn_macaddr_history (sessionkey, macaddr) VALUES ('%q','%q')",
+ session->sessionkey, macaddr);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0, "Failed to log new MAC address for session");
+ return 0;
+ }
+ sqlite_free_results(res);
+
+ // Update lastlog to reflect last used MAC address for the session
+ res = sqlite_query(ctx,
+ "UPDATE openvpn_lastlog SET sessionstatus = 2, macaddr = '%q' "
+ " WHERE sessionkey = '%q' AND sessionstatus = 1", macaddr, session->sessionkey);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0, "Could not update lastlog with new MAC address for session");
+ return 0;
+
+ }
+ sqlite_free_results(res);
+
+ // Save the MAC address in the session values register - needed for the destroy session
+ if( eDBset_session_value(ctx, session, "macaddr", macaddr) == 0 ) {
+ eurephia_log(ctx, LOG_FATAL, 0, "Could not save MAC address into session variables");
+ return 0;
+ }
+
+ 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 *duration)
+{
+ dbresult *res = NULL;
+
+ DEBUG(ctx, 20, "Function call: eDBregister_logout(ctx, '%s', %s, %s)",
+ skey->sessionkey, bytes_sent, bytes_received);
+
+ res = sqlite_query(ctx,
+ "UPDATE openvpn_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);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0, "Could not update lastlog with logout information (%s)",
+ skey->sessionkey);
+ return 0;
+ }
+ sqlite_free_results(res);
+ 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_seed(eurephiaCTX *ctx, const char *sessionseed) {
+ dbresult *res = NULL;
+ char *skey = NULL;
+
+ DEBUG(ctx, 20, "eDBget_sessionkey(ctx, '%s')", sessionseed);
+
+ if( sessionseed == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 1,
+ "eDBget_sessionkey: No session seed given - cannot locate sessionkey");
+ return NULL;
+ }
+ res = sqlite_query(ctx,
+ "SELECT sessionkey "
+ " FROM openvpn_sessionkeys "
+ " JOIN openvpn_lastlog USING (sessionkey)"
+ " WHERE sessionstatus IN (1,2)"
+ " AND sessionseed = '%q'",
+ sessionseed);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0,"Could not retrieve sessionkey from openvpn_sessionkeys (%s)",
+ sessionseed);
+ return NULL;
+ }
+ if( sqlite_get_numtuples(res) == 1 ) {
+ skey = strdup_nullsafe(sqlite_get_value(res, 0, 0));
+ } else {
+ skey = NULL;
+ }
+ sqlite_free_results(res);
+ return skey;
+}
+
+char *eDBget_sessionkey_macaddr(eurephiaCTX *ctx, const char *macaddr) {
+ dbresult *res = NULL;
+ char *skey = NULL;
+
+ // Find sessionkey from MAC address
+ res = sqlite_query(ctx,
+ "SELECT sessionkey "
+ " FROM openvpn_sessions "
+ " JOIN openvpn_lastlog USING (sessionkey)"
+ " WHERE sessionstatus = 3 "
+ " AND datakey = 'macaddr'"
+ " AND dataval = '%q'", macaddr);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0,
+ "Could not remove session from database (MAC addr: %s)", macaddr);
+ return 0;
+ }
+ skey = strdup_nullsafe(sqlite_get_value(res, 0, 0));
+ sqlite_free_results(res);
+
+ 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;
+
+ 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");
+ return 0;
+ }
+
+ res = sqlite_query(ctx,
+ "SELECT count(sessionkey) = 0 "
+ "FROM openvpn_lastlog WHERE sessionkey = '%q'", seskey);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0,
+ "eDBcheck_sessionkey_uniqness: Could not check uniqueness of sessionkey");
+ return 0;
+ }
+ uniq = atoi_nullsafe(sqlite_get_value(res, 0, 0));
+ sqlite_free_results(res);
+
+ 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;
+
+ DEBUG(ctx, 20, "eDBregister_sessionkey(ctx, '%s', '%s')", seed, seskey);
+ if( (seed == NULL) || (seskey == NULL) ) {
+ eurephia_log(ctx, LOG_FATAL, 1,
+ "eDBregister_sessionkey: Invalid session seed or session key given");
+ return 0;
+ }
+
+ res = sqlite_query(ctx,
+ "INSERT INTO openvpn_sessionkeys (sessionseed, sessionkey) VALUES('%q','%q')",
+ seed, seskey);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0,
+ "eDBregister_sessionkey: Error registering sessionkey into openvpn_sessionkeys");
+ return 0;
+ }
+ sqlite_free_results(res);
+ return 1;
+}
+
+// remove a session seed/session key link from openvpn_sessionkeys
+int eDBremove_sessionkey(eurephiaCTX *ctx, const char *seskey) {
+ dbresult *res;
+
+ DEBUG(ctx, 20, "eDBremove_sessionkey(ctx, '%s')", seskey);
+ if( seskey == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 1,
+ "eDBremove_sessionkey: Invalid session key given");
+ return 0;
+ }
+
+ res = sqlite_query(ctx, "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");
+ return 0;
+ }
+ sqlite_free_results(res);
+ 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;
+
+ if( (ctx == NULL) || (sesskey == NULL) ) {
+ return NULL;
+ }
+
+ DEBUG(ctx, 20, "Function call: eDBload_sessiondata(ctx, '%s')", sesskey);
+
+ sessvals = eCreate_value_space(ctx, 10);
+
+ res = sqlite_query(ctx, "SELECT datakey, dataval FROM openvpn_sessions WHERE sessionkey = '%q'",
+ sesskey);
+ if( (res != NULL) || (sqlite_get_numtuples(res) > 0) ) {
+ for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
+ eAdd_value(ctx, sessvals,
+ sqlite_get_value(res, i, 0),
+ sqlite_get_value(res, i, 1));
+ }
+ } else {
+ eurephia_log(ctx, LOG_CRITICAL, 0,
+ "Could not load session values for session '%s'", sesskey);
+
+ }
+ sqlite_free_results(res);
+ return sessvals;
+}
+
+
+// Store a new, update or delete a sessionvalue in the database
+int eDBstore_session_value(eurephiaCTX *ctx, eurephiaSESSION *session, int mode, const char *key, const char *val)
+{
+ dbresult *res = NULL;
+
+ if( session == NULL ) {
+ DEBUG(ctx, 20,
+ "Function call failed to eDBstore_session_value(ctx, ...): Non-existing session key");
+ return 0;
+ }
+
+ DEBUG(ctx, 20, "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 (sessionkey, datakey, dataval) "
+ "VALUES ('%q','%q','%q')", session->sessionkey, key, val);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0,
+ "Could not register new session variable into database: [%s] %s = %s",
+ session->sessionkey, key, val);
+ return 0;
+ }
+ break;
+
+ case SESSVAL_UPDATE:
+ res = sqlite_query(ctx,
+ "UPDATE openvpn_sessions SET dataval = '%q' "
+ " WHERE sessionkey = '%q' AND datakey = '%q'",
+ val, session->sessionkey, key);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0, "Could not update session variable: [%s] %s = %s ",
+ session->sessionkey, key, val);
+ return 0;
+ }
+ break;
+
+ case SESSVAL_DELETE:
+ res = sqlite_query(ctx,
+ "DELETE FROM openvpn_sessions "
+ " WHERE sessionkey = '%q' AND datakey = '%q'",
+ session->sessionkey, key);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0, "Could not delete session variable: [%s] %s",
+ session->sessionkey, key);
+ return 0;
+ }
+ break;
+
+ default:
+ eurephia_log(ctx, LOG_FATAL, 0, "Unknown eDBstore_session_value mode '%i'", mode);
+ return 0;
+ }
+ sqlite_free_results(res);
+ return 1;
+}
+
+
+// Delete session information from openvpn_sessions and update openvpn_lastlog with status
+int eDBdestroy_session(eurephiaCTX *ctx, eurephiaSESSION *session) {
+ dbresult *res = NULL;
+
+ DEBUG(ctx, 20, "Function call: eDBdestroy_session(ctx, '%s')", session->sessionkey);
+
+ if( (session == NULL) || (session->sessionkey == NULL) ) {
+ eurephia_log(ctx, LOG_WARNING, 1, "No active session given to be destroyed");
+ return 1;
+ }
+
+ // Update session status
+ res = sqlite_query(ctx,
+ "UPDATE openvpn_lastlog "
+ " SET sessionstatus = 4, session_deleted = CURRENT_TIMESTAMP "
+ " WHERE sessionkey = '%q' AND sessionstatus = 3", session->sessionkey);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0,
+ "Could not update session status in lastlog (%s))", session->sessionkey);
+ return 0;
+ }
+ sqlite_free_results(res);
+
+ // Delete session variables
+ res = sqlite_query(ctx, "DELETE FROM openvpn_sessions WHERE sessionkey = '%q'", session->sessionkey);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0,
+ "Could not delete session variables (%s))", session->sessionkey);
+ return 0;
+ }
+ sqlite_free_results(res);
+
+ // Remove the sessionkey from openvpn_sessions
+ if( eDBremove_sessionkey(ctx, session->sessionkey) == 0 ) {
+ return 0;
+ }
+ return 1;
+}
+
+
+char *eDBget_firewall_profile(eurephiaCTX *ctx, eurephiaSESSION *session)
+{
+ char *ret = NULL;
+ dbresult *res = NULL;
+
+ DEBUG(ctx, 20, "Function call: eDBget_firewall_profile(ctx, {session}'%s')",
+ session->sessionkey);
+
+ res = sqlite_query(ctx,
+ "SELECT fw_profile "
+ " FROM openvpn_lastlog "
+ " JOIN openvpn_usercerts USING(certid, uid)"
+ " JOIN openvpn_accesses USING(accessprofile)"
+ " WHERE sessionkey = '%q'", session->sessionkey);
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0, "Could not retrieve firewall profile for session '%s'",
+ session->sessionkey);
+ return NULL;
+ }
+ ret = strdup_nullsafe(sqlite_get_value(res, 0, 0));
+ sqlite_free_results(res);
+ return ret;
+}
+
+eurephiaVALUES *eDBget_blacklisted_ip(eurephiaCTX *ctx) {
+ eurephiaVALUES *ret = NULL;
+ dbresult *res = NULL;
+ int i = 0;
+ char *ip = NULL;
+
+ DEBUG(ctx, 20, "Function call: eDBget_blacklisted_ip(ctx)");
+
+ res = sqlite_query(ctx, "SELECT remoteip FROM openvpn_blacklist WHERE remoteip IS NOT NULL");
+ if( res == NULL ) {
+ eurephia_log(ctx, LOG_FATAL, 0,
+ "Could not retrieve blacklisted IP addresses from the database");
+ return NULL;
+ }
+ ret = eCreate_value_space(ctx, 21);
+ for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
+ if( (ip = sqlite_get_value(res, i, 0)) != NULL ) {
+ eAdd_value(ctx, ret, NULL, ip);
+ }
+ }
+ sqlite_free_results(res);
+
+ return ret;
+}
generated by cgit (git 2.34.1) at 2026-01-08 02:51:07 +0000