Android Tutorial Android database encryption with SQLCipher library

SQLCipher is an open source project that extends SQLite and adds full database encryption.
License: https://www.zetetic.net/sqlcipher/open-source/

B4A SQLCipher is a special subtype of SQL object. There is almost no need to change any code in order to switch from regular SQL to SQLCipher.

The only difference between SQL API and SQLCipher API is the Initialize method.
SQLCipher.Initialize expects two additional values: Password and a second parameter that is not used (it was used in the past).

Password is the database password. You can pass an empty string if there is no password. Note that it is not possible to change the password (or set a new password) to an existing database.

Code changes required to convert from SQL to SQLCipher
- Declare the SQL object as SQLCipher.
- Change the initialize code to:

SQL1.Initialize(File.DirRootExternal, "1.db", True, DB_PASSWORD, "")

V1.70

• Based on SQLCipher v4.5.4: https://www.zetetic.net/blog/2023/04/27/sqlcipher-4.5.4-release/

V1.60

• Based on SQLCipher v4.00: https://www.zetetic.net/blog/2018/11/30/sqlcipher-400-release/
• Includes 64 bit binaries and emulator binaries.
• Requires B4A v8+.
• Library is attached.
• Not compatible by default with databases created with older versions. See this link: https://www.zetetic.net/blog/2018/11/30/sqlcipher-400-release/#compatibility

V1.50

• Based on SQLCipher v3.59
• Supports targetSdkVersion 26.
• The icu.zip file is no longer required. You can delete it from the Files folder.
• It is no longer required to disable the debugger virtual assets feature.
  Remove this line: #DebuggerForceStandardAssets: True
• Old version: www.b4x.com/android/files/SQLCipher150.zip

Depends on: https://repo1.maven.org/maven2/net/...er/4.5.4/android-database-sqlcipher-4.5.4.aar
You should download and copy it to the additional libraries folder.

 

[Erel]

That was a difficult one.

SQLCipher native library depends on Guava library: guava-libraries - Guava: Google Core Libraries for Java 1.6+ - Google Project Hosting

This library includes this class: TldPatterns.java - guava-libraries - Guava: Google Core Libraries for Java 1.6+ - Google Project Hosting

 

[coyote]

Thanks Erel,

this was a reassuring reply.
But what are these TLD for, exactly? I could't unterstand it.

Wouldn't it be better to compile these files without the Domains?
I mean, when a customer ask me: "What the heck is that?"
I can not give him a satisfied answer und he's assuming that
the app steals his data. Based of .ru, .pl, ... Domains.

Do you know what I mean?

With regards, Coyote

 

[Erel]

This class is not really used. It is part of a utility library (Guava) that the open source project uses.

I haven't modified the open source project. I just wrapped it. If you like you can remove this class from Guava.jar.

 

[coyote]

Hi Erel,

I removed this class with zip -d ... and will try it in the next days.
I hope it will work as assumed.

Thanks, Coyote

 

[deantangNYP]

Hi Erel, i am not sure where to place the files as u described in post#1.
please advise. thanks.

1) Where to add "Add sqlcipher_native.zip"? where to set change the last parameter (NativeLibsFolder) to "File.DirAssets"? Do i need to unzip? Step 3 below.

Installation instructions:
- Unzip the file.
- Copy all files from the InternalLibrariesFolder to the internal libraries folder.
- Add sqlcipher_native.zip from the NativeResources folder to your project as described above.
- You should reference both SQL and SQLCipher in your project.

You should include the zip file in the Files tab and change the last parameter (NativeLibsFolder) to File.DirAssets.

 

[Erel]

You do not need to unzip it. Just add it to the Files tab.

 

[deantangNYP]

Hi Erel,

i tried modifying the DBUTILs example code:

Added the following code as recommended:

If FirstTime Then
   'SQL.Initialize(File.DirRootExternal, "1.db", True)
   ProgressDialogShow("Initializing database...")
   SQL1.Initialize(File.DirRootExternal, "1.db", True, DB_PASSWORD, File.DirRootExternal)
   ProgressDialogHide
End If

Added the "sqlcipher_native.zip" to Files Tab:
Capture1.JPG

But got an ERROR, May i know what did i do wrong?
Capture2.JPG

Can you share an example code of the DBUTIL with sqlcipher? pls.
Please advise. Thanks.

You do not need to unzip it. Just add it to the Files tab.

 

[Erel]

Note that you can copy the error from the logs (it includes more information) by right clicking on the logs.

Make sure that you are using the same SQL object in all your code.

 

[aggelos]

Hello

just a small problem.

app starts:
DB_PASSWORD="123"

sql1.IsInitialized is False

I create a database with
sql1.Initialize(File.DirDefaultExternal, "main3.db", True, DB_PASSWORD, File.DirAssets)
sql1.ExecNonQuery("CREATE TABLE table (col1 TEXT , col2 INTEGER, col3 INTEGER)")

sql1.IsInitialized is now true
everything is ok.


second run:

DB_PASSWORD="1234567890"

i connect to the same database
sql1.Initialize(File.DirDefaultExternal, "main3.db", True, DB_PASSWORD, File.DirAssets)

