Sharing database between windows and android

[Olly Hayes]

We've got two applications, a C#.net application running on windows,
and a Java application running on android, but don't seem to be able
to share the same database file between them.

Are we doing something wrong or is this not supporter by SqlCipher?

In both cases we just get a "Not a database or encrypted file" error.

So this happens when creating the db file in windows and trying to
open it on android, and when creating it on android and trying to open
it on windows. If we don't set an encryption password when creating
the file it works correctly in both directions and can be opened on
the other platform.

If this should work we'll spend more time on this I just wanted to
check we weren't trying to achieve the impossible.

Thanks in advance!

[Stephen Lombardo]

Hi Olly,

What you are describing will definitely work. The problem is quite likely due to the difference in SQLCipher major version number of between windows and android. On the windows side, SQLCipher 2.0 includes a format change by default to support HMAC which is not yet present in the Android libraries. 

To fix this problem, on the windows application, execute the following pragma immediately after the database key is set on the database:

PRAGMA cipher_use_hmac = OFF;

That will switch off the HMAC generation / check behavior in SQLCipher 2 and allow you to open, create, and manipulate databases that are compatible with the Android library.

We are going to be working with the team at the Guardian Project to update the Android project to use SQLCipher 2.0 in the coming weeks.

Please let us know if that works for you. Thanks!

Cheers,

Stephen

[Olly Hayes]

Thanks Stephen, I'm afraid I'm a little confused now though..




I had been using the SQLiteConnection.SetPassword("password") before
opening the connection when creating and opening a database. I know
notice that this function was in the old System.Data.Sqlite.dll, so is
this function now obsolete?




This is what I had:
using (SQLiteConnection conn = new
SQLiteConnection(_connectionString))
{
    conn.SetPassword(_password);
    conn.Open();




    if (createNew)
    {
        using (SQLiteCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "CREATE TABLE \"Test\"(\"X\" INTEGER);";
            cmd.ExecuteNonQuery();
        }
    }




    ...
}




I've changed it to:
using (SQLiteConnection conn = new
SQLiteConnection(_connectionString))
{
    conn.Open();




    using (SQLiteCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "PRAGMA key = '@Password'; PRAGMA
cipher_use_hmac = OFF;";
        cmd.Parameters.Add(new SQLiteParameter("@Password",
_password));
        cmd.ExecuteNonQuery();
    }




    if (createNew)
    {
        using (SQLiteCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "CREATE TABLE \"Test\"(\"X\" INTEGER);";
            cmd.ExecuteNonQuery();
        }
    }




    ...
}




Here the database is created correctly, but when I try to open it
again I get the same error, even on the same machine that created it.




File opened that is not a database file
file is encrypted or is not a database
   at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String
strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd,
CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior
behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
   at System.Data.SQLite.SQLiteConnection.Open()

[Stephen Lombardo]

Hi Olly,

Please give this code a try. You can use the SetPassword method, the important thing is to execute the second pragma immediately after. I have verified that this works and the resulting database can be opened with SQLCipher 1.1.x.

If this is still not working, please check the version of System.Data.SQLite that you are referencing in your project. It's very important to ensure that the project is referencing the SQLCipher version of System.Data.SQLite (Version=91.0.77.0, PublicKeyToken=2afdc9349188e576), and that the assembly is set to "copy local" so that it is present in the build directory to be used.

Please let us know if this works for you. Thanks!

  using (SQLiteConnection conn = new SQLiteConnection(_connectionString))

  {

    conn.SetPassword(_password);

    conn.Open();

    using (SQLiteCommand cmd = conn.CreateCommand())

    {

      cmd.CommandText = "PRAGMA cipher_use_hmac = OFF;";

[Olly Hayes]

Thanks, this works in that I can open the created database on the
phone, however now I can't open it on the PC. I'm still getting the
"encrypted or not a database file" error on the
SQLiteConnection.Open().

Presumably this is the same problem as creating a database on android
and transferring it across to a PC. Do I need to tell it it's opening
an old version database before connecting?

[Stephen Lombardo]

Hi Olly,

Thanks for letting us know about the continued issue. I believe this is due to an compatibility issue in the upstream provider implementation. It is manipulating the synchronous pragma from the default, causing the database to be manipulated before the HMAC pragma is called, and thus an exception is being thrown. Can you try to add the following to your database connection string?

Synchronous=Full;

With this set, your connection string might look like this:

"Data Source=sqlcipher.db;Pooling=false;Synchronous=Full;"

Please give this a try and let me know if it works as a temporary fix. In the mean time, we'll investigate this further and see if we can institute a more permanent solution.

For reference here is a complete working implementation that creates, opens, reads a database:

using System.Data;

using System.Data.SQLite;

namespace Example

{

    class Program

    {

        private static string _connectionString = "Data Source=sqlcipher.db;Pooling=false;Synchronous=Full;";

        private static string _password = "test123";

        

        static void Main(string[] args)

        {

            // create the database

            using (var conn = new SQLiteConnection(_connectionString))

            {

                conn.SetPassword(_password);

                conn.Open();

                using (var cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "PRAGMA cipher_use_hmac = OFF;";

                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "CREATE TABLE t1(a,b);";

                    cmd.ExecuteNonQuery();

                    

                    cmd.CommandText = "INSERT INTO t1(a,b) VALUES ('test0', 'test1');";

                    cmd.ExecuteNonQuery();

                }

                conn.Close();

            }

            using (var conn = new SQLiteConnection(_connectionString))

            {

                conn.SetPassword(_password);

                conn.Open();

                using (var cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "PRAGMA cipher_use_hmac = OFF;";

                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "SELECT * FROM t1;";

                    var reader = cmd.ExecuteReader();

                    while (reader.Read())

                    {

                        System.Console.WriteLine(string.Format("{0} | {1}", reader.GetString(0), reader.GetString(1)));

                    }

                }

            }

        }

    }

}

Cheers,

Stephen

[Olly Hayes]

That's great, seems to work perfectly.

Thanks for your help!

On Feb 6, 5:01 pm, Stephen Lombardo <sjlomba...@zetetic.net> wrote:
> Hi Olly,
>