SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Author Topic: Audit Trail  (Read 437 times)

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 601
    • View Profile
    • Software Development Company
Audit Trail
« on: September 11, 2018, 12:21:33 PM »

Hello,

Has anyone done Audit Trail using SQLitening? I searched but I did see anything of that sort here.

Writing SQLite Triggers would do?
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

Bern Ertl

  • Master Geek
  • ****
  • Posts: 496
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Audit Trail
« Reply #1 on: September 12, 2018, 07:04:28 AM »

Yeah, I created a system that allows me to log every SQL statement being executed in a transaction table.  See here:

https://www.sqlitening.planetsquires.com/index.php?topic=3539.msg18054;topicseen#msg18054

cj

  • Master Geek
  • ****
  • Posts: 696
    • View Profile
Re: Fredrick, Audit Trail (Version 2) much better
« Reply #2 on: September 13, 2018, 10:40:12 AM »

Didn't like previous version.
This version also captures error messages into log and doesn't need any other functions

#INCLUDE "sqlitening.inc"
$E1 = "...................................................." + $CR
$E2 = "...................................................." + $CR

FUNCTION PBMAIN () AS LONG  'errorlog.bas 9/13/18 CJ

 LOCAL sql AS STRING
 slSetProcessMods "E1"  'any error will be in the log

 KILL "junk.db3":ERRCLEAR
 slopen "junk.db3","C"

 sql = "create table if not exists trantable(statement)" :slexe sql:logit(sql)
 sql = "select * from trantable"                         :slsel sql:logit(sql)
 sql = "drop table if exists HeidiKlum"                  :slexe sql:logit(sql)
 sql = "create table if not exists trantable(statement)" :slexe sql:logit(sql)
 sql = "create table wrong"                              :slexe sql:logit(sql)
 sql = "drop table if exists Table1"                     :slexe sql:logit(sql)
 sql = "select rowid      from trantable"                :slsel sql:logit(sql)
 sql = "select statement  from trantable"                :slsel sql:logit(sql)
 sql = "JIBBERISH"                                       :slexe sql:logit(sql)
 sql = "select *          from trantable"                :slsel sql:logit(sql)
 sql = "select * from xyz"                               :slsel sql:logit(sql)
 sql = "select * from trantable"                         :slsel sql:logit(sql)
 sql = "select 'Have'||' a'||' good'||' day'"            :slsel sql:logit(sql)

 viewall

END FUNCTION

SUB ViewAll
 LOCAL sArray() AS STRING
 IF slSelAry("select statement from trantable",sArray(),"Q9E0") THEN
   ? slGetError,%MB_SYSTEMMODAL,"Viewer"
 ELSEIF UBOUND(sArray) < 1 THEN
  ? "No data",%MB_SYSTEMMODAL,"Viewer"
 ELSE
  ? JOIN$(sArray(),$CR),%MB_SYSTEMMODAL,"Viewer"
 END IF
END SUB

FUNCTION LogIt(s AS STRING) THREADSAFE AS LONG
 LOCAL sHeader AS STRING  'returns 1 on success
 LOCAL AnError AS LONG
 AnError = slGetErrorNumber 'set flag

 IF AnError THEN 'experimental, show last error in log
  slexebind "insert into trantable values(?)",slBuildBindDat($E1 + slGetError,"T"),"E0"
 END IF

 sHeader = LEFT$(DATE$,5) + " " + TIME$ + " " + s
 slexebind "insert into trantable values(?)",slBuildBindDat(sHeader,"T"),"E0"
 IF slGetErrorNumber THEN
   ? slGetError + $CR + $CR + s,%MB_SYSTEMMODAL,"LogIt"
   EXIT FUNCTION
 END IF
 IF AnError THEN slexebind "insert into trantable values(?)",slBuildBindDat($E2,"T"),"E0"
 IF slGetChangeCount <> 1 THEN
   ? "Write to log failed",%MB_SYSTEMMODAL,"LogIt"
 END IF

END FUNCTION
« Last Edit: September 13, 2018, 08:14:01 PM by cj »
Logged
CJ

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 601
    • View Profile
    • Software Development Company
Re: Audit Trail
« Reply #3 on: September 13, 2018, 03:43:55 PM »

Thanks Bern and CJ for your response.

I wanted to create an audit trail that would track users actions like: Save, Update and Delete on a table.

Earlier on I was having issues replying to posts here. It's all good now.
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 696
    • View Profile
Re: Audit Trail
« Reply #4 on: September 13, 2018, 08:10:56 PM »

Screenshot showing logging of all SQL statements and any errors.
Logged
CJ

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 601
    • View Profile
    • Software Development Company
Re: Audit Trail
« Reply #5 on: September 14, 2018, 06:12:55 AM »

Hello CJ,

