When password from sqlite3_key is applied

I have noticed that if an application opens a database and then calls the sqlite3_key() C function, the password is not really applied until the application attempts to read or write data. I did not notice any explicit documentation of this behavior, which I think is needed. My one remark is that this behavior could add a little more work to unit testing of password error handling in application code and higher-level wrapper libraries.

I noticed another gotcha that if an application opens a new database and sets a password using sqlite3_key(), the database file would have zero size until the application starts writing data. I did not notice any documentation of this, which I think is needed as well. I think this could also add more work to unit testing of password error handing.

My recollection from the past is that android-database-sqlcipher did immediately apply the password when opening a database, not sure at this point whether or not this is still the case. I wouldn’t mind documentation of this behavior as well.

I would like to thank the SQLCipher team for the efforts of developing and supporting this library over the years.

Hello @brodybits - that’s for getting in touch about this. We have previously documented the behavior of sqlite3_key() and PRAGMA key, with respect to when key derivation occurs, in the API documentation.

One of the sections in that documentation refers to Testing the Key. The recommended way of doing that is to execute a query that requires parsing of first few encrypted pages by querying the schema, i.e. SELECT count(*) FROM sqlite_master;. By performing that query after keying you can effectively trigger the key derivation so that it occurs prior to the using the database connection for application logic. That is exactly what the android-database-sqlcipher database wrapper does.

Please take a look at that section and let me know what you think.

Thanks @sjlombardo, I can’t believe I missed that.

I wouldn’t mind a comment like “see above for how sqlite3_key() works”, and maybe a more explicit explanation near the beginning that the password is actually not applied and therefore not validated before actually reading or writing data.

I guess I better read the API document more slowly next time.