• Welcome, Guest. Please login.
 
April 20, 2019, 06:28:45 am

News:

Welcome to the SQLitening support forums!


Recent posts

Pages 1 2 3 4 ... 10
12
You've got Questions? We've got Answers! / Re: Security
Last post by cj - December 19, 2018, 09:33:06 pm
QuoteUsing Sqlitening - What measures should be adopted to prevent SQL Injection attacks. Any thoughts would be appreciated.


This is an old thread from 2015, but these answers were not given.
Today is 12/19/2018.

slExeBind
slSelBind
https://www.sqlitening.planetsquires.com/index.php?topic=9730.msg26326;topicseen#msg26326

Thoughts:
I've wondered who would inject the code in a local network?
If they can inject code they can just as easily write sql statements or delete a database.
If used over the internet the transmits should be encrypted so they shouldn't be able to inject.

I like slExeBind because text can be inserted "as is" without needing to wrap text with $SQ and also wrap embedded $SQ's.
13
You've got Questions? We've got Answers! / Bind Select Statements to prev...
Last post by cj - December 19, 2018, 09:17:03 pm
Not sure how I missed slSelBind.
slSelBind was added a long time ago and can prevent SQLite injection
https://sqlitening.planetsquires.com/index.php?topic=3378.0;wap2
Quote
Added the slSelBind function in order to avoid SQL injection and to improve Unicode processing.

Example extracting encrypted text (3-ways)

slexe  "create table if not exists t1(MyKey UNIQUE,MyData)"
slSetProcessMods "K" + SPACE$(32)
slSelBind "select MyData from t1 where MyKey = ?",slBuildBindDat(sKey,"T")
DO WHILE slGetRow
  ? slConvertDat(slf(1),"D")
  ? slfx(1,"D")
  ? slfnx("MyData","D")
LOOP
14
You've got Questions? We've got Answers! / Re: Encryption
Last post by cj - December 19, 2018, 09:08: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
15
You've got Questions? We've got Answers! / Re: Encryption
Last post by D. Wilson - December 19, 2018, 03:05: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 ?
16
You've got Questions? We've got Answers! / Re: Encryption
Last post by cj - December 19, 2018, 02:37: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                   
17
You've got Questions? We've got Answers! / Re: Encryption
Last post by Fim - December 19, 2018, 04:54: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.
18
You've got Questions? We've got Answers! / Re: Encryption at database lev...
Last post by cj - December 18, 2018, 10:18: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

19
You've got Questions? We've got Answers! / Encryption
Last post by D. Wilson - December 18, 2018, 08:04: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.
20
Hello CJ,

Thanks as always.

Both samples code works good. I think I would go with the second one. I did something similar to that earlier.

The tricky part is:
Quote
2. Automatically upload records entered in local database to the remote server when the internet connection is available.


The client computers are multiple, not just one.

Pages 1 2 3 4 ... 10