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: Encryption  (Read 179 times)

D. Wilson

  • Guru
  • ***
  • Posts: 198
    • View Profile
Encryption
« on: December 18, 2018, 06:34:52 PM »

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.
Logged

cj

  • Master Geek
  • ****
  • Posts: 700
    • View Profile
Re: Encryption at database level
« Reply #1 on: December 18, 2018, 08:48:53 PM »

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

« Last Edit: December 18, 2018, 08:52:39 PM by cj »
Logged

Fim

  • Guru
  • ***
  • Posts: 153
    • View Profile
    • Fims sajt
Re: Encryption
« Reply #2 on: December 19, 2018, 03:24:40 AM »

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.
Logged
Fim W

cj

  • Master Geek
  • ****
  • Posts: 700
    • View Profile
Re: Encryption
« Reply #3 on: December 19, 2018, 01:07:27 PM »

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                   
« Last Edit: December 19, 2018, 08:15:42 PM by cj »
Logged

D. Wilson

  • Guru
  • ***
  • Posts: 198
    • View Profile
Re: Encryption
« Reply #4 on: December 19, 2018, 01:35:29 PM »

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

cj

  • Master Geek
  • ****
  • Posts: 700
    • View Profile
Re: Encryption
« Reply #5 on: December 19, 2018, 07:38:27 PM »

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
Logged