SQLitening Support Forum

Quote from: undefinedslExe "Begin"
  slExe slBuildInsertOrUpdate("tblJournalEntry", SQLiteDate(m_sDate) & $Nul & m_sReference _
  & $Nul & m_sNarration & $Nul & m_sAccount1 & $Nul & m_sDescription1 & $Nul & m_sDebit1 _
  & $Nul & m_sCredit1)
slExe "End"
     
slExe "Begin"
  slExe slBuildInsertOrUpdate("tblJournalEntry", SQLiteDate(m_sDate) & $Nul & m_sReference _
  & $Nul & m_sNarration & $Nul & m_sAccount2 & $Nul & m_sDescription2 & $Nul & m_sDebit2 _
  & $Nul & m_sCredit2)
slExe "End"

slExe "Begin Exclusive"
 slExe slBuildInsertOrUpdate("tblJournalEntry",(CHR$(SQLiteDate(Date),0,Ref,0,Narr,0,Acct1,0,Desc1,0,Debit1,0,Credit1)))
 slExe slBuildInsertOrUpdate("tblJournalEntry",(CHR$(SQLiteDate(Date),0,Ref,0,Narr,0,Acct2,0,Desc2,0,Debit2,0,Credit2)))
slexe "End"           

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN AS LONG

 LOCAL sql,sArray() AS STRING

 slopen "junk.db3","C"

 slexe "drop table if exists t1"
 slexe "create table if not exists t1(c1 integer)"

 slexe "insert into t1 values(-1000),(-10),(-9),(-1),(0),(1),(9),(10),(100),(1000)"

 sql = "select rowid,"  +_
       " case" +_
       " when c1 > 0 then       ltrim(printf('%.2f',c1*.01),'0')"  +_ 
       " when c1 < 0 then '-'|| ltrim(printf('%.2f',c1*.01),'-0')" +_ 
       " else '.00'" +_
       " end  AS TheValues"+_
       " from t1"

 slSelAry sql,sArray(),"Q9"
 ? JOIN$(sArray(),$CR),,"No leading zero"

END FUNCTION
  

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG 'group_by_having.bas alias wilson3.bas 4/13/19

 LOCAL sql     AS STRING
 LOCAL sresult AS STRING

 slopen "group_having.db3","C"

 slexe  "drop table if exists iTable"
 slexe  "drop table if exists hTable"
 slexe  "create table if not exists iTable (iKey unique,iQty INTEGER)"
 slexe  "create table if not exists hTable (hKey,       hQty INTEGER)"

 slexe  "insert into iTable values ('bear',1),('dog',0),('fox',3)"
 slexe  "insert into hTable values ('dog',3) ,('dog',2),('fox',4)"

 sql =  "select iKey as Code,sum(hQty)as Held,iQTY as OnHand from hTable,iTable" +_
        " where iKey=hKey" +_
        " group by hKey" + _
        " having Held > iQTY" 'changed sum(hQty) to Held

 sresult = getrs(sql)
 ? sresult,,"Held Report"

END FUNCTION

FUNCTION getrs (sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 slSelAry sql,sArray(),"Q9 E0"
 IF slGetErrorNumber THEN ? slGetError,,"GetData":EXIT FUNCTION
 IF UBOUND(sArray)>0 THEN
  FUNCTION = JOIN$(sArray(),$CR)
 ELSE
  FUNCTION = "No records found"
 END IF
END FUNCTION

'Testing new forum software 4/18/19
'Can edit, but can't create a new thread  or reply

QuoteI want to run a query that will give me result the shows the total holds vs inventory qty on hand. I want to show all the items that have more holds than the current 
inventory level.

1. Add OnHold column to Inventory table
2. select * from table1 where qty < OnHold"

QuoteIn theory the system will never allow a user to put on hold if there is not an enough inventory.

UpdateteFlag& = Increment(sCode$,HoldMore)  ' return 1 if successful (doesn't allow out of limits)

slexe "create table table1(code unique, qty, OnHold)"
slexe "insert into table1 values('wilson',9,0)"
slexe "insert into table1 values('klume',100,103)"
slexe "insert into table1 values('squires',500,602)"

Test program
GLOBAL gs AS STRING
#INCLUDE "sqlitening.inc"

FUNCTION Increment(sCode AS STRING, hold AS LONG) AS LONG
'Update OnHold if qty& >= OnHold and hold > 0
IF hold < 1 THEN EXIT FUNCTION 'updating by less than 1 could be endless
slexe "update table1 set OnHold=OnHold+"+STR$(hold) + " where code='" + sCode$ + "' and qty >= OnHold+" + STR$(hold)
FUNCTION = slGetChangeCount
END FUNCTION

