Just started using “DB Browser for SQLite” to view my database. Previously I used an old program called “MESASQLITE”. Using “DB Browser for SQLite” I encrypted the database with a password, like I did with “MESASQLITE”.
Now my MAC app will not open the database, it doesn’t seem to like the “DB.EncryptionKey” line which I always used prior. It opens fine if I don’t encrypt the database and remove that line.
DB = new sqlitedatabase
DB.databasefile =new folderitem( g_DataBase_Filename)
DB.EncryptionKey = “mypassword”
My understanding is that there are different, incompatible ways to encrypt a SQLite database, and I guess the DB Browser’s method is not compatible with Xojo’s.
Well you would do. SQLite, when opening a database, will look at the first few bytes of the DB and expect to see a string there such as “SQLite3”. If it doesn’t find it then it politely suggests that you go peddle your papers.
You might enquire of the DB Browser for SQLite what they use to encrypt it; the standard distribution doesn’t include encryption, IIRC, so they must have downloaded the encryption extension but I can’r remember who maintains that. You may wish to look at the SQLite website at:
Given the way I open my DB with “EncryptionKey” above…
“MESASQLITE” - EncryptionKey DOES work
“DB Browser for SQLite” - EncryptionKey does NOT work
I’m looking to use an updated free (or minimal cost) SQLite viewer, and it seems “DB Browser for SQLite” may not be my solution. Any other recommendations for a viewer that their Encryption is compatible?
There is a commercial product, build with Xojo, which supports the SEE (SQLite Encryption Extension) and can open encrypted SQLite databases: SQLiteManager. Its not really up to date (based an old sqlite version), but it works and is a helpful tool for me…
yes, after i thougth my question on how to open sqlite aes with third party seems suspicious too lol
i was just asking for thirdparty readers, because if the xojo app iteself won’t launch for some reasons, you can still open data even encrypted. that why xojo is great, your data is not dependant from xojo it uses standard like sqlite, and data is more imprtant than software, no matter what devs say
so i’m thinking about using aes in sqlite later, but for security it seems like one can not open a aes data on thirdparty, or is it ? with password of course ;p
so it means when encrypted sqlite dbs if you can’t launch xojo app (imagine opening an xojo.app
made in 2022, in 10 years…), data is lost
so maybe it would be necesary to provide two xojo apps, the main one, and a simple one for reading and exporting data as xojo source project…
Thanks. Makes sense. I tried the encryption key with with “aes256:”. No Luck.
Like PhillippeP - I simply want an external viewer that I can view/edit/make simple data base changes outside of XOJO. But I do want to be able to Encrypt the database and have XOJO open that database.
Status
“MESASQLITE” - EncryptionKey DOES work
“DB Browser for SQLite” - EncryptionKey does NOT work
“SQLiteManager” - EncryptionKey DOES work
Rick - Thanks
As of now I’ll use “SQLiteManager” to view/maintain/encrypt my database. But I like the thought of building a simple encrypt tool in XOJO. Thats my next project.
All - Thanks again for your thoughts/suggestions. This exercise has helped me.
I start by creating the file and add the needed TABLEs by code,
I standardize my data (I will need these in dot 3 below)
Then I add Import and Export methods,
I take some times to be sure I do not forget something.
Why ?
Because, if I forget a Column in my TABLEs, do some error(s) in the import Method, etc. I can trash the .sqlite file, modify the code / squash any bug and import the data again.
At that stage, I may want to look at what there really is in my .sqlite file. So i will add some features (at first, I created another application, but this can be added in the original project) using:
I even set code to export the TABLEs definitions to disk as text (not the data, the SQL code used to add the TABLEs: it looks like “CREATE TABLE foo()”…).
One can also save the .sqlite definition graphically (Master TABLE: list of the TABLEs names, TABLEs Column contents). Examples have been shared in this forum in the previous years.
And, if you are afraid to loose your data in the future, you can also make data backup (the .sqlite file and Export from each and every TABLEs as text); so in the future, you will be able to go back and make builds with whatever Xojo version available then.
Now, your mileage may vary: you are free to implement your own strategy using any tool: your own (if you have a bit of time) or third party if you prefer to rely on them.
