SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Show Posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Messages - cj

Pages: [1] 2 3 ... 47
1
You've got Questions? We've got Answers! / Re: Security
« on: December 19, 2018, 08:03:06 PM »
Quote
Using 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.

2
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

3
You've got Questions? We've got Answers! / Re: Encryption
« 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

4
You've got Questions? We've got Answers! / Re: Encryption
« 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                   

5
You've got Questions? We've got Answers! / Re: Encryption at database level
« 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


6
You've got Questions? We've got Answers! / Re: Auto Switching and Uploading
« on: December 06, 2018, 02:06:08 PM »
I don't think slpushdatabase and slpopdatabase will help much
The big thing is keep the databases in sync and how to prevent duplicate keys from being rejected.
I'm thinking like an ATM machine can't give out money if central server is down.
One thing about cloud computing is the data is duplicated and maintained by different locations.
Some may not know that they have to pay for that duplicated data if sent to different servers.
Is cloud safer and more secure?  Yes, in my opinion.  Sorry for getting off subject.

#DIM ALL
#INCLUDE "sqlitening.inc"
GLOBAL gConnect AS LONG
'-------------------------------------------------------
FUNCTION PBMAIN () AS LONG
 LOCAL x,ecode,portnumber AS LONG
 LOCAL sHost AS STRING
 slSetProcessMods "E0"

 sHost = "192.168.0.12"
 PortNumber = 80

 FOR x = 1 TO 1 'try multiple times to connect
  ecode = slConnect(shost,portnumber)
  IF ecode = 0 THEN gConnect = 1:EXIT FOR ELSE BEEP
 NEXT
 IF gConnect = 0 THEN
  IF MSGBOX("Could not connect, work local?",%MB_YESNO OR %MB_SYSTEMMODAL,"Connect error") = %IDNO THEN
   ? "Ending the program",%MB_SYSTEMMODAL,"Thank you"
   EXIT FUNCTION
  END IF
 END IF

 IF gConnect THEN
  slDisconnect
  ? "disconnect and end"
 ELSE
  ? "end"
 END IF
END FUNCTION

7
You've got Questions? We've got Answers! / Re: Auto Switching and Uploading
« on: December 06, 2018, 01:31:11 PM »
If you slPushDatabase the current database is no longer available until you slPopDatabase
Be careful if connection fails, etc.

This might work just as well without rotating database handle
1 IF can't get to host then no need to push and pop database handles
2 If can get to host then disconnect, read local log, reconnect, send local log
Sounds like good planning like no cloud no work.

Did this way to try to avoid lots of IF statements.

After looking at it awhile workinglocal  and workingremote would need to duplicate code.
The program would probably need to use IF gconnect to share the routines.

#DIM ALL
#INCLUDE "sqlitening.inc"
GLOBAL gConnect AS LONG
'-------------------------------------------------------
FUNCTION PBMAIN () AS LONG
 LOCAL x,ecode,portnumber AS LONG
 LOCAL sHost AS STRING
 slSetProcessMods "E0"

 sHost = "192.168.0.13"
 PortNumber = 80

 FOR x = 1 TO 1 'try multiple times to connect
  ecode = slConnect(shost,portnumber)
  IF ecode = 0 THEN gConnect = 1:EXIT FOR ELSE BEEP
 NEXT
 IF gConnect THEN
  WorkingRemote
 ELSE
  IF MSGBOX("Could not connect, work local?",%MB_YESNO OR %MB_SYSTEMMODAL,"Connect error") = %IDYES THEN
   WorkingLocal
  ELSE
   ? "Ending the program",%MB_SYSTEMMODAL,"Thank you"
  END IF
 END IF

END FUNCTION
'-------------------------------------------------------
SUB WorkingLocal
 ? "Local routines create log"
END SUB
'-------------------------------------------------------
SUB WorkingRemote
 slDisconnect
 ? "Connected/Disconnected"
END SUB

8

https://sqlite.org/2018/sqlite-dll-win32-x86-3260000.zip

477,151 bytes sqlite-dll-win32-x86-3260000.zip
911,698 bytes sqlite3.dll

9
You've got Questions? We've got Answers! / Re: Can not start the server
« on: December 04, 2018, 01:48:42 PM »
1) See if your shortcut has "Run as administrator checked
2) If not that, then "Allow an app  through Windows Firewall"
3) If neither of these then "IPCONFIG" at command prompt and see if IP address is different in sqliteningserver.cfg

10
You've got Questions? We've got Answers! / Re: Using rowid zero
« on: November 20, 2018, 01:08:46 PM »
'Upsert without rowid might be optimal

'A big advantage of binding is text doesn't have to be encoded with single quotes (strings and even files can be insert or updated into column "as is".)
'A good example of this is trying to save a SQL statement that has single quotes, commas and (punctuation) that would be very hard to insert.
'The Upsert is definitely optional, but highly suggest using binding.

$Insert ="Insert into t1 values(?,?) ON conflict(c1) DO update SET c2=?"
#INCLUDE "sqlitening.inc" 'upsert.bas

FUNCTION PBMAIN () AS LONG
 slOpen "upsert.db3","C"
 slexe  "create table if not exists t1(c1 text primary key,c2 text) without rowid"
 Upsert "key1","Jane Doe"
 Upsert "key1","Heidi Klum"
END FUNCTION

FUNCTION Upsert(sKey AS STRING, sData AS STRING) AS LONG
 LOCAL sBind AS STRING
 sBind  = slBuildBindDat(sKey, "T") +_   'bind key as text
          slBuildBindDat(sData,"T") +_   'insert
          slBuildBindDat(sData,"T")      'on conflict (update)
 slexebind $Insert,sBind
END FUNCTION

11
You've got Questions? We've got Answers! / Re: Using rowid zero
« on: November 20, 2018, 08:51:52 AM »
Thanks, Paul for bringing up using table 61.
Unique keys can be created as needed for titles, totals, anything ...

#INCLUDE "sqlitening.inc"     'onthefly.bas
THREADED ts AS STRING         'combine results
'NOTE: if sql uses LIKE or SUBSTR a full table SCAN is always performed  instead of indexed SEARCH.
'If someone knows how to use search instead of scan using LIKE, please let us know!

FUNCTION PBMAIN () AS LONG

 LOCAL sql,s AS STRING

 slOpen "junk.db3","C"
 slexe "drop table if exists t61"
 slexe "create table t61(t61key text primary key,t61answer) without rowid"

 slexe "insert or replace into t61 values('t01-client','1')"
 slexe "insert or replace into t61 values('t01-total' ,'2')"
 slexe "insert or replace into t61 values('t01-client','1000')" 'replace client
 slexe "insert or replace into t61 values('t01-total' ,'2000')" 'replace total

 GetData "select t61answer from t61 where t61key = 't01-client'"
 GetData "select t61answer from t61 where substr(t61key,1,3) ='t01'" 'SCANS entire table
 ? ts
END FUNCTION

SUB Getdata(sql AS STRING)
 LOCAL sArray() AS STRING
 slSelAry sql,sArray(),"Q9c"
 IF UBOUND(sArray)> 0 THEN ts+= sql + $CR + JOIN$(sArray(),",") + $CR + $CR
END SUB

12
You've got Questions? We've got Answers! / Re: Using rowid zero
« on: November 19, 2018, 10:48:44 AM »
No problem unless:
“The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.”

13
You've got Questions? We've got Answers! / Re: SQLitening & RMChart
« on: November 05, 2018, 09:51:28 AM »
Quote
Excellent. Thanks for the sample code. The problem is how do I individually read each values into the
aData array subscripts.
aData(0) = ? : aData(1) = ? : aData(2) = ? : aData(3) = ? : aData(4) = ?

The code did this so don't understand the question?   
aData(0) = row 1 temperature as a DOUBLE
aData(1) = row 2 temperature as a DOUBLE
aData(2) = row 3 temperature as a DOUBLE
aData(3) = row 4 temperature as a DOUBLE
aData(4) = row 5 temperature as a DOUBLE

REDIM sArray(0) AS STRING
slSelAry "select temperature from table1 LIMIT 5",sArray(),"Q9"
elements = UBOUND(sArray)
IF elements > 0 THEN
REDIM aData(elements) AS DOUBLE
 FOR x = 1 TO elements
  aData(x-1) = VAL(sArray(x)) read each value into the aData array subscript.
 NEXT
END IF

14
You've got Questions? We've got Answers! / Re: SQLitening & RMChart
« on: November 04, 2018, 09:09:12 PM »
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
 LOCAL x,elements AS LONG

 slOpen "junk.db3","C"
 slexe "drop table if exists table1
 slexe "create table if not exists table1(temperature integer)"
 slexe "insert into table1(temperature)values(30),(40),(70),(60),(20),(17),(88)"

 REDIM sArray(0) AS STRING
 slSelAry "select temperature from table1",sArray(),"Q9"

 'read each value into the aData array subscript.
 elements = UBOUND(sArray)
 IF elements > 0 THEN
  REDIM aData(elements) AS DOUBLE
  FOR x = 1 TO elements
   aData(x-1) = VAL(sArray(x))
  NEXT
 END IF
END FUNCTION


15
You've got Questions? We've got Answers! / Re: Messages suppression
« on: November 01, 2018, 12:58:19 PM »
Quote
How can I completely suppress every error message and let the library calls fail when there is a lock timeout?
Do I need to set the mods on every call?

slSetProcessMods "r" must be after slOpen (which you have done.)
Are you sure you need T1000 (1-second) which causes the problem and no retry?
The default of 10000 (10-seconds) works well so MSGBOX shouldn't appear.

Getting rid of the T option and the pops and pushes would be my suggestion.

Anyway, playing with this code might be useful.


#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
 LOCAL hThread AS LONG
 slSetProcessMods "E0"

 slopen "junk1","CT1000"  'make busy-timeout extremely small (not suggested)
 slSetProcessMods "r" 'required after slOpen so busy MSGBOX will not appear

 slexe "create table if not exists t1(c1)"
 THREAD CREATE LockDataBase(0) TO hThread
 THREAD CLOSE hThread to hThread
 SLEEP 1000 'so other thread locks before we attempt insert
 slexe "insert into t1 values('one')
 ? "Done"

END FUNCTION

THREAD FUNCTION LockDataBase(BYVAL notused AS LONG) AS LONG
 slOpen "junk1"
 slExe "begin exclusive"
 ? "transaction active",,FUNCNAME$
 slExe "end"
END FUNCTION

Pages: [1] 2 3 ... 47