FUNCTION PBMAIN () AS LONG
LOCAL sCode         AS STRING
LOCAL HoldMore      AS LONG
LOCAL UpdateFlag    AS LONG
gs+= CHR$("Update OnHold until >=Quantity",$CR,$CR,"Code",$TAB,"Qty",$TAB,"OnHold",$CR)
CreateTable

scode = "wilson"
HoldMore = 1
DO 'test Increment function by holding more until limit reached
  ShowTable "select * from table1 where code = '" + sCode + "'"
  UpdateFlag = Increment(sCode,HoldMore)
LOOP WHILE UpdateFlag

gs+= CHR$($CR,$CR,"Qty <= OnHold",$CR,$CR,"Code",$TAB,"Qty",$TAB,"OnHold",$CR)
ShowTable "select * from table1 where Qty <= OnHold"
? gs,,"Done"
END FUNCTION

SUB ShowTable(sql AS STRING)
LOCAL sArray() AS STRING
slSelAry sql,sArray(),"Q9c"
IF UBOUND(sArray) > 0 THEN
  gs = gs + JOIN$(sArray(),$CR) + $CR
ELSE
  gs = "No data" + $CR
END IF
END SUB

SUB CreateTable
LOCAL sCode AS STRING
LOCAL hold,UpdateFlag AS LONG
slOpen "table1","C"
slexe "drop table if exists table1"
slexe "create table table1(code unique, qty, OnHold)"
slexe "insert into table1 values('wilson',9,0)"
slexe "insert into table1 values('klume',100,103)"
slexe "insert into table1 values('squires',500,602)"
END SUB

Incorrect sql statements are caught by slSel, but a zero length sql statement will not error until slGetColumnCount with an error -14.

Added this error check:
IF LEN(sql) = 0 THEN ? "No select statement",%MB_SYSTEMMODAL OR %MB_ICONERROR,"GetRecordSet":EXIT FUNCTION

Also added:
If rownum = 0 then ERASE sRecordSet:EXIT FUNCTION ' so a big empty array is not returned when there is no data to return.


#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN()
LOCAL sql,sRecordSet(),sColumnDelimiter,sReturned AS STRING
sColumnDelimiter = "  "
slopen "sample.db3","C"
sql = "select rowid,manuf,price from parts limit 3"
sReturned = GetRecordSet(sql,sRecordSet(),sColumnDelimiter)
IF LEN(sReturned) THEN ? sReturned
END FUNCTION

FUNCTION GetRecordSet(sql AS STRING,sRecordSet() AS STRING,sColumnDelimiter AS STRING) AS STRING
LOCAL colnum,rownum,columns,highelement AS LONG
IF LEN(sql) = 0 THEN ? "No select statement",%MB_SYSTEMMODAL OR %MB_ICONERROR,"GetRecordSet":EXIT FUNCTION
slsel sql,0,"E0"

IF slGetErrorNumber THEN                          'execute the sql statement
   ? sql + $CR +$CR + slGetError,%MB_SYSTEMMODAL OR %MB_ICONERROR,"GetRecordSet"
   EXIT FUNCTION
END IF

columns = slGetColumnCount                        'number of columns to return

DIM sCol(1 TO columns) AS STRING                  'avoid concatenation of current row
highelement = 50000                               'arbitrary top limit of recordset array
REDIM sRecordset(1 TO highelement) AS STRING

DO WHILE slGetRow                                 'read recordset loop
  INCR rownum                                      'actual number of reads rows
  IF rownum > highelement THEN                     'redim recordset array if needed
    highelement = highelement + 50000
    REDIM PRESERVE sRecordset(1 TO highelement)
  END IF
  FOR colnum = 1 TO columns                        'read columns of current row
   sCol(colnum) = slf(colnum)                      'place into current column array
  NEXT
  sRecordset(rownum) = JOIN$(sCol(),sColumnDelimiter)  'concatenate columns into current recordset(row)
LOOP

IF rownum = 0 THEN
    ERASE sRecordSet
    ? "No data",%MB_SYSTEMMODAL,"GetRecordSet"
    EXIT FUNCTION      'exit if no rows returned
END IF

REDIM PRESERVE sRecordset(1 TO rownum)            'shrink array to actual size
FUNCTION = JOIN$(sRecordSet(),$CR)
END FUNCTION                     

QuoteI use a page up/page down to retreive the next record.

In SQLite the recordset is filled and scrolled.
The concept of previous/next is of the recordset.

Sounds like you are not reading to the end of the recordset which will close it.
If you issue slCloseSet like FIM says should fix the problem, but reading to end of data should be the solution.

Different set numbers should not be needed if the recordset is closed after each select by reading to the end.
The read recordset loop should be examined to see if it is being exited before reading everything.
I normally used slSelAry so never see this problem.   See the function below named FillControl.

