B4J Code Snippet Open Encrypted Sqlite Databases with B4J (SQLCipher)

Download the JDBC from this GitHub, and place it in your Additional folder

In your Main Module, use #AdditionalJar to add your jdbc.

Add JSQL library

The following JDBC has been tested and works fine. You can use any of them
'#AdditionalJar: sqlite-jdbc-3.30.0
'#AdditionalJar: sqlite-jdbc-3.31.1
'#AdditionalJar: sqlite-jdbc-3.35.5.1

'Using the latest Version, you need to download and Add the slf4j API, otherwise you will get Class not found Error
#AdditionalJar: sqlite-jdbc-3.46.0.0
#AdditionalJar: slf4j-api-1.7.36

Find Example Attached

 

[T201016]

Hi @mcqueccu
I used your example for testing
And I have to say that there are errors with individual JDBC:

'Working
#AdditionalJar: sqlite-jdbc-3.30.0
#AdditionalJar: sqlite-jdbc-3.31.1
#AdditionalJar: sqlite-jdbc-3.35.5.1

#AdditionalJar: slf4j-api-1.7.36

java.io.FileNotFoundException: Version file is null
java.io.FileNotFoundException: Version file is null
DB Started

#AdditionalJar: sqlite-jdbc-3.46.0.0
#AdditionalJar: slf4j-api-1.7.36

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
DB Started

For now, I have not managed to test other (older) JDBC version.

It can be ignored, of course. But adding:

#AdditionalJar: slf4j-nop-1.7.36

I get no warnings whatsoever. Cleaner log.

 

[hatzisn]

Hi @mcqueccu. Did you have any luck with the SQLCipher for B4J? I tried your project and it responds that the file is not a sqlite database.

Edit - With 3.7.2 and slf4j-api-1.7.36 it responds that it cannot find the table.

 

[mcqueccu]

Hi @mcqueccu. Did you have any luck with the SQLCipher for B4J? I tried your project and it responds that the file is not a sqlite database.

Edit - With 3.7.2 and slf4j-api-1.7.36 it responds that it cannot find the table.

It works fine. Make sure you using the recommended files

 

[Magma]

It works fine. Make sure you using the recommended files

Hi there !!!

well i ve tried your example with many sqlite drivers and with the recommended +slf4j-api-1.7.36... but the result was always the same:
org.sqlite.SQLiteException: [SQLITE_NOTADB] File opened that is not a database file (file is not a database)

I ve checked hundred of times if i was having something different... also tried open with db browser sqlcipher-version your file (and works)...
I ve also used direct path to database... but the same...
The error seems... comes when trying to open... the already founded file (so no path error)... and all seems that will go ok... but something other hits... not decrypting well, not using right the key passphrase in my b4j-jdk version (who knows)...

So I think the problem is deeper... so let's find it..

I am using B4J v10
Open JAVA JDK 19...

what versions are you using ?

ps: if the files are in additional folder (of libs) getting the same error with @hatzisn

 

[Magma]

...Hi there after, a lot of searching... found that my sqlite jdbc drivers not the right... also not using clean db but tried with an old to encrypt it from other app (in tests doing)

So I "confirm" that @mcqueccu snippet working good...

But i have a question - if you wanna open the sqlite created with encryption is it possible from 3rd app... perhaps dbbrowser and how ?

 

[Magma]

...After some tests.. not so stable... /lost connection...