I was trying to wrap my head around your sample code. I get it now. All I need to do is substitute the SQL statement with my Save, Update and Delete routines. Thanks.
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 696
    • View Profile
Re: Audit Trail
« Reply #6 on: September 14, 2018, 09:04:20 AM »

If the SQL statements don't use binding.
If binding is used wiill take some thought.

#INCLUDE "sqlitening.inc"
%KillDatabase=1

SUB LogIt(sql AS STRING)
 slexebind "insert into log values(?)",slBuildBindDat(sql,"T")
END SUB

FUNCTION PBMAIN () AS LONG  'errorlog2.bas 9/14/18
 IF %KillDatabase THEN KILL "junk.db3":ERRCLEAR

 LOCAL sql AS STRING

 slopen "junk.db3","C"

 slexe "create table if not exists log(statements)"
 slexe "create table if not exists t1(First,Last)"

 sql = "Insert into T1 values('John','Doe')"                :slexe sql:Logit sql
 sql = "Update t1 set First = 'Fredrick' where Last = 'Doe'":slexe sql:logit sql
 sql = "Delete from t1 where Last = 'Doe'"                  :slexe sql:Logit sql

 ? GetData("select * from LOG"),,"Log"

END FUNCTION

FUNCTION GetData(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 IF slSelAry(sql,sArray(),"Q9E0c") THEN
  ? slGetError,%MB_ICONERROR,FUNCNAME$
 ELSEIF UBOUND(sArray) < 1 THEN
  ? sql + $CR + "No data",%MB_SYSTEMMODAL,FUNCNAME$
 ELSE
  FUNCTION = JOIN$(sArray(),$CR)
 END IF
END FUNCTION

« Last Edit: September 14, 2018, 09:19:55 AM by cj »
Logged
CJ

cj

  • Master Geek
  • ****
  • Posts: 696
    • View Profile
Re: Audit Trail
« Reply #7 on: September 14, 2018, 10:30:32 AM »

In a real application, processing errors is required so this design might be more practical.
Other statements that might be important to log are  "create", "drop", "begin", "end"  and others?

Logging errors should be considered for security.
I removed the slGetChangeCount check because some statements like "begin" don't modify, but might be needed to see what is happening.

Again, this does not log statements using  binding like:  slexebind "insert into t1 values(?,,?,?) ",slBuildBindDat(sql,"T")

#INCLUDE "sqlitening.inc"
%KillDatabase=1

SUB LogIt(sql AS STRING)
 slexebind "insert into log values(?)",slBuildBindDat(sql,"T"),"E0"
END SUB

FUNCTION PBMAIN () AS LONG  'errorlog2.bas 9/13/18 CJ

 IF %KillDatabase THEN KILL "junk.db3":ERRCLEAR

 LOCAL result AS LONG
 LOCAL sql    AS STRING

 slopen "junk.db3","C"
 slexe "create table if not exists log(statements)"

 sql    = "drop table if exists t1"
 result = slExecute(sql)

 sql    = "create table if not exists t1(First,Last)"
 result = slExecute(sql)

 sql    = "Insert into t1 values('John','Doe')
 result = slExecute(sql)

 sql    = "Update t999 set First = 'Fredrick' where Last = 'Doe'"
 result = slExecute(sql)

 sql    = "Delete from t1 where First = 'Fredrick'"
 result = slExecute(sql)

 sql    = "Insert into t1 values('Heidi','Klum')
 result = slExecute(sql)


 ? "Log" + $CR + GetData("select * from log") + $CR + $CR +_
   "t1"  + $CR + GetData("select * from t1")

END FUNCTION

FUNCTION slExecute(sql AS STRING) AS LONG
  slexe(sql,"E0")
  logit sql
END FUNCTION

FUNCTION GetData(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 IF slSelAry(sql,sArray(),"Q9E0c") THEN
  ? slGetError,%MB_ICONERROR,FUNCNAME$
 ELSEIF UBOUND(sArray) < 1 THEN
  ? sql + $CR + "No data",%MB_SYSTEMMODAL,FUNCNAME$
 ELSE
  FUNCTION = JOIN$(sArray(),$CR)
 END IF
END FUNCTION
« Last Edit: September 14, 2018, 10:44:56 AM by cj »
Logged
CJ

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 601
    • View Profile
    • Software Development Company
Re: Audit Trail
« Reply #8 on: September 14, 2018, 05:04:24 PM »

Thanks CJ.
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 696
    • View Profile
Re: Audit Trail (Version 3)
« Reply #9 on: September 14, 2018, 06:43:50 PM »

Writing any text to a SQLite column is easy even if the data has embedded quotes.
slExeBind "Insert into Table1 values(?)",slBuildBindDat(Text$,"T")
----------------------------------------------------------------------------------------------------       

This enables logging any statements you want and disallowing statements that fail or didn't modify the table.
Statements that fail or didn't modify the log could be placed into another log.

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG  'errorlog3.bas 9/14/18 CJ
 slopen "junk.db3","C"
 slexe "drop table if exists log"
 slexe "drop table if exists t1"
 slexe "create table if not exists log(statements)"
 slExecute "create table if not exists t1(First,Last)"
 slExecute "Insert into t1 values('one','one')"
 slExecute "Insert into t1 values('two','two')"
 slExecute "Insert into t1 values('three','three')"
 slExecute "Insert into t1 values('four','four')"
 slExecute "Update T1 Set First = 'Heidi'"
 slExecute "Update T1 Set Last  = 'Klum'"
 slExecute "Delete from t1 where rowid > 2"

  ? "Log" + $CR + GetData("select * from log") + $CR + $CR +   "Data File"  + $CR + GetData("select rowid,* from t1")
END FUNCTION

FUNCTION slExecute(sql AS STRING) AS LONG
 LOCAL ChangeCount AS LONG
 ChangeCount = slGetChangeCount("T") 'system change count

 IF slexe(sql,"E0") THEN      'invalid statement, do something different with it
   FUNCTION = slGetErrorNumber
   ? slGetError+ $CR + sql,,"Statement Failed Log Not Updated"
   EXIT FUNCTION
 END IF

 ChangeCount = slGetChangeCount("T") - ChangeCount

 LOCAL s AS STRING
 s = LEFT$(LTRIM$(LCASE$(sql)),7) 'get rid of leading spaces, lower case
 SELECT CASE s
  CASE "insert ":? sql,,USING$("INSERT changes #",changecount)
  CASE "update ":? sql,,USING$("UPDATE changes #",changecount)
  CASE "delete ":? sql,,USING$("DELETE changes #",changecount)
  CASE ELSE     :? sql,,USING$("Other changes  #",ChangeCount)
 END SELECT

 'decide what to log
 IF ChangeCount > 0 THEN  'modify to taste
  slexebind "insert into log values(?)",slBuildBindDat(sql,"T"),"E0"
 END IF

END FUNCTION

FUNCTION GetData(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 IF slSelAry(sql,sArray(),"Q9E0c") THEN
  ? slGetError,%MB_SYSTEMMODAL,"Viewer"
 ELSEIF UBOUND(sArray)>0 THEN
  FUNCTION = JOIN$(sArray(),$CR)
 END IF
END FUNCTION


Code: [Select]
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG  'errorlog2.bas 9/13/18 CJ
 slopen "junk.db3","C"
 slexe "drop table if exists log"
 slexe "drop table if exists t1"
 slexe "create table if not exists log(statements)"
 slExecute "create table if not exists t1(First,Last)"
 slExecute "Insert into t1 values('one','one')"
 slExecute "Insert into t1 values('two','two')"
 slExecute "Insert into t1 values('three','three')"
 slExecute "Insert into t1 values('four','four')"
 slExecute "Update T1 Set First = 'Heidi'"
 slExecute "Update T1 Set Last  = 'Klum'"
 slExecute "Delete from t1 where rowid > 2"

  ? "Log" + $CR + GetData("select * from log") + $CR + $CR +   "Data File"  + $CR + GetData("select rowid,* from t1")
END FUNCTION

FUNCTION slExecute(sql AS STRING) AS LONG
 LOCAL ChangeCount AS LONG
 ChangeCount = slGetChangeCount("T") 'system change count

 IF slexe(sql,"E0") THEN      'invalid statement, do something different with it
   FUNCTION = slGetErrorNumber
   ? slGetError+ $CR + sql,,"Statement Failed Log Not Updated"
   EXIT FUNCTION
 END IF

 ChangeCount = slGetChangeCount("T") - ChangeCount

 LOCAL s AS STRING
 s = LEFT$(LTRIM$(LCASE$(sql)),7) 'get rid of leading spaces, lower case
 SELECT CASE s
  CASE "insert ":? sql,,USING$("INSERT changes #",changecount)
  CASE "update ":? sql,,USING$("UPDATE changes #",changecount)
  CASE "delete ":? sql,,USING$("DELETE changes #",changecount)
  CASE ELSE     :? sql,,USING$("Other changes  #",ChangeCount)
 END SELECT

 'decide what to log
 IF ChangeCount > 0 THEN  'modify to taste
  slexebind "insert into log values(?)",slBuildBindDat(sql,"T"),"E0"
 END IF

END FUNCTION

FUNCTION GetData(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 IF slSelAry(sql,sArray(),"Q9E0c") THEN
  ? slGetError,%MB_SYSTEMMODAL,"Viewer"
 ELSEIF UBOUND(sArray)>0 THEN
  FUNCTION = JOIN$(sArray(),$CR)
 END IF
END FUNCTION


« Last Edit: September 18, 2018, 12:19:08 AM by cj »
Logged
CJ