• Welcome, Guest. Please login.
 
April 23, 2019, 09:26:37 pm

News:

Welcome to the SQLitening support forums!


Audit Trail

Started by Fredrick Ughimi, September 11, 2018, 02:51:33 pm

Previous topic - Next topic

Fredrick Ughimi

Hello,

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

Writing SQLite Triggers would do?
Fredrick O. Ughimi

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

Bern Ertl

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

September 13, 2018, 01:10:12 pm #2 Last Edit: September 13, 2018, 10:44:01 pm by cj
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

Fredrick Ughimi

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.
Fredrick O. Ughimi

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

cj

Screenshot showing logging of all SQL statements and any errors.

Fredrick Ughimi

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.
Fredrick O. Ughimi

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

cj

September 14, 2018, 11:34:20 am #6 Last Edit: September 14, 2018, 11:49:55 am by cj
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


cj

September 14, 2018, 01:00:32 pm #7 Last Edit: September 14, 2018, 01:14:56 pm by cj
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

Fredrick Ughimi

Fredrick O. Ughimi

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

cj

September 14, 2018, 09:13:50 pm #9 Last Edit: September 18, 2018, 02:49:08 am by cj
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



#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