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.