Debian Codegraph @

database/sqlite/edb-sqlite.c

raw
1 /* edb-sqlite.c -- Main driver for eurephia authentication plugin for OpenVPN
2 * This is the SQLite database driver
3 *
4 * GPLv2 only - Copyright (C) 2008 - 2012
5 * David Sommerseth <[email protected]>
6 *
7 * This program is free software; you can redistribute it and/or
8 * modify it under the terms of the GNU General Public License
9 * as published by the Free Software Foundation; version 2
10 * of the License.
11 *
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with this program; if not, write to the Free Software
19 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
20 *
21 */
22
23 /**
24 * @file edb-sqlite.c
25 * @author David Sommerseth <[email protected]>
26 * @date 2008-08-06
27 *
28 * @brief eurephia database driver for the SQLite3 database.
29 * This file is the main API for the driver.
30 *
31 */
32
33
34 #include <stdio.h>
35 #include <stdlib.h>
36 #include <string.h>
37 #include <unistd.h>
38 #include <assert.h>
39
40 #define DRIVERVERSION "1.3" /**< Defines the software version of this driver */
41 #ifndef DRIVERAPIVERSION
42 # define DRIVERAPIVERSION 3 /**< Sets the API version level of this driver */
43 #endif
44
45 #include <sqlite3.h>
46
47 #include <eurephiadb_driver.h>
48 #include <eurephia_nullsafe.h>
49 #include <eurephia_log.h>
50 #include <eurephia_values.h>
51 #include <eurephiadb_session_common.h>
52 #include <eurephiadb_session_struct.h>
53 #include <passwd.h>
54
55 #include "sqlite.h"
56
57
58
59 /**
60 * Mapping struct - maps attempt types (attempt_IPADDR, attempt_CERTIFICATE, attempt_USERNAME)
61 * to database field names, configuration options (with default values) and description
62 */
63 typedef struct {
64 char *colname; /**< Field when doing look up in blacklist and attempts tables */
65 char *colname_where; /**< Field when using column name in WHERE section of SQL queries */
66 char *allow_cfg; /**< Configure parameter for the attempt limits */
67 char *descr; /**< Description, used to give more readable output for users */
68 char *default_value; /**< Default value, if config option is not found */
69 char *value_func; /**< If not NULL, the value will be passed through the given SQL function */
70 } eDBattempt_types_t;
71
72
73 /**
74 * Static mapping table with the needed values. Uses the eDBattempt_types_t struct.
75 */
76 static const eDBattempt_types_t eDBattempt_types[] = {
77 {NULL, NULL, NULL, NULL},
78 {"remoteip\0", "remoteip\0", "allow_ipaddr_attempts\0", "IP Address\0", "10\0", NULL},
79 {"digest\0", "lower(digest)\0", "allow_cert_attempts\0", "Certificate\0", "5\0", "lower\0"},
80 {"username\0", "username\0", "allow_username_attempts\0", "Username\0", "5\0", NULL},
81 {NULL, NULL, NULL, NULL}
82 };
83
84
85 /**
86 * @copydoc eDB_DriverVersion()
87 */
88 const char *eDB_DriverVersion(void) {
89 return "edb-sqlite (v"DRIVERVERSION") David Sommerseth 2008-2012 (C) GPLv2";
90 }
91
92
93 /**
94 * @copydoc eDB_DriverAPIVersion()
95 */
96 int eDB_DriverAPIVersion() {
97 return DRIVERAPIVERSION;
98 }
99
100
101 /*
102 * local functions
103 */
104
105 /**
106 * Internal driver function for simplifying update of openvpn_blacklist. It will simply just
107 * update the 'last_accessed' field in the blacklist table.
108 *
109 * @param ctx eurephiaCTX
110 * @param blid Blacklist ID, integer value corresponding to the record in the database
111 */
112 void update_attempts(eurephiaCTX *ctx, const char *blid) {
113 dbresult *res = NULL;
114
115 if( blid != NULL ) {
116 res = sqlite_query(ctx,
117 "UPDATE openvpn_blacklist "
118 " SET last_accessed = CURRENT_TIMESTAMP WHERE blid = %q", blid);
119 if( sqlite_query_status(res) != dbSUCCESS ) {
120 eurephia_log(ctx, LOG_CRITICAL, 0,
121 "Could not update openvpn_blacklist.last_accessed for blid=%s", blid);
122 sqlite_log_error(ctx, res);
123 }
124 sqlite_free_results(res);
125 }
126 }
127
128 /*
129 * Public driver functions
130 */
131
132 /**
133 * @copydoc eDBconnect()
134 * Connect to the database ... connection is stored in the eurephiaCTX context
135 */
136 int eDBconnect(eurephiaCTX *ctx, const int argc, const char **argv)
137 {
138 eDBconn *dbc = NULL;
139 dbresult *res = NULL;
140 int rc;
141
142 DEBUG(ctx, 20, "Function call: eDBconnect(ctx, %i, '%s')", argc, argv[0]);
143
144 if( (argc != 1) || (argv[0] == NULL) || (strlen(argv[0]) < 1) ) {
145 eurephia_log(ctx, LOG_PANIC, 0,
146 "Wrong parameters to edb-sqlite. Cannot open database.");
147 return 0;
148 }
149
150 // Connect to the database
151 dbc = (eDBconn *) malloc_nullsafe(ctx, sizeof(eDBconn)+2);
152 dbc->dbname = strdup(argv[0]);
153
154 eurephia_log(ctx, LOG_INFO, 1, "Opening database '%s'", dbc->dbname);
155
156 rc = sqlite3_open(argv[0], (void *) &dbc->dbhandle);
157 if( rc ) {
158 eurephia_log(ctx, LOG_PANIC, 0, "Could not open database '%s'", dbc->dbname);
159 free_nullsafe(ctx, dbc->dbname);
160 free_nullsafe(ctx, dbc);
161 return 0;
162 }
163
164 dbc->config = NULL;
165 ctx->dbc = dbc;
166
167 if( sqlite_init_functions(ctx) != dbSUCCESS ) {
168 sqlite3_close((sqlite3 *) dbc->dbhandle);
169 free_nullsafe(ctx, dbc->dbname);
170 dbc->dbhandle = NULL;
171 return 0;
172 }
173
174 // Load configuration parameters into memory
175 eurephia_log(ctx, LOG_INFO, 1, "Reading config from database (openvpn_config)");
176 res = sqlite_query(ctx, "SELECT datakey, dataval FROM openvpn_config");
177 if( sqlite_query_status(res) == dbSUCCESS ) {
178 int i = 0;
179 eurephiaVALUES *cfg = NULL;
180
181 cfg = eCreate_value_space(ctx, 11);
182 if( cfg == NULL ) {
183 eurephia_log(ctx, LOG_FATAL, 0, "Could not allocate memory for config variables");
184 sqlite_free_results(res);
185 return 0;
186 }
187 for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
188 eAdd_value(ctx, cfg, sqlite_get_value(res, i, 0), sqlite_get_value(res, i, 1));
189 }
190 ctx->dbc->config = cfg;
191 } else {
192 sqlite_log_error(ctx, res);
193 }
194 sqlite_free_results(res);
195 return 1;
196 }
197
198 /**
199 * @copydoc eDBdisconnect()
200 * Disconnect from the database
201 */
202 void eDBdisconnect(eurephiaCTX *ctx)
203 {
204 eDBconn *dbc = NULL;
205
206 DEBUG(ctx, 20, "Function call: eDBdisconnect(ctx)");
207
208 if( ctx->dbc == NULL ) {
209 eurephia_log(ctx, LOG_WARNING, 0, "Database not open, cannot close database.");
210 return;
211 }
212
213 dbc = ctx->dbc;
214 eurephia_log(ctx, LOG_INFO, 1, "Closing database '%s'", dbc->dbname);
215
216 // Close database connection
217 sqlite3_close((sqlite3 *) dbc->dbhandle);
218 free_nullsafe(ctx, dbc->dbname);
219 dbc->dbhandle = NULL;
220
221 // Free up config memory
222 eFree_values(ctx, dbc->config);
223 free_nullsafe(ctx, dbc);
224 ctx->dbc = NULL;
225 }
226
227
228 /**
229 * @copydoc eDBauth_TLS()
230 */
231 int eDBauth_TLS(eurephiaCTX *ctx, const char *org, const char *cname, const char *email,
232 const char *digest, const unsigned int depth)
233 {
234 dbresult *res = NULL;
235 int certid = 0;
236 char *blid = NULL;
237
238 DEBUG(ctx, 20, "Function call: eDBauth_TLS(ctx, '%s', '%s', '%s', '%s', %i)",
239 org, cname, email, digest, depth);
240
241 // Check if certificate is valid, and not too many attempts has been tried with the given certificate
242 res = sqlite_query(ctx,
243 "SELECT cert.certid, blid "
244 " FROM openvpn_certificates cert"
245 " LEFT JOIN openvpn_blacklist bl USING(digest)"
246 " WHERE organisation='%q' AND common_name='%q' "
247 " AND email='%q' AND depth='%i' AND lower(cert.digest)=lower('%q')%c",
248 org, cname, email, depth, digest, 0);
249
250 if( sqlite_query_status(res) == dbSUCCESS ) {
251 certid = atoi_nullsafe(sqlite_get_value(res, 0, 0));
252 blid = strdup_nullsafe(sqlite_get_value(res, 0, 1));
253
254 // Check if the certificate is blacklisted or not. blid != NULL when blacklisted
255 if( blid != NULL ) {
256 // If the certificate or IP is blacklisted, update status and deny access.
257 eurephia_log(ctx, LOG_WARNING, 0,
258 "Attempt with BLACKLISTED certificate (certid %i)", certid);
259 update_attempts(ctx, blid);
260 certid = -1;
261 }
262 free_nullsafe(ctx, blid);
263 } else {
264 eurephia_log(ctx, LOG_FATAL, 0, "Could not look up certificate information");
265 sqlite_log_error(ctx, res);
266 }
267 sqlite_free_results(res);
268
269 DEBUG(ctx, 20, "Result function call: eDBauth_TLS(ctx, '%s', '%s', '%s', '%s', %i) - %i",
270 org, cname, email, digest, depth, certid);
271
272 return certid;
273 }
274
275
276 /**
277 * @copydoc eDBauth_user()
278 */
279 int eDBauth_user(eurephiaCTX *ctx, const int certid, const char *username, const char *passwd)
280 {
281 dbresult *res = NULL;
282 char *crpwd = NULL, *activated = NULL, *deactivated = NULL, *blid_uname = NULL, *blid_cert = NULL;
283 char *dbpwd = NULL;
284 int uicid = 0, uid = 0, pwdok = 0;
285
286 DEBUG(ctx, 20, "Function call: eDBauth_user(ctx, %i, '%s','xxxxxxxx')", certid, username);
287
288
289 // Generate SHA512 hash of password, used for password auth
290 res = sqlite_query(ctx,
291 "SELECT uicid, ou.uid, activated, deactivated, bl1.blid, bl2.blid, password "
292 " FROM openvpn_users ou"
293 " JOIN openvpn_usercerts uc USING(uid) "
294 " LEFT JOIN openvpn_blacklist bl1 ON( ou.username = bl1.username) "
295 " LEFT JOIN (SELECT blid, certid "
296 " FROM openvpn_certificates "
297 " JOIN openvpn_blacklist USING(digest)) bl2 ON(uc.certid = bl2.certid)"
298 " WHERE uc.certid = '%i' AND ou.username = '%q'",
299 certid, username);
300 memset(crpwd, 0, strlen_nullsafe(crpwd));
301 free_nullsafe(ctx, crpwd);
302 if( sqlite_query_status(res) != dbSUCCESS ) {
303 eurephia_log(ctx, LOG_FATAL, 0,
304 "Could not lookup user in database (certid %i, username '%s'", certid, username);
305 sqlite_log_error(ctx, res);
306 sqlite_free_results(res);
307 return 0;
308 }
309
310 if( sqlite_get_numtuples(res) == 1 ) {
311 uid = atoi_nullsafe(sqlite_get_value(res, 0, 1));
312 activated = sqlite_get_value(res, 0, 2);
313 deactivated = sqlite_get_value(res, 0, 3);
314 blid_uname = sqlite_get_value(res, 0, 4);
315 blid_cert = sqlite_get_value(res, 0, 5);
316 dbpwd = sqlite_get_value(res, 0, 6);
317
318 if( dbpwd == NULL ) {
319 eurephia_log(ctx, LOG_WARNING, 0,"Authentication failed for user '%s'. DB error.",
320 username);
321 pwdok = 0;
322 } else {
323 crpwd = eurephia_pwd_crypt(ctx, passwd, dbpwd);
324 pwdok = ((crpwd != NULL) && (strcmp(crpwd, dbpwd) == 0) ? 1 : 0);
325 memset(crpwd, 0, strlen_nullsafe(crpwd));
326 memset(dbpwd, 0, strlen_nullsafe(dbpwd));
327 free_nullsafe(ctx, crpwd);
328 }
329
330 if( blid_uname != NULL ) {
331 eurephia_log(ctx, LOG_WARNING, 0, "User account is BLACKLISTED (uid: %i, %s)",
332 uid, username);
333 uicid = -1;
334 } else if( blid_cert != NULL ) {
335 eurephia_log(ctx, LOG_WARNING, 0,
336 "User account linked with a BLACKLISTED certificate "
337 "(uid: %i, %s) - certid: %s",
338 uid, username, certid);
339 uicid = -1;
340 } else if( activated == NULL ) {
341 eurephia_log(ctx, LOG_WARNING, 0, "User account is not activated (uid: %i, %s)",
342 uid, username);
343 uicid = -1;
344 } else if( deactivated != NULL ) {
345 eurephia_log(ctx, LOG_WARNING, 0, "User account is deactivated (uid: %i, %s)",
346 uid, username);
347 uicid = -1;
348 } else if( pwdok != 1 ) {
349 eurephia_log(ctx, LOG_WARNING, 0,"Authentication failed for user '%s'. Wrong password.",
350 username);
351 sleep(2);
352 uicid = -1;
353 } else {
354 dbresult *upd = NULL;
355
356 uicid = atoi_nullsafe(sqlite_get_value(res, 0, 0));
357
358 // Update last accessed status
359 upd = sqlite_query(ctx,
360 "UPDATE openvpn_users SET last_accessed = CURRENT_TIMESTAMP"
361 " WHERE uid = %i", uid);
362 if( sqlite_query_status(upd) != dbSUCCESS) {
363 eurephia_log(ctx, LOG_ERROR, 0,
364 "Could not update last access status for uid %i", uid);
365 sqlite_log_error(ctx, upd);
366 }
367 sqlite_free_results(upd);
368 }
369 } else {
370 eurephia_log(ctx, LOG_WARNING, 0, "Authentication failed for user '%s'. "
371 "Could not find user or user-certificate link.", username);
372 sleep(2);
373 uicid = 0;
374 }
375 sqlite_free_results(res);
376
377 DEBUG(ctx, 20, "Result function call: eDBauth_user(ctx, %i, '%s','xxxxxxxx') - %i",
378 certid, username, uicid);
379
380 return uicid;
381 }
382
383 /**
384 * @copydoc eDBget_uid()
385 */
386 int eDBget_uid(eurephiaCTX *ctx, const int certid, const char *username)
387 {
388 dbresult *res = NULL;
389 int ret = 0;
390
391 DEBUG(ctx, 20, "Function call: eDBget_uid(ctx, %i, '%s')", certid, username);
392
393 res = sqlite_query(ctx,
394 "SELECT uid "
395 " FROM openvpn_usercerts "
396 " JOIN openvpn_users USING (uid) "
397 " WHERE certid = '%i' AND username = '%q'",
398 certid, username);
399 if( (sqlite_query_status(res) != dbSUCCESS) || (sqlite_get_numtuples(res) != 1) ) {
400 eurephia_log(ctx, LOG_FATAL, 0, "Could not lookup userid for user '%s'", username);
401 if( sqlite_query_status(res) == dbERROR ) {
402 sqlite_log_error(ctx, res);
403 }
404 ret = -1;
405 } else {
406 ret = atoi_nullsafe(sqlite_get_value(res, 0, 0));
407 }
408 sqlite_free_results(res);
409
410 return ret;
411 }
412
413
414 /**
415 * @copydoc eDBblacklist_check()
416 */
417 int eDBblacklist_check(eurephiaCTX *ctx, const int type, const char *val)
418 {
419 dbresult *blr = NULL, *atpr = NULL;
420 int atpexceed = -1, blacklisted = 0;
421 char *atpid = NULL, *blid = NULL;
422
423 DEBUG(ctx, 20, "Function call: eDBblacklist_check(ctx, '%s', '%s')",
424 eDBattempt_types[type].descr, val);
425
426 blr = sqlite_query(ctx, "SELECT blid FROM openvpn_blacklist WHERE %s = %s%s'%q'%s",
427 eDBattempt_types[type].colname_where,
428 defaultValue(eDBattempt_types[type].value_func, ""),
429 (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? "(" : ""),
430 val,
431 (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? ")" : ""));
432 if( sqlite_query_status(blr) == dbSUCCESS ) {
433 blid = strdup_nullsafe(sqlite_get_value(blr, 0, 0));
434
435 if( blid != NULL ) {
436 eurephia_log(ctx, LOG_WARNING, 0, "Attempt from blacklisted %s: %s",
437 eDBattempt_types[type].descr, val);
438 blacklisted = 1; // [type] is blacklisted
439 }
440 // Update attempt information
441 update_attempts(ctx, blid);
442 } else {
443 eurephia_log(ctx, LOG_FATAL, 0, "Querying openvpn_blacklist for blacklisted %s failed",
444 eDBattempt_types[type].descr);
445 sqlite_log_error(ctx, blr);
446 }
447 sqlite_free_results(blr);
448
449 if( blacklisted == 0 ) {
450 // Check if this [type] has been attempted earlier - if it has reaced the maximum
451 // attempt limit, blacklist it
452 atpr = sqlite_query(ctx,
453 "SELECT atpid, attempts >= %q FROM openvpn_attempts WHERE %s = '%q'",
454 defaultValue(eGet_value(ctx->dbc->config, eDBattempt_types[type].allow_cfg),
455 eDBattempt_types[type].default_value),
456 eDBattempt_types[type].colname_where, val);
457
458 if( sqlite_query_status(atpr) == dbSUCCESS ) {
459 atpid = strdup_nullsafe(sqlite_get_value(atpr, 0, 0));
460 atpexceed = atoi_nullsafe(sqlite_get_value(atpr, 0, 1));
461
462 // If [type] has reached attempt limit and it is not black listed, black list it
463 if( (atpexceed > 0) && (blid == NULL) ) {
464 eurephia_log(ctx, LOG_WARNING, 0,
465 "%s got BLACKLISTED due to too many failed attempts: %s",
466 eDBattempt_types[type].descr, val);
467 blr = sqlite_query(ctx,
468 "INSERT INTO openvpn_blacklist (%s) VALUES ('%q')",
469 eDBattempt_types[type].colname, val);
470 if( sqlite_query_status(blr) != dbSUCCESS ) {
471 eurephia_log(ctx, LOG_CRITICAL, 0,
472 "Could not blacklist %s (%s)",
473 eDBattempt_types[type].descr, val);
474 sqlite_log_error(ctx, blr);
475 }
476 blacklisted = 1; // [type] is blacklisted
477 sqlite_free_results(blr);
478 }
479 free_nullsafe(ctx, atpid);
480 } else {
481 eurephia_log(ctx, LOG_CRITICAL, 0, "Querying openvpn_attempts for blacklisted %s failed",
482 eDBattempt_types[type].descr);
483 sqlite_log_error(ctx, blr);
484 }
485 sqlite_free_results(atpr);
486 }
487 free_nullsafe(ctx, blid);
488
489 DEBUG(ctx, 20, "Result - function call: eDBblacklist_check(ctx, '%s', '%s') - %i",
490 eDBattempt_types[type].descr, val, blacklisted);
491
492 return blacklisted;
493 }
494
495 /**
496 * @copydoc eDBregister_attempt()
497 */
498 void eDBregister_attempt(eurephiaCTX *ctx, int type, int mode, const char *value) {
499 dbresult *res;
500 char *id = NULL, *atmpt_block = NULL, *blid = NULL;
501 int attempts = 0;
502
503 DEBUG(ctx, 20, "Function call: eDBregister_attempt(ctx, %s, %s, '%s')",
504 eDBattempt_types[type].colname,
505 (mode == ATTEMPT_RESET ? "ATTEMPT_RESET" : "ATTEMPT_REGISTER"),
506 value);
507
508 //
509 // openvpn_attempts
510 //
511 res = sqlite_query(ctx,
512 "SELECT atpid, attempts > %s, blid, attempts "
513 " FROM openvpn_attempts "
514 " LEFT JOIN openvpn_blacklist USING(%s)"
515 " WHERE %s = %s%s'%q'%s",
516 defaultValue(eGet_value(ctx->dbc->config, eDBattempt_types[type].allow_cfg),
517 eDBattempt_types[type].default_value),
518 eDBattempt_types[type].colname,
519 eDBattempt_types[type].colname_where,
520 defaultValue(eDBattempt_types[type].value_func, ""),
521 (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? "(" : ""),
522 value,
523 (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? ")" : "")
524 );
525 if( sqlite_query_status(res) != dbSUCCESS ) {
526 eurephia_log(ctx, LOG_FATAL, 0, "Could not look up atpid in openvpn_attempts");
527 sqlite_log_error(ctx, res);
528 sqlite_free_results(res);
529 return;
530 }
531
532 attempts = atoi_nullsafe(sqlite_get_value(res, 0, 3));
533 // If we are asked to reset the attempt counter and we do not find any attempts, exit here
534 if( (mode == ATTEMPT_RESET) && ((sqlite_get_numtuples(res) == 0) || (attempts == 0))) {
535 sqlite_free_results(res);
536 return;
537 }
538
539 id = strdup_nullsafe(sqlite_get_value(res, 0, 0));
540 atmpt_block = strdup_nullsafe(sqlite_get_value(res, 0, 1));
541 blid = strdup_nullsafe(sqlite_get_value(res, 0, 2));
542
543 sqlite_free_results(res);
544
545 if( (id == NULL) && (mode == ATTEMPT_REGISTER) ) {
546 // Only insert record when we are in registering mode
547 res = sqlite_query(ctx, "INSERT INTO openvpn_attempts (%s, attempts) VALUES ('%q', 1)",
548 eDBattempt_types[type].colname, value);
549 } else if( id != NULL ){
550 // if a attempt record exists, update it according to mode
551 switch( mode ) {
552 case ATTEMPT_RESET:
553 res = sqlite_query(ctx,
554 "UPDATE openvpn_attempts "
555 " SET attempts = 0 "
556 " WHERE atpid = '%q'", id);
557 break;
558 default:
559 res = sqlite_query(ctx,
560 "UPDATE openvpn_attempts "
561 " SET last_attempt = CURRENT_TIMESTAMP, attempts = attempts + 1"
562 " WHERE atpid = '%q'", id);
563 break;
564 }
565 }
566 if( sqlite_query_status(res) != dbSUCCESS ) {
567 eurephia_log(ctx, LOG_CRITICAL, 0,
568 "Could not update openvpn_attempts for %s = %s",
569 eDBattempt_types[type].colname, value);
570 sqlite_log_error(ctx, res);
571 }
572 sqlite_free_results(res);
573
574 // If attempts have exceeded attempt limit, blacklist it immediately if not already registered
575 if( (mode == ATTEMPT_REGISTER)
576 && (blid == NULL) && (atmpt_block != NULL) && (atoi_nullsafe(atmpt_block) > 0) ) {
577 eurephia_log(ctx, LOG_WARNING, 0, "Blacklisting %s due to too many attempts: %s",
578 eDBattempt_types[type].descr, value);
579 res = sqlite_query(ctx, "INSERT INTO openvpn_blacklist (%s) VALUES ('%q')",
580 eDBattempt_types[type].colname, value);
581 if( sqlite_query_status(res) != dbSUCCESS ) {
582 eurephia_log(ctx, LOG_CRITICAL, 0,
583 "Could not blacklist %s: %s", eDBattempt_types[type].descr, value);
584 sqlite_log_error(ctx, res);
585 }
586 sqlite_free_results(res);
587 }
588 free_nullsafe(ctx, id);
589 free_nullsafe(ctx, atmpt_block);
590 free_nullsafe(ctx, blid);
591 }
592
593
594 /**
595 * @copydoc eDBregister_login()
596 */
597 int eDBregister_login(eurephiaCTX *ctx, eurephiaSESSION *skey, const int certid, const int uid,
598 const char *proto, const char *remipaddr, const char *remport,
599 const char *vpnipaddr, const char *vpnipmask)
600 {
601 dbresult *res = NULL;
602 int ret = 0;
603
604 DEBUG(ctx, 20, "Function call: eDBregister_login(ctx, '%s', %i, %i, '%s','%s','%s','%s','%s')",
605 skey->sessionkey, certid, uid, proto, remipaddr, remport, vpnipaddr, vpnipmask);
606
607 if( skey->sessionstatus != SESSION_NEW ) {
608 eurephia_log(ctx, LOG_ERROR, 5, "Not a new session, will not register it again");
609 return 1;
610 }
611
612 res = sqlite_query(ctx,
613 "INSERT INTO openvpn_lastlog (uid, certid, "
614 " protocol, remotehost, remoteport,"
615 " vpnipaddr, vpnipmask,"
616 " sessionstatus, sessionkey, login) "
617 "VALUES (%i, %i, '%q','%q','%q','%q','%q', 1,'%q', CURRENT_TIMESTAMP)",
618 uid, certid, proto, remipaddr, remport, vpnipaddr, vpnipmask, skey->sessionkey);
619 if( sqlite_query_status(res) != dbSUCCESS ) {
620 eurephia_log(ctx, LOG_FATAL, 0, "Could not insert new session into openvpn_lastlog");
621 sqlite_log_error(ctx, res);
622 ret = 0;
623 } else {
624 skey->sessionstatus = SESSION_REGISTERED;
625 ret = 1;
626 }
627 sqlite_free_results(res);
628
629 return ret;
630 }
631
632 /**
633 * @copydoc eDBregister_vpnmacaddr()
634 */
635 int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const char *macaddr)
636 {
637 dbresult *res = NULL;
638 int ret = 0;
639
640 DEBUG(ctx, 20, "Function call: eDBregister_vpnmacaddr(ctx, '%s', '%s')",
641 session->sessionkey, macaddr);
642
643 if( (macaddr == NULL) && (strlen_nullsafe(macaddr) > 18) ) {
644 eurephia_log(ctx, LOG_FATAL, 0, "Invalid MAC address");
645 return 0;
646 }
647
648 // Register MAC address into history table
649 res = sqlite_query(ctx, "INSERT INTO openvpn_macaddr_history (sessionkey, macaddr) VALUES ('%q','%q')",
650 session->sessionkey, macaddr);
651 if( sqlite_query_status(res) != dbSUCCESS ) {
652 eurephia_log(ctx, LOG_FATAL, 0, "Failed to log new MAC address for session");
653 sqlite_log_error(ctx, res);
654 ret = 0;
655 goto exit;
656 }
657 sqlite_free_results(res);
658
659 // Update lastlog to reflect last used MAC address for the session
660 res = sqlite_query(ctx,
661 "UPDATE openvpn_lastlog SET sessionstatus = 2, macaddr = '%q' "
662 " WHERE sessionkey = '%q' AND sessionstatus = 1", macaddr, session->sessionkey);
663 if( sqlite_query_status(res) == dbSUCCESS ) {
664 // Save the MAC address in the session values register - needed for the destroy session
665 if( eDBset_session_value(ctx, session, "macaddr", macaddr) == 0 ) {
666 eurephia_log(ctx, LOG_FATAL, 0, "Could not save MAC address into session variables");
667 ret = 0;
668 } else {
669 ret = 1;
670 }
671 } else {
672 eurephia_log(ctx, LOG_FATAL, 0, "Could not update lastlog with new MAC address for session");
673 sqlite_log_error(ctx, res);
674 ret = 0;
675 }
676
677 exit:
678 sqlite_free_results(res);
679 return ret;
680 }
681
682
683 /**
684 * @copydoc eDBregister_vpnclientaddr()
685 */
686 int eDBregister_vpnclientaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const char *macaddr,
687 const char *vpnip4addr, const char *vpnip6addr)
688 {
689 dbresult *res = NULL;
690 int ret = 0;
691
692 DEBUG(ctx, 20, "Function call: eDBregister_vpnclientaddr(ctx, '%s', '%s', '%s', '%s')",
693 session->sessionkey, macaddr, vpnip4addr, vpnip6addr);
694
695 if( (macaddr == NULL) && (strlen_nullsafe(macaddr) > 18) ) {
696 eurephia_log(ctx, LOG_FATAL, 0, "Invalid MAC address");
697 return 0;
698 }
699
700 // Register client addresses into history table
701 res = sqlite_query(ctx,
702 "INSERT INTO openvpn_vpnaddr_history (sessionkey, macaddr, ip4addr, ip6addr) "
703 "VALUES ('%q','%q','%q','%q')",
704 session->sessionkey,
705 (ctx->tuntype == tuntype_TAP ? macaddr : ""),
706 (vpnip4addr ? vpnip4addr : ""), (vpnip6addr ? vpnip6addr : ""));
707 if( sqlite_query_status(res) != dbSUCCESS ) {
708 eurephia_log(ctx, LOG_FATAL, 0, "Failed to log new VPN client addresses for session");
709 sqlite_log_error(ctx, res);
710 ret = 0;
711 goto exit;
712 }
713 sqlite_free_results(res);
714
715 // Update lastlog to reflect last used MAC address for the session
716 res = sqlite_query(ctx,
717 "UPDATE openvpn_lastlog SET sessionstatus = 2, macaddr = '%q', vpnipaddr = '%q', vpnipv6addr = '%q' "
718 " WHERE sessionkey = '%q' AND sessionstatus = 1",
719 (macaddr ? macaddr : ""), (vpnip4addr ? vpnip4addr : ""), (vpnip6addr ? vpnip6addr : ""),
720 session->sessionkey);
721 if( sqlite_query_status(res) == dbSUCCESS ) {
722 // TAP mode: Save the MAC address in the session values register - needed for the destroy session
723 if( (ctx->tuntype == tuntype_TAP) && eDBset_session_value(ctx, session, "macaddr", macaddr) == 0 ) {
724 eurephia_log(ctx, LOG_FATAL, 0, "Could not save MAC address into session variables");
725 ret = 0;
726 } else {
727 ret = 1;
728 }
729 } else {
730 eurephia_log(ctx, LOG_FATAL, 0, "Could not update lastlog with new VPN client addresses for session");
731 sqlite_log_error(ctx, res);
732 ret = 0;
733 }
734
735 exit:
736 sqlite_free_results(res);
737 return ret;
738 }
739
740
741
742 /**
743 * @copydoc eDBregister_logout()
744 */
745 int eDBregister_logout(eurephiaCTX *ctx, eurephiaSESSION *skey,
746 const char *bytes_sent, const char *bytes_received, const char *duration)
747 {
748 dbresult *res = NULL;
749 int ret = 0;
750
751 DEBUG(ctx, 20, "Function call: eDBregister_logout(ctx, '%s', %s, %s)",
752 skey->sessionkey, bytes_sent, bytes_received);
753
754 res = sqlite_query(ctx,
755 "UPDATE openvpn_lastlog "
756 " SET sessionstatus = 3, logout = CURRENT_TIMESTAMP, "
757 " bytes_sent = '%i', bytes_received = '%i', session_duration = '%i' "
758 " WHERE sessionkey = '%q' AND sessionstatus = 2",
759 atoi_nullsafe(bytes_sent), atoi_nullsafe(bytes_received),
760 atoi_nullsafe(duration), skey->sessionkey);
761 if( sqlite_query_status(res) == dbSUCCESS ) {
762 skey->sessionstatus = SESSION_LOGGEDOUT;
763 ret = 1;
764 } else {
765 eurephia_log(ctx, LOG_FATAL, 0, "Could not update lastlog with logout information (%s)",
766 skey->sessionkey);
767 ret = 0;
768 }
769 sqlite_free_results(res);
770 return ret;
771 }
772
773
774 /**
775 * @copydoc eDBget_sessionkey_seed()
776 */
777 char *eDBget_sessionkey_seed(eurephiaCTX *ctx, sessionType type, const char *sessionseed) {
778 dbresult *res = NULL;
779 char *skey = NULL;
780
781 DEBUG(ctx, 20, "eDBget_sessionkey_seed(ctx, %i, '%s')", type, sessionseed);
782
783 if( sessionseed == NULL ) {
784 eurephia_log(ctx, LOG_FATAL, 1,
785 "eDBget_sessionkey: No session seed given - cannot locate sessionkey");
786 return NULL;
787 }
788
789 switch( type ) {
790 case stSESSION:
791 res = sqlite_query(ctx,
792 "SELECT sessionkey "
793 " FROM openvpn_sessionkeys "
794 " JOIN openvpn_lastlog USING (sessionkey)"
795 " WHERE sessionstatus IN (1,2)"
796 " AND sessionseed = '%q'",
797 sessionseed);
798 break;
799
800 case stAUTHENTICATION:
801 res = sqlite_query(ctx,
802 "SELECT sessionkey"
803 " FROM openvpn_sessionkeys"
804 " LEFT JOIN openvpn_lastlog USING(sessionkey)"
805 " WHERE sessionstatus IS NULL"
806 " AND sessionseed = '%q'",
807 sessionseed);
808 break;
809
810 default:
811 eurephia_log(ctx, LOG_ERROR, 0, "Invalid session type: %i", type);
812 return NULL;
813 }
814
815 if( sqlite_query_status(res) == dbSUCCESS ) {
816 if( sqlite_get_numtuples(res) == 1 ) {
817 skey = strdup_nullsafe(sqlite_get_value(res, 0, 0));
818 } else {
819 skey = NULL;
820 }
821 } else {
822 eurephia_log(ctx, LOG_FATAL, 0,"Could not retrieve sessionkey from openvpn_sessionkeys (%s)",
823 sessionseed);
824 sqlite_log_error(ctx, res);
825 skey = NULL;
826 }
827 sqlite_free_results(res);
828 return skey;
829 }
830
831
832 /**
833 * @copydoc eDBget_sessionkey_macaddr()
834 */
835 char *eDBget_sessionkey_macaddr(eurephiaCTX *ctx, const char *macaddr) {
836 dbresult *res = NULL;
837 char *skey = NULL;
838
839 DEBUG(ctx, 20, "eDBget_sessionkey_macaddr(ctx, '%s')", macaddr);
840
841 // Find sessionkey from MAC address
842 res = sqlite_query(ctx,
843 "SELECT sessionkey "
844 " FROM openvpn_sessions "
845 " JOIN openvpn_lastlog USING (sessionkey)"
846 " WHERE sessionstatus = 3 "
847 " AND datakey = 'macaddr'"
848 " AND dataval = '%q'", macaddr);
849 if( sqlite_query_status(res) == dbSUCCESS ) {
850 skey = strdup_nullsafe(sqlite_get_value(res, 0, 0));
851 } else {
852 eurephia_log(ctx, LOG_FATAL, 0,
853 "Could not remove session from database (MAC addr: %s)", macaddr);
854 sqlite_log_error(ctx, res);
855 skey = NULL;
856 }
857 sqlite_free_results(res);
858
859 return skey;
860 }
861
862
863 /**
864 * @copydoc eDBcheck_sessionkey_uniqueness()
865 */
866 int eDBcheck_sessionkey_uniqueness(eurephiaCTX *ctx, const char *seskey) {
867 dbresult *res;
868 int uniq = 0;
869
870 DEBUG(ctx, 20, "eDBcheck_sessionkey_uniqueness(ctx, '%s')", seskey);
871 if( seskey == NULL ) {
872 eurephia_log(ctx, LOG_FATAL, 1,
873 "eDBcheck_sessionkey_uniqness: Invalid session key given");
874 return 0;
875 }
876
877 switch( ctx->context_type ) {
878 case ECTX_NO_PRIVILEGES:
879 return 0;
880 break;
881
882 case ECTX_ADMIN_CONSOLE:
883 case ECTX_ADMIN_WEB:
884 res = sqlite_query(ctx,
885 "SELECT count(sessionkey) = 0 "
886 "FROM eurephia_adminlog WHERE sessionkey = '%q'", seskey);
887 break;
888
889 case ECTX_PLUGIN_AUTH:
890 default:
891 res = sqlite_query(ctx,
892 "SELECT count(sessionkey) = 0 "
893 "FROM openvpn_lastlog WHERE sessionkey = '%q'", seskey);
894 break;
895 }
896
897 if( sqlite_query_status(res) == dbSUCCESS ) {
898 uniq = atoi_nullsafe(sqlite_get_value(res, 0, 0));
899 } else {
900 eurephia_log(ctx, LOG_FATAL, 0,
901 "eDBcheck_sessionkey_uniqness: Could not check uniqueness of sessionkey");
902 sqlite_log_error(ctx, res);
903 uniq = 0;
904 }
905 sqlite_free_results(res);
906
907 return uniq;
908 }
909
910
911 /**
912 * @copydoc eDBregister_sessionkey()
913 */
914 int eDBregister_sessionkey(eurephiaCTX *ctx, const char *seed, const char *seskey) {
915 dbresult *res;
916 int ret = 0;
917
918 DEBUG(ctx, 20, "eDBregister_sessionkey(ctx, '%s', '%s')", seed, seskey);
919 if( (seed == NULL) || (seskey == NULL) ) {
920 eurephia_log(ctx, LOG_FATAL, 1,
921 "eDBregister_sessionkey: Invalid session seed or session key given");
922 return 0;
923 }
924
925 res = sqlite_query(ctx,
926 "INSERT INTO openvpn_sessionkeys (sessionseed, sessionkey) VALUES('%q','%q')",
927 seed, seskey);
928 if( sqlite_query_status(res) == dbSUCCESS ) {
929 ret = 1;
930 } else {
931 eurephia_log(ctx, LOG_FATAL, 0,
932 "eDBregister_sessionkey: Error registering sessionkey into openvpn_sessionkeys");
933 sqlite_log_error(ctx, res);
934 ret = 0;
935 }
936 sqlite_free_results(res);
937 return ret;
938 }
939
940 /**
941 * Removes a session key reference to a short time session seed conversion table
942 *
943 * @param ctx eurephiaCTX
944 * @param seskey String containing the session key reference to remove
945 *
946 * @return Returns 1 on success, otherwise 0.
947 *
948 */
949 int eDBremove_sessionkey(eurephiaCTX *ctx, const char *seskey) {
950 dbresult *res;
951 int ret = 0;
952
953 DEBUG(ctx, 20, "eDBremove_sessionkey(ctx, '%s')", seskey);
954 if( seskey == NULL ) {
955 eurephia_log(ctx, LOG_FATAL, 1,
956 "eDBremove_sessionkey: Invalid session key given");
957 return 0;
958 }
959
960 res = sqlite_query(ctx, "DELETE FROM openvpn_sessionkeys WHERE sessionkey = '%q'", seskey);
961 if( sqlite_query_status(res) == dbSUCCESS ) {
962 ret = 1;
963 } else {
964 eurephia_log(ctx, LOG_FATAL, 0,
965 "eDBremove_sessionkey: Error removing sessionkey from openvpn_sessionkeys");
966 ret = 0;
967 }
968 sqlite_free_results(res);
969 return ret;
970 }
971
972 /**
973 * @copydoc eDBload_sessiondata()
974 */
975 eurephiaVALUES *eDBload_sessiondata(eurephiaCTX *ctx, const char *sesskey) {
976 dbresult *res = NULL;
977 eurephiaVALUES *sessvals = NULL;
978 int i;
979
980 if( (ctx == NULL) || (sesskey == NULL) ) {
981 return NULL;
982 }
983
984 DEBUG(ctx, 20, "Function call: eDBload_sessiondata(ctx, '%s')", sesskey);
985
986 sessvals = eCreate_value_space(ctx, 10);
987
988 res = sqlite_query(ctx, "SELECT datakey, dataval FROM openvpn_sessions WHERE sessionkey = '%q'",
989 sesskey);
990 if( sqlite_query_status(res) == dbSUCCESS ) {
991 for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
992 eAdd_value(ctx, sessvals,
993 sqlite_get_value(res, i, 0),
994 sqlite_get_value(res, i, 1));
995 }
996 } else {
997 eurephia_log(ctx, LOG_CRITICAL, 0,
998 "Could not load session values for session '%s'", sesskey);
999 sqlite_log_error(ctx,res);
1000 }
1001 sqlite_free_results(res);
1002 return sessvals;
1003 }
1004
1005 /**
1006 * @copydoc eDBstore_session_value()
1007 */
1008 int eDBstore_session_value(eurephiaCTX *ctx, eurephiaSESSION *session, int mode, const char *key, const char *val)
1009 {
1010 dbresult *res = NULL;
1011 int ret = 0;
1012
1013 if( session == NULL ) {
1014 DEBUG(ctx, 20,
1015 "Function call failed to eDBstore_session_value(ctx, ...): Non-existing session key");
1016 return 0;
1017 }
1018
1019 DEBUG(ctx, 20, "Function call: eDBstore_session_value(ctx, '%s', %i, '%s', '%s')",
1020 session->sessionkey, mode, key, val);
1021
1022 switch( mode ) {
1023 case SESSVAL_NEW:
1024 res = sqlite_query(ctx,
1025 "INSERT INTO openvpn_sessions (sessionkey, datakey, dataval) "
1026 "VALUES ('%q','%q','%q')", session->sessionkey, key, val);
1027 if( sqlite_query_status(res) != dbSUCCESS ) {
1028 eurephia_log(ctx, LOG_FATAL, 0,
1029 "Could not register new session variable into database: [%s] %s = %s",
1030 session->sessionkey, key, val);
1031 sqlite_log_error(ctx, res);
1032 goto exit;
1033 }
1034 break;
1035
1036 case SESSVAL_UPDATE:
1037 res = sqlite_query(ctx,
1038 "UPDATE openvpn_sessions SET dataval = '%q' "
1039 " WHERE sessionkey = '%q' AND datakey = '%q'",
1040 val, session->sessionkey, key);
1041 if( sqlite_query_status(res) != dbSUCCESS ) {
1042 eurephia_log(ctx, LOG_FATAL, 0, "Could not update session variable: [%s] %s = %s ",
1043 session->sessionkey, key, val);
1044 sqlite_log_error(ctx, res);
1045 goto exit;
1046 }
1047 break;
1048
1049 case SESSVAL_DELETE:
1050 res = sqlite_query(ctx,
1051 "DELETE FROM openvpn_sessions "
1052 " WHERE sessionkey = '%q' AND datakey = '%q'",
1053 session->sessionkey, key);
1054 if( sqlite_query_status(res) != dbSUCCESS ) {
1055 eurephia_log(ctx, LOG_FATAL, 0, "Could not delete session variable: [%s] %s",
1056 session->sessionkey, key);
1057 sqlite_log_error(ctx, res);
1058 goto exit;
1059 }
1060 break;
1061
1062 default:
1063 eurephia_log(ctx, LOG_FATAL, 0, "Unknown eDBstore_session_value mode '%i'", mode);
1064 return 0;
1065 }
1066 exit:
1067 ret = (sqlite_query_status(res) == dbSUCCESS ? 1 : 0);
1068 sqlite_free_results(res);
1069 return ret;
1070 }
1071
1072
1073 /**
1074 * @copydoc eDBdestroy_session()
1075 */
1076 int eDBdestroy_session(eurephiaCTX *ctx, eurephiaSESSION *session) {
1077 dbresult *res = NULL;
1078
1079 DEBUG(ctx, 20, "Function call: eDBdestroy_session(ctx, '%s')", session->sessionkey);
1080
1081 if( (session == NULL) || (session->sessionkey == NULL) ) {
1082 eurephia_log(ctx, LOG_WARNING, 1, "No active session given to be destroyed");
1083 return 1;
1084 }
1085
1086 // Update session status - if we have a "real" session (not auth-session)
1087 if( session->type == stSESSION ) {
1088 res = sqlite_query(ctx,
1089 "UPDATE openvpn_lastlog "
1090 " SET sessionstatus = 4, session_deleted = CURRENT_TIMESTAMP "
1091 " WHERE sessionkey = '%q' AND sessionstatus = 3", session->sessionkey);
1092 if( sqlite_query_status(res) != dbSUCCESS ) {
1093 eurephia_log(ctx, LOG_FATAL, 0,
1094 "Could not update session status in lastlog (%s))", session->sessionkey);
1095 sqlite_log_error(ctx, res);
1096 sqlite_free_results(res);
1097 return 0;
1098 }
1099 sqlite_free_results(res);
1100 }
1101
1102 // Delete session variables
1103 res = sqlite_query(ctx, "DELETE FROM openvpn_sessions WHERE sessionkey = '%q'", session->sessionkey);
1104 if( sqlite_query_status(res) != dbSUCCESS ) {
1105 eurephia_log(ctx, LOG_FATAL, 0,
1106 "Could not delete session variables (%s))", session->sessionkey);
1107 sqlite_log_error(ctx, res);
1108 sqlite_free_results(res);
1109 return 0;
1110 }
1111 sqlite_free_results(res);
1112
1113 // Remove the sessionkey from openvpn_sessions
1114 if( eDBremove_sessionkey(ctx, session->sessionkey) == 0 ) {
1115 return 0;
1116 }
1117 return 1;
1118 }
1119
1120
1121 /**
1122 * @copydoc eDBget_firewall_profile()
1123 */
1124 char *eDBget_firewall_profile(eurephiaCTX *ctx, eurephiaSESSION *session)
1125 {
1126 char *ret = NULL;
1127 dbresult *res = NULL;
1128
1129 DEBUG(ctx, 20, "Function call: eDBget_firewall_profile(ctx, {session}'%s')",
1130 session->sessionkey);
1131
1132 res = sqlite_query(ctx,
1133 "SELECT fw_profile "
1134 " FROM openvpn_lastlog "
1135 " JOIN openvpn_usercerts USING(certid, uid)"
1136 " JOIN openvpn_accesses USING(accessprofile)"
1137 " WHERE sessionkey = '%q'", session->sessionkey);
1138 if( sqlite_query_status(res) == dbSUCCESS ) {
1139 ret = strdup_nullsafe(sqlite_get_value(res, 0, 0));
1140 } else {
1141 eurephia_log(ctx, LOG_FATAL, 0, "Could not retrieve firewall profile for session '%s'",
1142 session->sessionkey);
1143 sqlite_log_error(ctx, res);
1144 ret = NULL;
1145 }
1146 sqlite_free_results(res);
1147 return ret;
1148 }
1149
1150
1151 /**
1152 * @copydoc eDBget_blacklisted_ip()
1153 */
1154 eurephiaVALUES *eDBget_blacklisted_ip(eurephiaCTX *ctx) {
1155 eurephiaVALUES *ret = NULL;
1156 dbresult *res = NULL;
1157 int i = 0;
1158 char *ip = NULL;
1159
1160 DEBUG(ctx, 20, "Function call: eDBget_blacklisted_ip(ctx)");
1161
1162 res = sqlite_query(ctx, "SELECT remoteip FROM openvpn_blacklist WHERE remoteip IS NOT NULL");
1163 if( sqlite_query_status(res) == dbSUCCESS ) {
1164 ret = eCreate_value_space(ctx, 21);
1165 for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
1166 if( (ip = sqlite_get_value(res, i, 0)) != NULL ) {
1167 eAdd_value(ctx, ret, NULL, ip);
1168 }
1169 }
1170 } else {
1171 eurephia_log(ctx, LOG_FATAL, 0,
1172 "Could not retrieve blacklisted IP addresses from the database");
1173 sqlite_log_error(ctx, res);
1174 ret = NULL;
1175 }
1176 sqlite_free_results(res);
1177
1178 return ret;
1179 }
1180