What are the steps to provide encryption to an sqlite database ??
If I encrypt a database using SQLitening can I open it and decrypt it using third party tools (ie a SQLite Database Viewers)
Any help would be appreciated.
SQlitening's built-in AES256 Cookbook encryption is used on columns and can be turned on and off.
SQLitening's was written by Greg Turcheson and would not be understood by third-party viewers.
Buying SQLite's AES256 encryption or https://www.zetetic.net/sqlcipher/ encrypt at the database level.
These products may work with some third-party products
With sqlitenings encryption you can not use SELECT * FROM TEXT WHERE ORD = 'ab??rice'
but you can do that with SQLite's encryption.
Am I right??
/Fim W.
Searching on the blob column may not work unless you know the exact value.
It makes more sense to search on the non-encrypted columns such as a key column.
See post #5 (below) which makes more sense.
It shows using slExeBind and slSelBind which may prevent SQL injection
Get equal (=) tests seem to work (with binding), but (< and >) are not correct.
Notice in this example "Apple" is less than "B", but "Apple" is not returned.
If anyone sees an error in my ways, please post it!
https://www.sqlitening.planetsquires.com/index.php?topic=9579.msg25200#msg25200
Hopefully other products handle this.
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
LOCAL s AS STRING
slopen "junk.db3","C"
slexe "drop table if exists t1"
slexe "create table if not exists t1(c1)"
slSetProcessMods "K" + SPACE$(32) 'set encrypt key
slExeBind "insert into t1 values(?)",slBuildBindDat("Apple","TN") 'insert encrypted Apple
slSelBind "select c1 from t1 where c1 < ?",slBuildBindDat("B","TN") 'Apple less than B test
DO WHILE slGetRow
s+= slfx(1,"D") + $CR
LOOP
? s
END FUNCTION
That was going to be my next question. Do I write/query the database just like normal ? What about blob fields that contain images ?
Search on name of image or a non-encrypted column
Added slSelBind
THREADED sb AS ISTRINGBUILDERA
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
LOCAL x AS LONG
LOCAL sKey AS STRING
sb = CLASS "StringBuilderA"
slopen "junk.db3","C"
slexe "drop table if exists t1"
slexe "create table if not exists t1(MyKey UNIQUE,MyData)"
slSetProcessMods "K" + SPACE$(32)
sKey = "key1"
slExeBind "insert into t1 values(?,?)",slBuildBindDat(sKey,"T") +_
slBuildBindDat("Heidi","TN")
IF slGetChangeCount <> 1 THEN ? "Insert error":EXIT FUNCTION
slSel "select MyData from t1 where MyKey = " + WRAP$(sKey,$SQ,$SQ)
DO WHILE slGetRow
AddItem slConvertDat(slf(1),"D")
AddItem slfx(1,"D")
AddItem slfnx("MyData","D")
LOOP
LOCAL sArray() AS STRING
slSelAry "select MyData from t1 where MyKey="+WRAP$(sKey,$SQ,$SQ),sArray(),"D1 Q9c"
FOR x = 1 TO UBOUND(sArray)
AddItem sArray(x)
NEXT
'This could prevent sql injection
slSelBind "select MyData from t1 where MyKey = ?",slBuildBindDat(sKey,"T")
DO WHILE slGetRow
AddItem slConvertDat(slf(1),"D")
AddItem slfx(1,"D")
AddItem slfnx("MyData","D")
LOOP
? sb.string
END FUNCTION
SUB AddItem(s AS STRING)
sb.add s + $CR
END SUB