Example filling a textbox with multiple recordsets from the SQLite database "sample.db3"  "parts" table using a global string.
If slSelAry in the below function FillControl is replaced with your read recordset loop should reveal the error.


#PBFORMS CREATED V2.01 'scroll.bas for future reference https://www.sqlitening.planetsquires.com/index.php?topic=9733.msg26332#msg26332
#COMPILE EXE
#DIM ALL
GLOBAL ghDlg AS DWORD  'global handle to dialog
GLOBAL gs AS STRING    'recordset string to fill textbox
#PBFORMS BEGIN INCLUDES
#INCLUDE ONCE "WIN32API.INC"
#PBFORMS END INCLUDES
#INCLUDE "SQLITENING.INC"
#PBFORMS BEGIN CONSTANTS
%BUTTON_SELECT = 1001
%TEXT_RESULT   = 1002
%TEXT_SQL      = 1003
#PBFORMS END CONSTANTS
#PBFORMS DECLARATIONS
'------------------------------------------------------------------------------
FUNCTION FillControl AS LONG
LOCAL sql      AS STRING
LOCAL sArray() AS STRING
'slconnect "192.168.0.2"
slopen "sample.db3"
CONTROL GET TEXT ghDlg,%TEXT_SQL TO sql    'sql statement
slselary sql,sArray(),"Q9c"                'get recordset
gs+=JOIN$(sArray(),$CRLF)                  'add to previous recordset
gs+=$CRLF + STRING$(150,".") + $CRLF       'optional
CONTROL SET TEXT ghDlg,%TEXT_RESULT, gs    'fill control
'CONTROL SEND ghDlg,%TEXT_Result,%WM_VSCROLL,%SB_BOTTOM,0  'scroll to bottom
'slClose                                    'close database
'slDisconnect                               'disconnect
END FUNCTION
'------------------------------------------------------------------------------
FUNCTION PBMAIN()
ShowDIALOG1 %HWND_DESKTOP
END FUNCTION

CALLBACK FUNCTION ShowDIALOG1Proc()
  SELECT CASE AS LONG CB.MSG
    CASE %WM_INITDIALOG
     Fillcontrol

    CASE %WM_COMMAND
      SELECT CASE AS LONG CB.CTL

       CASE %BUTTON_SELECT
          IF CB.CTLMSG = %BN_CLICKED OR CB.CTLMSG = 1 THEN
            FillControl
          END IF
      END SELECT
  END SELECT
END FUNCTION
FUNCTION ShowDIALOG1(BYVAL hParent AS DWORD) AS LONG
  LOCAL lRslt AS LONG

#PBFORMS BEGIN DIALOG %IDD_DIALOG1->->
  LOCAL hDlg  AS DWORD

  DIALOG NEW hParent, "Dialog1", 457, 206, 358, 225, %WS_POPUP OR %WS_BORDER _
    OR %WS_DLGFRAME OR %WS_CAPTION OR %WS_SYSMENU OR %WS_CLIPSIBLINGS OR _
    %WS_VISIBLE OR %DS_MODALFRAME OR %DS_3DLOOK OR %DS_NOFAILCREATE OR _
    %DS_SETFONT, %WS_EX_CONTROLPARENT OR %WS_EX_LEFT OR %WS_EX_LTRREADING OR _
    %WS_EX_RIGHTSCROLLBAR, TO hDlg
  CONTROL ADD BUTTON,  hDlg, %BUTTON_SELECT, "Select", 200, 200, 50, 15
  CONTROL ADD TEXTBOX, hDlg, %TEXT_RESULT, "", 5, 5, 345, 185, %WS_CHILD OR _
    %WS_VISIBLE OR %WS_HSCROLL OR %WS_VSCROLL OR %ES_LEFT OR %ES_MULTILINE _
    OR %ES_AUTOHSCROLL OR %ES_AUTOVSCROLL OR %ES_WANTRETURN, _
    %WS_EX_CLIENTEDGE OR %WS_EX_LEFT OR %WS_EX_LTRREADING OR _
    %WS_EX_RIGHTSCROLLBAR
  CONTROL ADD TEXTBOX, hDlg, %TEXT_SQL, "select rowid,MANUF,PRODUCT from " + _
    "parts limit 5", 5, 200, 190, 13
#PBFORMS END DIALOG
  ghDlg = hDlg
  DIALOG SHOW MODAL hDlg, CALL ShowDIALOG1Proc TO lRslt
#PBFORMS BEGIN CLEANUP %IDD_DIALOG1
#PBFORMS END CLEANUP
FUNCTION = lRslt
END FUNCTION'

https://www.sqlite.org/lang_vacuum.html#vacuuminto

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.

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

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

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                   

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

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

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

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

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