search

m4heshd / better-sqlite3-multiple-ciphers

better-sqlite3 with multiple-cipher encryption support 🔒
MIT License
140 stars 27 forks source link

.backup is not creating a encrypted db #46

Closed Jun90925 closed 1 year ago

Jun90925 commented 1 year ago

How can I create a encrypted backup by using .backup() ?

m4heshd commented 1 year ago

Hi @Jun90925,

The backup API is not supposed to create an encrypted copy. Once the DB is decrypted for usage, it's simply a regular DB for the application. Also, it supports in-memory DBs which aren't supported by encryption extensions.

You can just re-encrypt the backup after the backup process finishes.

utelle commented 1 year ago

The backup API is not supposed to create an encrypted copy.

This is not completely correct. SQLite's backup API takes 2 database connections, source and target. In principal, both can be configured to use encryption. However, the backup API is limited in respect to the combinations of encryption schemes.

If source and target use the same encryption scheme, using the backup API will always work. But if the encryption schemes for source and target are not the same, using the backup API may fail. This post contains a matrix showing which combinations will work, not work, or work with restrictions.

Especially, using the backup API to create a backup of an encrypted database without configuring the encryption scheme for the target database, will usually not work.

Conclusion: Don't use the backup API to change the encryption scheme.

To change the encryption scheme of a database there are 2 ways to accomplish that:

  1. Use SQL command PRAGMA rekey (after having configured the encryption scheme) This will re-encrypt the database in-place. However, depending on the selected encryption scheme a temporary copy of the database will be created.
  2. Use the SQL command VACUUM INTO (configure encryption scheme of target via URI parameter) This will create a copy of the database using the new encryption scheme.

Once the DB is decrypted for usage, it's simply a regular DB for the application. Also, it supports in-memory DBs which aren't supported by encryption extensions.

For in-memory databases and temporary databases SQLite doesn't support encryption. This is true for all SQLite encryption extensions I know of. Therefore the backup API can't be used to copy an encrypted database to an in-memory database.

You can just re-encrypt the backup after the backup process finishes.

The drawback of that approach is that an unencrypted database will be present on disk at least for a certain period of time. This may impose a security risk.

Recommendation:

  1. Use the backup API with the same encryption scheme for both, source and target database, or
  2. Use VACUUM INTO to create a backup copy with a differing encryption scheme