Desencrypt an encrypted SQLite database and vice versa

May 11

Desencrypt an encrypted SQLite database and vice versa

The choice of using an encrypted database or not for an application is not easy as the operation at database level can’t be reverted. But actually you still can "desencrypt" an encrypted SQLite database and vice versa, later if you change your mind. The main principle is that a new database file has to be created and the content of the old one to be transferred into the new one. This requires some manual operations but nothing too costly.

Here is a short procedure, that of course is supported by the SQLite Sorcerer. I will take the case where you have an encrypted database that you want to desencrypt.

Create an empty non encrpyted database, the one that will receive the content of the encrypted one.
Launch the comparison process (only to view both database files during the operation).
Open your encrypted database in the second visualizer using the password or the encryption key.
Get its SQL code.
Copy it to clipboard.
Open the query Panel of the new database file, in the 1^st visualizer.
Paste the copied code
Remove (copy them in clipboard) trigger creation statements
Execute the batch query
Return to step 7 for each trigger creation statement

You should now have the same structure in both files

Now, for each table of the encrypted file (in the second visualizer), open the table.
Show its data.
Export data to a file (keep default options).
Once all table data are exported, import all csv files in the corresponding tables of the empty new file.

And you should now have a filled non-encrypted copy of your original encrypted database file, with the same structure and containing the same data. Just test your new non encrypted database, of course.

I can streamline this process in a future version of SQLite Sorcerer. A dump creation feature would be great but quite touchy because of data types or a "transfer to" feature may be…