sql1.IsInitialized is now true (????)

i execute
sql1.ExecNonQuery("CREATE TABLE anothertable (col1 TEXT , col2 INTEGER, col3 INTEGER)")

app crashes.


Is sql1.IsInitialized supposed to turn true when the password is incorrect?

 

[Erel]

You can execute a "dummy" statement after you open the database and use Try / Catch block to catch the error (and show a message to the user).

 

[aggelos]

Hello

I wonder if its useful and needed to have both armeabi and x86 libs(of sqlcipher) in the apk.
i noticed "the problem" when i ran my app in the emulator using intel atom image.

I've read about some phones/tablets using Intel cpus.and Intel has an sdk
for developing on android.


too soon to worry ?

If needed i am thinking of having 2 .zips in different directories and choose what path to set in sql1.initialize
after reading the cpuinfo.any better/simpler ideas?


Angelos

 

[Erel]

According to this thread it might work with the arm library: How does native android code written for ARM run on x86? - Stack Overflow

Another option is to rebuild SQLCipher library and include both libraries. The downside is that the size of this library will be larger.

 

[Rusty]

When you use SQLCipher, will you be able to issue SQL queries against the database using un-encrypted select statement parameters/strings?

For example, against a fully encrypted database issue:

Select * From Table1 where FirstName = "Fred";
and 
Select * from Table1 where FirstName Like "%Fred%";

Thanks,
Rusty

 

[Erel]

Yes. Once the database is opened it behaves exactly like an unencrypted database.

 

[Rusty]

Cool!
Thanks Erel!

 

[tkristensen]

Erel,

We're currently testing sqlcipher functionality.

We created a database using the .net version of the library and can read the database correctly within .net.

We used SQLiteViewer as a base for creating an android test app. With the following modifications.

Dim SQL As SQLCipher at the top of the project replace DIM SQL as SQL

I use the following to init the database:
SQL.Initialize(Dir, FileName, False, "test123", File.DirAssets )

I've verified that I have the sqlcipher_native.zip added to my files.

The program will read an unencrypted database using
SQL.Initialize(Dir, FileName, False, "", File.DirAssets )

but will not read an encrypted database with:
SQL.Initialize(Dir, FileName, False, "test123", File.DirAssets )

The password is the same in both programs.

Any help appreciated

Tom

 

[Erel]

Have you tried to create a new password protected database in the Android app?

 

[tkristensen]

Yes. The database created by the android the android can read, but our windows app cannot.

Basically each side (windows and android) can create an sqlcipher database with the same password and can read it's own creation, but cannot read the database created by the other side.

Here is the android code I'm using...

Dim dir As String = "/mnt/sdcard/survey/data"
Dim filename As String = "sqlcipher.db3"
SQL.Initialize(dir, filename, True, "test123", File.DirAssets )
SQL.ExecNonQuery ("CREATE TABLE t1(a,b);")
SQL.ExecNonQuery ("INSERT INTO t1(a,b) VALUES ('test0', 'test1');" )
SQL.Close

SQL.Initialize(dir, filename, True, "test123", File.DirAssets )
Dim cursor1 As Cursor = SQL.ExecQuery("SELECT * FROM t1;" )
If cursor1.RowCount > 0 Then
cursor1.Position = 0
Msgbox(cursor1.GetString("a"),"title")
End If

On the PC side we're using the following code:

Private Shared _connectionString As String = "Data Source=c:\share\sqlcipher.db3;Pooling=false;Synchronous=Full;"
Private Shared _password As String = "test123"

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

Using conn = New SQLiteConnection(_connectionString)
conn.SetPassword(_password)
conn.Open()

Using cmd = conn.CreateCommand()
cmd.CommandText = "CREATE TABLE t1(a,b);"
cmd.ExecuteNonQuery()

cmd.CommandText = "INSERT INTO t1(a,b) VALUES ('test0', 'test1');"
cmd.ExecuteNonQuery()
End Using
conn.Close()
End Using

Using conn = New SQLiteConnection(_connectionString)
conn.SetPassword(_password)
conn.Open()

Using cmd = conn.CreateCommand()
cmd.CommandText = "SELECT * FROM t1;"
Try
Dim reader = cmd.ExecuteReader()
While reader.Read()
Debug.Print(String.Format("{0} | {1}", reader.GetString(0), reader.GetString(1)))
End While
Catch ex As Exception
MsgBox(ex.Message)
End Try


End Using
conn.Close()


End Using


End Sub

The windows side is using version 91.0.80.0 of sqlcipher library.

It looks like both sides work, but are not compatible with each other. Is there a certain version of the sqlcipher libs that we should be using to be compatible with the android version?

 

[Erel]

I'm not sure. The latest Android SQL Cipher is 2.1.1. The current library uses an older version. Though it should be simple to update it.

You may want to post this question in their Google group: https://groups.google.com/forum/?fromgroups#!forum/sqlcipher

 

[tkristensen]

I've gone through the messages in their google groups and it seems there are issues reading between programs built with their version 1 series and their version 2.

Would it be possible to get a b4a library updated to their 2.1.1 version?