SQLCipher 4, plaintext header, and key salt problem


#1

I started to play a bit with the new features of SQLCipher 4. Using the new pragma cipher_plaintext_header_size, I stumbled over a problem which seems to be related to the key salt.

I opened a new, empty database, then issued

PRAGMA key=‘password’;
PRAGMA cipher_plaintext_header_size=32;

Then I created a table and entered a few values for testing purposes. Thereafter I closed and reopened the database connection, and entered again the 2 above pragma commands. However, on trying to access the test data I got the error message SQL logic error.

I started again from scratch, but now I used

PRAGMA cipher_salt;

to retrieve the key salt. I saved the key salt in my editor. I closed and reopened the database connection, but now I entered the following pragma commands:

PRAGMA key=‘password’;
PRAGMA cipher_plaintext_header_size=32;
PRAGMA cipher_salt=“x’saved key salt value’”;

This allowed me then to access the test data.

Obviously the key salt is not saved somewhere in the database, if one uses the pragma cipher_plaintext_header_size. Is this on purpose? If yes, it should be documented that one has to retrieve the key salt generated when creating the database, or that alternatively one has to generate the key salt and to set it explicitly manually on creating the database. Otherwise one ends up easily with an encrypted, but inaccessible database.

Regards,

Ulrich


#2

Hello @utelle - thanks for checking out SQLCipher 4. Yes, the behavior you’ve observed is correct. When specifying a plaintext header, the application becomes responsible for managing the salt and providing it back to SQLCipher each time a database was opened.

For additional context, much of initial discussion about this feature was originally captured in SQLCipher issue 255. You are abosolutely right about needing this to be captured in the documentation, so I’ve updated the official API documentation about this feature to include the problem it addresses, information about the implementation, and it’s intended use.

Please take a look and let me know if this change sufficiently addresses any questions or concerns.


#3

@sjlombardo, thanks for your quick response. Now I better understand the reasoning behind the new pragma cipher_plaintext_header_size and how to use it together with pragma cipher_salt. IMHO the solution for SQLCipher issue 255 is a bit cumbersome, but probably there is no simpler way to overcome the issue.

Please take a look and let me know if this change sufficiently addresses any questions or concerns.

Thanks for updating the documentation. IMHO the documentation is detailed enough to explain the feature and its use. However, there are a few spelling mistakes - following a quote from the docs with corrections in bold face:

This PRAGMA is primarily intended for use on iOS when a WAL mode database will be stored in a shared container. In this special case iOS actually examines a database file to determine whether it is a SQLite database in WAL mode. If it is, then iOS extends special privileges, allowing the application process to maintain a file lock on the main database while it is in the background. […]

[…] Then the next time a database is opened it can be used to provide the salt value back to SQLCipher in BLOB format (16 bytes, hex encoded):

I would assume that the official SQLite Encryption Extension (SEE) should suffer from similar problems as discussed in SQLCipher issue 255. However, I haven’t seen any reports indicating this. I would be curious to know whether and - if yes - how SEE addresses this problem.


#4

Hello @utelle. Thanks for the suggested corrections - I forgot to spellcheck before pushing the documentation change - my mistake. Those errors have been fixed now.

If you have suggestions for alternate ways to address this issue we’d certainly be open to hearing about them. Note that this is a technically undocumented iOS restriction and it couldn’t be changed on already shipped iOS versions. Thus, we don’t really consider resolution in the OS upstream as a viable short-term fix (though this and several other options were originally discussed with the team from Signal that reported the problem).

The SQLCipher team does not have any direct knowledge of SEE. Because SQLCipher is a clean-room design and implementation we deliberately avoid technical discussions related to SEE. You are certainly encouraged to inquire about this with the SEE team, and you are more than welcome to share our findings with them. However, we would respectfully request that you avoid posting any technical information about SEE (e.g. whether the same problem happens with SEE, how SEE deals with this or doesn’t deal with it, related implementation details, etc.) on this site. Thank you for your understanding about this.

Let us know if you have any other comments or questions about this feature, and thanks again for your feedback.


#5

Hi @sjlombardo,

At the moment I don’t have a concrete suggestion how to address the issue in a less cumbersome way. Of course, I’ll let you know if I can come up with a feasible idea.

Neither do I. My knowledge is restricted to what is publicly available on the SEE website.

I’ll consider to contact the SEE team.

Sure.

One question I have is what is the right channel to contact you to discuss technical issues or proposals. Should this be done in this discussion forum or on github or somehow in private?


#6

Hello @utelle - That is a great question.

  • For general technical discussions about SQLCipher we like to use the discussion site we’re conversing on now. You are always welcome to create Topics here and engage in discussion with the SQLCipher team.
  • For reproducible bugs, defects, and code issues of a non-sensitive matter we use GitHub issues. This is useful because of the tracking of issues to commits, pull requests, etc.
  • For private or sensitive discussions and disclosures, we are available at support@zetetic.net (with optional GPG).

If you are ever unsure about the right place to post something, please contact us at support@zetetic.net and we’ll evaluate and recommend on a case-by-case basis.


#7

Thanks a lot for the detailed answer.