B4J Question SQLite: Password Protected. Possible in B4J? (-> discussion about encrypted DB)

jmon

Well-Known Member
Licensed User
Longtime User
Hi,

I want to protect an sqlite Database. I know sqlCipher is not available for B4J, so I searched for an alternative. I read this answer on StackOverflow:
You can password protect SQLite3 DB. For the first time before doing any operations, set password as follows.
B4X:
SQLiteConnection conn =newSQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
conn.SetPassword("password");
conn.open();
then next time you can access it like
B4X:
conn =newSQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;Password=password;");
conn.Open();
This wont allow any GUI editor to view your data. Some editors can decrypt the DB if you provide the password. The algorithm used is RSA.

Later if you wish to change the password, use
B4X:
conn.ChangePassword("new_password");
To reset or remove password, use
B4X:
conn.ChangePassword(String.Empty);

I was wondering if it's possible to do that with B4J? With a custom DriverClass? Or should I not bother, and just encrypt the sensitive data with jB4XEncryption?

Thanks for your help.
Jmon
 

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
i had a similar experience with SQLITE, yes it is free and awesome, but if you want encryption it will cost you $2000.00 US dollars.

WHUUT!!!!

I had a customer that explicitly requiered an encrypted database and at the end i ended using Access that has in built encryption, free.
the jdbc driver is provided by UcanAccess
 
Upvote 0

jmon

Well-Known Member
Licensed User
Longtime User
the jdbc driver is provided by UcanAccess
Great, I managed to get it working properly, I ran into the same problem you had about the data not being saved, but managed to get over it:
https://www.b4x.com/android/forum/threads/jsql-and-ucanaccess.62909/#post-477390

In this thread:
https://www.b4x.com/android/forum/threads/jsql-and-ucanaccess.62909/
@Erel was saying that it's not a "proper" db engine. Have you ran into any problem with MS Access? I didn't know anything about it before you mentioned it.

Regarding the encryption or security, I see that there are multiple layers of security:
https://msdn.microsoft.com/en-us/library/ms378988(v=sql.110).aspx
This is what I use now:
B4X:
msSQ.Initialize("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess://" & msaccessfile & ";memory=true;password=xxxxx;encrypt=true;")
so that the connection is encrypted, and the database is protected by password. I created my DB using MDBPlus and set the password in it.

Do you know if that is "secure" enough? Is there any full-encryption method like SQLCipher?

[edit]
More about what I'm using this DB for. I want to use it as a local store, for data waiting to be uploaded. A bit like a database replica of an online MySQL DB. Because I will be storing people email locally on the workstation, I need it very secure.
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
It is completly secure!

if it is stolen, it is imposible for someone without the key to open it.

Yet you have to be sure that you are encrypting it and not just password protecting it, because with the password alone, you can see it decompiling the file.

Other observations:
Minimum version: Access 2007
Extension file: Accdb, do not use MDB

If you encrypted it right, you cannot open it with a simply user password relationship, you need jackcessOpener

Let me search for it, and i can share it with you. how do you implement the jackessOpener.

by the way, the jackessopener DO NOT Encrypt the database, it just opens it.
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
how to call it:

B4X:
      SQL1.Initialize2("net.ucanaccess.jdbc.UcanaccessDriver","jdbc:ucanaccess://"&AppDrive&"\DB\Demo.accdb;jackcessOpener=your.package.yourmodule$cryptcodecopener;memory=false;ignorecase=true;COLUMNORDER=DISPLAY;singleconnection=true","","yourPassword")

keys: your.package <- you can find this Project, build configurations, package (it is b4j.example by default)
yourmodule <- you have to add a module containing the below class. (you know java better than me)


B4X:
import java.io.File;
import java.io.IOException;
import net.ucanaccess.jdbc.JackcessOpenerInterface;
import com.healthmarketscience.jackcess.CryptCodecProvider;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import java.sql.SQLException;

