"File is not a database" 3.4.2 to 4.2.0

SQLCipher
1

I have an application which is currently using SQLCipher for Xamarin Android v3.4.2 which I am attempting to update to 4.2.0.

Instead of using Mono.Data.Sqlcipher.SqliteConnection I am using Microsoft.Data.Sqlite.SqliteConnection.

Everything works fine except trying to access an existing database.

In the 3.4.2 version I use SqliteConnection.SetPassword() with a byte array.

Using 4.2.0 I’m trying to use PRAGMA key but I can’t get this to work. I end up with “SQLite Error 26: ‘file is not a database’”

e.g.

PRAGMA key = x’5D852DAC2953A0E37802A87EECDE99B91AA07CDA571E52B942D6D05C22CEE766"

followed by

PRAGMA cipher_default_compatibility = 3

I’m assuming I’m constructing the x’’ blob incorrectly from the byte but I can’t figure this out.

PRAGMA cipher_version returns “4.2.0 zetetic”

Thanks
Simon

2

Hello @simonwipf

You will likely want to call pragma cipher_compatibility = 3 instead as it applies to the current connection, whereas cipher_default_compatibility is for future connections created. Alternatively, you could also perform a one time migration of the database file using pragma cipher_migrate.

3

Aplogies. Copy/paste error. I am calling cipher_compatibility.

4

I’ve adjusted the way we call the pragmas on startup and added some logging.

DbSecurity: Trace: DbSecurity.ApplyPragmas() Applying 6 pragmas
DbSecurity: Trace: PRAGMA key = “x’5D852DAC2953A0E37802A87EECDE99B91AA07CDA571E52B942D6D05C22CEE766’”;
DbSecurity: Trace: PRAGMA cipher_compatibility = 3;
DbSecurity: Trace: PRAGMA foreign_keys = on;
DbSecurity: Trace: PRAGMA journal_mode = WAL;
DbSecurity: Trace: DbSecurity.ApplyPragmas() Failed: SQLite Error 26: ‘file is not a database’.

So the error is thrown setting journal_mode. I’ve tried cipher_migrate instead of cipher_compatibility with no success, so I’m sure it must be the way I’ve constructed the literal from the byte array:

BitConverter.ToString(key).Replace("-", “”)

5

Hello @simonwipf - I suspect the problem here is that you were not previously using raw key syntax, which has a specific meaning to SQLCipher, and were instead just passing an arbitrary byte array to SetPassword. Could you tell me a bit more about how the value of key is generated in your application, and what it contains? Is it purely random, the byte[] representation of a UTF-8 string, or something else? The answer to this question will help us advise on how to proceed here.

6

@simonwipf - could you perhaps also provide a raw code snippet for how you were initializing the database, vs how you are doing it now, in plain text?

7

Two snippets below. I ran the first version to create the database and the second to test after updating to 4.2.0. This is analogous to our production code.

Before (reference to sqlcipher-xamarin-android 3.4.2)

        try
        {
            var key = new byte[] { 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1A, 0x1B, 0x1C, 0x1D, 0x1E, 0x1F,
                                   0x20, 0x21, 0x22, 0x23, 0x24, 0x25, 0x26, 0x27, 0x28, 0x29, 0x2A, 0x2B, 0x2C, 0x2D, 0x2E, 0x2F };

            var filename = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "test.sqlcipher");

            using (var c = new Mono.Data.Sqlcipher.SqliteConnection($"data source={filename}"))
            {
                c.SetPassword(key);
                c.Open();

                using (var cmd = c.CreateCommand())
                {
                    cmd.CommandText = "PRAGMA cipher_version;";
                    var cipherVersion = cmd.ExecuteScalar() as string;

                    cmd.CommandText = "create table test ( forename text );"; cmd.ExecuteNonQuery();
                    cmd.CommandText = "insert into test ( forename ) values ( 'simon' );"; cmd.ExecuteNonQuery();

                    cmd.CommandText = "select count(*) rows from test";
                    var rows = cmd.ExecuteScalar() as long?;
                }
            }
        }
        catch (Exception e)
        {
            var message = e.Message;
        }

