SQLitening Support Forum

The SELECT-statement works ok sometimes but not alla times.
How to solve the problem?

SYNONYM_START:
    SlSetProcessMods("E0")
    SLOPEN "ORDKORS.D.DB3"
    CRYPT_KEY = "1234567890123456"
    slSetProcessMods "K" + CRYPT_KEY
    RC = AuxRutsA(2, WORD_PIC, ENCRYPTED_WORD_PIC)
    IF RC <> 0 THEN
        CALL ERROR_OUT("AuxRutsA RC=" + STR$(RC))
        EXIT FUNCTION
    END IF 
   
SYNONYM_SELECT_STATS:
    SELECT_SATS = "SELECT * FROM SYNONYMER WHERE WORD =" + $SQ + ENCRYPTED_WORD_PIC + $SQ
    MSGBOX SELECT_SATS
    SLSEL SELECT_SATS


Fim W

No idea with auxillary routines so somebody else will have to step in.
How do you insert the data?
Here is a little example encrypting and returning with slSelAry.
Also check out returning with
DO WHILE slGetRow
      slFX(1, "DU")
      slFX(2, "DU")

LOOP       


#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
  LOCAL sql,sData, Crypt_key AS STRING
  LOCAL s() AS STRING
  Crypt_key = "1234567890123456"
  slSetProcessMods "K" + CRYPT_KEY
  slOpen "ORDKORS.D.DB3","C"
  slExe "Create table if not exists t1(c1,c2)"
  sql = "insert into t1 values(?,?)"
  sData = slBuildBindDat("a","TN")   '1st column of encrypted text
  sData+= slBuildBindDat("b","TN")   '2nd column of encrypted text
  slExeBind(sql,sData)
  slSelAry("select * from t1",s(),"D1,2 Q9") 'decrypt cols 1,2 tab delimited
  ? JOIN$(s(),$CR)
END FUNCTION
                               

If I understand you correctly, you mean you want me to read the entire table from beginning to end and makes selecting the desired entries in the program. So have I done now. It was acceptably fast, even though I have 120,000 records in the table. So I content myself with the solution.
Thanks for the help.

Fim W

No.
There is no reason to read the entire table.
Use any select statement you want, but search using an encrypted string (see slConvertDat.)

or something like:

search= "'" + slConvertDat("a","N") + "'"  'encrypted string to search for
slSel "select * from t1 where c1 = " + search
DO WHILE slGetRow
  s+= slFX(1,"D") + "," +   slFX(2,"D") + $CR
LOOP
? s

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
  LOCAL sql,sData, Crypt_key, search AS STRING
  LOCAL s() AS STRING
  Crypt_key = "1234567890123456"
  slSetProcessMods "K" + CRYPT_KEY
  slOpen "ORDKORS.D.DB3","C"
  slExe "Create table if not exists t1(c1,c2)"
  sql = "insert into t1 values(?,?)"
  sData = slBuildBindDat("a","TN")   '1st column of encrypted text
  sData+= slBuildBindDat("b","TN")   '2nd column of encrypted text
  slExeBind(sql,sData)
  search= "'" + slConvertDat("a","N") + "'"  'encrypted string to search for
  slSelAry("select * from t1 where c1 =" + search,s(),"D1,2 Q9") 'decrypt cols 1,2 tab delimited
  ? JOIN$(s(),$CR)
END FUNCTION                   

Is this issue resolved?

cj, do you know if slConvertDat("a","N") is guaranteed to return string value that excludes the single quote and/or $NUL?  From what I see from a quick look through the source files, it's using the Rijndael algo.

I do not know and am very curious.
Could it cause a problem forming select statements?

A single quote in the output would definitely cause problems with the SELECT statements.  $NUL in the output shouldn't interfere with the SELECT as far as I know, but could cause issues if ASCIIZ variables are used to handle things.

I did some searching around and so far, I haven't been able to find an answer (to the potential output data).

Sounds like tests may be needed. 
I haven't seen others use slConvertDat.

http://www.sqlitening.com/support/index.php?topic=3059.0

Quote from: Fred Meier on August 22, 2009, 10:48:29 AM
SQLite does not support encryption/compression of the entire database, nor
will it encrypt/compress fields.  But since SQLite does allow binary data,
encryption/compression can be done by the SQLite using program.  By the
way, I believe that is the better way since there are so many different
ways to do enencryption/compression. 

SQLtening allows you to store binary data using the slExeBind command. 
The slExeBind command accepts BindDats.  The SQLitening slBuildBindDat
command has an encryption/compression routine built-in.  You may use these
routines or provide your own.  See code in ExampleC.

In summary:
   There is no "special" SQLite needed to do encryption/compression. 
   SQLitening allows you to store binary data using the slExeBind command. 
   SQLitening offers you one encryption/compression method.
   You may roll-your-own encryption/compression method.

I'm guessing that Rijndael can potentially output any character including single quotes.  If you have need to reference encrypted data in SQL (SELECT/UPDATE) statements, you probably should roll your own encryption for SQLitening.  Perhaps wrap the Rijndael routines with BASE64 encoding.

Use slSelBind instead of slSel with bind data if there is a WHERE clause.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  LOCAL sInput,sBind AS STRING
  slSetProcessmods "E2"
  slOpen "sample.db3","C"
  sInput = "Bern, '''hello, world!'''"
  DO
    slexe "drop table if exists simple"
    slexe "create table if not exists simple(c1)"
    slSetProcessMods "K" + STRING$(32,"A") '16,24, 32 bytes
    sInput = INPUTBOX$("Add this line","Encrypt test",sInput)
    IF LEN(sInput) = 0 THEN EXIT DO  'was END
    sBind = slBuildBindDat(sInput,"TN")
    slExeBind "insert into simple values(?)",sBind
    slSelBind "Select c1 from simple where c1 =?",sBind
    'slSel "select c1 from simple"  'if no WHERE
    IF slGetErrorNumber = 0 THEN
      DO WHILE slGetRow
        ? slfx(1,"D"),,"slSelBind Results" 'D=decrypt, U=uncompress
      LOOP
    END IF
  LOOP
END FUNCTION

Ah!  Nice solution.

cj

It works prefect with WHERE and bind data and slSelBind.
I have tested with 100,000 records, all with different value of C1.