Migration to SQLCipher 3.0.0 on Android

[Werner Dittmann]

SQLCipher changes the KDF and thus a migration is necessary. 

The documentation describes how to do this using command line tools. My question is how to do it without command line tools? On Android a user does not have the command line tools available.

My Android application uses the 'net.sqlcipher.database.SQLiteOpenHelper' class to manage the SQLCipher database.

Because I can use 'PRAGMA' on an open database only I wonder if the following sequence is correct:

1. open an existing DB with the password (getWritableDatabase("YourKeyGoesHere") ), thus using the old settings (KDF iteration count)
   
2. then use the PRAGMA statements on the open database to perform the migration:

     PRAGMA key = 'YourKeyGoesHere';

     PRAGMA cipher_migrate;

using something like 

   

    db.rawExecSQL("PRAGMA key = 'YourKeyGoesHere'");

    db.rawExecSQL("PRAGMA cipher_migrate");

Assuming the sequence is somewhat correct: 

if the application successfully opened the database (step 1 above) is it then necessary to execute the first PRAGMA before it executes the second one (cipher_migrate)?

Next questions: 

Can I check if the database was migrated (cipher_migrate) to skip the migration or can I perform the migration every time and SQLCipher just skips it if already done? Or is 'onUpgrade(...) the right way (incrementning the DB version of course :-) ? This would imply that step 1 above works and SQLiteOpenHelper calls onUpgrade correctly.

Thanks,

Werner

[Nick Parker]

Hi Werner,

Thanks for taking a look at the new SQLCipher for Android beta release.  To invoke cipher_migrate from SQLCipher for Android, one must use the SQLiteDatabaseHook which provides a means to properly execute the statement directly following the keying.  An example is here [1].  Note that this currently precludes SQLiteOpenHelper as it does not accept a SQLiteDatabaseHook as a constructor argument at the moment.

Executing "PRAGMA cipher_migrate;" will return a status code of which 0 (i.e., SQLITE_OK) is success.  I just committed a change yesterday that will allow you to retrieve the status code like this:

Cursor cursor = db.rawQuery("PRAGMA cipher_migrate;", new String[]{});

The routine [2] will check to see if it is able to properly open the database first, without any configuration changes.  If that is successful, it will simply exit the routine and no upgrades are performed.  The onUpgrade function of the SQLiteOpenHelper is more focused on schema/data changes to the database.  I would suggest running the cipher_migrate pragma before you initiate the SQLiteOpenHelper process within your application.  Let us know if you have any further questions.  Thanks!

1.  https://github.com/sqlcipher/sqlcipher-android-tests/blob/prerelease/src/main/java/net/zetetic/tests/MigrateDatabaseFrom1xFormatToCurrentFormat.java
2.  https://github.com/sqlcipher/sqlcipher/blob/prerelease/src/crypto_impl.c#L948

--
 
---
You received this message because you are subscribed to the Google Groups "SQLCipher Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlcipher+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

-- 
Nick Parker

[Werner Dittmann]

Nick,

I think most Android developers who use SQLCipher are not happy with this because it contradicts the proposed way to use SQLite (and SQLCipher). The "hook" solution may work (see below). I don't see how I can access the function shown in "routine 2" from Java. 

Based on what I understand an Android programmer needs to do something like that:

SQLiteDatabaseHook hook = new SQLiteDatabaseHook() {
    public void preKey(SQLiteDatabase database) {}
    public void postKey(SQLiteDatabase database) {
         database.rawExecSQL("PRAGMA cipher_migrate;");
     }
};
SQLiteDatabase source = SQLiteDatabase.openOrCreateDatabase(sourceDatabase, password, null, hook);
source.close();

and only after that create the class that extends SQLiteOpenHelper. Does this look OK :-) ?

I don't see yet how the solution with the 'hook' works if I open the database a second time with the hook enabled. Shall the application "remember" that it migrated the database and don't use the hook again?

Werner

[Nick Parker]

Hi Werner,

Can you elaborate a bit on how this deviates from SQLite and/or
SQLCipher usage specifically - I'm not sure I follow?

Yes, these would be the steps to migrate the database file format to the
latest configuration. In the current prerelease branch (but not the
current binaries) you will also be able to return a Cursor for the
database.rawQuery("PRAGMA cipher_migrate", new String[]{}) call to
verify the results of the migration.

The hook only needs to be run once, as it will perform an export of your
database and then swap the exported btree back into place at runtime.
Subsequent connections, such as your application usage of a
SQLiteOpenHelper subclass will then work. If the database is not
migrated, using the new binaries you would need to use the hook to
adjust the kdf iterations, otherwise access to the database would fail.
If you database does not exist on the device, there is no need to run
cipher_migrate.

What function are you trying to access that is unavailable?

>> 1. open an existing DB with the password

>> (getWritableDatabase("YourKeyGoesHere") ), thus using the old
>> settings (KDF iteration count)

>> 2. then use the PRAGMA statements on the open database to perform

>> the migration:
>>
>> PRAGMA key = 'YourKeyGoesHere';
>> PRAGMA cipher_migrate;
>>
>> using something like
>>
>> db.rawExecSQL("PRAGMA key = 'YourKeyGoesHere'");
>> db.rawExecSQL("PRAGMA cipher_migrate");
>>
>> Assuming the sequence is somewhat correct:
>> if the application successfully opened the database (step 1 above)
>> is it then necessary to execute the first PRAGMA before it
>> executes the second one (cipher_migrate)?
>>
>> Next questions:
>> Can I check if the database was migrated (cipher_migrate) to skip
>> the migration or can I perform the migration every time and
>> SQLCipher just skips it if already done? Or is 'onUpgrade(...) the
>> right way (incrementning the DB version of course :-) ? This would
>> imply that step 1 above works and SQLiteOpenHelper calls onUpgrade
>> correctly.
>>
>> Thanks,
>>
>> Werner
>> --
>>
>> ---
>> You received this message because you are subscribed to the Google
>> Groups "SQLCipher Users" group.
>> To unsubscribe from this group and stop receiving emails from it,

