Announcement

Collapse
No announcement yet.

sqlitening

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • sqlitening

    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?
    djthain

  • #2
    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
    Last edited by Mike Doty; 8 Jun 2020, 06:34 AM.
    How long is an idea? Write it down.

    Comment


    • #3
      Originally posted by Mike Doty View Post
      [
      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

      Comment


      • #4
        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.
        Code:
        #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 'insert error, might want to "rollback"
         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
         slExeBind sql, JOIN$(sArray(),""),"E0 V"+FORMAT$(cols)
         IF slGetErrorNumber THEN ? slGetError,,FUNCNAME$:EXIT FUNCTION
         changes = slGetChangeCount("T") - changes   'changes after inserting (we should be in a transaction)
         FUNCTION = changes
        END FUNCTION
        How long is an idea? Write it down.

        Comment


        • #5
          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.

          Comment


          • #6
            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
            How long is an idea? Write it down.

            Comment


            • #7
              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
              Code:
               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
              Last edited by Mike Doty; 23 Jun 2020, 10:14 PM.
              How long is an idea? Write it down.

              Comment


              • #8
                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

                Comment


                • #9
                  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.
                  Last edited by Mike Doty; 30 Jun 2020, 08:40 AM.
                  How long is an idea? Write it down.

                  Comment


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

                    Comment


                    • #11
                      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.

                      Code:
                      %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
                      How long is an idea? Write it down.

                      Comment

                      Working...
                      X