public static class cryptcodecopener implements JackcessOpenerInterface {
    public Database open(File fl,String pwd) throws IOException {
       DatabaseBuilder dbd =new DatabaseBuilder(fl);
       dbd.setAutoSync(false);
       dbd.setCodecProvider(new CryptCodecProvider(pwd));
       dbd.setReadOnly(false);
       return dbd.open();
   
  //Notice that the parameter setting autosync =true is recommended with UCanAccess for performance reasons.
  //UCanAccess flushes the updates to disk at transaction end.
  //For more details about autosync parameter (and related tradeoff), see the Jackcess documentation.
  }
}

Requiered libraries (look for upadted:

B4X:
    #AdditionalJar: ucanaccess-3.0.3.1.jar
    #AdditionalJar: hsqldb.jar
    #AdditionalJar: jackcess-2.1.3.jar
    #AdditionalJar: jackcess-encrypt-2.1.1.jar
    #AdditionalJar: commons-logging-1.1.1.jar
    #AdditionalJar: commons-lang-2.6.jar
    #AdditionalJar: bcprov-jdk15on-154.jar

and thats all i believe.
 
Last edited:
Upvote 0

jmon

Well-Known Member
Licensed User
Longtime User
If you find a JDBC driver that supports encryption then it will work.
I found one! http://sqlite-crypt.com/download.htm

I works well, very easy to setup, and the price is very reasonable, $ 128. The link to the JDBC driver gives option to download a demo version of the jdbc. The demo only limits password length of 6 characters [edit: tried with more characters and it works ??], and writes the password in the header of the database (first word visible if DB is opened with notepad).

I tested and it looks simple to implement:
B4X:
#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 400

    #AdditionalJar: sqlite-jdbc-3.13.0.jar 'this is their jdbc
#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form

    Private SQLite As SQL
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
    MainForm.Show

    Dim dbPath As String = File.Combine(File.DirApp, "temp3.db")
    SQLite.Initialize("org.sqlite.JDBC", "jdbc:sqlite:" & dbPath)
    SQLite.ExecNonQuery("PRAGMA key = 'blob';")
    SQLite.ExecNonQuery("PRAGMA lic = '77523-009-0000007-72328';")

    SQLite.ExecNonQuery("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);")
    SQLite.ExecNonQuery2("INSERT INTO test (id, name) VALUES (?, ?);", Array(Rnd(0,9999), "John"))

    Dim crsr As ResultSet = SQLite.ExecQuery("SELECT * FROM test;")
    Do While crsr.NextRow
        Log(crsr.GetString("name"))
 
    Loop
    crsr.Close
End Sub
This will encrypt the DB with the password "blob". I tried opening the db with SQLite Expert Personal and DB Browser For SQLite and they failed at opening it.

I'll test a bit more and maybe buy it.

I wasn't very convinced by MS Access... Quite difficult to setup

[edit] I'm contacting the developers of SQLiteCrypt, I have a few questions to ask them, I'll post the answers in this thread later...
 
Last edited:
Upvote 0

jmon

Well-Known Member
Licensed User
Longtime User
I correct the above post: the demo is limited to 6 characters password, so it will match anything starting with those 6 characters. for example "hello123456789" will be displayed as "hello1 " in the header, and opening the db with the password "hello1" will work.
 
Last edited:
Upvote 0

ivan.tellez

Active Member
Licensed User
Longtime User
i had a similar experience with SQLITE, yes it is free and awesome, but if you want encryption it will cost you $2000.00 US dollars.

WHUUT!!!!

I had a customer that explicitly requiered an encrypted database and at the end i ended using Access that has in built encryption, free.
the jdbc driver is provided by UcanAccess

@Erel, Could you consider licensing The SQLite Encryption Extension (SEE) for the B4X products? Then you could offer a multiplatform database encription and compatibility with other tools already using the "oficial" Extension.
 
Upvote 0

jmon

Well-Known Member
Licensed User
Longtime User
I'll post the answers in this thread later
I got some answers from the developers of SQLiteCrypt.

After paying $128:
- they send the retail version (a different .jar file) within 1 business day
- they send a license number
- password is removed from the header of the file
- no limitation on password length
- they will send a modified version of sqlite.exe. So it's possible to open the database and view the contents
 
Upvote 0
Top