>> send an email to sqlcipher+...@googlegroups.com <javascript:>.

>> For more options, visit https://groups.google.com/groups/opt_out

>> <https://groups.google.com/groups/opt_out>.
>
> --
> Nick Parker

[Mark Murphy]

On Thu, Oct 17, 2013 at 9:47 AM, Nick Parker <npa...@zetetic.net> wrote:
> To
> invoke cipher_migrate from SQLCipher for Android, one must use the
> SQLiteDatabaseHook which provides a means to properly execute the statement

> directly following the keying... Note that this

> currently precludes SQLiteOpenHelper as it does not accept a
> SQLiteDatabaseHook as a constructor argument at the moment.

The SQLCipher for Android version of SQLiteOpenHelper should be
applying this automatically then. After all:

> The routine [2] will check to see if it is able to properly open the
> database first, without any configuration changes. If that is successful,
> it will simply exit the routine and no upgrades are performed.

Hence, since invoking this PRAGMA is a no-op for an already-migrated
database, there should be no harm in SQLCipher for Android's
SQLiteOpenHelper invoking this PRAGMA for us. Right?

Having the SQLiteDatabaseHook for those who eschew SQLiteOpenHelper is
perfectly reasonable for lower-level implementations.

--
Mark Murphy (a Commons Guy)
http://commonsware.com | http://github.com/commonsguy
http://commonsware.com/blog | http://twitter.com/commonsguy

_The Busy Coder's Guide to Android Development_: 2,500+ Pages, Updated
Frequently!

[Nick Parker]

Hi Mark,

On 10/18/13 9:38 AM, Mark Murphy wrote:
> On Thu, Oct 17, 2013 at 9:47 AM, Nick Parker <npa...@zetetic.net> wrote:
>> To
>> invoke cipher_migrate from SQLCipher for Android, one must use the
>> SQLiteDatabaseHook which provides a means to properly execute the statement
>> directly following the keying... Note that this
>> currently precludes SQLiteOpenHelper as it does not accept a
>> SQLiteDatabaseHook as a constructor argument at the moment.
>
> The SQLCipher for Android version of SQLiteOpenHelper should be
> applying this automatically then. After all:

SQLiteOpenHelper does not currently support the SQLiteDatabaseHook.

>
>> The routine [2] will check to see if it is able to properly open the
>> database first, without any configuration changes. If that is successful,
>> it will simply exit the routine and no upgrades are performed.
>
> Hence, since invoking this PRAGMA is a no-op for an already-migrated
> database, there should be no harm in SQLCipher for Android's
> SQLiteOpenHelper invoking this PRAGMA for us. Right?

It is not exactly a no-op, it attempts to key the database and perform a
read, which result in PKCS5_PBKDF2_HMAC_SHA1 being called with an
iteration of 64000, this would be a wasted effort if the database had
already been migrated, or if the database were created with a kdf set to
64000.

>
> Having the SQLiteDatabaseHook for those who eschew SQLiteOpenHelper is
> perfectly reasonable for lower-level implementations.
>

--

Nick Parker

[Mark Murphy]

On Fri, Oct 18, 2013 at 10:49 AM, Nick Parker <npa...@zetetic.net> wrote:
>> The SQLCipher for Android version of SQLiteOpenHelper should be
>> applying this automatically then. After all:
>
> SQLiteOpenHelper does not currently support the SQLiteDatabaseHook.

It is your implementation of SQLiteOpenHelper:

https://github.com/sqlcipher/android-database-sqlcipher/blob/master/src/net/sqlcipher/database/SQLiteOpenHelper.java

It would seem that you could modify your implementation of
SQLiteOpenHelper to apply your own internal SQLiteDatabaseHook, the
same way you are expecting us to do (see line 107).

>> Hence, since invoking this PRAGMA is a no-op for an already-migrated
>> database, there should be no harm in SQLCipher for Android's
>> SQLiteOpenHelper invoking this PRAGMA for us. Right?
>
> It is not exactly a no-op, it attempts to key the database and perform a
> read, which result in PKCS5_PBKDF2_HMAC_SHA1 being called with an
> iteration of 64000, this would be a wasted effort if the database had
> already been migrated, or if the database were created with a kdf set to
> 64000.

Yes, it is wasted work. Considering that an app usually opens a
database once per process invocation, unless the overhead is such that
it is likely to be noticed by the user, I doubt that it will be a big
problem, assuming that the execution time for this logic is measured
in milliseconds.

[Nick Parker]

Hi Mark,

On 10/18/13 10:01 AM, Mark Murphy wrote:
> On Fri, Oct 18, 2013 at 10:49 AM, Nick Parker <npa...@zetetic.net> wrote:
>>> The SQLCipher for Android version of SQLiteOpenHelper should be
>>> applying this automatically then. After all:
>>
>> SQLiteOpenHelper does not currently support the SQLiteDatabaseHook.
>
> It is your implementation of SQLiteOpenHelper:
>
> https://github.com/sqlcipher/android-database-sqlcipher/blob/master/src/net/sqlcipher/database/SQLiteOpenHelper.java

What I meant is that the SQLiteOpenHelper does not currently accept a
SQLiteDatabaseHook, but 107 is where it would be passed through, yes.

>
> It would seem that you could modify your implementation of
> SQLiteOpenHelper to apply your own internal SQLiteDatabaseHook, the
> same way you are expecting us to do (see line 107).
>
>>> Hence, since invoking this PRAGMA is a no-op for an already-migrated
>>> database, there should be no harm in SQLCipher for Android's
>>> SQLiteOpenHelper invoking this PRAGMA for us. Right?
>>
>> It is not exactly a no-op, it attempts to key the database and perform a
>> read, which result in PKCS5_PBKDF2_HMAC_SHA1 being called with an
>> iteration of 64000, this would be a wasted effort if the database had
>> already been migrated, or if the database were created with a kdf set to
>> 64000.
>
> Yes, it is wasted work. Considering that an app usually opens a
> database once per process invocation, unless the overhead is such that
> it is likely to be noticed by the user, I doubt that it will be a big
> problem, assuming that the execution time for this logic is measured
> in milliseconds.
>

The operation is not defined in time, but iterations and because
SQLCipher for Android has a broad range of Android platform support we
will not include a change into the default client library that would
slow it up by always executing cipher_migrate, as it only needs to be
executed once.

--
Nick Parker

[William Gray]

unless the overhead is such that
it is likely to be noticed by the user, I doubt that it will be a big
problem, assuming that the execution time for this logic is measured
in milliseconds.

Just so you're aware, on certain hardware and crypto implementations, e.g. armv6 using OpenSSL, those 64,000 operations can be very expensive. Doing it on an iPhone 3G without hardware acceleration was pretty much a non-starter. Not that we support armv6 anymore, just suggesting that you'll be better off using a migration flag of some sort to avoid performing this check if the upgrade has already been run. Also, your app will simply consume less power by checking a flag and only calling cipher_migrate if the upgraded flag is not set.

Cheers,

B! 

--

---
You received this message because you are subscribed to the Google Groups "SQLCipher Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlcipher+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Team Zetetic
http://zetetic.net

[Mark Murphy]

On Fri, Oct 18, 2013 at 11:24 AM, William Gray <wg...@zetetic.net> wrote:
> Just so you're aware, on certain hardware and crypto implementations, e.g.
> armv6 using OpenSSL, those 64,000 operations can be very expensive. Doing it
> on an iPhone 3G without hardware acceleration was pretty much a non-starter.
> Not that we support armv6 anymore, just suggesting that you'll be better off
> using a migration flag of some sort to avoid performing this check if the
> upgrade has already been run. Also, your app will simply consume less power
> by checking a flag and only calling cipher_migrate if the upgraded flag is
> not set.

OK, now I am completely lost. And since I am delivering a presentation
on SQLCipher for Android at droidcon UK in a bit less than a week, I'd
like to become non-lost. :-)

>> It is not exactly a no-op, it attempts to key the database and perform a
>> read, which result in PKCS5_PBKDF2_HMAC_SHA1 being called with an
>> iteration of 64000, this would be a wasted effort if the database had
>> already been migrated, or if the database were created with a kdf set to
>> 64000.

Upon further review, this is where I am becoming lost.

According to the blog post:

> With this release the default iteration count used for PBKDF2 is 64000, up from the previous default of 4000 - an increase of 16 times our previous cycle count.

However, that would imply that *every* time we are opening the
database, we are going through the 64,000 round iteration. After all,
it is those 64,000 rounds that is converting our human-entered
passphrase into the key for the database, based upon my understanding
of PBKDF2.

Hence, the PRAGMA cipher_migrate should be a net no-op on an
already-migrated database, as we have to do that 64,000-iteration
PBKDF2 round anyway. Moreover, as this is being done in postKey(),
SQLCipher already did the 64,000-iteration PBKDF2 before we even ask
for cipher_migrate (otherwise, this would be in preKey(), unless you
have different meanings for "pre", "post", or "key" than I do). I can
definitely see where cipher_migrate will be expensive to execute to
actually do the re-keying that first time, but I fail to see how it
will be expensive if called every time.

And, if there is hardware in which a 64,000-iteration PBKDF2 is a
"non-starter", then isn't SQLCipher a "non-starter" for such hardware?
And, if so, can we get some clarity on the new minimum hardware
requirements for SQLCipher?

[Nick Parker]

Hi Mark,

On 10/18/13 10:47 AM, Mark Murphy wrote:
> On Fri, Oct 18, 2013 at 11:24 AM, William Gray <wg...@zetetic.net> wrote:
>> Just so you're aware, on certain hardware and crypto implementations, e.g.
>> armv6 using OpenSSL, those 64,000 operations can be very expensive. Doing it
>> on an iPhone 3G without hardware acceleration was pretty much a non-starter.
>> Not that we support armv6 anymore, just suggesting that you'll be better off
>> using a migration flag of some sort to avoid performing this check if the
>> upgrade has already been run. Also, your app will simply consume less power
>> by checking a flag and only calling cipher_migrate if the upgraded flag is
>> not set.
>
> OK, now I am completely lost. And since I am delivering a presentation
> on SQLCipher for Android at droidcon UK in a bit less than a week, I'd
> like to become non-lost. :-)

Great news - do tell us how it goes!

>
>>> It is not exactly a no-op, it attempts to key the database and perform a
>>> read, which result in PKCS5_PBKDF2_HMAC_SHA1 being called with an
>>> iteration of 64000, this would be a wasted effort if the database had
>>> already been migrated, or if the database were created with a kdf set to
>>> 64000.
>
> Upon further review, this is where I am becoming lost.
>
> According to the blog post:
>
>> With this release the default iteration count used for PBKDF2 is 64000, up from the previous default of 4000 - an increase of 16 times our previous cycle count.
>
> However, that would imply that *every* time we are opening the
> database, we are going through the 64,000 round iteration. After all,
> it is those 64,000 rounds that is converting our human-entered
> passphrase into the key for the database, based upon my understanding
> of PBKDF2.
>
> Hence, the PRAGMA cipher_migrate should be a net no-op on an
> already-migrated database, as we have to do that 64,000-iteration
> PBKDF2 round anyway. Moreover, as this is being done in postKey(),
> SQLCipher already did the 64,000-iteration PBKDF2 before we even ask
> for cipher_migrate (otherwise, this would be in preKey(), unless you
> have different meanings for "pre", "post", or "key" than I do). I can
> definitely see where cipher_migrate will be expensive to execute to
> actually do the re-keying that first time, but I fail to see how it
> will be expensive if called every time.

