Encryption

[D. Wilson]

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.

[cj]

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

[Fim]

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.

[cj]

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                   

[D. Wilson]

That was going to be my next question. Do I write/query the database just like normal ? What about blob fields that contain images ?

[cj]

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