and for some reason now (even if reboot)... taking from sqlite string of connection the jdbc...file:... and saving the db as "file." - if remove the file,,, and set in string perhpas mydb without ".db" create a file "mydb" and now on the size is 0... :-(

very strange... quitting the tries - because i think is unstable and not safe for production

 

[Mariano Ismael Castro]

...After some tests.. not so stable... /lost connection...

and for some reason now (even if reboot)... taking from sqlite string of connection the jdbc...file:... and saving the db as "file." - if remove the file,,, and set in string perhpas mydb without ".db" create a file "mydb" and now on the size is 0... :-(

very strange... quitting the tries - because i think is unstable and not safe for production

DB Browser for SQLCipher.exe

You can create the database with this program and open it from B4J. I also encrypted a database using .NET and can open it using this code.
There is a paid program called Navicat that also allows you to open these encrypted databases; it's just a matter of using the correct encryption/decryption algorithm.

 

[Magma]

DB Browser for SQLCipher.exe

You can create the database with this program and open it from B4J. I also encrypted a database using .NET and can open it using this code.
There is a paid program called Navicat that also allows you to open these encrypted databases; it's just a matter of using the correct encryption/decryption algorithm.

Hi there... [I am talking for B4J here]

I know the program... and before some time.. first tried to create from it the db and use it... never worked...

But when manually created with github jdbc driver of this thread the database using password db encrypted and created... the prob was the dbbrowser or any tool tried can't read this file with any way i ve tried... but in b4x was working...

The 2nd problem is i didn't manage to have it in a different path than the \objects\ folder (same path of app running)... even tried \ / or any combination... using file or not using file after jdbc:sqlite: ...

After those tries - and returning to previous state.... using the db from objects folder... sqlite driver begun to giving me wrong db filename or creating 0kb size of db files and unstable...

So using DBBrowser SQLCipher can;t solve this prob at least using latest jdbc driver in Windows 10/B4J 10.20/openJava 19 or previous 14, 11

I don't know if there is official solution for B4J combination with SQLCipher but sure for me until now with any knowledge i have is not secure for production.

 

[mcqueccu]

@Mariano Ismael Castro is confirming that it works even if you create the database with 3rd party apps like DB Browser for SQLite. I personally tried it and it works as well

Check the initialize code for the legacy number and iteriation

This specifies the legacy database format used for encryption. SQLCipher has had several changes over its versions in how the encryption key and settings are applied.

legacy=1: SQLCipher 1.x format (uses 4000 KDF iterations, HMAC-SHA1)
legacy=2: SQLCipher 2.x (uses 64000 KDF iterations, HMAC-SHA1)
legacy=3: SQLCipher 3.x (uses 64000 KDF, HMAC-SHA1, and PBKDF2)
✅ legacy=4: SQLCipher 4.x (uses 256000 KDF iterations, HMAC-SHA512, PBKDF2-HMAC-SHA512)

sql.Initialize("org.sqlite.JDBC",$"jdbc:sqlite:file:test.db?cipher=sqlcipher&legacy=4&kdf_iter=256000&key=${key}"$)

 

[Magma]

@Mariano Ismael Castro is confirming that it works even if you create the database with 3rd party apps like DB Browser for SQLite. I personally tried it and it works as well

Check the initialize code for the legacy number and iteriation

This specifies the legacy database format used for encryption. SQLCipher has had several changes over its versions in how the encryption key and settings are applied.

legacy=1: SQLCipher 1.x format (uses 4000 KDF iterations, HMAC-SHA1)
legacy=2: SQLCipher 2.x (uses 64000 KDF iterations, HMAC-SHA1)
legacy=3: SQLCipher 3.x (uses 64000 KDF, HMAC-SHA1, and PBKDF2)
✅ legacy=4: SQLCipher 4.x (uses 256000 KDF iterations, HMAC-SHA512, PBKDF2-HMAC-SHA512)

sql.Initialize("org.sqlite.JDBC",$"jdbc:sqlite:file:test.db?cipher=sqlcipher&legacy=4&kdf_iter=256000&key=${key}"$)

View attachment 163377

Thanks for your answer

At dbbrowser sqlcipher version using those settings with a simple passphrase as "test" and never opened the db !

1. which version of sqlite driver jdbc (from github link) you are working ?
2. can you have different path at db file ?

 

[mcqueccu]

1. I just tried the latest JDBC from the github link and it stills works: #AdditionalJar: sqlite-jdbc-3.49.1.0 - MY DB Browser version: 3.13.1
2. Yes you can choose a different path for the Database: You can see it in the example attached. That is initialization line that is commented out.

sql.Initialize("org.sqlite.JDBC","jdbc:sqlite:file:C:\Users\DEVN\Desktop\test.db?cipher=sqlcipher&legacy=4&key=12345678&kdf_iter=256000")

You can upload a sample database for testing