The cipher_migrate routine attempts to determine what database format it
needs to upgrade from, which includes support for our 1x and 2x formats.
In the event that you issue cipher_migrate every time the connection is
opened, and the user provides an invalid passphrase it will attempt to
determine the database format which would include calls to sqlite3_key
followed with a query that would trigger key derivation to occur per
format. In this case where an invalid passphrase is provided, key
derivation would occur multiple times, slowing down the process.

>
> And, if there is hardware in which a 64,000-iteration PBKDF2 is a
> "non-starter", then isn't SQLCipher a "non-starter" for such hardware?
> And, if so, can we get some clarity on the new minimum hardware
> requirements for SQLCipher?
>

It isn't so much that 64000 iterations via SQLCipher is the problem in
the context of our discussion, however performing those key derivation
operations multiple times in certain scenarios wouldn't be prudent given
that the upgrade only needs to occur once.

--
Nick Parker

[Mark Murphy]

On Fri, Oct 18, 2013 at 12:01 PM, Nick Parker <npa...@zetetic.net> wrote:
> The cipher_migrate routine attempts to determine what database format it
> needs to upgrade from, which includes support for our 1x and 2x formats.
> In the event that you issue cipher_migrate every time the connection is
> opened, and the user provides an invalid passphrase it will attempt to
> determine the database format which would include calls to sqlite3_key
> followed with a query that would trigger key derivation to occur per
> format. In this case where an invalid passphrase is provided, key
> derivation would occur multiple times, slowing down the process.

I thought we wanted invalid passphrases to be slow... :-)

> It isn't so much that 64000 iterations via SQLCipher is the problem in
> the context of our discussion, however performing those key derivation
> operations multiple times in certain scenarios wouldn't be prudent given
> that the upgrade only needs to occur once.

OK. So this is an optimization. In that case, why doesn't
SQLiteOpenHelper keep track of whether the database might need
cipher_migrate?

Basically, if *every current developer* using SQLCipher for Android
needs to have logic to deal with cipher_migrate, it makes a lot more
sense for SQLCipher for Android to handle cipher_migrate itself, not
only to minimize overall ecosystem effort, but to have consistent
practices.

If you're expecting us to maintain our own flag (e.g., in
SharedPreferences) to know whether or not we need to do
cipher_migrate, SQLiteOpenHelper can do the same thing. You have a
Context. You can maintain your own independent SharedPreferences (or
other sort of file), where you track this information. Offer some
static methods somewhere for resetting this, in case we are restoring
a database from a backup and need to let you know that you might need
to consider cipher_migrate again.

The point behind SQLiteOpenHelper is for it to handle all this crap
for us. While in standard Android that is mostly limited to schema
changes and related stuff, in the realm of SQLCipher for Android, your
SQLiteOpenHelper should be helping with these sorts of changes as
well.

Or am I missing something again?

[Nick Parker]

Hi Mark,

On 10/18/13 11:11 AM, Mark Murphy wrote:
> On Fri, Oct 18, 2013 at 12:01 PM, Nick Parker <npa...@zetetic.net> wrote:
>> The cipher_migrate routine attempts to determine what database format it
>> needs to upgrade from, which includes support for our 1x and 2x formats.
>> In the event that you issue cipher_migrate every time the connection is
>> opened, and the user provides an invalid passphrase it will attempt to
>> determine the database format which would include calls to sqlite3_key
>> followed with a query that would trigger key derivation to occur per
>> format. In this case where an invalid passphrase is provided, key
>> derivation would occur multiple times, slowing down the process.
>
> I thought we wanted invalid passphrases to be slow... :-)

:-)

>
>> It isn't so much that 64000 iterations via SQLCipher is the problem in
>> the context of our discussion, however performing those key derivation
>> operations multiple times in certain scenarios wouldn't be prudent given
>> that the upgrade only needs to occur once.
>
> OK. So this is an optimization. In that case, why doesn't
> SQLiteOpenHelper keep track of whether the database might need
> cipher_migrate?

It becomes application specific metadata in a sense - the state of your
application. For example, if *your* application uses 20000 iterations
for key derivation, SQLCipher provides no means to store that, so the
onus falls on the client application developer to store and configure
the library with the constraints of their application.

>
> Basically, if *every current developer* using SQLCipher for Android
> needs to have logic to deal with cipher_migrate, it makes a lot more
> sense for SQLCipher for Android to handle cipher_migrate itself, not
> only to minimize overall ecosystem effort, but to have consistent
> practices.

This is possible, but not something that currently exists within the
library.

>
> If you're expecting us to maintain our own flag (e.g., in
> SharedPreferences) to know whether or not we need to do
> cipher_migrate, SQLiteOpenHelper can do the same thing. You have a
> Context. You can maintain your own independent SharedPreferences (or
> other sort of file), where you track this information. Offer some
> static methods somewhere for resetting this, in case we are restoring
> a database from a backup and need to let you know that you might need
> to consider cipher_migrate again.
>

Yes, maintaining whether you have upgraded the database externally would
be the recommended approach at this time. We have some long-term plans
that may help alleviate this situation within SQLCipher core, but we are
far off from that currently.

> The point behind SQLiteOpenHelper is for it to handle all this crap
> for us. While in standard Android that is mostly limited to schema
> changes and related stuff, in the realm of SQLCipher for Android, your
> SQLiteOpenHelper should be helping with these sorts of changes as
> well.
>
> Or am I missing something again?
>

