SQLCipher instead of SQLite

[amir.eng]

Hi all,
I connected  lazarus to SQLite database with TSQLite3Conncetion successfully, and now I want to connect the program to SQLCipher that I've created by SQLiteStudio, but it gives this error :

"SQLite3Connection1 : file is not a database"

I also wrote the database password on the properties of "SQLite3Connection1".

Is it possible to connect encryption SQLite database (sqlcipher or wxsqlite3) with Lazarus?

[amir.eng]

 I need to secure SQLite., Any suggestion ?

Is there any way to connect lazarus to encryption types of SQLite (SQLCiper or wxSQLite) ??

Thanks in advance.

[MarkMLl]

I suggest searching the forum before posting. Note this from TWO DAYS ago:

https://forum.lazarus.freepascal.org/index.php/topic,57470.msg427342.html#msg427342

MarkMLl

[amir.eng]

Thank you for replying, I had read the topic and searched before posting, unfortunately I couldn't find useful information about wxSQLite or SQLCipher.
At below topic only is written that Lazarus supports encrypted SQLite after 2012 version, but it is not explain how to do this.
https://wiki.freepascal.org/SQLite#Support_for_SQLite_encryption

I want to know if there is a way to secure SQLite or encrypt it ?

[PierceNg]

Thank you for replying, I had read the topic and searched before posting, unfortunately I couldn't find useful information about wxSQLite or SQLCipher.
At below topic only is written that Lazarus supports encrypted SQLite after 2012 version, but it is not explain how to do this.
https://wiki.freepascal.org/SQLite#Support_for_SQLite_encryption

I want to know if there is a way to secure SQLite or encrypt it ?

Whichever crypto extension to SQLite you use, you need the appropriate DLL/so/dylib. Just specifying am encryption password with the standard SQLite library is not going to work.

[amir.eng]

I thought all of SQLite,SQLCpher and wxSQLite use SQLite3.DLL 

Where can I find crypto extension's DLL of SQLite or wxSQLite ? of course, there is wxSQLite3 documents on GitHub without any DLL:
https://github.com/utelle/wxsqlite3

[kjteng]

I have not much experience in using TSQLite3Conncetion with wsSqlite.

I recommend u to use zeoslib with some minor change to source. See https://github.com/kjteng/zeoslib/tree/main/ZDbcSqLite

Using TSQlite3connection,  you should be able to open an encrypted sqlite database (say cipher=aes256cbc, passwrod=123123) with wxsqlite as follows

Code: Pascal  [Select][+][-]

1.   sqlconnector1.Connected:= False;
2.   sqlconnector1.DatabaseName := 'yourdbname.db');
3. // always run the following two lines before you connect the database
4.   sqlconnector1.ExecuteDirect('PRAGMA cipher=''aes256cbc''');  // connectorType='SQLite3'
5.   sqlconnector1.ExecuteDirect('PRAGMA key=''123123''') ;
6.   sqlconnector1.Connect;
7.   sqlquery1.Open;  

Attached: a simple demo

Note: Berfore you try the above code, please  download sqlite3mc.dll from https://github.com/utelle/SQLite3MultipleCipher and rename it to sqlite3.dll  (to replace the one from sqlite website)

[PierceNg]

I thought all of SQLite,SQLCpher and wxSQLite use SQLite3.DLL 

No.

At the risk of confusing you even more, the filename could be SQLite3.DLL, to make it easy to use through FPC's DB interfaces, but whether it is wxSQLite or SQLcipher the DLL has to contain the crypto functionality.

Where can I find crypto extension's DLL of SQLite or wxSQLite ? of course, there is wxSQLite3 documents on GitHub without any DLL:
https://github.com/utelle/wxsqlite3

You'll have to build from source. According to its Github readme, wxSQLite is using https://github.com/utelle/SQLite3MultipleCiphers, so you could just build that instead, since wxSQLite is a C++ interface, and interfacing FPC with C++ will be more troublesome than interfacing with C.

[kjteng]

No.

At the risk of confusing you even more, the filename could be SQLite3.DLL, to make it easy to use through FPC's DB interfaces, but whether it is wxSQLite or SQLcipher the DLL has to contain the crypto functionality.

You'll have to build from source. According to its Github readme, wxSQLite is using https://github.com/utelle/SQLite3MultipleCiphers, so you could just build that instead, since wxSQLite is a C++ interface, and interfacing FPC with C++ will be more troublesome than interfacing with C.

1. wxSQlite =  quite a recent version of sqlite + encryption function.
2. For windows platform, you dont have to rebuild from source. The dll is downloadable at utella's site.

[amir.eng]

Great kjteng, it works perfectly ,thanks a million
Can we embed the database (test.db) or sqlite3.dll into the executable file (Compiled .exe) ?

[af0815]

On WIndows i used this code for embedding sqlit3.dll. You can use the same code for extracting da DB from Resources.

Code: Pascal  [Select][+][-]

1. function LoadFromResourceAndMakeFile(Instance: THandle; const ResName: String;
2.   const FileAndPathName: String): Boolean;
3. var
4.   S: TResourceStream;
5.   F: TFileStream;
6. begin
7.   Result := False;
8.   // create a resource stream which points to our resource
9.   S := TResourceStream.Create(Instance, ResName, RT_RCDATA);
10.   try
11.     // create a file  in the application directory
12.     F := TFileStream.Create(FileAndPathName, fmCreate);
13.     try
14.       F.CopyFrom(S, S.Size); // copy data from the resource stream to file stream
15.       Result:=True;
16.     finally
17.       F.Free; // destroy the file stream
18.     end;
19.   finally
20.     S.Free; // destroy the resource stream
21.   end;
22. end;
23.  

But AV-Scanner want this Code :-) I have changed to InnoSetup to make installers instead of having all in one file. To deal with Resources is not praticale in companies with distributionsystems for software. Normal user cannot create such files and for the DB you must use the correct places, to make it work.