From 1ea1bda6ea2508c5e07a81186d2e2924f485c328 Mon Sep 17 00:00:00 2001 From: Metabox Date: Sat, 20 Apr 2019 08:29:26 +1000 Subject: [PATCH] Login, config and multi database implementation. --- Installation.txt | 6 +- htdocs/cgi-bin/config.cgi | 158 ++++++++--------------------- htdocs/cgi-bin/login_ctr.cgi | 191 ++++++++++++++++++++++++++++++----- htdocs/cgi-bin/main.cgi | 157 ++++++++++++---------------- 4 files changed, 276 insertions(+), 236 deletions(-) diff --git a/Installation.txt b/Installation.txt index bf7cc47..8ab22f7 100644 --- a/Installation.txt +++ b/Installation.txt @@ -63,9 +63,9 @@ sudo cpanm Text::CSV; sudo cpanm Number::Bytes::Human; sudo cpanm CGI::Session; sudo cpanm TryCatch; -sudo cpanm Number/Bytes/Human.pm -sudo cpanm Regexp::Common - +sudo cpanm Number/Bytes/Human.pm; +sudo cpanm Regexp::Common; +sudo cpanm Crypt::CBC; #Upgrade Instructions diff --git a/htdocs/cgi-bin/config.cgi b/htdocs/cgi-bin/config.cgi index 7b31005..b74d748 100755 --- a/htdocs/cgi-bin/config.cgi +++ b/htdocs/cgi-bin/config.cgi @@ -1,11 +1,15 @@ #!/usr/bin/perl - +# +# Programed in vim by: Will Budic +# Open Source License -> https://choosealicense.com/licenses/isc/ +# use strict; use warnings; use Try::Tiny; use Switch; use CGI; +use CGI::Session '-ip_match'; use DBI; use DateTime; @@ -13,30 +17,40 @@ use DateTime::Format::SQLite; use DateTime::Duration; use Text::CSV; -my $driver = "SQLite"; -my $database = "../../dbLifeLog/data_log.db"; -my $dsn = "DBI:$driver:dbname=$database"; -my $userid = $ENV{'DB_USER'}; -my $password = $ENV{'DB_PASS'}; +#DEFAULT SETTINGS HERE! +our $REC_LIMIT = 25; +our $TIME_ZONE = 'Australia/Sydney'; +our $PRC_WIDTH = '60'; +#END OF DEFAULT SETTINGS + -my $db = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) - or die "

Error->"& $DBI::errstri &"

"; +my $q = CGI->new; +my $session = new CGI::Session(undef, $q); +my $dbname=$session->param('database'); +my $userid=$session->param('alias'); +my $password=$session->param('passw'); + +### Authenticate session to alias password +# +if(!$userid || !$dbname){ + print $q->redirect('login_ctr.cgi'); + exit; +} + +my $database = '../../dbLifeLog/'.$dbname; +my $dsn= "DBI:SQLite:dbname=$database"; +my $db = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die "

Error->"& $DBI::errstri &"