This is something we can look into, but no one has requested the feature
for SQLCipher for Android.

cipher_migrate was introduced to help make the migration of the database
format easier for individuals that use the default SQLCipher
configuration. When we added per-page HMAC support to SQLCipher in 2.0,
we had a Android specific helper method that a user could call to
perform the migration. Moving this feature into SQLCipher core made
more sense as this impacts other users beyond Android.

Anyone who makes changes to the default kdf iterations, cipher, page
size would need to perform the upgrade process themselves and not use
cipher_migrate.


--
Nick Parker

[Stephen Lombardo]

Hi All,

I just wanted to chime in briefly on this thread, since it seems like there are a lot of varied and valid opinions here.

We definitely want SQLCipher to be as easy to use as possible. That said, since we are not targeting a single platform, and have had to pull platform-specific functionality back into core on multiple occasions, we have become more sensitive to maintaining consistency across platforms, and making decisions about what belongs in core vs platform wrappers.

As you know, during past upgrades, i.e. from SQLCipher < 1 to 1, 1 to 2, etc, it has always been necessary for developers to coordinate migration of their databases. However, each successive major upgrade introduced new settings and features. So, we decided that having a single PRAGMA to manage "default" migrations would be sensible. This allows developers to greatly reduce the number of calls they need to migrate a database, instead of having to check each possible combination. However the cipher_migrate function is just an automation - there is no way to tell today what settings a database was created with, so we must enumerate each combination, which is expensive. While this feature doesn't completely eliminate the migrations from a developer, we do think it is an improvement and a step in the right direction that aligns well with future plans for SQLCipher.

To expand on Nick's earlier comment about the future direction, we are working out how to store additional header information in SQLCipher databases, hopefully as a major feature in SQLCipher 4. This will allow us to determine exactly what settings should be used with any given database. It would eliminate the need to check different combinations of settings entirely, and would turn cipher_migrate into a true, inexpensive no-op if a database was already converted. Once we can get to that point, migrations could very easily be inlined so developers wouldn't need to worry about the specifics any more. Plus, since this would all be built into core, we would avoid adding / changing interfaces in SQLCipher for Android entirely, and provide equivalent functionality to all supported platforms at one time. This is one of the main reasons we are hesitant to make a lot of additional changes to the Android-specific logic for migrations at this time.

In the short term there is another benefit to the hook approach we are already using today. It allows developers flexibility to run other pragmas than cipher_migrate. As a case in point, one could upgrade the library, but choose to call PRAGMA kdf_iter=X to keep compatibility with the previous version of SQLCipher, instead of running a time consuming migration at all. Or, one could use the hooks to perform a custom migration if other settings were adjusted in the past. Obviously, this isn't necessarily mutually exclusive with automated migrations, but the current approach does cover multiple use cases in one go.

Finally, we're hoping to release SQLCipher 3 soon, and don't want to hold things up. That said, we'll could to take a closer look into low-impact options for simplifying migrations for a follow-on release. I'm interested to know whether publishing some easy-to-integrate reference code for handling migrations might address these perceived issues in the short term, since it's our sincere hope that migration issues will be addressed in the core library in the near future.

Cheers,
Stephen

[Mark Murphy]

On Fri, Oct 18, 2013 at 3:30 PM, Stephen Lombardo
<sjlom...@zetetic.net> wrote:
> In the short term there is another benefit to the hook approach we are
> already using today. It allows developers flexibility to run other pragmas
> than cipher_migrate. As a case in point, one could upgrade the library, but
> choose to call PRAGMA kdf_iter=X to keep compatibility with the previous
> version of SQLCipher, instead of running a time consuming migration at all.
> Or, one could use the hooks to perform a custom migration if other settings
> were adjusted in the past. Obviously, this isn't necessarily mutually
> exclusive with automated migrations, but the current approach does cover
> multiple use cases in one go.

And if your SQLiteOpenHelper were tied into your hook system, that
would be wonderful.

For example, your SQLiteOpenHelper could implement SQLiteDatabaseHook,
passing `this` to SQLiteDatabase.openOrCreateDatabase() as the fourth
parameter, and offer no-op implementations of preKey() and postKey().
That way, handling PRAGMA cipher_migrate, or your other scenarios,
would be a matter of overriding one method, and not have to affect
anything else in the application. By implementing the interface
yourself with no-op methods, it would not break any existing code
bases when they upgraded to the new SQLCipher for Android JAR.

By contrast, what you are proposing involves greater modifications to
a SQLCipher for Android-based application, as I suspect that most of
them use SQLiteOpenHelper rather than directly hitting SQLiteDatabase.
No longer can we solely use SQLiteOpenHelper to get at our database.
Instead, we need to rewrite all our access paths (as it may not be
predictable which part of the app hits the database first) to run
through some separate "maybe we need to run PRAGMA cipher_migrate
first" code, before we can employ SQLiteOpenHelper.

> Finally, we're hoping to release SQLCipher 3 soon, and don't want to hold
> things up.

Which is why I am anticipating writing SQLCipherV3Helper, either
extending or replacing your SQLiteOpenHelper, to implement the above,
plus provide a stock implementation of PRAGMA cipher_migrate
management. That will require developers to grab another Java class
(or JAR) and refactor their code to extend my class rather than yours,
but at least it will reduce the "WTF?" reactions that I anticipate
when I try to explain to developers what you would be proposing.

My apologies for not having more substantively reviewed your beta when
it was initially released, and therefore not echoing these concerns
earlier in your development cycle. I will endeavor to pay more
attention in the future. Feel free to yell at me (ideally via private
email) if you have not gotten comments from me within a few days of
future beta releases. :-)

[Stephen Lombardo]

Hello Mark,

On Fri, Oct 18, 2013 at 4:20 PM, Mark Murphy <mmu...@commonsware.com> wrote:

