1 /**************************************************
2 OpengateM - a MAC address authentication system
3 module for local work database
5 Copyright (C) 2011 Opengate Project Team
6 Written by Yoshiaki Watanabe
8 This program is free software; you can redistribute it and/or
9 modify it under the terms of the GNU General Public License
10 as published by the Free Software Foundation; either version 2
11 of the License, or (at your option) any later version.
13 This program is distributed in the hope that it will be useful,
14 but WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 GNU General Public License for more details.
18 You should have received a copy of the GNU General Public License
19 along with this program; if not, write to the Free Software
20 Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
22 Email: watanaby@is.saga-u.ac.jp
23 **************************************************/
24 #include "opengatemd.h"
27 static int sqliteBusyTimeout=100; /* value used in sqite3_busy_timeout() */
28 static sqlite3 *dbMd; /* handle for opengatemd.db */
30 /*******************************************************************
31 read sqlite busy timeout value from conf and set it to static variable
32 *******************************************************************/
33 int setupSqliteBusyTimeoutValue(void){
37 /* if set in conf, use the value. if not, use the above default. */
38 str=GetConfValue("SqliteBusyTimeout");
39 if(str!=NULL) sqliteBusyTimeout=atoi(str);
41 return sqliteBusyTimeout;
44 /********************************************
45 initialize work db implemented with sqlite
46 ********************************************/
51 /* SQL CREATE TABLE COMMANDs */
52 char *createCmd1="CREATE TABLE IF NOT EXISTS sessionmd "
53 "(macAddress TEXT PRIMARY KEY, "
54 "userId TEXT, extraId TEXT, openTime INTEGER, checkTime INTEGER, "
55 "ruleNumber INTEGER)";
56 char *createCmd2="CREATE TABLE IF NOT EXISTS macinfo "
57 "(macAddress TEXT PRIMARY KEY ON CONFLICT REPLACE, "
58 "detectTime INTEGER, ttl INTEGER, isNat INTEGER)";
59 char *createCmd3="CREATE TABLE IF NOT EXISTS macippair "
61 "ipAddress TEXT, findTime INTEGER)";
63 /* setup SqLite3_busy_timeout read from conf */
64 SetupSqliteBusyTimeoutValue();
67 if(sqlite3_open(GetConfValue("SqliteDbMd"),&dbMd)!=SQLITE_OK){
68 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
71 sqlite3_busy_timeout(dbMd, sqliteBusyTimeout);
74 if(sqlite3_exec(dbMd, createCmd1, NULL, NULL, &pErrMsg)!=SQLITE_OK){
75 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
80 if(sqlite3_exec(dbMd, createCmd2, NULL, NULL, &pErrMsg)!=SQLITE_OK){
81 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
86 if(sqlite3_exec(dbMd, createCmd3, NULL, NULL, &pErrMsg)!=SQLITE_OK){
87 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
94 /********************************************
95 finalize work db implemented with sqlite
96 ********************************************/
97 int finalizeWorkDb(void){
99 /* Close db for opengatemd */
105 /************************************************************
106 insert session-info to work db at starting session
107 ************************************************************/
108 int insertSessionToWorkDb(char* macAddress, char* userId, char* extraId,
114 /* SQL INSERT COMMAND, where %x is replaced in snprintf */
115 char *insertFormat="INSERT INTO sessionmd "
116 "(macAddress, userId, extraId, openTime, checkTime, ruleNumber) "
117 "values ('%s','%s','%s', %d, %d, %d)";
121 /* Prepare insert command */
122 insertCmd=sqlite3_mprintf(insertFormat, macAddress, userId,extraId,
123 time(NULL), time(NULL), ruleNumber);
125 /* Execute insert to sqlite */
126 if((rc=sqlite3_exec(dbMd, insertCmd, NULL, NULL, &pErrMsg))!=SQLITE_OK){
128 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
131 /*Memory free for sqlite3 string */
132 sqlite3_free(insertCmd);
137 /*************************************************************
138 update checkTime to now
139 *************************************************************/
140 int updateCheckTimeInWorkDb(char* macAddress){
144 /* SQL UPDATE COMMAND, where %x is replaced in mprintf */
145 char *updateFormat="UPDATE sessionmd "
146 "SET checkTime=%d WHERE macAddress='%s'";
150 /* prepare command */
151 updateCmd=sqlite3_mprintf(updateFormat, time(NULL), macAddress);
153 /* execute update to sqlite */
154 if(sqlite3_exec(dbMd, updateCmd, NULL, NULL, &pErrMsg)!=SQLITE_OK){
156 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
159 /*memory free for sqlite3 string */
160 sqlite3_free(updateCmd);
165 /*************************************************************
166 delete session-info in work db at stop session
167 *************************************************************/
168 int delSessionFromWorkDb(char* macAddress){
172 /* SQL DELETE COMMAND, where %x is replaced in mprintf */
173 char *deleteFormat="DELETE FROM sessionmd WHERE macAddress='%s'";
177 /* prepare command */
178 deleteCmd=sqlite3_mprintf(deleteFormat, macAddress);
181 if(sqlite3_exec(dbMd, deleteCmd, NULL, NULL, &pErrMsg)!=SQLITE_OK){
183 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
186 /*memory free for sqlite3 string */
187 sqlite3_free(deleteCmd);
192 /************************************************
193 get session-info from work db
194 input = macAddress, output = others
195 *************************************************/
196 int getSessionFromWorkDb(char* macAddress, char* userId, char* extraId,
197 int* openTime, int* checkTime,
202 /* SQL SELECT COMMAND, where %x is replaced in snprintf */
203 char *selectFormat="SELECT userId, extraId, openTime, checkTime, "
204 "ruleNumber FROM sessionmd WHERE macAddress='%s'";
208 /* prepare command string */
209 selectCmd=sqlite3_mprintf(selectFormat, macAddress);
211 /* compile to internal statement */
212 if(sqlite3_prepare(dbMd, selectCmd, BUFFMAXLN, &stmt, NULL)!=SQLITE_OK){
214 err_msg("ERR at %s#%d: sqlite3_prepare",__FILE__,__LINE__);
217 sqlite3_free(selectCmd);
218 sqlite3_finalize(stmt);
223 /* get first record */
224 if(sqlite3_step(stmt)==SQLITE_ROW){
225 strlcpy(userId, (char*)sqlite3_column_text(stmt, 0), USERMAXLN);
226 strlcpy(extraId, (char*)sqlite3_column_text(stmt, 1), USERMAXLN);
227 *openTime=(int)sqlite3_column_int(stmt, 2);
228 *checkTime=(int)sqlite3_column_int(stmt, 3);
229 *ruleNumber=(int)sqlite3_column_int(stmt, 4);
241 sqlite3_free(selectCmd);
242 sqlite3_finalize(stmt);
248 /************************************************
249 close sessions that exceed time limit
250 1. select timeover records and close the firewall
251 2. delete the records
252 if delayed=FALSE, close all sessions without delay
253 if delayed=TRUE, close sessions that exceed time limit
254 *************************************************/
255 int delUselessSessionsInWorkDb(int delayed){
258 int uselessLimitTime;
260 /* the session is useless, if it doesn't update after this time */
261 uselessLimitTime = time(NULL)-atoi(GetConfValue("UselessTimeout"));
263 /* if delayed is false, all sessions(before now) are deleted */
264 if(!delayed) uselessLimitTime = time(NULL);
266 /* SQL SELECT COMMAND, where %x is replaced in snprintf */
267 char *selectFormat="SELECT ruleNumber, userId, extraId, "
268 "macAddress, openTime FROM sessionmd WHERE checkTime<%d";
269 char *deleteFormat="DELETE FROM sessionmd WHERE checkTime<%d";
274 /* prepare command string for select */
275 selectCmd=sqlite3_mprintf(selectFormat, uselessLimitTime);
277 /* exec command, callback function = CloseSession() */
278 if(sqlite3_exec(dbMd, selectCmd, CloseSession, NULL, &pErrMsg)!=SQLITE_OK){
280 err_msg("ERR at %s#%d: sqlite_exec:%s",__FILE__,__LINE__, pErrMsg);
283 /* prepare command string for update */
284 deleteCmd=sqlite3_mprintf(deleteFormat, uselessLimitTime);
287 if(sqlite3_exec(dbMd, deleteCmd, NULL, NULL, &pErrMsg)!=SQLITE_OK){
289 err_msg("ERR at %s#%d: sqlite_exec:%s",__FILE__,__LINE__, pErrMsg);
293 sqlite3_free(selectCmd);
294 sqlite3_free(deleteCmd);
299 /************************************************
300 get list of sessions from work DB and create hash table of sessions
302 key=macAddress, value=0
303 *************************************************/
304 int getSessionTableFromWorkDb(DB* sessionTable){
307 sqlite3_stmt *stmt=NULL;
308 int resultFlag=FALSE;
309 char macAddress[ADDRMAXLN];
312 /* SQL SELECT COMMAND to get all mac address in session table */
313 char *selectCmd="SELECT macAddress FROM sessionmd";
315 /* compile to internal statement */
316 if(sqlite3_prepare(dbMd, selectCmd, BUFFMAXLN, &stmt, NULL)!=SQLITE_OK){
317 err_msg("ERR at %s#%d: sqlite3_prepare",__FILE__,__LINE__);
320 sqlite3_finalize(stmt);
324 /* get result rows */
325 while(sqlite3_step(stmt)==SQLITE_ROW){
327 strlcpy(macAddress,(char*)sqlite3_column_text(stmt, 0), ADDRMAXLN);
329 /* put to hash table */
330 hashVal.data = &zero;
331 hashVal.size = sizeof(int);
332 hashKey.data = macAddress;
333 hashKey.size = strlen(macAddress)+1;
334 if(sessionTable->put(sessionTable, &hashKey, &hashVal, 0) == -1) {
335 err_msg("ERR at %s#%d: fail to put into hash table",__FILE__,__LINE__);
340 sqlite3_finalize(stmt);
346 /**********************************
347 put out detected mac related info to macinfo table in work db
348 it is used at checking nat
349 **********************************/
350 int putMacInfoToWorkDb(char* macAddress, int ttl, int isNat){
355 /* SQL INSERT COMMAND, where %x is replaced in snprintf */
356 char *insertFormat="INSERT INTO macinfo "
357 "(macAddress, detectTime, ttl, isNat) "
358 "values ('%s', %d, %d, %d)";
362 /* Prepare insert command */
363 insertCmd=sqlite3_mprintf(insertFormat, macAddress, time(NULL), ttl, isNat);
365 /* Execute insert to sqlite */
366 if((rc=sqlite3_exec(dbMd, insertCmd, NULL, NULL, &pErrMsg))!=SQLITE_OK){
368 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
371 /*Memory free for sqlite3 string */
372 sqlite3_free(insertCmd);
378 /**********************************
379 get mac related info from macinfo table in work db
380 **********************************/
381 int getMacInfoFromWorkDb(char* macAddress, char* detectTimeStr, int* pTtl){
385 /* SQL SELECT COMMAND, where %x is replaced in snprintf */
386 char *selectFormat="SELECT datetime(detectTime,'unixepoch','localtime'), "
387 " ttl FROM macinfo WHERE macAddress='%s' ";
391 /* set default value */
393 detectTimeStr[0]='?';
394 detectTimeStr[1]='\0';
396 /* Prepare select command */
397 selectCmd=sqlite3_mprintf(selectFormat, macAddress);
399 /* compile to internal statement */
400 if(sqlite3_prepare(dbMd, selectCmd, BUFFMAXLN, &stmt, NULL)!=SQLITE_OK){
402 err_msg("ERR at %s#%d: sqlite3_prepare",__FILE__,__LINE__);
405 sqlite3_free(selectCmd);
406 sqlite3_finalize(stmt);
410 /* get first record */
411 if(sqlite3_step(stmt)==SQLITE_ROW){
412 strlcpy(detectTimeStr, (char*)sqlite3_column_text(stmt, 0), WORDMAXLN);
413 *pTtl=(int)sqlite3_column_int(stmt, 1);
420 sqlite3_free(selectCmd);
421 sqlite3_finalize(stmt);
425 /************************************
426 is the rule number active in opengatemd session table
427 ************************************/
428 int isActiveRuleInWorkDb(int ruleNumber){
432 /* SQL SELECT COMMAND, where %x is replaced in snprintf */
433 char *selectFormat="SELECT * FROM sessionmd "
434 " WHERE ruleNumber=%d";
438 /* prepare command string */
439 selectCmd=sqlite3_mprintf(selectFormat, ruleNumber);
441 /* compile to internal statement */
442 if(sqlite3_prepare(dbMd, selectCmd, BUFFMAXLN, &stmt, NULL)!=SQLITE_OK){
444 err_msg("ERR at %s#%d: sqlite3_prepare",__FILE__,__LINE__);
447 sqlite3_free(selectCmd);
448 sqlite3_finalize(stmt);
452 /* get first record */
453 if(sqlite3_step(stmt)==SQLITE_ROW) resultFlag=TRUE;
454 else resultFlag=FALSE;
457 sqlite3_free(selectCmd);
458 sqlite3_finalize(stmt);
462 /********************************************
463 Is the MAC-IP pair found in work db
464 ********************************************/
465 int isFoundMacIpPairInWorkDb(char* macAddress, char* ipAddress){
468 /* SQL SELECT COMMAND, where %x is replaced in snprintf */
469 char *selectFormat="SELECT * FROM macippair "
470 " WHERE macAddress='%s' AND ipAddress='%s'";
474 /* prepare command string */
475 selectCmd=sqlite3_mprintf(selectFormat, macAddress, ipAddress);
477 /* compile to internal statement */
478 if(sqlite3_prepare(dbMd, selectCmd, BUFFMAXLN, &stmt, NULL)!=SQLITE_OK){
480 err_msg("ERR at %s#%d: sqlite3_prepare",__FILE__,__LINE__);
483 sqlite3_free(selectCmd);
484 sqlite3_finalize(stmt);
488 /* get first record */
489 if(sqlite3_step(stmt)==SQLITE_ROW) resultFlag=TRUE;
490 else resultFlag=FALSE;
493 sqlite3_free(selectCmd);
494 sqlite3_finalize(stmt);
499 /********************************************
500 Insert MAC-IP pair to work db
501 ********************************************/
502 int putMacIpPairToWorkDb(char* macAddress, char* ipAddress){
506 /* SQL INSERT COMMAND, where %x is replaced in snprintf */
507 char *insertFormat="INSERT INTO macippair "
508 "(macAddress, ipAddress, findTime) "
509 "values ('%s','%s', %d)";
513 /* Prepare insert command */
514 insertCmd=sqlite3_mprintf(insertFormat, macAddress, ipAddress, time(NULL));
516 /* Execute insert to sqlite */
517 if((rc=sqlite3_exec(dbMd, insertCmd, NULL, NULL, &pErrMsg))!=SQLITE_OK){
519 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
522 /*Memory free for sqlite3 string */
523 sqlite3_free(insertCmd);
528 /********************************************
529 Delete the mac-ip pairs in work db
530 input=macAddress only
531 ********************************************/
532 int delMacIpPairsInWorkDb(char* macAddress){
535 /* SQL DELETE COMMAND, where %x is replaced in mprintf */
536 char *deleteFormat="DELETE FROM macippair WHERE macAddress='%s'";
540 /* prepare command */
541 deleteCmd=sqlite3_mprintf(deleteFormat, macAddress);
544 if(sqlite3_exec(dbMd, deleteCmd, NULL, NULL, &pErrMsg)!=SQLITE_OK){
546 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
549 /*memory free for sqlite3 string */
550 sqlite3_free(deleteCmd);
556 /*********************************************************
557 routines for debugging output
558 *********************************************************/
559 int SetupSqliteBusyTimeoutValue(void){
561 if(debug>1) err_msg("DEBUG:=>setupSqliteBusyTimeoutValue()");
562 ret=setupSqliteBusyTimeoutValue();
563 if(debug>1) err_msg("DEBUG:(%d)<=setupSqliteBusyTimeoutValue()",ret);
567 int InitWorkDb(void){
569 if(debug>1) err_msg("DEBUG:=>initWorkDb( )");
571 if(debug>1) err_msg("DEBUG:(%d)<=initWorkDb( )",ret);
575 int FinalizeWorkDb(void){
577 if(debug>1) err_msg("DEBUG:=>finalizeWorkDb( )");
578 ret = finalizeWorkDb();
579 if(debug>1) err_msg("DEBUG:(%d)<=finalizeWorkDb( )",ret);
583 int InsertSessionToWorkDb(char* macAddress, char* userId, char* extraId,
586 if(debug>1) err_msg("DEBUG:=>insertSessionToWorkDb(%s,%s,%s,%d)",
587 macAddress, userId, extraId, ruleNumber);
588 ret = insertSessionToWorkDb(macAddress, userId, extraId, ruleNumber);
589 if(debug>1) err_msg("DEBUG:(%d)<=insertSessionToWorkDb( )",ret);
593 int UpdateCheckTimeInWorkDb(char* macAddress){
595 if(debug>1) err_msg("DEBUG:=>updateCheckTimeInWorkDb(%s)", macAddress);
596 ret = updateCheckTimeInWorkDb(macAddress);
597 if(debug>1) err_msg("DEBUG:(%d)<=updateCheckTimeInWorkDb( )",ret);
601 int DelSessionFromWorkDb(char* macAddress){
603 if(debug>1) err_msg("DEBUG:=>delSessionFromWorkDb(%s)", macAddress);
604 ret = delSessionFromWorkDb(macAddress);
605 if(debug>1) err_msg("DEBUG:(%d)<=delSessionFromWorkDb( )",ret);
609 int GetSessionFromWorkDb(char* macAddress, char* userId, char* extraId,
610 int* openTime, int* checkTime, int* ruleNumber){
612 if(debug>1) err_msg("DEBUG:=>getSessionFromWorkDb(%s)", macAddress);
613 ret = getSessionFromWorkDb(macAddress, userId, extraId, openTime,
614 checkTime, ruleNumber);
615 if(debug>1) err_msg("DEBUG:(%d)<=getSessionFromWorkDb(,%s,%s,%d,%d,%d)",
616 ret,userId,extraId,*openTime,*checkTime, *ruleNumber);
620 int DelUselessSessionsInWorkDb(int delayed){
622 if(debug>1) err_msg("DEBUG:=>delUselessSessionsInWorkDb(%d)", delayed);
623 ret=delUselessSessionsInWorkDb(delayed);
624 if(debug>1) err_msg("DEBUG:(%d)<=delUselessSessionsInWorkDb( )",ret);
628 int GetSessionTableFromWorkDb(DB* sessionTable){
630 if(debug>1) err_msg("DEBUG:=>getSessionTableFromWorkDb( )");
631 ret=getSessionTableFromWorkDb(sessionTable);
632 if(debug>1) err_msg("DEBUG:(%d)<=getSessionTableFromWorkDb( )", ret);
636 int PutMacInfoToWorkDb(char* macAddress, int ttl, int isNat){
638 if(debug>1) err_msg("DEBUG:=>putMacInfoToWorkDb(%s,%d,%d)",macAddress,ttl,isNat);
639 ret=putMacInfoToWorkDb(macAddress,ttl,isNat);
640 if(debug>1) err_msg("DEBUG:(%d)<=putMacInfoToWorkDb( )", ret);
644 int IsActiveRuleInWorkDb(int ruleNumber){
646 if(debug>1) err_msg("DEBUG:=>isActiveRuleInWorkDb(%d)",ruleNumber);
647 ret=isActiveRuleInWorkDb(ruleNumber);
648 if(debug>1) err_msg("DEBUG:(%d)<=isActiveRuleInWorkDb( )", ret);
652 int GetMacInfoFromWorkDb(char* macAddress, char* detectTimeStr, int* pTtl){
654 if(debug>1) err_msg("DEBUG:=>getMacInfoFromWorkDb(%s)", macAddress);
655 ret = getMacInfoFromWorkDb(macAddress, detectTimeStr, pTtl);
656 if(debug>1) err_msg("DEBUG:(%d)<=getMacInfoFromWorkDb(,%s,%d)",
657 ret, detectTimeStr, *pTtl);
661 int IsFoundMacIpPairInWorkDb(char* macAddress, char* ipAddress){
663 if(debug>1) err_msg("DEBUG:=>isFoundMacIpPairInWorkDb(%s,%s)",
664 macAddress,ipAddress);
665 ret=isFoundMacIpPairInWorkDb(macAddress, ipAddress);
666 if(debug>1) err_msg("DEBUG:(%d)<=isfoundMacIpPairInWorkDb( )", ret);
670 int PutMacIpPairToWorkDb(char* macAddress, char* ipAddress){
672 if(debug>1) err_msg("DEBUG:=>putMacIpPairToWorkDb(%s,%s)",
673 macAddress,ipAddress);
674 ret=putMacIpPairToWorkDb(macAddress, ipAddress);
675 if(debug>1) err_msg("DEBUG:(%d)<=putMacIpPairtoWorkDb( )", ret);
680 int DelMacIpPairsInWorkDb(char* macAddress){
682 if(debug>1) err_msg("DEBUG:=>delMacIpPairsInWorkDb(%s)",
684 ret=delMacIpPairsInWorkDb(macAddress);
685 if(debug>1) err_msg("DEBUG:(%d)<=delMacIpPairsInWorkDb( )", ret);