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

News:

Welcome to the SQLitening support forums!


slSelBind only use equal search with compressed or encrypted

Started by cj, May 07, 2016, 12:40:56 pm

Previous topic - Next topic

cj

Bind select (slSelBind)  only works on EQUAL column comparisons
Suggest mentioning in help

Collation sequence is probably the reason.
SQLite has an encryption library for $2000.
Other alternatives are available.

1. This demo inserts 4 records, C then B then A then D
2. Search on column c1>='B' should return C then B then D
3. Incorrect results with slSelbind C=Compress or N=Encrypt

Bottom line, don't search on encrypted/compressed columns unless for an exact match.


#INCLUDE "sqlitening.inc"
GLOBAL gsResult AS STRING
FUNCTION PBMAIN () AS LONG
  LOCAL sBind, sSearchFor AS STRING, x AS LONG
  slSetProcessMods "K1234567890123456" 'encrypt key if bind includes eNcrypt
  slOpen "sample.db3","C"
'search on column c1>= 'B' correct result should be CBD
  FOR x = 1 TO 5
    IF x = 1 THEN sBind = " " 'correct, results will be CBD
    IF x = 2 THEN sBind = "B" 'correct, results will be CBD
    IF x = 3 THEN sBind = "T" 'correct, results will be CBD
    IF x = 4 THEN sbind = "C" 'wrong,   results will be CBA
    IF x = 5 THEN sBind = "N" 'wrong,   results will be CB
    slexe "drop table if exists t1"
    slexe "create table if not exists t1(c1 TEXT)"
   
    slExe "Begin Exclusive"
    slExeBind "insert into t1 values(?)",slBuildBindDat("C",sBind)
    slExeBind "insert into t1 values(?)",slBuildBindDat("B",sBind)
    slExeBind "insert into t1 values(?)",slBuildBindDat("A",sBind)
    slExeBind "insert into t1 values(?)",slBuildBindDat("D",sBind)
    slExe "End"

    sSearchFor = slBuildBindDat("B",sBind)
    slSelBind "Select * from t1 where c1>=?",sSearchFor 'c1>= <---
    GetResult(sBind)
  NEXT
  ? gsResult
END FUNCTION

SUB GetResult(sBind AS STRING)
  LOCAL sUnBind AS STRING
  IF INSTR(sBind,"N") THEN sUnBind = "D" 'encrypted  so decrypt
  IF INSTR(sBind,"C") THEN sUnBind+= "U" 'compressed so uncompress
  gsResult+=CHR$($DQ,sBind,$DQ," ")
  DO WHILE slGetRow
    gsResult+=slfx(1,sUnBind)
  LOOP
  gsResult+=$CR
END SUB