On Fri, Oct 18, 2013 at 3:30 PM, Stephen Lombardo
<sjlom...@zetetic.net> wrote:
> In the short term there is another benefit to the hook approach we are
> already using today. It allows developers flexibility to run other pragmas
> than cipher_migrate.

And if your SQLiteOpenHelper were tied into your hook system, that
would be wonderful.

For example, your SQLiteOpenHelper could implement SQLiteDatabaseHook,
passing `this` to SQLiteDatabase.openOrCreateDatabase() as the fourth
parameter, and offer no-op implementations of preKey() and postKey().
That way, handling PRAGMA cipher_migrate, or your other scenarios,
would be a matter of overriding one method, and not have to affect
anything else in the application.

 

This is a fair compromise that we may be able to squeeze in before launch. We're tentatively planning to release SQLCipher 3 the last week of October. In the interest of simplifying this, we'll try to work this in early next week, and will put out one more beta version before launch.

My apologies for not having more substantively reviewed your beta when
it was initially released, and therefore not echoing these concerns
earlier in your development cycle. I will endeavor to pay more
attention in the future. Feel free to yell at me (ideally via private
email) if you have not gotten comments from me within a few days of
future beta releases. :-)

That would be great, we really appreciate early feedback and validation, especially on these large releases where there are major changes in play.

Thanks, and have a good weekend!

Cheers,
Stephen

[Mark Murphy]

On Fri, Oct 18, 2013 at 4:40 PM, Stephen Lombardo
<sjlom...@zetetic.net> wrote:
> This is a fair compromise that we may be able to squeeze in before launch.
> We're tentatively planning to release SQLCipher 3 the last week of October.
> In the interest of simplifying this, we'll try to work this in early next
> week, and will put out one more beta version before launch.

FWIW, here is a Gist containing:

- SQLiteHookedHelper, which is a SQLiteOpenHelper that implements
SQLiteDatabaseHook and also exposes a getContext() to subclasses

- SQLCipherV3Helper, which extends SQLiteHookedHelper and implements
PRAGMA cipher_migrate logic in postKey(), complete with maintaining a
flag to only do this once

https://gist.github.com/commonsguy/7058172

[Werner Dittmann]

Mark,

thanks for the code - thus I don't need to write it myself ;-) .

Short question (also to Nick): what happens if it's a new database? Does SQLCipher calls the hook

on new databases as well and tries to perform a migration?

Werner

[Abel Luck]

Stephen Lombardo:

> Hello Mark,
>
> On Fri, Oct 18, 2013 at 4:20 PM, Mark Murphy <mmu...@commonsware.com>wrote:
>
>> On Fri, Oct 18, 2013 at 3:30 PM, Stephen Lombardo
>> <sjlom...@zetetic.net> wrote:
>>> In the short term there is another benefit to the hook approach we are
>>> already using today. It allows developers flexibility to run other
>> pragmas
>>> than cipher_migrate.
>>
>> And if your SQLiteOpenHelper were tied into your hook system, that
>> would be wonderful.
>>
>> For example, your SQLiteOpenHelper could implement SQLiteDatabaseHook,
>> passing `this` to SQLiteDatabase.openOrCreateDatabase() as the fourth
>> parameter, and offer no-op implementations of preKey() and postKey().
>> That way, handling PRAGMA cipher_migrate, or your other scenarios,
>> would be a matter of overriding one method, and not have to affect
>> anything else in the application.
>>
>
> This is a fair compromise that we may be able to squeeze in before launch.
> We're tentatively planning to release SQLCipher 3 the last week of October.
> In the interest of simplifying this, we'll try to work this in early next
> week, and will put out one more beta version before launch.
>

For the sake of posterity: The Hook-aware SQLiteOpenHelper was committed and made it into
v3.0.0 [0], therefore, the custom open helper linked by Mark down thread is unnecessary.

~abel


[0]:
https://github.com/sqlcipher/android-database-sqlcipher/commit/530b05858abc16fe496e14ece543007bf2b4fbb2

[Kiran Musham]

Hi,

I am trying to migrate to 3.0.0 from 2.2.0 and here is the error I am getting

01-08 16:14:53.236: I/Database(1472): sqlite returned: error code = 26, msg = statement aborts at 1: [PRAGMA user_version;] file is encrypted or is not a database

01-08 16:14:53.236: W/dalvikvm(1472): threadid=11: thread exiting with uncaught exception (group=0x40c46300)

01-08 16:14:53.247: E/AndroidRuntime(1472): FATAL EXCEPTION: AsyncTask #1

01-08 16:14:53.247: E/AndroidRuntime(1472): java.lang.RuntimeException: An error occured while executing doInBackground()

01-08 16:14:53.247: E/AndroidRuntime(1472): at android.os.AsyncTask$3.done(AsyncTask.java:299)

01-08 16:14:53.247: E/AndroidRuntime(1472): at java.util.concurrent.FutureTask$Sync.innerSetException(FutureTask.java:273)

01-08 16:14:53.247: E/AndroidRuntime(1472): at java.util.concurrent.FutureTask.setException(FutureTask.java:124)

01-08 16:14:53.247: E/AndroidRuntime(1472): at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:307)

01-08 16:14:53.247: E/AndroidRuntime(1472): at java.util.concurrent.FutureTask.run(FutureTask.java:137)

01-08 16:14:53.247: E/AndroidRuntime(1472): at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:230)

01-08 16:14:53.247: E/AndroidRuntime(1472): at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1076)

01-08 16:14:53.247: E/AndroidRuntime(1472): at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:569)

01-08 16:14:53.247: E/AndroidRuntime(1472): at java.lang.Thread.run(Thread.java:856)

