B4J Question SQLite JDBC Driver with encryption and authentication support

[yiankos1]

Hello team,
Do you think this driver will let us add encryption at our sqlite databases? Does anyone understands the method we can apply encryption at our db?
Thank you for your time.

 

[OliverA]

Interesting find. That driver is more up to date than the Xerial one found here https://github.com/xerial/sqlite-jdbc. As of this writing Xerial is at 3.27.2.1 (even though the readme.md shows 3.28) and this one is at 3.30.0 (just a tad behind SQLite's current version of 3.30.1).

 

[yiankos1]

So, can it be used instead of 3.7.2 sqlite version in order to encrypt a database?

Interesting find. That driver is more up to date than the Xerial one found here https://github.com/xerial/sqlite-jdbc. As of this writing Xerial is at 3.27.2.1 (even though the readme.md shows 3.28) and this one is at 3.30.0 (just a tad behind SQLite's current version of 3.30.1).

 

[OliverA]

So, can it be used instead of 3.7.2 sqlite version in order to encrypt a database?

A quick read of the docs seem to suggest that. It seems to even be compatible with SQLCipher. Cool thing is that this would require no wrapper (such as in the case of SQLCipher: https://www.b4x.com/android/forum/t...ryption-with-sqlcipher-library.14965/#content). Due to today's work load, I'll be unable to play with this, but technically all you have to do is put the .jar file in the Additional Libraries folder, include it in your project and start playing with it. Looks like the initial encryption is handled with SQL statements and then the encrypted database is opened via parameters passed along in the JDBC URL.

 

[yiankos1]

Thank you for answering, but initializing jSQL for sqlite, does not allow you to pass any JDBC URL. Maybe needs a new wrapper for jSQL?

 

[OliverA]

Thank you for answering, but initializing jSQL for sqlite, does not allow you to pass any JDBC URL. Maybe needs a new wrapper for jSQL?

Don't use InitializeSQLite. Use Initialize or Initialize2. Something like

sql.Initialize("com.sqlite.JdbcUrl","jdbc:sqlite:test.db")

That should open an unencrypted test.db SQLite database file that is located in the same directory as the the program file (.jar) or, while developing, in the Objects directory of your project. Note: The test.db must exist, so you either need to use InitializeSQLite to create the database file or another external program. Once you open the unencrypted DB, you can encrypt it as per documentation. You then need to change the URL to include the encryption parameters so the driver can properly open your database.

 

[yiankos1]

Don't use InitializeSQLite. Use Initialize or Initialize2. Something like

sql.Initialize("com.sqlite.JdbcUrl","jdbc:sqlite:test.db")

That should open an unencrypted test.db SQLite database file that is located in the same directory as the the program file (.jar) or, while developing, in the Objects directory of your project. Note: The test.db must exist, so you either need to use InitializeSQLite to create the database file or another external program. Once you open the unencrypted DB, you can encrypt it as per documentation. You then need to change the URL to include the encryption parameters so the driver can properly open your database.

Thank you very much.
I will try this out. If you find spare time, give it a try and let us know if it worked for you.

 

[Erel]

Something that you should be aware of: if you don't initialize it with InitializeSQLite then the library will not prevent multiple non-query commands to be executed concurrently if you are using the async methods. It shouldn't be too complicated to take care of this in your app.

 

[yiankos1]

So i managed to get this working.
1) First download latest Jar
2) Copy jar at Additional libraries folder inside B4J installation folder

'REMOVE this line, if exists
#AdditionalJar: sqlite-jdbc-3.7.0

'add this line on Project Attributes. if there is newer version, just change version number
#AdditionalJar: sqlite-jdbc-3.30.0

'add this at AppStart
SQL.Initialize("org.sqlite.JDBC","jdbc:sqlite:file:data.db?cipher=sqlcipher&legacy=1&kdf_iter=4000&key=YOURKEY")

For more info about configuration please check README.md

 

[yiankos1]

Something that you should be aware of: if you don't initialize it with InitializeSQLite then the library will not prevent multiple non-query commands to be executed concurrently if you are using the async methods. It shouldn't be too complicated to take care of this in your app.

Good morning Erel, i think this jdbc driver should be default at jSQL library about sqlite. It could be [B4X] solution because supports all operating systems and encryptions.

 

[OliverA]

Something that you should be aware of: if you don't initialize it with InitializeSQLite then the library will not prevent multiple non-query commands to be executed concurrently if you are using the async methods. It shouldn't be too complicated to take care of this in your app.

1) If in the jSQL library (I'm looking at the source here: https://github.com/AnywhereSoftware/B4J_SQL/blob/master/src/anywheresoftware/b4j/objects/SQL.java)
sqliteLock where public, we could also use JavaObject to set it with a "new ReentrantLock()".
2) Or if there were an "enableSQLiteLock" method in jSQL
3) Or if Initialize2 could just enable the lock if it sees the connection string starting with "jdbc:sqilte"
Then we could have the best of both worlds: 1) Use different SQLite JDBC driver and 2) let jSQL handle the locking as it already does

Update: I put in a wish here: https://www.b4x.com/android/forum/threads/enable-locking-for-alternative-sqlite-jdbc-drivers.110605/

 

[mcqueccu]

UPDATE: For SQlCipher 4 support change Legacy value to 4, and Iterations to around 256000

SQL.Initialize("org.sqlite.JDBC","jdbc:sqlite:file:data.db?cipher=sqlcipher&legacy=4&kdf_iter=256000&key=YOURKEY")