• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

Using encrypted data in SELECT-statement

Started by Fim, April 26, 2015, 08:18:10 AM

Previous topic - Next topic

Fim

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
Fim W

cj

#1
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
                               
"No email alerts being received"  Please often check back.

Fim

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
Fim W

cj

#3
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?

"No email alerts being received"  Please often check back.

Bern Ertl

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.

cj

I do not know and am very curious.
Could it cause a problem forming select statements?
"No email alerts being received"  Please often check back.

Bern Ertl

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).

cj

#7
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.
"No email alerts being received"  Please often check back.

Bern Ertl

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.

cj

#9
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
"No email alerts being received"  Please often check back.

Bern Ertl


Fim

#11
cj

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