01-08 16:14:53.247: E/AndroidRuntime(1472): Caused by: net.sqlcipher.database.SQLiteException: error code 26: file is encrypted or is not a database

01-08 16:14:53.247: E/AndroidRuntime(1472): at net.sqlcipher.database.SQLiteStatement.native_1x1_long(Native Method)

01-08 16:14:53.247: E/AndroidRuntime(1472): at net.sqlcipher.database.SQLiteStatement.simpleQueryForLong(SQLiteStatement.java:128)

01-08 16:14:53.247: E/AndroidRuntime(1472): at net.sqlcipher.database.SQLiteDatabase.getVersion(SQLiteDatabase.java:993)

01-08 16:14:53.247: E/AndroidRuntime(1472): at com.ctt.celltrak2.db.DbAdapter$1.postKey(DbAdapter.java:395)

01-08 16:14:53.247: E/AndroidRuntime(1472): at net.sqlcipher.database.SQLiteDatabase.<init>(SQLiteDatabase.java:1931)

01-08 16:14:53.247: E/AndroidRuntime(1472): at net.sqlcipher.database.SQLiteDatabase.openDatabase(SQLiteDatabase.java:871)

01-08 16:14:53.247: E/AndroidRuntime(1472): at net.sqlcipher.database.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:899)

01-08 16:14:53.247: E/AndroidRuntime(1472): at net.sqlcipher.database.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:895)

01-08 16:14:53.247: E/AndroidRuntime(1472): at com.ctt.celltrak2.db.DbAdapter.open(DbAdapter.java:403)

01-08 16:14:53.247: E/AndroidRuntime(1472): at com.ctt.celltrak2.db.DbAdapter.getInstance(DbAdapter.java:374)

01-08 16:14:53.247: E/AndroidRuntime(1472): at com.ctt.celltrak2.CellTrak$StartActivityAsyncTask.doInBackground(CellTrak.java:36)

01-08 16:14:53.247: E/AndroidRuntime(1472): at com.ctt.celltrak2.CellTrak$StartActivityAsyncTask.doInBackground(CellTrak.java:1)

01-08 16:14:53.247: E/AndroidRuntime(1472): at android.os.AsyncTask$2.call(AsyncTask.java:287)

01-08 16:14:53.247: E/AndroidRuntime(1472): at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:305)

01-08 16:14:53.247: E/AndroidRuntime(1472): ... 5 more

Can someone find out what was wrong, here is the sample code

SQLiteDatabase.loadLibs(mCtx);

mOpenHelper = new DatabaseHelper(mCtx);

SQLiteDatabaseHook hook = new SQLiteDatabaseHook() {

public void preKey(SQLiteDatabase database) {

}

public void postKey(SQLiteDatabase database) {

if (database.getVersion() <= 3) {

database.rawExecSQL("PRAGMA cipher_migrate;");

}

}

};

File dbFile = mCtx.getDatabasePath(DbDef.DB_NAME); 

mDb = SQLiteDatabase.openOrCreateDatabase(dbFile, DbDef.DB_PWD,

null, hook);

Thanks

Kiran

[Nick Parker]

Hello Kiran,

If you are migrating from version 2x to 3x of SQLCipher for Android, you
are likely getting the error in your call to getVersion as that method
will invoke PRAGMA user_version and KDF interation length of 64,000 used
by the 3x library will not match your needed value of 4,000 to access
the current version of the database.

If you are looking to upgrade to the latest libraries, it is recommended
that you track your need to upgrade the file format (i.e., through the
usage of PRAGMA cipher_migrate) externally from the database, for
example through a SharedPreference.

[Kiran Musham]

Thanks for a quick reply. 

I just commented out the database version check to use sharedpreferences. This time seems like I am getting a different error.

01-09 11:30:14.144: I/System.out(25780): migrating sqlciphe 2 to 3 start

01-09 11:30:16.152: I/Database(25780): sqlite returned: error code = 26, msg = statement aborts at 1: [PRAGMA user_version;] file is encrypted or is not a database

01-09 11:30:16.402: I/Database(25780): sqlite returned: error code = 26, msg = statement aborts at 1: [PRAGMA user_version;] file is encrypted or is not a database

01-09 11:30:23.105: I/System.out(25780): migrating sqlciphe 2 to 3 end

Regards

Kiran

[Nick Parker]

Hello Kiran,

Can you share the updated snippet of code you are invoking, along with
the result returned from PRAGMA cipher_migrate?

[Kiran Musham]

Here is the modified code:

private DbAdapter open(Context ctx) throws SQLException {

SQLiteDatabase.loadLibs(mCtx);

mOpenHelper = new DatabaseHelper(mCtx);

net.sqlcipher.database.SQLiteDatabaseHook hook = new net.sqlcipher.database.SQLiteDatabaseHook() {

public void preKey(SQLiteDatabase database) {

}

public void postKey(SQLiteDatabase database) {

// if (database.getVersion() <= 3) {

try {

System.out.println("migrating sqlcipher 2 to 3 start");

database.rawExecSQL("PRAGMA cipher_migrate;");

System.out.println("migrating sqlcipher 2 to 3 end");

} catch (Exception e) {

e.printStackTrace();

}

// }

}

};

java.io.File dbFile = mCtx.getDatabasePath(DbDef.DB_NAME);

mDb = SQLiteDatabase.openOrCreateDatabase(dbFile, DbDef.DB_PWD, null,

hook);

return this;

}

Result:

01-09 13:56:11.183: I/System.out(30258): migrating sqlcipher 2 to 3 start

01-09 13:56:13.746: I/Database(30258): sqlite returned: error code = 26, msg = statement aborts at 1: [PRAGMA user_version;] file is encrypted or is not a database

01-09 13:56:13.996: I/Database(30258): sqlite returned: error code = 26, msg = statement aborts at 1: [PRAGMA user_version;] file is encrypted or is not a database

