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

jmon · Jan 13, 2017

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:

B4X:
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

Erel · Jan 13, 2017

SQLite doesn't support encryption without extensions.

If you find a JDBC driver that supports encryption then it will work.

jmon said:
Or should I not bother, and just encrypt the sensitive data with jB4XEncryption?

This is possible. You need to save the data in BLOB fields.

EnriqueGonzalez · Jan 13, 2017

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

jmon · Jan 13, 2017

Cool thank you for the info

jmon · Jan 13, 2017

Enrique Gonzalez R said:
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.

EnriqueGonzalez · Jan 13, 2017

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.

EnriqueGonzalez · Jan 13, 2017

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.

jmon · Jan 18, 2017

Erel said:
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...

jmon · Jan 18, 2017

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.

ivan.tellez · Feb 3, 2017

Enrique Gonzalez R said:
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.

jmon · Feb 3, 2017

jmon said:
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

ivan.tellez · Feb 3, 2017

jmon said:
I got some answers from the developers of SQLiteCrypt.
After paying $128:

"Current price for one platform binary is US$128. "

$380 for the Source code license: same source code works in all platforms.

jmon · Feb 3, 2017

I'm not sure I understand the difference. Does that mean that 128 us is for only one program?

giga · Feb 4, 2017

Good find! @jmon

ivan.tellez · Feb 7, 2017

jmon said:
Does that mean that 128 us is for only one program?

Any number of apps, but $128 for Android, $128 For windows, $128 For ios and so on. Or $380 for All of them.

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

jmon

Well-Known Member

Erel

B4X founder

EnriqueGonzalez

Well-Known Member

jmon

Well-Known Member

jmon

Well-Known Member

EnriqueGonzalez

Well-Known Member

EnriqueGonzalez

Well-Known Member

jmon

Well-Known Member

jmon

Well-Known Member

ivan.tellez

Active Member

jmon

Well-Known Member

ivan.tellez

Active Member

jmon

Well-Known Member

giga

Well-Known Member

ivan.tellez

Active Member