Sqlcipher db migration from version 3.7.9 to 3.28.0

I am trying to open the sqlcipher db created with sqlcipher based on sqlite version 3.7.9 with sqlcipher based on sqlite version 3.28.0. Is this even possible? I know that the time difference between versions is 8 years…

I have tried following commands:

sqlite> PRAGMA key = 'myPassword';
sqlite> .schema 
Error: file is not a database
sqlite> PRAGMA cipher_migrate;
1
sqlite> .schema
Error: file is not a database
sqlite> PRAGMA cipher_compatibility = 1;
sqlite> .schema
Error: out of memory

How could I investigate why exactly the migration fails?

Hello @flukson - 3.7.9 was the baseline for SQLCipher 2. It should be migratable, but checking the schema after setting the key is likely interfering. Try the following

sqlite> PRAGMA key = 'myPassword';
sqlite> PRAGMA cipher_migrate;

Alternately, for backwards compatibility, try:

sqlite> PRAGMA key = 'myPassword';
sqlite> PRAGMA cipher_compatibility = 2;

If those don’t work you may be using some custom settings, and it would be most useful to check the code that created the database to see if there were any PRAGMA statements in use that adjusted things like the KDF iterations, HMAC, etc.

@sjlombardo, thank you for your answer.
I have tried both these methods. Unfortunately I get the “file is not a database” or “out of memory” errors. Is there any way to print more useful information about what exactly happens?

These errors (especially first one) are quite generic. Because of that I have built the sqlcipher with flags:

-DSQLITE_DEBUG
-DSQLITE_ENABLE_EXPLAIN_COMMENTS
-DSQLITE_ENABLE_SELECTTRACE
-DSQLITE_ENABLE_WHERETRACE

When I try to migrate the database, I get following output:

sqlite> .eqp full
sqlite> PRAGMA key = ‘myPassword’;
addr opcode p1 p2 p3 p4 p5 comment
0 Init 0 1 0 00 Start at 1
1 Halt 0 0 0 00
sqlite> PRAGMA cipher_migrate;
addr opcode p1 p2 p3 p4 p5 comment
0 Init 0 1 0 00 Start at 1
1 String8 0 1 0 1 00 r[1]=‘1’
2 ResultRow 1 1 0 00 output=r[1]
3 Halt 0 0 0 00
1
sqlite> .schema
Error: file is not a database

Unfortunately, I do not know what it means and if it is useful…

And are you sure that this was SQLCipher version 2? As far as I interpret the ‘git log’ properly, it was rather SQLCipher version 1.1.9 (maybe both 1.1.9 and 2.0.0 were based on Sqlite 3.7.9).

Hello @flukson - you are right, I believe that SQLite 3.7.9 spanned two major releases. A thought occurred to me about this. There was a short period between 1.1.9 and 2.0.0 where a beta version fo 2.0 contained settings that were ultimately different in the release version of 2.0.0. If you happened to be using SQLCipher from that brief window, you may not be using standard defaults. Can you try to pen the database using the following?

    PRAGMA key = 'myPassword';
    PRAGMA kdf_iter = 4000;
    PRAGMA fast_kdf_iter = 4000;
    PRAGMA cipher_hmac_salt_mask = "x'00'";
    PRAGMA cipher_page_size = 1024;
    PRAGMA cipher_hmac_algorithm = HMAC_SHA1;
    PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;

Also, just to rule this out, I noticed that the example you pasted in used curly single quotes around myPassword. That will not work properly with SQLCipher. I suspect this was some sort of autocorrection when you wrote/pasted, but just to verify, please make sure you are using standard ASCII single quote characters around myPassword.

Let me know if either of those suggestions proves fruitful.

Hi, @sjlombardo - thank you for your last message. I have tried your suggestions but unfortunately they do not work. Additionally, some of these settings give messages that they are deprecated.

I will describe my situation since the beginning:
I have an application that uses sqlcipher 1.1.9 or 2.0 in amalgamation version. After updating the sqlcipher to version 4.3.0 (latest one), the application does not work with the old database. Fortunately, it has a mechanism that recreates the db so when I remove the old db by hand, the app recreates it (using new sqlcipher). Then the application works properly. Both amalgamation versions were taken without any important changes.

Additionally, I have built the sqlcipher (in version 4.3.0) as a separate binary. Using the command line tool I can properly open the database created by the updated version of an app. However, I cannot open the old database although the key is the same.

The ideal situation would be to migrate somehow old database to new one.

Here I have some questions: is there any way to print all settings of the sqlcipher in the cmd line tool? Is there any api in c++ to use in the code to print the sqlcipher settings? Do settings for the amalgamation version differ from the settings for the standalone version?

Hello @flukson. I think I have a good understanding of the situation you are describing. We have fairly extensive test coverage for opening all major database versions, e.g. :

Since all of these scenarios are tested before each release using canonical database examples created with the respective SQLCipher versions which are stored in the repository, we are fairly confident that backwards compatibility works will all publicly released versions of the software.

You didn’t mention what platform you are testing on. If you the architecture is big endian, then there may be one additional setting to try (which you can see above).

Barring that, the most likely reason for incompatibility would be that some custom settings were used, either via runtime settings or code changes to SQLCipher itself, in your original release. To say for sure you’d either need to look at the application code (for relevant PRAGMA settings) or examine your old amalgamation code.

Hi @sjlombardo. Thank you for your help. I have tried settings from listed tests and got following outputs:

