2 small SQLite questions

Getting Started
1

Hi, I created a database some time ago using Xojo 2016r3 with encryption on it. Now I want to use the database for a new development and want the database in clear while developing. I also use DB Browser for SQLite 3.10.1 now with cipher version 3.15.2.
Now I am faced with 2 problems:

  1. How can I make the DB permanently in-encrypted again?
  • I tried in the code to decrypt the database and not encrypt it again: No cure
  • I tried in the code to decrypt the database, update one field, committed it and did not end with db.encrypt: no cure

How can I remove the encryption again?

  1. I tried to open the database with DB Browser 3.10.1, entered the correct password, but that failed with: Could not open database file. Reason: Invalid file format.
    I have an old “clear text” copy of the database, created when I developed the desktop app and that one opened without any problem. However, I want to open the current database, because this one does have recent data and I want to see that data. I have to update the app and add new tables to the database based upon info inside the current database.

What can I do to read the database again? Do I need to find an old DB Browser for SQLite of the same period of Xojo 2016r3?

2

I think you have to use the xojo version used to create the encryption.
Then set db.EncryptionKey = “yourpassword” and then connect and call db.Decrypt().

They added some notes in the docs about (used xojo versions) this:
http://documentation.xojo.com/index.php/SQLiteDatabase.EncryptionKey
And here
http://documentation.xojo.com/index.php/SQLiteDatabase.Decrypt

3

Xojo doesn’t use Cipher, it uses SEE (SQLite Encryption Extension).

The two are not compatible.
DB Browser won’t be able to open database encrypted with Xojo.

See Derk’s response above to decrypt your database.

4

If you use Cipher, you can open such a database with MBS SQL Plugin by loading the cipher version of the sqlite library.

5

Once you encrypt an SQLite database you lose the ability to read it with generic SQLite tools. I suggest you write a Xojo tool to decrypt/re-encrypt a copy of the database.

6

OK, thank you all for your answers. Point 2 is understood. Whenever a database is encrypted with Xojo, you are not able to open it anymore in DB Browser for SQLite.

Concerning point 1:
Encrypting and decrypting the database in the Xojo desktop app (on Windows 10) goes smoothly. Not a problem within the use of Xojo.

However, I want to know how I permanently can decrypt the database, so I will have a clear text database again. Is there a way to do it?

When I decrypt the database now and do not encrypt it anymore, the database is still encrypted on disk. Even after an update in the database. (I open the database, when connected I decrypt the database, write the update, commit the update and do not use db.encrypt after the update. Still the database in not in clear text and asks for a password when I try to open it.)

I do the encryption/decryption they way as described in the Xojo docs (see the above links, thank you Derk)

7

Yes. Open the database using the original key and then set the encryption key property to an empty string.

8

Thank you Greg, so simple I did not think about it :slight_smile:

Thank you all for your responses. it was all helpful :slight_smile: