sqlitening

[david thain]

I use slsel in my sqlitening programs.What is the use for slselbind or what does bind do that slsel doesn't? What is injection?

[Mike Doty]

David,

1. Prepared statements are optimized
2. Sqlitening adds optional compression and encryption when using binding
3. Data is passed "as is" without wrapping each value with $SQ

Description of injection in web pages:
https://www.w3schools.com/sql/sql_injection.asp (corrected, see Stuart's below post 6/8/2020 6:34 AM)

Using binding is not required and you may not see a noticable performance increase.
I like it because arrays and files can be easily inserted without the wrapping of values.
I always use Begin Immediate transaction if there are multiple statements or arrays so the database is only locked once.


See slExeBind (rsStatement String, rsBindDats String, [rsModChars String]) Long

[Stuart McLachlan]

[
Description of injection in web pages:

https://www.w3schools.com/sql/sql_injection.aspsl

SQL Injection is what happens when you don't sanitize user inputs i.e. they enter bad or malicious data into a form and you use the data exactly as entered in an SQL command.

I get a 404 with that link, try:

https://www.w3schools.com/sql/sql_injection.asp

[Mike Doty]

I use slsel in my sqlitening programs.What is the use for slselbind or what does bind do that slsel doesn't? What is injection?

4. Insert hundreds of thousands of records per second.
This example uses a 2-dimensional array which is great when working with listview or a grid.
No need to work with clumsy values(?,?,?) statements by using a helper function (see InsertClient.)
Also, note that statements and data are not concatenated and again $SQ is never needed.

This is describing slExeBind and not slSelBind (my bad.) Never had a need for slSelBind.

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG 'bind1.bas 6/8/2020
 LOCAL rownum,cols,rows,changes AS LONG

 slopen "junk.db3","C"
 slexe  "create table If Not Exists Table1 (rownum integer primary key, first text, last text)"

 cols=3
 rows=10000   'create/bind 10,000 records of 3 columns
 REDIM sRow(1 TO cols, 1 TO rows) AS STRING
 FOR rownum= 1 TO rows                          'create row(s)
  sRow(1,rownum) = slBuildBindDat("","Z")       ' rownum
  sRow(2,rownum) = slBuildBindDat("FIRST","T")  ' first name
  sRow(3,rownum) = slBuildBindDat("LAST ","T")  ' last name
 NEXT

 slexe "Begin Immediate"  'use immediate on a network
 changes = InsertClient(sRow())
 IF changes <> UBOUND(sRow,2) THEN BEEP[B] 'insert error, might want to "rollback"[/B]
 slExe "End"

 ? ShowResults("select * from Table1 order by rowid desc limit 1")
END FUNCTION

FUNCTION ShowResults(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 slSelAry sql,sArray(), "Q44 E0"  'separate columns with comma, E0 = suppress error messages
 IF slGetErrorNumber THEN ? slGetError,,FUNCNAME$:EXIT FUNCTION
 FUNCTION =  JOIN$(sArray(),$CR)
END FUNCTION

FUNCTION InsertClient(sArray() AS STRING) AS LONG
 LOCAL cols,changes AS LONG, sql AS STRING
 changes = slGetChangeCount("T")  'changes before insert (we should be in a transaction)
 cols = UBOUND(sArray,1)
 sql = "Insert into Table1 values(" + LEFT$(REPEAT$(cols,"?,"),-1) + ")" 'generate values(?,? ...) portion of statement
[COLOR=#c0392b][B] slExeBind sql, JOIN$(sArray(),""),"E0 V"+FORMAT$(cols)[/B][/COLOR]
 IF slGetErrorNumber THEN ? slGetError,,FUNCNAME$:EXIT FUNCTION
 changes = slGetChangeCount("T") - changes   'changes after inserting (we should be in a transaction)
 FUNCTION = changes
END FUNCTION

[Michael Rice]

Mike, Quick question about sqlitening... So if one had an SQLite database and wanted to place it on a workstation in a DMZ...sqlitening could allow the database to be accessed (queried) by others using the internet as the communication link? Assuming of course proper ports open in firewalls, etc... If this is possible...also able to limit clients to read only access? I have some experience using SQLite, but only as single access local database... btw...everything in the database is already on the web...security in that regard in NOT an issue.

[Mike Doty]

Yes.
Clients would need to be using SQLiteningClient.DLL or write some code for your own client-side code.
Everything is open source so it can modified to anything wanted.

https://sqlitening.planetsquires.com...12778#msg12778

https://forum.powerbasic.com/forum/u...some-direction

[Mike Doty]

Two passwords to a remote server that opens the database named "mike.db3"
Note: passwords containing one or more '%' indicates a read-only password.

1. Edit SQLiteningServer.CFG and restart server using SQLiteningServerAdmin.exe (run as admin is required.)
Port =12345
Hosts = 192.168.0.2
CreateDatabaseAllowed=no
mike.db3 = pas%sword, power

2. Router: forward port 12345 to 192.168.0.2
3. Allow SQLiteningServer.exe through firewall

 slConnect "192.168.0.2",12345
 slOpen "mike.db3" + $BS + "pas%sword", "R"  '1 open with read-only password
 or
 slOpen "mike.db3" + $BS + "power"           '2 open with read/write password

[david thain]

What is the difference between using the server portion of sqlitening and the sqlitening.dll version? I use the sqlitening.dll version and I can have more than one user at a time going to the same database. I can set security in my directory or database file. I can use a domain server or a peer to peer server.

[Mike Doty]

What is the difference between using the server portion of sqlitening and the sqlitening.dll version? I use the sqlitening.dll version and I can have more than one user at a time going to the same database. I can set security in my directory or database file. I can use a domain server or a peer to peer server.
djthain

The difference is client/server vs peer-to-peer networking.

SQLiteningServer.exe can optionally be started on one machine (it is a service) to handle all i/o as a TCP server.
Client/server is considered more secure and reliable since only the server has exclusive access to all files.

slConnect Ip$, PortNumber&
The rest of the code is the same.

[david thain]

How many clients can Sqlitening handle within a comfortable margin for speed and reliability? I have only tested 2 clients without collision.

[Mike Doty]

David,
More than 20?

Since the database is locked on writes it is very safe updating multiple tables.

Depends upon how fast your network is and how many people write/lock at the same time.
Windows has a 20-limit unless you use a server version.
SQLitening has a limit of 65535, but the server would run out of memory before then.

Since SQLite locks the database on writes using a transaction is essential with multiple writes.
Using multiple databases can also prevent locking out users working on different tables.
Nothing prevents mixing with PowerBasic files to prevent locking out users.

CubeSQL.Com says they can handle 1000's with SQLite, but have never used it.

%threads=20
$Database = "junk.db3"
$Ip  = "192.168.0.2"
%Port= 12345
%UseServer=0
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
 LOCAL x,hThread AS LONG,s, sArray() AS STRING
 slopen $Database,"C"   'create if not exists
 slexe "drop table if exists t1"
 slexe "create table if not exists t1(c1 text)"        'create a table
 slexe "create index if not exists t1_index on t1(c1)" 'create an index
 FOR x = 1 TO %threads
  THREAD CREATE MyTest(x) TO hThread
  SLEEP 1
  THREAD CLOSE hThread TO hThread
 NEXT
 DO:SLEEP 10:LOOP UNTIL THREADCOUNT=1
 slselary "select * from t1 order by c1",sArray(),"Q9c"
 s =JOIN$(sArray(),"")
 ? s,,USING$("Length #",LEN(s))
END FUNCTION

THREAD FUNCTION MyTest(BYVAL x AS LONG) AS LONG
 RANDOMIZE
 SLEEP 1
 LOCAL sData AS STRING
 sData = CHR$(RND(65,90))
 sdata = slBuildBindDat(sdata,"T")                'bind as text
 IF %UseServer THEN slConnect $Ip,%Port,"E0"      'optional connect
 IF slGetErrorNumber THEN ? slGetError,,"Thread"+STR$(x):END
 DIM sArray() AS STRING
 slopen $database                                 'open database
 slexebind "insert into t1 values(?)",sdata       'insert a record
 slselary "select count(*) from t1",sArray(),"Q9" 'count records
 slclose                                          'close database
END FUNCTION