A tip to use dates in a WHERE clause in a AIR admin tool for SQLite GradientComponents: showcase
Apr 30

 

The SQLite Sorcerer, in its v1.2, now provides the ability to create and open encrypted databases. To reencrypt them also. Here is how the feature is addressed in the tool.

The first point for me was to ensure the compatibility with LITA. For this, as I don’t want to reinvent the wheel, I decided to test the Paul Robertson’s encryption class and its simple version by David Deraedt.

Excepted that the Paul’s version can use "salt" that adds a security level, I have seen no difference in using one or the other for my application:

  • a hashed key is generated from a strong password
  • you can then open a database by different applications when you use the hashed key (this is actually just the way AIR works).
  • the same password won’t generate the same hashed key if you use different apps even if the encryption class is the same : LITA, the SQLite Sorcerer in debug mode and an installed version of the SQLite Sorcerer don’t give the same key… this is probably due to the SHA256 class of AIR but it was quite disappointing for a feature I wanted to implement. Or maybe, did I misunderstood something in the encryption mechanism.

My second point was to avoid or limit the necessity of storing the hashed key somewhere or maintain a hashed key repository. To copy/paste a key to open an encrypted database is really ennoying from my own point of view. So I decided to give the ability to open an encrypted database by entering the password that was used to generate the hashed key. Knowing that this is really limited because, you can reopen an encrypted database using the password only from the machine and the application you used to encrypt the database. So other developpers will still have to enter the hashed key and the creator of the database can still use its favorite password. Very useful if you are responsible for the database, quite useless if several developpers need to play with the db without sharing the same installation of the application…

Now, the different features in SQLite Sorcerer:

Creation:

When you click the Create button, you open a popup in which you can:

  • Browse your folders and type the name of your new database. Don’t forget the file extension (.db, .db3, .sqlite)
  • If you want to encrypt the database, so type a strong password.
    • you will get a message if the password is not strong enough
    • you can display the password as normal text by clicking the blue globe
    • you can copy an existing hashed key: this key will be directly used to encrypt the new database
  • Once you click the Create button, the hashed key is generated and immediately copied to the clipboard. You can paste it quickly into… what you want.
    • The hashed key is also kept by a owl during your work session in this database. So you can click the owl to copy the hashed again in the clipboard, the owl will then vanish.

Open:

As provided by the SQLite Sorcerer, you can open an encrypted database by:

  • browsing your files after a click on the cauldron
  • by drag and dropping your file to the cauldron from either your OS explorer or the "Recent files" list
  • by double-clicking the "Recent files" list

If the database is encrypted a popup is displayed and you can:

  • enter the password if your are the developper who created the database or if you just work on his machine
  • paste the hashed key of the database

Reencrypt:

From the popup button in the Schema Panel, just select Reencrypt. In the popup, type a new password or paste another hashed key.

If you used a password, the generated hashed key will be directly copied to the clipboard and sent to the owl that will keep it for you during the session…

Remember that

If you install a new version of the SQLite Sorcerer (non donators), your strong password won’t work again and you will have to use the hashed key to open the file again. So check your key repository before installing the new version. If you want to continue to use your usuable password, you can rencrypt your db with the new version and update the repository with the new key.

 

Don’t hesitate to send me your feedback on this new feature in the SQLite Sorcerer.

Have fun

Flattr this

Related posts

Written by Arnaud
Creative Commons License
Tags: , , ,

Share/Save/Bookmark

Help me improve my blog by rating this post or sending a comment.

not goodquite goodgoodvery goodexcellent (No Ratings Yet)
Loading ... Loading ...


Comments