Android Tutorial Android database encryption with SQLCipher library

Status
Not open for further replies.
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:
B4X:
SQL1.Initialize(File.DirRootExternal, "1.db", True, DB_PASSWORD, "")

V1.70
V1.60
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.
 

Attachments

  • SQLCipher.zip
    37.2 KB · Views: 366
Last edited:

coyote

Member
Licensed User
Longtime User
Thanks Erel,

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

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
 

coyote

Member
Licensed User
Longtime User
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

Active Member
Licensed User
Longtime User
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.
 
Last edited:

deantangNYP

Active Member
Licensed User
Longtime User
Hi Erel,

i tried modifying the DBUTILs example code:

Added the following code as recommended:
B4X:
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.
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    14.7 KB · Views: 610
  • Capture2.JPG
    Capture2.JPG
    24.4 KB · Views: 541
Last edited:

aggelos

Member
Licensed User
Longtime User
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?
 

aggelos

Member
Licensed User
Longtime User
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
 

Rusty

Well-Known Member
Licensed User
Longtime User
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:
B4X:
Select * From Table1 where FirstName = "Fred";
and 
Select * from Table1 where FirstName Like "%Fred%";
Thanks,
Rusty
 

tkristensen

Member
Licensed User
Longtime User
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
 

tkristensen

Member
Licensed User
Longtime User
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?
 

tkristensen

Member
Licensed User
Longtime User
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?
 
Status
Not open for further replies.
Top