[solved] sqlcipher.dll problem

[menschmarkus_]

in addition to following discussions
viewtopic.php?t=73046&hilit=sqlcipher
https://www.purebasic.fr/german/viewtopic.php?t=33021 German Forum

I ask the same question here in english forum as well

I use SQLite Database for a long time. Everything is working fine. I startet to change SQLite to ciphered version and included the sqlciper.dll library. On my development system it is woking fine. As soon I copy the executable file to a third party PC I run in problems.
Of cource the sqlcipher.dll is within the program directory.

Code: Select all

UseSQLiteDatabase("sqlcipher.dll")
EnableExplicit
Define mydir.s, found.i,result.s
;
found = 0
ExamineDirectory(0,GetCurrentDirectory(),"*.db3")
While NextDirectoryEntry(0)
  If DirectoryEntryType(0) = #PB_DirectoryEntry_File
    If DirectoryEntryName(0) = "test.db3"
      found = 1
    EndIf
  EndIf
Wend
FinishDirectory(0)
If found = 0
  CreateFile(0,"test.db3")
  CloseFile(0)
EndIf
;
If OpenDatabase(0,"test.db3","","",#PB_Database_SQLite)
  DatabaseUpdate(0,"PRAGMA key = 'passkey';")
  FinishDatabaseQuery(0)
  If found = 0
    DatabaseUpdate(0,"CREATE TABLE test (field1 INTEGER,field2 INTEGER);")
    DatabaseUpdate(0,"INSERT INTO test (field1,field2) VALUES (1,1),(2,2),(3,3);")
    DatabaseQuery(0,"SELECT * FROM test;")
    While NextDatabaseRow(0)
      result + GetDatabaseString(0,DatabaseColumnIndex(0,"field1")) + " , " + GetDatabaseString(0,DatabaseColumnIndex(0,"field2")) + Chr(10)
    Wend
    FinishDatabaseQuery(0)
    MessageRequester("Hinweis",result)        
  Else
    DatabaseQuery(0,"SELECT * FROM test;")
    While NextDatabaseRow(0)
      result + GetDatabaseString(0,DatabaseColumnIndex(0,"field1")) + " , " + GetDatabaseString(0,DatabaseColumnIndex(0,"field2")) + Chr(10)
    Wend
    FinishDatabaseQuery(0)
    MessageRequester("Hinweis",result)
  EndIf
Else
  MessageRequester("Hinweis","Open Error: " + DatabaseError())
EndIf
CloseDatabase(0)
  

Following Debugger Message appears


To me it looks like the library is not included. Unfortunately there is no Return value using UseSQLiteDatabase() command
Does someone have the same effect?
Does someone have Ideas what may happen?
Or in perfect case can give a solution
Thanks


// Moved from "Tricks 'n' Tips" to "Coding Questions" (Kiffi)

[infratec]

Is this a tip or a trick?

Please move it to coding questions.

Btw. use depends or something similar to check if your sqlcipher.dll needs an additional crypt dll.

[menschmarkus_]

sry was reading previous posts and stuck at Tricks 'n' Tips. Forgot to change to Coding Questions.
Admin should move it.

Btw. use depends or something similar to check if your sqlcipher.dll needs an additional crypt dll.

I used SQLiteBrowser and copied all DLL of this Browser to programs directory. Effect was the same.

[infratec]

I just checked it:

You need sqlcipher.dll and libcrypto-1_1.dll

[infratec]

Btw.:

Code: Select all

DatabaseUpdate(0,"PRAGMA key = 'passkey';")
FinishDatabaseQuery(0)

is wrong.

FinishDatabaseQuery() is only for DatabaseQuery() but not for DatabaseUpdate()

[infratec]

this works for me, also as exe file with


sqlcipher.dll
libcrypto-1_1.dll

in the same directory as the exe file.


Extracted from:
https://download.sqlitebrowser.org/DB.B ... -win32.zip

For PB x64 you need:
https://download.sqlitebrowser.org/DB.B ... -win64.zip
But not tested with this.

Code: Select all

EnableExplicit

Define.i FileExitsAlready
Define Filename$, Result$

UseSQLiteDatabase("sqlcipher.dll")

Filename$ = GetPathPart(ProgramFilename()) + GetFilePart(ProgramFilename(), #PB_FileSystem_NoExtension) + ".sqlite"

If FileSize(Filename$) = -1
  If CreateFile(0, Filename$)
    CloseFile(0)
  EndIf
Else
  FileExitsAlready = #True
EndIf

If OpenDatabase(0, Filename$, "", "")
  If DatabaseUpdate(0, "PRAGMA key = 'passkey';")
    
    If Not FileExitsAlready
      If DatabaseUpdate(0,"CREATE TABLE test (field1 INTEGER,field2 INTEGER);")
        MessageRequester("Info", "Table created")
        DatabaseUpdate(0,"INSERT INTO test (field1,field2) VALUES (1,1),(2,2),(3,3);")
      EndIf
    EndIf
    
    If DatabaseQuery(0,"SELECT field1, field2 FROM test;")
      While NextDatabaseRow(0)
        Result$ + GetDatabaseString(0, 0) + " , " + GetDatabaseString(0, 1) + #LF$
      Wend
      FinishDatabaseQuery(0)
      
      MessageRequester("Result", Result$)
      
    EndIf
  EndIf
  
  CloseDatabase(0)
EndIf

[menschmarkus_]

ok thanks for modified code. Regarding FinishDatabaseQuery() after UpdateDatabase() I wasn't sure so for secure reasons I did it. It had no negative influences. Good to know that this is not necessary

Adding lipcrypto-1_1.dll to program directory I already tried without success.
However in the meantime I ignored PB Help information and additionally installed sqlcipher.dll in C:\Windows\system32 directory.
Due to the fact I do not compile 32bit version there is no need to install 32bit version in C:\Windows\SysWOW64
I did not include libcrypto-1_1.dll anywhere in the system and it is working.
My conclusion is: just copying sqlcipher.dll in program directory is not working. Installing library file in Standard Windows system directory is necessary to be sure system will work.
I think loading external library needs to be aproved in PB. Returnvalue by calling UseSQLiteDatabase("<libname>") would be helpful.
Thank you infratec for your assistance.

[infratec]

A dll is always first tried to load from the same directory where the program ist started from.
Then it went to the 'usual' places and look for it.

[menschmarkus_]

A dll is always first tried to load from the same directory ...

I don't want to spread negative vibes here but obviously loading the library in the program directory doesn't work quite correctly. How else would the error message be explained. One way that works is the one I described above.

Anyway I could offer a proper solution. This is important for all
Thanks for your assistance

[infratec]

In my tests it works without any problems.

PB 6.02 x86 on Win10 x64

[menschmarkus_]

just to complete information
I develop under Win 11 in this project with 5.73LTS but tried with 6.02LTS as well. No problems on this machine
Third party System for crosscheck was Win10 and Win11 OS. WIN10 was an existing productive system and Win11 was brand new system without any software already installed. In Both systems problem appeared.
For future I install library in program directory as well as in system directories as redundance.

[Boulcat]

I had encountered the same kind of problem a while ago, and solved it by adding:

Code: Select all

SetCurrentDirectory(GetPathPart(ProgramFilename()))

But now, even without it, I can't reproduce! but maybe give it a try.

However in the meantime I ignored PB Help information and additionally installed sqlcipher.dll in C:\Windows\system32 directory.
I did not include libcrypto-1_1.dll anywhere in the system and it is working.

um, no, it doesn't work, libcrypto-1_1.dll is really required

I tested Infratec's code (windows 10 x64), with sqlcipher.dll and libcrypto-1_1-x64.dll next to the exe and everything works fine.
Copying libcrypto-1_1-x64.dll (and/or sqlcipher.dll) to C:\Windows\system32 works, of course, but it shouldn't be required.

[menschmarkus_]

I had encountered the same kind of problem a while ago, and solved it by adding:

Code: Select all

SetCurrentDirectory(GetPathPart(ProgramFilename()))

But now, even without it, I can't reproduce! but maybe give it a try.

I tried it out but was not working on my third party PCs

um, no, it doesn't work, libcrypto-1_1.dll is really required

Please clear the situation. Do you mean if you include DLLs in Programs directory only?
I tried this as well without success.

Repeating myself I just copied the sqlcipher.dll in System directory. Thats all. No further dll. It works here. Please try this as well and report it here.
(Third Party WIN11 system was without installed PB. I Just used my programs EXE file or PB_compilation EXE to follow debugger info.)
Thank you Boulcat

[Boulcat]

um, no, it doesn't work, libcrypto-1_1.dll is really required

Please clear the situation. Do you mean if you include DLLs in Programs directory only?
I tried this as well without success.

It was in relation to your previous message

I did not include libcrypto-1_1.dll anywhere in the system and it is working.

libcrypto-1_1.dll is required as a dependency of sqlcipher.dll.No matter where it is.
It is searched first in the current directory, where the program started.
And if not found there, the system searches for it in your path: C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;...

Without libcrypto-1_1.dll (and/or sqlcipher.dll) next to the exe or somewhere in the path, it can't work. sqlcipher_demo.sqlite is created but without table and data.

With sqlcipher.dll and libcrypto-1_1.dll next to the exe, it should work. Without the need to copy both dll in C:\Windows\system32.
if you add one or the other in C:\Windows\system32, it will also work but it shouldn't be needed normally!

[skywalk]

Is this help file example wrong?

Btw.:

Code: Select all

DatabaseUpdate(0,"PRAGMA key = 'passkey';")
FinishDatabaseQuery(0)

is wrong.

FinishDatabaseQuery() is only for DatabaseQuery() but not for DatabaseUpdate()

Code: Select all

; First, connect to a database with an employee table
;
  If DatabaseQuery(#Database, "SELECT * FROM employee") ; Get all the records in the 'employee' table
  
    While NextDatabaseRow(#Database) ; Loop for each records
      
      ; Update the 'checked' field for each records, assuming the 'id' field is 
      ; the first one in the 'employee' table
      ;
      DatabaseUpdate(#Database, "UPDATE employee SET checked=1 WHERE id="+GetDatabaseString(#Database, 0)) 
    Wend
    
    FinishDatabaseQuery(#Database)
  EndIf