Fix for #218 breaks existing databases (Version 2.2.5+)

[XyFreak]

In the fix for #218 (released with 2.2.5) the previously incorrect handling of SQLiteCipher key schemes was rectified.
However it leaves databases created during the time where this bug existed inaccessible if the SQLiteCipher key scheme was used.

I have attempted to provide the key to SQLite3MC via the various interfaces but all of them end up in GenerateKey*Cipher, where sqlite3mcExtractRawKey unconditionally, making it impossible to use strings of the form x'.....' as a passphrase instead of it being treated as raw key.
While I agree that the bug had to be fixed for the existing interfaces, there has to be a way to explicitly make use of the previous behavior to access databases created with versions prior to 2.2.5.

If such an interface already exists, I apologize. I have attempted:

• Pass passphrase to PRAGMA key, as 'x''...''' (quoted ')
  -> SQLite decodes the string correctly, sqlite3mcExtractRawKey extracts the key and sets bypass = 1
• Pass passphrase in hex encoding to PRAGMA hexkey
  -> The hex encoded string is decoded, sqlite3mcExtractRawKey extracts the key and sets bypass = 1
• Pass passphrase to sqlite3_key.
  -> Same as above.

As far as I have seen in the code, there is no way to avoid sqlite3mcExtractRawKey being called.

Unfortunately it's not possible to bypass this issue by running the KDF on the passphrase myself, as the salt is part of the db itself (default behavior). I could parse the database header to get it but this does not seem like a reasonable path forward to me.

I think a good way to solve this issue would be to provide a way to specify a passphrase without any kind of special string parsing / interpretation (either via a new pragma to provide the passphrase to or a pragma / option to disable sqlite3mcExtractRawKey).

I'm sorry to bring this up and I am well aware it can be annoying having to provide these kind of workarounds to keep compatibility with previously erroneous behavior but I need to access old databases with new versions of SQLite3(MC). Patching in the bug again is an option for the time being but I'd prefer there being a proper solution eventually.

Thank you for your work and best regards

[XyFreak]

Ok I just noticed you do document that the salt is simply stored as the first 16 byte of the database file - with that I can implement a better workaround for my use case.
I still think there should be a less brute-force way to handle this, though.

[utelle]

In the fix for #218 (released with 2.2.5) the previously incorrect handling of SQLiteCipher key schemes was rectified. However it leaves databases created during the time where this bug existed inaccessible if the SQLiteCipher key scheme was used.

Unfortunately, you are right. If the raw key feature (with SQLCipher syntax) was used with SQLite3MC versions between 2.2.1 and 2.2.4 (that is, between July 9, 2025 and November 6, 2025), this resulted in databases that couldn't be accessed with versions 2.2.5+.

Not many users make use of the raw key feature. Otherwise, this problem would have been brought to my attention much earlier.

I have attempted to provide the key to SQLite3MC via the various interfaces but all of them end up in GenerateKey*Cipher, where sqlite3mcExtractRawKey unconditionally, making it impossible to use strings of the form x'.....' as a passphrase instead of it being treated as raw key.

Unfortunately, you are right again.

One way to solve the problem would be to decrypt the database (or to apply an ordinary key) with a version between 2.2.1 and 2.2.4 using PRAGMA rekey, and to encrypt it again using the raw key feature with version 2.2.5 or above, or alternatively with the raw key syntax of sqleet (beginning the key with "raw:"). However, I agree this can be quite cumbersome depending on the circumstances where the affected databases are used.

While I agree that the bug had to be fixed for the existing interfaces, there has to be a way to explicitly make use of the previous behavior to access databases created with versions prior to 2.2.5.

Although I understand your wish to have a simpler way to access such databases than the way described above, I'm a bit reluctant to implement a separate pragma to solve the issue, because it would have to stay in the code forever, increasing chances of misuse - accidentally or on purpose.

As far as I have seen in the code, there is no way to avoid sqlite3mcExtractRawKey being called.

Right.

Unfortunately it's not possible to bypass this issue by running the KDF on the passphrase myself, as the salt is part of the db itself (default behavior). I could parse the database header to get it but this does not seem like a reasonable path forward to me.

If you can access the database with the SQLite command line tool coming with version 2.2.1 to 2.2.4, you could use PRAGMA cipher_salt; to extract the cipher salt from the database.

I think a good way to solve this issue would be to provide a way to specify a passphrase without any kind of special string parsing / interpretation (either via a new pragma to provide the passphrase to or a pragma / option to disable sqlite3mcExtractRawKey).

I would strongly recommend to convert databases affected by this problem (with the method described above) to make them compatible with recent SQLite3MC versions again, instead of keeping them in the erroneous state.

I'm sorry to bring this up and I am well aware it can be annoying having to provide these kind of workarounds to keep compatibility with previously erroneous behavior but I need to access old databases with new versions of SQLite3(MC). Patching in the bug again is an option for the time being but I'd prefer there being a proper solution eventually.

Would converting the old databases be an option for you? That could be done quite easily with the SQLite command line tool (of version 2.2.1 to 2.2.4).

[utelle]

Ok I just noticed you do document that the salt is simply stored as the first 16 byte of the database file

Correct. Alternatively, PRAGMA cipher_salt can be used to extract the salt.

with that I can implement a better workaround for my use case. I still think there should be a less brute-force way to handle this, though.

As I wrote in my previous post, I think that converting the affected databases should be preferred.

[XyFreak]

Ok I just noticed you do document that the salt is simply stored as the first 16 byte of the database file

Correct. Alternatively, PRAGMA cipher_salt can be used to extract the salt.

with that I can implement a better workaround for my use case. I still think there should be a less brute-force way to handle this, though.

As I wrote in my previous post, I think that converting the affected databases should be preferred.

I agree that converting the affected databases is the preferred goal. For that to work I need a code path to first load them though ;-).
Just to clarify things as you mentioned it multiple times: I have thought about invoking external (outdated) tools but decided against it as having 3rd party code in the code base that must not be updated is undesirable. Basically it needs to happen in one go.

