B4J Question SQLite JDBC Driver with encryption and authentication support

Discussion in 'B4J Questions' started by yiankos1, Oct 16, 2019.

  1. yiankos1

    yiankos1 Active Member Licensed User

    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 likes this.
  2. OliverA

    OliverA Expert Licensed User

    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 likes this.
  3. yiankos1

    yiankos1 Active Member Licensed User

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

    OliverA Expert Licensed User

    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 likes this.
  5. yiankos1

    yiankos1 Active Member Licensed User

    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?
     
  6. OliverA

    OliverA Expert Licensed User

    Don't use InitializeSQLite. Use Initialize or Initialize2. Something like
    Code:
    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.
     
    Erel and yiankos1 like this.
  7. yiankos1

    yiankos1 Active Member Licensed User

    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.
     
  8. Erel

    Erel Administrator Staff Member Licensed User

    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.
     
    OliverA likes this.
  9. yiankos1

    yiankos1 Active Member Licensed User

    So i managed to get this working.
    1) First download latest Jar
    2) Copy jar at Additional libraries folder inside B4J installation folder
    Code:
    'REMOVE this line, if exists
    #AdditionalJar: sqlite-jdbc-3.7.0
    Code:
    'add this line on Project Attributes. if there is newer version, just change version number
    #AdditionalJar: sqlite-jdbc-3.30.0
    Code:
    '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
     
    Last edited: Oct 17, 2019
    amykonio likes this.
  10. yiankos1

    yiankos1 Active Member Licensed User

    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.
     
  11. OliverA

    OliverA Expert Licensed User

    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/
     
    Last edited: Oct 17, 2019
    yiankos1 likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice