1 /**************************************************
2 OpengateM - MAC address authentication system
3 module for local work database (SQLite3)
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 "opengatemmng.h"
27 static int sqliteBusyTimeout=100; /* value used in sqite3_busy_timeout() */
29 /********************************************************************/
30 /* set static variable for sqlite-busy-timeout defined in conf file */
31 /********************************************************************/
32 int setupSqliteBusyTimeoutValue(void){
36 /* if set in conf, use the value. if not, use the above default. */
37 str=GetConfValue("SqliteBusyTimeout");
38 if(str!=NULL) sqliteBusyTimeout=atoi(str);
40 return sqliteBusyTimeout;
43 /**********************************************/
44 /* initialize work db implemented with sqlite */
45 /**********************************************/
50 char *createCmd1="CREATE TABLE IF NOT EXISTS maccheck "
51 "(macAddress TEXT PRIMARY KEY, "
52 "ipv4 TEXT DEFAULT '', ipv6 TEXT DEFAULT '', "
53 " pid INTEGER DEFAULT 0, ruleIpv4 INTEGER DEFAULT 0, "
54 " ruleIpv6 INTEGER DEFAULT 0, detectTime INTEGER DEFAULT 0)";
56 char *createCmd2="CREATE TABLE IF NOT EXISTS cookietable "
57 "(cookie TEXT PRIMARY KEY, "
58 " saveTime INTEGER DEFAULT 0,"
59 " userId TEXT DEFAULT '',"
60 " extraId TEXT DEFAULT '',"
61 " userType INTEGER DEFAULT 0,"
62 " mailDefault TEXT DEFAULT '',"
63 " macAddress TEXT DEFAULT '')";
65 char *createCmd3="CREATE TABLE IF NOT EXISTS sessionmd "
66 "(ipAddress TEXT PRIMARY KEY, "
67 "userId TEXT, extraId TEXT, openTime INTEGER, checkTime INTEGER, "
68 "macAddress TEXT, ruleNumber INTEGER)";
70 char *createCmd4="CREATE TABLE IF NOT EXISTS macinfo "
71 "(macAddress TEXT PRIMARY KEY ON CONFLICT REPLACE, "
72 "detectTime INTEGER, ttl INTEGER, isNat INTEGER)";
74 /* setup static variable value for SqLite3_busy_timeout from conf */
75 SetupSqliteBusyTimeoutValue();
77 /* Open sqlite for opengateMmng */
78 if(sqlite3_open(GetConfValue("SqliteDbMmng"),&db)!=SQLITE_OK){
79 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
83 sqlite3_busy_timeout(db, sqliteBusyTimeout);
85 /* create table1 (maccheck table in opengateMmng) */
86 if(sqlite3_exec(db, createCmd1, NULL, NULL, &pErrMsg)!=SQLITE_OK){
87 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
91 /* create table2 (cookietable in opengateMmng) */
92 if(sqlite3_exec(db, createCmd2, NULL, NULL, &pErrMsg)!=SQLITE_OK){
93 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
97 /* close opengateMmng */
100 /* Open sqlite for opengateMd */
101 if(sqlite3_open(GetConfValue("SqliteDbMd"),&db)!=SQLITE_OK){
102 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
106 sqlite3_busy_timeout(db, sqliteBusyTimeout);
108 /* create table3 (sessionmd table in opengateMd) */
109 if(sqlite3_exec(db, createCmd3, NULL, NULL, &pErrMsg)!=SQLITE_OK){
110 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
114 /* create table4 (macinfo table in opengateMd) */
115 if(sqlite3_exec(db, createCmd4, NULL, NULL, &pErrMsg)!=SQLITE_OK){
116 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
120 /* close opengateMd */
125 /********************************************/
126 /* create temporary table for mac checking */
127 /* (gather mac addresses from arp and ndp) */
128 /********************************************/
129 int createMacCheckTableInWorkDb(void){
134 char *delCmd="DELETE FROM maccheck";
137 if(sqlite3_open(GetConfValue("SqliteDbMmng"),&db)!=SQLITE_OK){
138 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
142 sqlite3_busy_timeout(db, sqliteBusyTimeout);
144 /* renew mac check table */
145 if(sqlite3_exec(db, delCmd, NULL, NULL, &pErrMsg)!=SQLITE_OK){
146 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
150 /* get arp entry and add to mac check table */
152 GetMacAddrListFromArp(GetConfValue("Device"));
153 AddIpv4ToMacCheckTable();
155 /* get ndp entry and add to mac check table */
157 GetMacAddrListFromNdp(GetConfValue("Device"));
158 AddIpv6ToMacCheckTable();
165 /********************************************************/
166 /* add mac-ipv4 pair to mac check table */
167 /* execute both of update and insert commands */
168 /* because the mac address exists or not exist in table */
169 /********************************************************/
170 int addIpv4ToMacCheckTable(void){
175 char macAddr[ADDRMAXLN];
176 char ipAddr[ADDRMAXLN];
180 /* SQL COMMAND, where %x is replaced in snprintf */
181 char *insertFormat="INSERT INTO maccheck "
182 " (ipv4, macAddress, detectTime) values ('%s','%s', %d)";
187 /* get server and remote client address */
188 serverAddr=getenv("SERVER_ADDR");
191 if(sqlite3_open(GetConfValue("SqliteDbMmng"),&db)!=SQLITE_OK){
192 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
196 sqlite3_busy_timeout(db, sqliteBusyTimeout);
198 /* get queued items */
199 while(Dequeue(macAddr, ipAddr)){
201 /* if server addr (localhost addr), ignore */
202 if(strcmp(serverAddr,ipAddr)==0)continue;
204 /* get detect time from md.db */
205 detectTime=GetDetectTimeFromMacinfoTable(macAddr);
207 /* execute insert command */
208 insertCmd=sqlite3_mprintf(insertFormat, ipAddr, macAddr, detectTime);
209 if((rc=sqlite3_exec(db, insertCmd, NULL, NULL, &pErrMsg))!=SQLITE_OK){
211 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
213 sqlite3_free(insertCmd);
219 /********************************************************/
220 /* add mac-ipv6 pair to mac check table */
221 /* execute both of update and insert commands */
222 /* because the mac address exists or not exist in table */
223 /********************************************************/
224 int addIpv6ToMacCheckTable(void){
229 char macAddr[ADDRMAXLN];
230 char ipAddr[ADDRMAXLN];
234 /* get server and remote client address */
235 serverAddr=getenv("SERVER_ADDR");
237 /* SQL COMMAND, where %x is replaced in snprintf */
238 char *updateFormat="UPDATE maccheck "
239 " SET ipv6='%s',detectTime=%d where macAddress='%s'";
240 char *insertFormat="INSERT INTO maccheck "
241 " (ipv6, macAddress, detectTime) values ('%s','%s', %d)";
248 if(sqlite3_open(GetConfValue("SqliteDbMmng"),&db)!=SQLITE_OK){
249 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
253 sqlite3_busy_timeout(db, sqliteBusyTimeout);
255 /* get queued items */
256 while(Dequeue(macAddr, ipAddr)){
258 /* if server addr, ignore */
259 if(strcmp(serverAddr,ipAddr)==0)continue;
261 /* get detect time from md.db */
262 detectTime=GetDetectTimeFromMacinfoTable(macAddr);
264 /* execute update command */
265 updateCmd=sqlite3_mprintf(updateFormat, ipAddr, detectTime, macAddr);
266 if((rc=sqlite3_exec(db, updateCmd, NULL, NULL, &pErrMsg))!=SQLITE_OK){
268 /* if failed, execute insert command */
269 insertCmd=sqlite3_mprintf(insertFormat, ipAddr, macAddr, detectTime);
270 if((rc=sqlite3_exec(db, insertCmd, NULL, NULL, &pErrMsg))!=SQLITE_OK){
272 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
274 sqlite3_free(insertCmd);
276 sqlite3_free(updateCmd);
283 /***********************************************************************/
284 /* get maccheck table row */
285 /* at first call, query is done and the first row is returned */
286 /* and from second call, next row of previous query is returned */
287 /* this function should be called continuously until false is returned */
288 /***********************************************************************/
289 int getNextRowInMacCheckTable(char* macAddress, char* ipv4, char* ipv6){
291 static sqlite3_stmt *stmt=NULL;
294 /* SQL SELECT COMMAND, where %x is replaced in snprintf */
295 /* get info order by detecting time */
296 char *selectCmd="SELECT macAddress,ipv4,ipv6 "
297 " FROM maccheck order by detectTime desc ";
305 /* execute at the first call of this func */
306 /* from second call, query is not done and query result in first call is used */
310 if(sqlite3_open(GetConfValue("SqliteDbMmng"),&db)!=SQLITE_OK){
311 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
315 sqlite3_busy_timeout(db, sqliteBusyTimeout);
317 /* compile to internal statement */
318 if(sqlite3_prepare(db, selectCmd, BUFFMAXLN, &stmt, NULL)!=SQLITE_OK){
320 err_msg("ERR at %s#%d: sqlite3_prepare",__FILE__,__LINE__);
323 sqlite3_finalize(stmt);
329 /* get a record (the query result is held until next call of this func) */
330 if(sqlite3_step(stmt)==SQLITE_ROW){
331 strlcpy(macAddress, (char*)sqlite3_column_text(stmt, 0), ADDRMAXLN);
332 strlcpy(ipv4, (char*)sqlite3_column_text(stmt, 1), ADDRMAXLN);
333 strlcpy(ipv6, (char*)sqlite3_column_text(stmt, 2), ADDRMAXLN);
337 /* if not get record, clear the query result and return false */
339 sqlite3_finalize(stmt);
347 /*******************************************************/
348 /* get info from db input=macAddress, output=ipv4,ipv6 */
349 /*******************************************************/
350 int getIpFromMacCheckTable(char* macAddress, char* ipv4, char* ipv6){
352 sqlite3_stmt *stmt=NULL;
355 /* SQL SELECT COMMAND, where %x is replaced in snprintf */
356 char* selectFormat="SELECT ipv4,ipv6 "
357 " FROM maccheck where macAddress='%s'";
366 if(sqlite3_open(GetConfValue("SqliteDbMmng"),&db)!=SQLITE_OK){
367 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
371 sqlite3_busy_timeout(db, sqliteBusyTimeout);
373 /* prepare execute command */
374 selectCmd=sqlite3_mprintf(selectFormat, macAddress);
376 /* compile to internal statement */
377 if(sqlite3_prepare(db, selectCmd, BUFFMAXLN, &stmt, NULL)!=SQLITE_OK){
379 err_msg("ERR at %s#%d: sqlite3_prepare",__FILE__,__LINE__);
382 sqlite3_free(selectCmd);
383 sqlite3_finalize(stmt);
389 if(sqlite3_step(stmt)==SQLITE_ROW){
390 strlcpy(ipv4, (char*)sqlite3_column_text(stmt, 0), ADDRMAXLN);
391 strlcpy(ipv6, (char*)sqlite3_column_text(stmt, 1), ADDRMAXLN);
395 /* if not get record, return false */
401 sqlite3_free(selectCmd);
402 sqlite3_finalize(stmt);
407 /*****************************************************/
408 /* get pid and rule number from maccheck table in db */
409 /* macAddress:(input),pid,ruleIpv4,ruleIpv6:(output) */
410 /*****************************************************/
411 int getPidFromMacCheckTable(char* macAddress, int* pid, int* ruleIpv4, int* ruleIpv6){
412 sqlite3_stmt *stmt=NULL;
415 /* SQL SELECT COMMAND, where %x is replaced in snprintf */
416 char* selectFormat="SELECT pid,ruleIpv4,ruleIpv6 "
417 " FROM maccheck where macAddress='%s'";
427 if(sqlite3_open(GetConfValue("SqliteDbMmng"),&db)!=SQLITE_OK){
428 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
432 sqlite3_busy_timeout(db, sqliteBusyTimeout);
434 /* prepare execute command */
435 selectCmd=sqlite3_mprintf(selectFormat, macAddress);
437 /* compile to internal statement */
438 if(sqlite3_prepare(db, selectCmd, BUFFMAXLN, &stmt, NULL)!=SQLITE_OK){
440 err_msg("ERR at %s#%d: sqlite3_prepare",__FILE__,__LINE__);
443 sqlite3_free(selectCmd);
444 sqlite3_finalize(stmt);
450 if(sqlite3_step(stmt)==SQLITE_ROW){
451 *pid = (int)sqlite3_column_int(stmt, 0);
452 *ruleIpv4 = (int)sqlite3_column_int(stmt, 1);
453 *ruleIpv6 = (int)sqlite3_column_int(stmt, 2);
457 /* if not get record, return false */
463 sqlite3_free(selectCmd);
464 sqlite3_finalize(stmt);
469 /****************************************************/
470 /* save pid and rule number to maccheck table in db */
471 /****************************************************/
472 int savePidToMacCheckTable(char* macAddress, int pid, int ruleIpv4, int ruleIpv6){
477 /* SQL COMMAND, where %x is replaced in snprintf */
478 char *updateFormat="UPDATE maccheck "
479 " SET pid=%d, ruleIpv4=%d, ruleIpv6=%d where macAddress='%s'";
484 if(sqlite3_open(GetConfValue("SqliteDbMmng"),&db)!=SQLITE_OK){
485 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
489 sqlite3_busy_timeout(db, sqliteBusyTimeout);
491 /* prepare execute update command */
492 updateCmd=sqlite3_mprintf(updateFormat, pid,
493 ruleIpv4, ruleIpv6, macAddress);
494 if((rc=sqlite3_exec(db, updateCmd, NULL, NULL, &pErrMsg))!=SQLITE_OK){
496 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
500 sqlite3_free(updateCmd);
505 /****************************************************/
506 /* get detect time of mac from macinfo table in db */
507 /****************************************************/
508 int getDetectTimeFromMacinfoTable(char* macAddress){
510 sqlite3_stmt *stmt=NULL;
514 /* SQL SELECT COMMAND, where %x is replaced in snprintf */
515 char* selectFormat="SELECT detectTime "
516 " FROM macinfo where macAddress='%s'";
521 if(sqlite3_open(GetConfValue("SqliteDbMd"),&db)!=SQLITE_OK){
522 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
526 sqlite3_busy_timeout(db, sqliteBusyTimeout);
528 /* prepare execute command */
529 selectCmd=sqlite3_mprintf(selectFormat, macAddress);
531 /* compile to internal statement */
532 if(sqlite3_prepare(db, selectCmd, BUFFMAXLN, &stmt, NULL)!=SQLITE_OK){
534 err_msg("ERR at %s#%d: sqlite3_prepare",__FILE__,__LINE__);
537 sqlite3_free(selectCmd);
538 sqlite3_finalize(stmt);
544 if(sqlite3_step(stmt)==SQLITE_ROW){
545 detectTime = (int)sqlite3_column_int(stmt, 0);
549 sqlite3_free(selectCmd);
550 sqlite3_finalize(stmt);
555 /*********************************************/
556 /* the cookie is found in db or not found */
557 /* in: cookie, userType; out: userId,extraId */
558 /*********************************************/
559 int isCookieFoundInWorkDb(char* cookie, char* userId, char* extraId, int userType){
560 sqlite3_stmt *stmt=NULL;
563 /* SQL SELECT COMMAND, where %x is replaced in snprintf */
564 char* selectFormat="SELECT userId,extraId "
565 " FROM cookietable WHERE cookie='%s' and userType=%d" ;
570 if(sqlite3_open(GetConfValue("SqliteDbMmng"),&db)!=SQLITE_OK){
571 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
575 sqlite3_busy_timeout(db, sqliteBusyTimeout);
577 /* prepare execute command */
578 selectCmd=sqlite3_mprintf(selectFormat, cookie, userType);
580 /* compile to internal statement */
581 if(sqlite3_prepare(db, selectCmd, BUFFMAXLN, &stmt, NULL)!=SQLITE_OK){
583 err_msg("ERR at %s#%d: sqlite3_prepare",__FILE__,__LINE__);
586 sqlite3_free(selectCmd);
587 sqlite3_finalize(stmt);
593 if(sqlite3_step(stmt)==SQLITE_ROW){
594 strlcpy(userId, (char*)sqlite3_column_text(stmt, 0), USERMAXLN);
595 strlcpy(extraId, (char*)sqlite3_column_text(stmt, 1), USERMAXLN);
598 else resultFlag=FALSE;
601 sqlite3_free(selectCmd);
602 sqlite3_finalize(stmt);
607 /***************************************************/
608 /* is session for the mac found in table sessionmd */
609 /***************************************************/
610 int isSessionFoundInSessionTable(char* macAddress){
612 sqlite3_stmt *stmt=NULL;
616 /* SQL SELECT COMMAND, where %x is replaced in snprintf */
617 char* selectFormat="SELECT count(*) "
618 " FROM sessionmd where macAddress='%s' limit 1";
623 if(sqlite3_open(GetConfValue("SqliteDbMd"),&db)!=SQLITE_OK){
624 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
628 sqlite3_busy_timeout(db, sqliteBusyTimeout);
630 /* prepare execute command */
631 selectCmd=sqlite3_mprintf(selectFormat, macAddress);
633 /* compile to internal statement */
634 if(sqlite3_prepare(db, selectCmd, BUFFMAXLN, &stmt, NULL)!=SQLITE_OK){
636 err_msg("ERR at %s#%d: sqlite3_prepare",__FILE__,__LINE__);
639 sqlite3_free(selectCmd);
640 sqlite3_finalize(stmt);
646 if(sqlite3_step(stmt)==SQLITE_ROW){
647 count = (int)sqlite3_column_int(stmt, 0);
651 sqlite3_free(selectCmd);
652 sqlite3_finalize(stmt);
658 /*******************************************************/
659 /* save cookie(sended to client) to cookie table in db */
660 /*******************************************************/
661 int saveCookieToWorkDb(char* cookie, char* userId, char* extraId, int userType){
667 char* delFormat="DELETE FROM cookietable where saveTime < %d";
669 char* insertFormat="INSERT INTO cookietable "
670 " (cookie, saveTime, userId, extraId, userType) "
671 " values ('%s', %d, '%s', '%s', %d)";
676 if(sqlite3_open(GetConfValue("SqliteDbMmng"),&db)!=SQLITE_OK){
677 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
681 sqlite3_busy_timeout(db, sqliteBusyTimeout);
683 /* execute delete command to delete items before 24 hours */
684 delCmd=sqlite3_mprintf(delFormat, time(NULL)-(60*60*24));
685 if((rc=sqlite3_exec(db, delCmd, NULL, NULL, &pErrMsg))!=SQLITE_OK){
687 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
690 /* execute insert command */
691 insertCmd=sqlite3_mprintf(insertFormat, cookie, time(NULL), userId, extraId, userType);
692 if((rc=sqlite3_exec(db, insertCmd, NULL, NULL, &pErrMsg))!=SQLITE_OK){
694 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
698 sqlite3_free(delCmd);
699 sqlite3_free(insertCmd);
704 /********************************************/
705 /* save mac address corresponding to cookie */
706 /* to cookie table in db */
707 /********************************************/
708 int saveMacForCookieToWorkDb(char* cookie, char* macAddress){
714 char* updateFormat="UPDATE cookietable "
715 " SET macAddress='%s' where cookie='%s'";
720 if(sqlite3_open(GetConfValue("SqliteDbMmng"),&db)!=SQLITE_OK){
721 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
725 sqlite3_busy_timeout(db, sqliteBusyTimeout);
727 /* execute update command */
728 updateCmd=sqlite3_mprintf(updateFormat, macAddress, cookie);
729 if((rc=sqlite3_exec(db, updateCmd, NULL, NULL, &pErrMsg))!=SQLITE_OK){
731 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
735 sqlite3_free(updateCmd);
740 /*****************************************************/
741 /* save mail address default corresponding to cookie */
742 /* to cookie table in db */
743 /*****************************************************/
744 int saveMailDefalutForCookieToWorkDb(char* cookie, char* mailDefault){
750 char* updateFormat="UPDATE cookietable "
751 " SET mailDefault='%s' where cookie='%s'";
756 if(sqlite3_open(GetConfValue("SqliteDbMmng"),&db)!=SQLITE_OK){
757 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
761 sqlite3_busy_timeout(db, sqliteBusyTimeout);
763 /* execute update command */
764 updateCmd=sqlite3_mprintf(updateFormat, mailDefault, cookie);
765 if((rc=sqlite3_exec(db, updateCmd, NULL, NULL, &pErrMsg))!=SQLITE_OK){
767 err_msg("ERR at %s#%d: sqlite3_exec: %s",__FILE__,__LINE__,pErrMsg);
771 sqlite3_free(updateCmd);
776 /*******************************************/
777 /* get maildefault corresponding to cookie */
778 /* from cookie table in db */
779 /*******************************************/
780 int getMailDefaultFromWorkDb(char* cookie, char* mailDefault){
781 sqlite3_stmt *stmt=NULL;
784 /* SQL SELECT COMMAND, where %x is replaced in snprintf */
785 char* selectFormat="SELECT mailDefault "
786 " FROM cookietable WHERE cookie='%s'" ;
791 if(sqlite3_open(GetConfValue("SqliteDbMmng"),&db)!=SQLITE_OK){
792 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
796 sqlite3_busy_timeout(db, sqliteBusyTimeout);
798 /* prepare execute command */
799 selectCmd=sqlite3_mprintf(selectFormat, cookie);
801 /* compile to internal statement */
802 if(sqlite3_prepare(db, selectCmd, BUFFMAXLN, &stmt, NULL)!=SQLITE_OK){
804 err_msg("ERR at %s#%d: sqlite3_prepare",__FILE__,__LINE__);
807 sqlite3_free(selectCmd);
808 sqlite3_finalize(stmt);
814 if(sqlite3_step(stmt)==SQLITE_ROW){
815 strlcpy(mailDefault, (char*)sqlite3_column_text(stmt, 0), USERMAXLN);
818 else resultFlag=FALSE;
821 sqlite3_free(selectCmd);
822 sqlite3_finalize(stmt);
827 /************************************************/
828 /* read mac address corresponding to cookie */
829 /* in cookie table in work db */
830 /************************************************/
831 int loadMacForCookieFromWorkDb(char* cookie, char* macAddress){
832 sqlite3_stmt *stmt=NULL;
835 /* SQL SELECT COMMAND, where %x is replaced in snprintf */
836 char* selectFormat="SELECT macAddress "
837 " FROM cookietable WHERE cookie='%s'" ;
842 if(sqlite3_open(GetConfValue("SqliteDbMmng"),&db)!=SQLITE_OK){
843 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
847 sqlite3_busy_timeout(db, sqliteBusyTimeout);
849 /* prepare execute command */
850 selectCmd=sqlite3_mprintf(selectFormat, cookie);
852 /* compile to internal statement */
853 if(sqlite3_prepare(db, selectCmd, BUFFMAXLN, &stmt, NULL)!=SQLITE_OK){
855 err_msg("ERR at %s#%d: sqlite3_prepare",__FILE__,__LINE__);
858 sqlite3_free(selectCmd);
859 sqlite3_finalize(stmt);
865 if(sqlite3_step(stmt)==SQLITE_ROW){
866 strlcpy(macAddress, (char*)sqlite3_column_text(stmt, 0), USERMAXLN);
869 else resultFlag=FALSE;
871 sqlite3_free(selectCmd);
872 sqlite3_finalize(stmt);
879 /**********************************************/
880 /* is the mac address NAT */
881 /* (suspected info is in macinfo table in db) */
882 /* *if db access fails, return false* */
883 /**********************************************/
884 int isNatSuspectedInWorkDb(char* macAddr){
890 /* SQL SELECT COMMAND, where %x is replaced in snprintf */
891 char *selectFormat="SELECT isNat "
892 "FROM macinfo WHERE macAddress='%s'";
897 if(sqlite3_open(GetConfValue("SqliteDbMd"),&db)!=SQLITE_OK){
898 err_msg("ERR at %s#%d: sqlite3_open",__FILE__,__LINE__);
902 sqlite3_busy_timeout(db, sqliteBusyTimeout);
904 /* prepare command string */
905 selectCmd=sqlite3_mprintf(selectFormat, macAddr);
907 /* compile to internal statement */
908 if(sqlite3_prepare(db, selectCmd, BUFFMAXLN, &stmt, NULL)!=SQLITE_OK){
909 err_msg("ERR at %s#%d: sqlite3_prepare",__FILE__,__LINE__);
912 sqlite3_free(selectCmd);
913 sqlite3_finalize(stmt);
918 /* get first record */
919 if(sqlite3_step(stmt)==SQLITE_ROW){
920 isNat=(int)sqlite3_column_int(stmt, 0);
927 sqlite3_free(selectCmd);
928 sqlite3_finalize(stmt);
935 /*******************************************************************/
936 /* is active session for Opengate(not OpengateM) found for the mac */
937 /*******************************************************************/
938 int isActiveSessionFoundInOpengateSessionTable(char* macAddress){
940 sqlite3_stmt *stmt=NULL;
944 /* SQL SELECT COMMAND, where %x is replaced in snprintf */
945 char* selectFormat="SELECT count(*) "
946 " FROM session where (clientAddr4='%s' or clientAddr6='%s') "
947 " and closeTime='-' limit 1";
951 /* Open sqlite (failed if no opengate usage) */
952 if(sqlite3_open(GetConfValue("SqliteDb"),&db)!=SQLITE_OK){
956 sqlite3_busy_timeout(db, sqliteBusyTimeout);
958 /* prepare execute command */
959 selectCmd=sqlite3_mprintf(selectFormat, macAddress, macAddress);
961 /* compile to internal statement */
962 if(sqlite3_prepare(db, selectCmd, BUFFMAXLN, &stmt, NULL)!=SQLITE_OK){
964 err_msg("ERR at %s#%d: sqlite3_prepare",__FILE__,__LINE__);
967 sqlite3_free(selectCmd);
968 sqlite3_finalize(stmt);
974 if(sqlite3_step(stmt)==SQLITE_ROW){
975 count = (int)sqlite3_column_int(stmt, 0);
979 sqlite3_free(selectCmd);
980 sqlite3_finalize(stmt);
986 /*********************************************************
987 routines for debugging output
988 *********************************************************/
989 int SetupSqliteBusyTimeoutValue(void){
991 if(debug>1) err_msg("DEBUG:=>setupSqliteBusyTimeoutValue()");
992 ret=setupSqliteBusyTimeoutValue();
993 if(debug>1) err_msg("DEBUG:(%d)<=setupSqliteBusyTimeoutValue()",ret);
997 int InitWorkDb(void){
999 if(debug>1) err_msg("DEBUG:=>initWorkDb( )");
1001 if(debug>1) err_msg("DEBUG:(%d)<=initWorkDb( )",ret);
1005 int CreateMacCheckTableInWorkDb(void){
1007 if(debug>1) err_msg("DEBUG:=>createmacChecktableinWorkDb( )");
1008 ret = createMacCheckTableInWorkDb();
1009 if(debug>1) err_msg("DEBUG:(%d)<=createMacCheckTableInWorkDb()", ret);
1013 int AddIpv4ToMacCheckTable(void){
1015 if(debug>1) err_msg("DEBUG:=> addIpv4ToMacCheckTable( )");
1016 ret = addIpv4ToMacCheckTable();
1017 if(debug>1) err_msg("DEBUG:(%d)<= addIpv4ToMacCheckTable( )", ret);
1021 int AddIpv6ToMacCheckTable(void){
1023 if(debug>1) err_msg("DEBUG:=>addIpv6ToMacCheckTable( )");
1024 ret = addIpv6ToMacCheckTable();
1025 if(debug>1) err_msg("DEBUG:(%d)<=addIpv6ToMacCheckTable( )", ret);
1029 int GetNextRowInMacCheckTable(char* macAddress, char* ipv4, char* ipv6){
1031 if(debug>1) err_msg("DEBUG:=>getNextRowInMacCheckTable( )");
1032 ret = getNextRowInMacCheckTable(macAddress, ipv4, ipv6);
1033 if(debug>1) err_msg("DEBUG:(%d)<=getNextRowInMacCheckTable(%s,%s,%s)",
1034 ret,macAddress,ipv4,ipv6);
1038 int GetIpFromMacCheckTable(char* macAddress, char* ipv4, char* ipv6){
1040 if(debug>1) err_msg("DEBUG:=>getIpFromMacCheckTable(%s)",macAddress);
1041 ret = getIpFromMacCheckTable(macAddress, ipv4, ipv6);
1042 if(debug>1) err_msg("DEBUG:(%d)<=getIpFromMacCheckTable(,%s,%s)",
1046 int GetPidFromMacCheckTable(char* macAddress, int* pid, int* ruleIpv4, int* ruleIpv6){
1048 if(debug>1) err_msg("DEBUG:=>getPidFromMacCheckTable(%s)",macAddress);
1049 ret = getPidFromMacCheckTable(macAddress, pid, ruleIpv4, ruleIpv6);
1050 if(debug>1) err_msg("DEBUG:(%d)<=getPidFromMacCheckTable(,%d,%d,%d)",
1051 ret,*pid,*ruleIpv4,*ruleIpv6);
1054 int SavePidToMacCheckTable(char* macAddress, int pid, int ruleIpv4, int ruleIpv6){
1056 if(debug>1) err_msg("DEBUG:=>savePidToMacCheckTable(%s,%d,%d,%d)",
1057 macAddress, pid, ruleIpv4, ruleIpv6);
1058 ret = savePidToMacCheckTable(macAddress, pid, ruleIpv4, ruleIpv6);
1059 if(debug>1) err_msg("DEBUG:(%d)<=savePidToMacCheckTable( )",ret);
1063 int GetDetectTimeFromMacinfoTable(char* macAddress){
1065 if(debug>1) err_msg("DEBUG:=>getDetectTimeFromMacinfoTable(%s)",macAddress);
1066 ret = getDetectTimeFromMacinfoTable(macAddress);
1067 if(debug>1) err_msg("DEBUG:(%d)<=getDetectTimeFromMacinfoTable( )",ret);
1070 int IsSessionFoundInSessionTable(char* macAddress){
1072 if(debug>1) err_msg("DEBUG:=>isSessionFoundInSessionTable(%s)",macAddress);
1073 ret = isSessionFoundInSessionTable(macAddress);
1074 if(debug>1) err_msg("DEBUG:(%d)<=isSessionFoundInSessionTable( )",ret);
1078 int SaveCookieToWorkDb(char* cookie, char* userId, char* extraId, int userType){
1080 if(debug>1) err_msg("DEBUG:=>saveCookieToWorkDb(%s,%s,%s,%d)",cookie,userId,extraId,userType);
1081 ret = saveCookieToWorkDb(cookie,userId,extraId,userType);
1082 if(debug>1) err_msg("DEBUG:(%d)<=saveCookieToWorkDb( )",ret);
1086 int IsCookieFoundInWorkDb(char* cookie, char* userId, char* extraId, int userType){
1088 if(debug>1) err_msg("DEBUG:=> isCookieFoundInWorkDb(%s,%d)",cookie, userType);
1089 ret = isCookieFoundInWorkDb(cookie,userId,extraId,userType);
1090 if(debug>1) err_msg("DEBUG:(%d)<=isCookieFoundInWorkDb(%s,%s)",ret, userId,extraId);
1094 int GetMailDefaultFromWorkDb(char* cookie, char* mailDefault){
1096 if(debug>1) err_msg("DEBUG:=> getMailDefaultFromWorkDb(%s)",cookie);
1097 ret = getMailDefaultFromWorkDb(cookie,mailDefault);
1098 if(debug>1) err_msg("DEBUG:(%d)<=getMailDefaultFromWorkDb(%s)",ret,mailDefault);
1102 int IsNatSuspectedInWorkDb(char* macAddr){
1104 if(debug>1) err_msg("DEBUG:=>isNatSuspectedInWorkDb(%s)", macAddr);
1105 ret = isNatSuspectedInWorkDb(macAddr);
1106 if(debug>1) err_msg("DEBUG:(%d)<=isNatSuspectedInWorkDb( )", ret);
1110 int SaveMacForCookieToWorkDb(char* cookie, char* macAddress){
1112 if(debug>1) err_msg("DEBUG:=>saveMacForCookieToWorkDb(%s,%s)", cookie,macAddress);
1113 ret = saveMacForCookieToWorkDb(cookie,macAddress);
1114 if(debug>1) err_msg("DEBUG:(%d)<=saveMacForCookieToWorkDb( )", ret);
1118 int SaveMailDefalutForCookieToWorkDb(char* cookie, char* mailDefault){
1120 if(debug>1) err_msg("DEBUG:=>saveMailDefalutForCookieToWorkDb(%s,%s)", cookie,mailDefault);
1121 ret = saveMailDefalutForCookieToWorkDb(cookie,mailDefault);
1122 if(debug>1) err_msg("DEBUG:(%d)<=saveMailDefalutForCookieToWorkDb( )", ret);
1127 int LoadMacForCookieFromWorkDb(char* cookie, char* macAddress){
1129 if(debug>1) err_msg("DEBUG:=>loadMacForCookieFromWorkDb(%s)", cookie);
1130 ret = loadMacForCookieFromWorkDb(cookie,macAddress);
1131 if(debug>1) err_msg("DEBUG:(%d)<=loadMacForCookieFromWorkDb(%s)", ret, macAddress);
1135 int IsActiveSessionFoundInOpengateSessionTable(char* macAddress){
1137 if(debug>1) err_msg("DEBUG:=>isActiveSessionFoundInOpengateSessionTable(%s)", macAddress);
1138 ret = isActiveSessionFoundInOpengateSessionTable(macAddress);
1139 if(debug>1) err_msg("DEBUG:(%d)<=isActiveSessionFoundInOpengateSessionTable( )", ret);