"; -#DEFAULT SETTINGS HERE! -my $REC_LIMIT = 25; -my $TIME_ZONE = 'Australia/Sydney'; -#END OF my $rv; my $dbs; my $today = DateTime->now; $today->set_time_zone( $TIME_ZONE ); ##################### - &checkCreateTablesAndSettings; + &getConfiguration; ##################### -my $q = CGI->new; print $q->header(-expires=>"+6os", -charset=>"UTF-8"); @@ -208,113 +222,27 @@ catch{ } -sub checkCreateTablesAndSettings{ - - -$dbs = $db->prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='LOG';"); - $dbs->execute(); -try{ - if(!$dbs->fetchrow_array()) { - my $stmt = qq( - CREATE TABLE LOG ( - ID_CAT TINY NOT NULL, - DATE DATETIME NOT NULL, - LOG VCHAR(128) NOT NULL, - AMMOUNT integer - ); - ); - - $rv = $db->do($stmt); - - if($rv < 0) { - print "

Error->"& $DBI::errstri &"

"; - } +sub getConfiguration{ + try{ + $dbs = $db->prepare("SELECT * FROM CONFIG;"); + $dbs->execute(); - $dbs = $db->prepare('INSERT INTO LOG VALUES (?,?,?,?)'); + while (my @r=$dbs->fetchrow_array()){ + + switch ($r[1]) { - $dbs->execute( 3, $today, "DB Created!",0); + case "REC_LIMIT" {$REC_LIMIT=$r[2]} + case "TIME_ZONE" {$TIME_ZONE=$r[2]} + case "PRC_WIDTH" {$PRC_WIDTH=$r[2]} + else {print "Unknow variable setting: ".$r[1]. " == ". $r[2]} - - } - - $dbs = $db->prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='CAT';"); - $dbs->execute(); - if(!$dbs->fetchrow_array()) { - my $stmt = qq( - CREATE TABLE CAT( - ID TINY PRIMARY KEY NOT NULL, - NAME VCHAR(16), - DESCRIPTION VCHAR(64) - ); - ); - - $rv = $db->do($stmt); - - if($rv < 0) { - print "

Error->"& $DBI::errstri &"

"; - } - - $dbs = $db->prepare('INSERT INTO CAT VALUES (?,?,?)'); - - $dbs->execute(1,"Unspecified", "For quick uncategoriesed entries."); - $dbs->execute(3,"File System", "Operating file system short log."); - $dbs->execute(6,"System Log", "Operating system important log."); - $dbs->execute(9,"Event", "Event that occured, meeting, historical important."); - $dbs->execute(28,"Personal", "Personal log of historical importants, diary type."); - $dbs->execute(32, "Expense", "Significant yearly expense."); - $dbs->execute(35, "Income", "Significant yearly income."); - $dbs->execute(40, "Work", "Work related entry, worth monitoring."); - $dbs->execute(45, "Food", "Quick reference to recepies, observations."); - } - - $dbs = $db->prepare("SELECT name FROM sqlite_master - WHERE type='table' AND name='CONFIG';"); - $dbs->execute(); - - if(!$dbs->fetchrow_array()) { - - my $stmt = qq( - - CREATE TABLE CONFIG( - ID INT PRIMARY KEY NOT NULL, - NAME VCHAR(16), - VALUE VCHAR(64) - ); - - ); - - $rv = $db->do($stmt); - - if($rv < 0) { - print "

Error->"& $DBI::errstri &"

"; - } - - $dbs = $db->prepare('INSERT INTO CONFIG VALUES (?,?)'); - $dbs->execute("REC_LIMIT", "25"); - $dbs->execute("TIME_ZONE", "Australia/Sydney"); - - } - - $dbs = $db->prepare("SELECT * FROM CONFIG;"); - $dbs->execute(); - - while (my @r=$dbs->fetchrow_array()){ - - switch ($r[1]) { - - case "REC_LIMIT" {$REC_LIMIT=$r[2]} - case "TIME_ZONE" {$TIME_ZONE=$r[2]} - else {print "Unknow variable setting: ".$r[1]. " == ". $r[2]} + } } - } - -} -catch{ - print "SERVER ERROR:".$_; -} - + catch{ + print "SERVER ERROR:".$_; + } } diff --git a/htdocs/cgi-bin/login_ctr.cgi b/htdocs/cgi-bin/login_ctr.cgi index ea931e6..f7ac08a 100755 --- a/htdocs/cgi-bin/login_ctr.cgi +++ b/htdocs/cgi-bin/login_ctr.cgi @@ -1,62 +1,199 @@ #!/usr/bin/perl - +# +# Programed in vim by: Will Budic +# Open Source License -> https://choosealicense.com/licenses/isc/ +# use strict; use warnings; -use Try::Tiny; -use Switch; - +use Try::Tiny; use CGI; -use CGI::Session; +use CGI::Session '-ip_match'; use DBI; use DateTime; use DateTime::Format::SQLite; use DateTime::Duration; use Text::CSV; +use Crypt::CBC; +use Crypt::IDEA; -my $driver = "SQLite"; -my $database = "../../dbLifeLog/data_config_test_log.db"; -my $dsn = "DBI:$driver:dbname=$database"; -my $userid = $ENV{'DB_USER'}; -my $password = $ENV{'DB_PASS'}; +#DEFAULT SETTINGS HERE! +our $REC_LIMIT = 25; +our $TIME_ZONE = 'Australia/Sydney'; +our $PRC_WIDTH = '60'; +#END OF DEFAULT SETTINGS +my $cgi = CGI->new; +my $session = new CGI::Session(undef,$cgi); +#dev session setting change to +1h, hard coded for now. - WB +$session->expire('+2m'); +my $sid=$session->id(); +my $cookie = $cgi->cookie(CGISESSID => $sid); -my $rv; -my $dbs; +my $alias = $cgi->param('alias'); +my $passw = $cgi->param('passw'); +if(!$alias){$alias=""}; +if(!$passw){$passw=""} +#This is the OS developer release key and cipher, replace on istallation. As it is not secure. +my $cipher_key = '95d7a85ba891da896d0d87aca6d742d5'; +my $cipher = new Crypt::CBC({key => $cipher_key, cipher => 'IDEA'}); -my $q = CGI->new; - -print $q->header(-expires=>"+6os", -charset=>"UTF-8"); -print $q->start_html(-title => "Personal Log", - -script=>{-type => 'text/javascript', -src => 'wsrc/main.js'}, - -style =>{-type => 'text/css', -src => 'wsrc/main.css'}, - ); + +if(&processSubmit){ + +}else{ + +print $cgi->header(-expires=>"+6os", -charset=>"UTF-8", -cookie=>$cookie); +print $cgi->start_html(-title => "Personal Log Login", + -script=>{-type => 'text/javascript', -src => 'wsrc/main.js'}, + -style =>{-type => 'text/css', -src => 'wsrc/main.css'}, + ); my $frm = qq( -
+
- - + + - - + + -
LOGIN
Alias:
Alias:
Password:
Password:
NOTICE!  If here the first time? Write down your alias and password, before proceeding. So you can comeback in the future to continue. Only you can know it. -
); + NOTICE!  If here the first time? Write down your alias and password, before proceeding. So you can comeback in the future to continue. Only you can know it. + + + +); print "
"; print "\n
\n" . $frm ."\n
\n
"; print "
"; +} -print $q->end_html; +print $cgi->end_html; exit; +sub processSubmit{ + if($alias&&$passw){ + $passw = $cipher->encrypt_hex($passw); + &checkCreateTables; + $session = CGI::Session->load(); + $session->param('alias', $alias); + $session->param('passw', $passw); + $session->param('database', 'data_'.$alias.'_log.db'); + $session->param('cipher', $cipher_key); + $session->save_param($cgi); + print $cgi->redirect('main.cgi'); + + return 1; + } +return 0; +} + +sub checkCreateTables{ +try{ + my $today = DateTime->now; + $today->set_time_zone( $TIME_ZONE ); + my $database = '../../dbLifeLog/'.'data_'.$alias.'_log.db'; + my $dsn= "DBI:SQLite:dbname=$database"; + my $db = DBI->connect($dsn, $alias, $passw, { RaiseError => 1 }) + or die "

Error->"& $DBI::errstri &"

"; + my $rv; + my $st = $db->prepare(selSQLTbl('LOG')); + $st->execute(); + + if(!$st->fetchrow_array()) { + my $stmt = qq( + CREATE TABLE LOG ( + ID_CAT TINY NOT NULL, + DATE DATETIME NOT NULL, + LOG VCHAR(128) NOT NULL, + AMMOUNT integer + ); + ); + $rv = $db->do($stmt); + if($rv < 0){print "

Error->"& $DBI::errstri &"

";} + + $st = $db->prepare('INSERT INTO LOG VALUES (?,?,?,?)'); + $st->execute( 3, $today, "DB Created!",0); + } + $st = $db->prepare(selSQLTbl('CAT')); + $st->execute(); + if(!$st->fetchrow_array()) { + my $stmt = qq( + CREATE TABLE CAT( + ID TINY PRIMARY KEY NOT NULL, + NAME VCHAR(16), + DESCRIPTION VCHAR(64) + ); + ); + $rv = $db->do($stmt); + + $st = $db->prepare('INSERT INTO CAT VALUES (?,?,?)'); + $st->execute(1,"Unspecified", "For quick uncategories entries."); + $st->execute(3,"File System", "Operating file system short log."); + $st->execute(6,"System Log", "Operating system inportant log."); + $st->execute(9,"Event", "Event that occured, meeting, historical important."); + $st->execute(28,"Personal", "Personal log of historical importants, diary type."); + $st->execute(32, "Expense", "Significant yearly expense."); + $st->execute(35, "Income", "Significant yearly income."); + $st->execute(40, "Work", "Work related entry, worth monitoring."); + $st->execute(45, "Food", "Quick reference to recepies, observations."); + } + $st = $db->prepare(selSQLTbl('AUTH')); + $st->execute(); + if(!$st->fetchrow_array()) { + my $stmt = qq( + CREATE TABLE AUTH( + alias TEXT PRIMARY KEY, + passw TEXT + ) WITHOUT ROWID; + ); + $rv = $db->do($stmt); + if($rv < 0){print "

Error->"& $DBI::errstri &"

"}; + + } + + $st = $db->prepare("SELECT * FROM AUTH WHERE alias='$alias' AND passw='$passw';"); + $st->execute(); + if(!$st->fetchrow_array()) { + $st = $db->prepare('INSERT INTO AUTH VALUES (?,?)'); + $st->execute($alias, $passw); + } + + $st = $db->prepare(selSQLTbl('CONFIG')); + $st->execute(); + if(!$st->fetchrow_array()) { + my $stmt = qq( + CREATE TABLE CONFIG( + ID INT PRIMARY KEY NOT NULL, + NAME VCHAR(16), + VALUE VCHAR(64) + ); + ); + $rv = $db->do($stmt); + + $st = $db->prepare('INSERT INTO CONFIG VALUES (?,?)'); + $st->execute("REC_LIMIT", $REC_LIMIT); + $st->execute("TIME_ZONE", $TIME_ZONE); + $st->execute("PRC_WIDTH", $PRC_WIDTH); + } +} + catch{ + print "SERVER ERROR:".$_; + } +} + +sub selSQLTbl{ + my $name = @_; +return "SELECT name FROM sqlite_master WHERE type='table' AND name='$name';" +} + ### CGI END diff --git a/htdocs/cgi-bin/main.cgi b/htdocs/cgi-bin/main.cgi index 0c56acb..9f40369 100755 --- a/htdocs/cgi-bin/main.cgi +++ b/htdocs/cgi-bin/main.cgi @@ -6,24 +6,42 @@ use strict; use warnings; use Try::Tiny; +use Switch; use CGI; +use CGI::Session '-ip_match'; use DBI; use DateTime; use DateTime::Format::SQLite; use DateTime::Duration; use Regexp::Common qw /URI/; +use Crypt::CBC; +use Crypt::IDEA; -my $driver = "SQLite"; -my $database = "../../dbLifeLog/data_log.db"; -my $dsn = "DBI:$driver:dbname=$database"; -my $userid = $ENV{'DB_USER'}; -my $password = $ENV{'DB_PASS'}; -my $db = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) - or die "

Error->"& $DBI::errstri &"

"; +my $q = CGI->new; +my $session = new CGI::Session(undef, $q); +my $sid=$session->id(); +my $dbname=$session->param('database'); +my $userid=$session->param('alias'); +my $password=$session->param('passw'); +my $cphr=$session->param('cipher'); + + +### Authenticate session to alias password +# +if(!$userid || !$dbname){ + print $q->redirect('login_ctr.cgi'); + exit; +} +# "../../dbLifeLog/data_log.db"; +#my $database = "/home/will/dev/LifeLog/dbLifeLog/data_log.db"; +my $cipher = new Crypt::CBC({key => $cphr, cipher => 'IDEA'}); +my $database = '../../dbLifeLog/'.$dbname; +my $dsn= "DBI:SQLite:dbname=$database"; +my $db = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die "

Error->"& $DBI::errstri &"

"; @@ -32,8 +50,8 @@ our $REC_LIMIT = 25; our $TIME_ZONE = 'Australia/Sydney'; our $PRC_WIDTH = '60'; #END OF SETTINGS +&getConfiguration($db); -my $q = CGI->new; my $tbl_rc = 0; my $tbl_rc_prev = 0; my $tbl_cur_id; @@ -56,25 +74,23 @@ if($rs_dat_from && $rs_dat_to){ my $toggle =""; if($rs_keys||$rs_cat_idx||$stmD){$toggle=1;}; -print $q->header(-expires=>"+6os", -charset=>"UTF-8"); - +print $q->header(-expires=>"+6os", -charset=>"UTF-8"); print $q->start_html(-title => "Personal Log", - -script=>{-type => 'text/javascript',-src => 'wsrc/main.js'}, - -style =>{-type => 'text/css', -src => 'wsrc/main.css'}, - -onload => "loadedBody('".$toggle."');" + -script=>{-type => 'text/javascript',-src => 'wsrc/main.js'}, + -style =>{-type => 'text/css', -src => 'wsrc/main.css'}, + -onload => "loadedBody('".$toggle."');" ); +#print $q->div("session->".$session->header()); +#print $q->div("user:".$userid." passw:".$password); my $rv; my $st; my $today = DateTime->now; $today->set_time_zone( $TIME_ZONE ); -##################### - &checkCreateTables; -##################### my $stmtCat = "SELECT * FROM CAT;"; -my $stmt = "SELECT rowid, ID_CAT, DATE, LOG, AMMOUNT FROM LOG ORDER BY DATE DESC, rowid DESC;"; +my $stmt = "SELECT rowid, ID_CAT, DATE, LOG, AMMOUNT FROM LOG ORDER BY rowid DESC, DATE DESC;"; $st = $db->prepare( $stmtCat ); @@ -272,7 +288,7 @@ if($rv < 0) { '; - my $frm = qq( + my $frm = qq(
@@ -287,7 +303,7 @@ if($rv < 0) { - + +$srh .= qq( @@ -324,8 +342,8 @@ $srh .= ' - '; + + ); if($rs_keys || $rs_cat_idx || $stmD){ $srh .= ''; + $tbl .= qq!!; if($rs_prev && $rs_prev>0 && $tbl_start>0){ - $tbl = $tbl . ''; + $tbl = $tbl . qq!!; } else{ - $tbl = $tbl .''; + $tbl .= ''; } - $tbl = $tbl .''; + $tbl .= ''; if($is_end_of_rs == 1){ $tbl = $tbl .''; } else{ - $tbl = $tbl . ''; + $tbl .= qq!!; } $tbl = $tbl .''; } -sub checkCreateTables(){ - - $st = $db->prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='LOG';"); - $st->execute(); - - if(!$st->fetchrow_array()) { - my $stmt = qq( - - CREATE TABLE LOG ( - ID_CAT TINY NOT NULL, - DATE DATETIME NOT NULL, - LOG VCHAR(128) NOT NULL, - AMMOUNT integer - ); - - ); +sub getConfiguration{ + my $st = $_[0]->prepare("SELECT * FROM CONFIG;"); + $st->execute(); + while (my @r=$st->fetchrow_array()){ + + switch ($r[1]) { - $rv = $db->do($stmt); + case "REC_LIMIT" {$REC_LIMIT=$r[2]} + case "TIME_ZONE" {$TIME_ZONE=$r[2]} + case "PRC_WIDTH" {$PRC_WIDTH=$r[2]} + else {print "Unknow variable setting: ".$r[1]. " == ". $r[2]} - if($rv < 0) { - print "

Error->"& $DBI::errstri &"

"; - } - - $st = $db->prepare('INSERT INTO LOG VALUES (?,?,?,?)'); - - $st->execute( 3, $today, "DB Created!",0); - - - } - - $st = $db->prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='CAT';"); - $st->execute(); - if(!$st->fetchrow_array()) { - my $stmt = qq( - - CREATE TABLE CAT( - ID INT PRIMARY KEY NOT NULL, - NAME VCHAR(16), - DESCRIPTION VCHAR(64) - ); - - ); - - $rv = $db->do($stmt); - - if($rv < 0) { - print "

Error->"& $DBI::errstri &"

"; - } - - $st = $db->prepare('INSERT INTO CAT VALUES (?,?,?)'); - - $st->execute(1,"Unspecified", "For quick uncategories entries."); - $st->execute(3,"File System", "Operating file system short log."); - $st->execute(6,"System Log", "Operating system inportant log."); - $st->execute(9,"Event", "Event that occured, meeting, historical important."); - $st->execute(28,"Personal", "Personal log of historical importants, diary type."); - $st->execute(32, "Expense", "Significant yearly expense."); - $st->execute(35, "Income", "Significant yearly income."); - $st->execute(40, "Work", "Work related entry, worth monitoring."); - $st->execute(45, "Food", "Quick reference to recepies, observations."); - } + } + } } + +sub authenticate{ + return 0; +} \ No newline at end of file -- 2.34.1
* LOG ENTRY FORM *
Log: Category: ).$cats.qq(
Category: $cats
↡ Ammount: @@ -301,7 +317,9 @@ if($rv < 0) { - + + + ); @@ -312,7 +330,7 @@ my $srh = qq( ); $cats =~ s/selected//g; -$srh .= '
View by Category:'.$cats.'
View by Category:.$cats.
View by Category:'.$cats.'
Keywords: -
@@ -348,6 +366,7 @@ print ""; print $q->end_html; $st->finish; $db->disconnect(); +$session->flush(); exit; ### CGI END @@ -456,97 +475,53 @@ sub buildNavigationButtons{ $tfId = 1; } - $tbl .= '
- + TopTopEnd