After: (references to zetetic-sqlcipher-android 4.2.0 and Microsoft.Data.Sqlite 2.2.6)

        try
        {
            var key = new byte[] { 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1A, 0x1B, 0x1C, 0x1D, 0x1E, 0x1F,
                                   0x20, 0x21, 0x22, 0x23, 0x24, 0x25, 0x26, 0x27, 0x28, 0x29, 0x2A, 0x2B, 0x2C, 0x2D, 0x2E, 0x2F };

            var filename = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "test.sqlcipher");
            
            using (var c = new Microsoft.Data.Sqlite.SqliteConnection($"data source={filename}"))
            {
                c.Open();

                using (var cmd = c.CreateCommand())
                {
                    var blob = BitConverter.ToString(key).Replace("-", "");
                    cmd.CommandText = $"PRAGMA key = \"x'{blob}'\";"; cmd.ExecuteNonQuery();
                    cmd.CommandText = "PRAGMA cipher_compatibility = 3;"; cmd.ExecuteNonQuery();                        

                    cmd.CommandText = "PRAGMA cipher_version;";
                    var cipherVersion = cmd.ExecuteScalar() as string;

                    cmd.CommandText = "select count(*) rows from test";
                    var rows = cmd.ExecuteScalar() as long?;
                }
            }
        }
        catch (Exception e)
        {
            var message = e.Message;
        }

Edit: The keys are randomly generated:

        using (var rngCryptoServiceProvider = new System.Security.Cryptography.RNGCryptoServiceProvider())
        {
            var randomBytes = new byte[32];
            rngCryptoServiceProvider.GetBytes(randomBytes);
            return randomBytes;
        }

Edit 2: I think you’re right about the key. I used “PRAGMA rekey” at the end of the first version and the second version can now use the database. Obviously the first version using SetPassword() no longer works after doing this.

8

Hi @simonwipf

Would you try setting your password this way instead:

var password = Encoding.UTF8.GetString(key);
cmd.CommandText = $"PRAGMA key = '{password}';";
cmd.ExecuteScalar();
9

That didn’t work as “password” contains values which cause the “PRAGMA key” statement to throw a syntax error.

I have a solution I now I think. If I reference both the 3.4.2 and 4.2.0 NuGet packages I can do the following: SetPassword() and Open() using 3.4.2 followed by a rekey using blob notation. After that I sanity test that I can use SetPassword() with the blob string. From that point on I can use PRAGMA key with 4.2.0.

Can you envisage any conflict problems with this dual NuGet approach, other than a little APK bloat?

var newkey = $"x'{BitConverter.ToString(key).Replace("-", "")}'";

using (var c = new Mono.Data.Sqlcipher.SqliteConnection($"data source={filename}"))
{
    var sqliteErrorCode = Mono.Data.Sqlcipher.SQLiteErrorCode.Ok;

    try
    {
        c.SetPassword(key);
        c.Open();
        var cmd = c.CreateCommand();
        cmd.CommandText = $"PRAGMA rekey = \"{newkey}\";";
        cmd.ExecuteNonQuery();
        c.Close();
    }
    catch (Mono.Data.Sqlcipher.SqliteException se)
    {
        sqliteErrorCode = se.ErrorCode;
    }

    if (sqliteErrorCode == Mono.Data.Sqlcipher.SQLiteErrorCode.NotADatabase)
    {
        // sanity check that the error is because we have already used rekey
        // so the Open() below should work ok
        c.SetPassword(newkey);
        c.Open();
        c.Close();
    }
}
10

Answering my own question, the above wasn’t quite doing what I expected - PRAGMA cipher_version returned 3.4.2 on both connections.

11

Hello @simonwipf

Would you try this instead with only the SQLCipher 4.2.0 library:

var key = $"x'{BitConverter.ToString(key).Replace("-", "")}'";
cmd.CommandText = $"PRAGMA hexkey = '{key}';";
cmd.ExecuteScalar();
12

Thank you! I’d only been looking through the SQLCipher pragmas and not the base SQLite ones. minor tweak to the above for the correct syntax for hexkey, but now working!

var hexkey = BitConverter.ToString(key).Replace("-", "");
cmd.CommandText = $"PRAGMA hexkey = '{hexkey}';";
cmd.ExecuteScalar();
13

Hi @simonwipf

Excellent, we are happy to hear that worked for you!