Encripting plaintext sqlite database in xamarin ios app

SQLCipher
1

Hi Guys,
i am encripting plaintext sqlite database file that is kept in resources folder but unable to open it with sqlcipher.

code snap:
using (
SQLiteConnection connection = new SQLiteConnection(new SQLitePlatformIOS(""), “ceodb.temp.db”)) {
connection.Query(“ATTACH DATABASE ‘ceodb.db’ AS encrypted KEY ‘rk6907’;”);
connection.Query(“SELECT sqlcipher_export(‘ceodb’);”);
connection.Query(“DETACH DATABASE ceodb;”);
}

This ceodb.temp.db is kept in resources folder. Am i doing encription in a wrong way.
Hope you guys would help.

2

Hello @rk6907 - you should be constructing your database paths relative to the documents or resources folders, as shown in the example here:

var documentsPath = Environment.GetFolderPath (Environment.SpecialFolder.Personal);
var libraryPath = Path.Combine (documentsPath, "..", "Library");
var databasePath = Path.Combine(libraryPath, "ceodb.db");

You should be able to get the path to the file in the resource bundle using PathForResource:

var tempDatabasePath = NSBundle.MainBundle.PathForResource ("ceodb.temp", "db");

Please try to use the correct paths, and then let us know if it works.

3

Hi sjlombardo,
Thanks for your time but seems issue is not because of paths.
Here is the complete code.
ProjectSummary table is there in coedb.temp but as i use “encrypted.db”, it returns exception as “There is no project summary table”.
Return values of varibles are shown in comments.

var data = “”;
var password = “rk7096”;
var documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
var libraryPath = Path.Combine(documentsPath, “…”, “Library”);
var databasePath = Path.Combine(libraryPath, “encrypted.db”);

        File.Delete(databasePath);
       
        var tempDatabasePath = NSBundle.MainBundle.PathForResource("ceodb.temp", "db");

        using (             
        SQLiteConnection conn= new SQLiteConnection(new SQLitePlatformIOS(""), tempDatabasePath)) {

//Returned attachRows=0
var attachRows= conn.Execute(“ATTACH DATABASE ‘encrypted.db’ AS encrypted KEY ‘testkey’;”);
//Returned scalar=null
var scalar=conn.ExecuteScalar(“SELECT sqlcipher_export(‘encrypted’);”);
//Returned detach=0
var detach=conn.Execute(“DETACH DATABASE encrypted;”);
//Returned rekey=0
var rekey= conn.Execute(“PRAGMA rekey = ‘rk7096’;”);
}

using (var conn = new SQLiteConnection(new SQLitePlatformIOS(password), databasePath))
{
var table = conn.Table();
foreach (var item in table)
{
/// failed !! Exception is produced. No ProjectSummaryTable.
data = item.CustomerName+" "+ item.CustomerCode;
}

        }

Also return values of attach and detach commands are 0 and that of export command is null.
Kindly suggest.

4

@rk6907 - You need to pass the full path to the database file to the ATTACH command. Also, you shoul dnot be using there is no reason to use PRAGMA rekey. You should instead pass ‘rk7096’ on the ATTACH command instead of ‘testkey’.

5

Hi sjlombardo,
Thanks for your time again.
I followed as you said but this time export command throws exception as "SQLite.Net.SQLiteException: unknown database encryptedNew"
even though this database is there in path.
Am i still missing something.

using (var conn = new SQLiteConnection(new SQLitePlatformIOS(“12345”), “encryptedNew.db”))
{
}
var data = “”;
var password = “12345”;
var documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
var libraryPath = Path.Combine(documentsPath, “…”, “Library”);
var databasePath = Path.Combine(libraryPath, “encryptedNew.db”);

    var tempDatabasePath = NSBundle.MainBundle.PathForResource("ceodb.temp", "db");

    using (             
    SQLiteConnection conn= new SQLiteConnection(new SQLitePlatformIOS(""), tempDatabasePath)) {

//Returned attachRows=0
var attachRows= conn.Execute(“ATTACH DATABASE ? as encrypted KEY ?;”, databasePath, “12345”);
//Returned Exception
string scalar = conn.ExecuteScalar(“SELECT sqlcipher_export(‘encryptedNew’);”);

var detach=conn.Execute(“DETACH DATABASE encryptedNew;”);
}

using (var conn = new SQLiteConnection(new SQLitePlatformIOS(password), databasePath))
{
var table = conn.Table();
foreach (var item in table)
{
data = item.CustomerName+" "+ item.CustomerCode;
}
}

6

I got my error.
Thanks sjlombardo.
In export and detach commands it should be “encrypted” only.