After having found the documentation on the salt, I implemented the old behavior with OpenSSL's KDF, as I didn't want to just copy&paste crypto code plus OpenSSL is already a dependency in my code. Using that combo I can generate the "old" key and rekey the database with the proper key. Fixed.

Since I now have a workaround I'm good but am a little concerned whether or not others may run into this issue. Granted you mentioned this is more of a niche case (you are the expert here) so if you decide this not being worth the effort or cluttering the API for, then that's what it's gonna be.

Thank you very much for bringing PRAGMA cipher_salt to my attention. That's definitely cleaner than reading some (the first 16) bytes from the database file.

And also thank you for your extremely fast response. Much appreciated! 👍

[utelle]

I agree that converting the affected databases is the preferred goal. For that to work I need a code path to first load them though ;-).

It is difficult to tell which approach would be best for you, because I don't know whether or not you need to distribute a solution to other (external) users. Using the SQLite command line tool (based on SQLite3MC 2.2.1 to 2.2.4) it would require to issue just a few pragma commands to convert the database.

I have thought about invoking external (outdated) tools but decided against it as having 3rd party code in the code base that must not be updated is undesirable. Basically it needs to happen in one go.

Ok, that is difficult to accomplish as it would require to patch the SQLite3MC code.

After having found the documentation on the salt, I implemented the old behavior with OpenSSL's KDF, as I didn't want to just copy&paste crypto code plus OpenSSL is already a dependency in my code. Using that combo I can generate the "old" key and rekey the database with the proper key. Fixed.

Good to hear.

Since I now have a workaround I'm good but am a little concerned whether or not others may run into this issue. Granted you mentioned this is more of a niche case (you are the expert here) so if you decide this not being worth the effort or cluttering the API for, then that's what it's gonna be.

Well, adding a pragma for handling this issue would open the path to create flawed databases forever. Therefore I don't intend to do that. In principle, converting affected databases is straight forward and easy to accomplish (given that the SQLite3MC command line tool can be used).

In case a larger number of affected users contact me I will reconsider the decision, of course. However, most users of SQLite3MC just use the default settings, and therefore I don't expect this issue to be a problem for many users.

Nevertheless, thank you for bringing the issue to my attention.

And also thank you for your extremely fast response. Much appreciated! 👍

You are welcome.