Version 1
PRAGMA cipher_use_hmac = off;
PRAGMA kdf_iter = 4000;
PRAGMA cipher_page_size = 1024;
PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;
Error: file is not a database

Version 2 beta
PRAGMA kdf_iter = 4000;
PRAGMA fast_kdf_iter = 4000;
PRAGMA cipher_hmac_salt_mask = “x’00’”;
PRAGMA cipher_page_size = 1024;
PRAGMA cipher_hmac_algorithm = HMAC_SHA1;
PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;
Error: file is not a database

Version 2 little endian
PRAGMA kdf_iter = 4000;
PRAGMA cipher_page_size = 1024;
PRAGMA cipher_hmac_algorithm = HMAC_SHA1;
PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;
Error: malformed database schema (sqlcipher_autoindex_SCHEMA_INFO_1) - orphan index

Version 2 big endian
PRAGMA cipher_hmac_pgno = be;
PRAGMA kdf_iter = 4000;
PRAGMA cipher_page_size = 1024;
PRAGMA cipher_hmac_algorithm = HMAC_SHA1;
PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;
Error: file is not a database

Version 3
PRAGMA cipher_page_size = 1024;
PRAGMA kdf_iter = 64000;
PRAGMA cipher_hmac_algorithm = HMAC_SHA1;
PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;
Error: malformed database schema (sqlcipher_autoindex_SCHEMA_INFO_1) - orphan index

I am using i586 architecture.

I would be very grateful if you could refer also to following questions:
Is there any way to print all settings of the sqlcipher in the cmd line tool? Is there any api in c++ to use in the code to print the sqlcipher settings? Do settings for the amalgamation version differ from the settings for the standalone version?

Currently I am sure that I use proper key in "PRAGMA key = ". However, if it is possible I would also like to be sure that I use proper settings before further investigations in code.

Hello @flukson - looking at the log output, it seems that the standard version 2 cipher settings are actually opening the database. The error you are seeing is because the database appears to have a corrupt schema. In other words, the key and settings are correct, and the database is being correctly processed by SQLCipher, but the database is corrupt. If this database is working with older versions, it is likely that there is still a problem with it, but its only newer versions of SQLCipher (which encorporate more recent versions of SQLite that have better corruption detection) are reporting an error.

Is there a single copy of this database in use, or are you seeing corruption across multiple independent databases?

Hi @sjlombardo - thank you once again for your time and valuable suggestions. You are right that the scheme was malformed and finally I have managed to migrate the database. Of course the problem was in my code, not in sqlcipher.

In one of previous messages I have written that “amalgamation was taken without any important changes”. However, there were some changes and it turned out that they were much more important that I thought.

8 years ago, when sqlcipher was incorporated to our project, the project was also using sqlite. Because of problems with overlapping symbols, there was a global rename made in the amalgamation version (‘sqlite’ was replaced with ‘sqlcipher’ using sed everywhere in code).
Unfortunately, not only symbols were modified but also sqlite system tables. While it really worked fine for all these years, the migration was not possible with unmodified sqlcipher.

To perform the migration, I have taken the newest sqlcipher (4.3.0) and performed a similar global rename but only for sqlite system tables:

for i in $(git grep --full-name -l sqlite_master); do perl -p -i -e 's/sqlite_master/sqlcipher_master/g' $i; done
for i in $(git grep --full-name -l sqlite3_master); do perl -p -i -e 's/sqlite3_master/sqlcipher3_master/g' $i; done
for i in $(git grep --full-name -l sqlite_temp_master); do perl -p -i -e 's/sqlite_temp_master/sqlcipher_temp_master/g' $i; done
for i in $(git grep --full-name -l sqlite_sequence); do perl -p -i -e 's/sqlite_sequence/sqlcipher_sequence/g' $i; done
for i in $(git grep --full-name -l sqlite_stat1); do perl -p -i -e 's/sqlite_stat1/sqlcipher_stat1/g' $i; done
for i in $(git grep --full-name -l sqlite_stat2); do perl -p -i -e 's/sqlite_stat2/sqlcipher_stat2/g' $i; done
for i in $(git grep --full-name -l sqlite_stat3); do perl -p -i -e 's/sqlite_stat3/sqlcipher_stat3/g' $i; done
for i in $(git grep --full-name -l sqlite_stat4); do perl -p -i -e 's/sqlite_stat4/sqlcipher_stat4/g' $i; done
for i in $(git grep --full-name -l sqlite_autoindex); do perl -p -i -e 's/sqlite_autoindex/sqlcipher_autoindex/g' $i; done

After applying above changes, I have managed to perform the migration with the modified sqlcipher with ‘PRAGMA cipher_migrate;’ command.

One problem that I still have to solve is how to migrate the malformed scheme to the proper scheme in runtime and use the upstream sqlcipher.
I would like to ask, is this possible in runtime to force rename sqlite system tables? I know that it is possible to edit schema with ‘PRAGMA writable_schema=ON’. However, renaming of sqlite system tables (with ALTER TABLE) is not allowed.
Is there any other way to force it in my case?

If force rename is not possible I will try to migrate my malformed database manually. I will use old modified amalgamation version of sqlcipher to load malformed database and save data to new database created using linked updated upstream sqlcipher.

Hello @flukson, thanks for getting back and explaining the root cause of this problem. This is certainly an unusual situation. I can’t really think of a way to alter the names of the system tables like that. As you noted, those are pretty much off limits in terms of modification, even with writable_schema. I think the option of migrating the database manually would probably be the most straightforward approach.