01-09 13:56:21.215: I/System.out(30258): migrating sqlcipher 2 to 3 end

Regards

Kiran

[Nick Parker]

Hello Kiran,

You will need to use rawQuery retrieve the result code of the PRAGMA
cipher_migrate call. What result do you get from that?

[Kiran Musham]

Ok, I just used rawQuery, but seems like the same issue.

Here is the updated code.

private DbAdapter open(Context ctx) throws SQLException {

    SQLiteDatabase.loadLibs(mCtx);

    mOpenHelper = new DatabaseHelper(mCtx);

    net.sqlcipher.database.SQLiteDatabaseHook hook = new net.sqlcipher.database.SQLiteDatabaseHook() {

      public void preKey(SQLiteDatabase database) {

      }

      public void postKey(SQLiteDatabase database) {

        // if (database.getVersion() <= 3) {

        Cursor cursor = null;

        try {

          System.out.println("migrating sqlcipher 2 to 3 start");

          cursor = database.rawQuery("PRAGMA cipher_migrate;",

              new String[] {});

          if (cursor != null) {

            System.out

                .println("migrate pragma query result count = "

                    + cursor.getCount());

          }

          System.out.println("migrating sqlcipher 2 to 3 end");

        } catch (Exception e) {

          e.printStackTrace();

        } finally {

          if (cursor != null) {

            cursor.close();

            cursor = null;

          }

        }

        // }

      }

    };

    java.io.File dbFile = mCtx.getDatabasePath(DbDef.DB_NAME);

    mDb = SQLiteDatabase.openOrCreateDatabase(dbFile, DbDef.DB_PWD, null,

        hook);    

    return this;

  }

and the output is :

01-09 15:05:55.957: I/System.out(876): migrating sqlcipher 2 to 3 start

01-09 15:05:57.972: I/Database(876): sqlite returned: error code = 26, msg = statement aborts at 1: [PRAGMA user_version;] file is encrypted or is not a database

01-09 15:05:58.222: I/Database(876): sqlite returned: error code = 26, msg = statement aborts at 1: [PRAGMA user_version;] file is encrypted or is not a database

01-09 15:06:04.660: I/System.out(876): migrate pragma query result count = 1

01-09 15:06:04.660: I/System.out(876): migrating sqlcipher 2 to 3 end

Regards

Kiran

[Nick Parker]

Hello Kiran,

PRAGMA cipher_migrate will return a single integer value as a string
representing the result code of the operation. Could you call
getString(0) from your Cursor and share what value you get back?

[Kiran Musham]

cursor.getString(0) returned "0"

Regards

Kiran

[Nick Parker]

Hello Kiran,

The migration of the database to file format version 3x was successful
as the result codes map to SQLite result codes [1]. Can you either
inspect the database with the SQLCipher command line shell [2] or
attempt to run your application again?

1. http://www.sqlite.org/c3ref/c_abort.html
2. http://sqlcipher.net/introduction/

[Kiran Musham]

App is working fine even though sqlcipher returns/shows error. 

I implemented the complete solution with shared preferences. 

Do you want me to safely assume that DB is migrated to 3.x and ignore the error?

or am I doing something wrong? please confirm.

Regards

Kiran

[Nick Parker]

Hello Kiran,

It would be best to verify the database with the SQLCipher command line
shell. Can you show me exactly where you are receiving an exception
within your application? Please consider using Gist
(https://gist.github.com/) for sending any blocks of code in your
messages. Thanks!

[Kiran Musham]

Please see the migration code at https://gist.github.com/kmusham/8214418bdd71ef6e5de3

throwing fake error after pragma call to cipher_migrate pragma

Regards

Kiran

[Nick Parker]

Hi Kiran,

Thanks for submitting your snippet. Are you saying that the exception
is thrown on line 11 [1]? I was under the impression you were able to
complete the upgrade, with a result code of 0 from cipher_migrate?
Also, are you using the public 3.0.1 binaries that we distribute?

1.
https://gist.github.com/kmusham/8214418bdd71ef6e5de3#file-gistfile1-txt-L11

[Kiran Musham]

Correct, but I still see the error shown in the logcat. app works fine after the migration. 

yes, I am using 3.01 binaries. 

For now, I am ignoring the error messages .., but this is something you guys want to look into it in your next release.

Thanks for your help.

Kiran

>     <https:/...

[Nick Parker]

Hello Kiran,

I just verified the results you are seeing from our test suite, that
output comes from the upgrade process determining what environment your
database matches against, it uses PRAGMA user_version to determine if it
has found a match. You can ignore those statements, however you should
in fact check the result code returned from running PRAGMA
cipher_migrate and validate you receive 0 back.

[Stephen Lombardo]

Hello Kiran,

Just to close the loop on this topic, the messages you are seeing are a result of the custom logger attached by the android database wrapper (https://github.com/sqlcipher/android-database-sqlcipher/blob/master/jni/net_sqlcipher_database_SQLiteDatabase.cpp#L84).

PRAGMA cipher_migrate calls PRAGMA user_version internally to determine if a database is readable with a given key and configuration set. If it is not readable, it handles and recovers from the error, and tries the next configuration set. This results in several expected "internal" errors that don't bubble up to the application, but are logged anyway.

It would not be possible to suppress these log messages without adjusting the custom logger or removing the callback entirely. Either of these options would require a custom build. Therefore, as Nick mentioned, you should check the result from PRAGMA cipher_migrate to determine if the operation was successful, disregarding the log statements.

Cheers,
Stephen

[Kiran Musham]

Thank you very much for confirming.

Regards

Kiran 

>     <https://github.com/sqlcipher/sqlcipher-android-tests/blob/prerelease/src/main/java/net/zetetic/tests/MigrateDatabaseFrom1xForma...