Note: the content in this post is outdated. Please use our new recommendations for how to encrypt a plaintext database with SQLCipher.

We’ve fielded several questions on the SQLCipher mailing list recently about how to convert a standard SQLite database to an encrypted SQLCipher database. This is a pretty common requirement for applications with existing databases that need to be converted to use SQLCipher full database encryption.

Attached Database (Recommended)

SQLCipher 1.1.1 added support for attaching an encrypted database to an unencrypted database to copy data between them. For instance, assume you have an standard unencrypted SQLite database called unencrypted.db with a single table, t1(a,b). To create an encrypted copy you could open up the databases and run the following:

ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'secret'; -- create a new encrypted database
CREATE TABLE encrypted.t1(a,b); -- recreate the schema in the new database (you can inspect all objects using SELECT * FROM sqlite_master)
INSERT INTO encrypted.t1 SELECT * FROM t1; -- copy data from the existing tables to the new tables in the encrypted database
DETACH DATABASE encrypted;

It is fairly trivial to get a list of all schema objects from the sqlite_master table you would just repeat the CREATE TABLE / INSERT cycle once for each table. Then you can delete the unencrypted database and re-open the encrypted version with the sqlite3_key or PRAGMA key. This approach requires more work that just calling rekey, but it is be much more stable across versions.

In the mean time, we are working on finding a way to improve the built in rekey functionality in future versions. Keep an eye out here, or join the SQLCipher mailing list to keep up to date on the latest development.

We’ve been really busy over the past week finishing up some big enhancements to SQLCipher, the open-source full-database SQLite encryption extension that powers Strip, our Data Vault for the iPhone.

The latest version SQLCipher 1.1.1, is based on the most recent release of SQLite (3.6.21), which has a many improvements and bug fixes. In addition the new SQLCipher features include:

1. Code clean up to allow compilation with VS.NET on windows.
2. Enhancements to the codebase to allow encrypted databases to be attached to unencrypted database for the purpose data migration
3. Resolution of a minor memory leak in the pager subsystem
4. Documentation and test enhancements

The newest build makes SQLCipher truly cross-platform – it’s now been used successfully under Mac OS X, iPhone, Linux, and Windows (across MinGW, Cygwin, and native VS.NET). The new version is a drop in replacement for previous releases, so we’re recommending that all SQLCipher users upgrade to the latest.

Check out SQLCipher on GitHub.

Today and tomorrow I’ll be bringing you some news and updates related to Strip, answering some common email queries, and providing an overdue update on where we’re at with the desktop version. Before I get into the upcoming stuff, however, we could use your help! Strip for iPhone been nominated in the Best App Ever awards, under the Best Productivity Enhancer category. As I know there are a lot of you out there who have quite an affinity for Strip, mind clicking over to the page and voting for us?

Nominate
Strip – Password Manager & Data Vault…
for
Best Productivity Enhancer

We really appreciate your continued support and enthusiasm for the platform. Stay tuned for updates!

Having been a big fan of CLERKS and most things brought into this world by Kevin Smith, I was delighted to receive an email this morning telling us that our NSDate category made itself into the credits for the new Kevin Smith iPhone app by DenVog. It’s exactly what you’d expect from Smith — it’s silly, awesome, immature, and well done. I can’t tell you how many times I watched CLERKS in high school, so being exactly that mature still, I downloaded it immediately.

This is my favorite part:

Weekend terror will ensue. None will be spared!

NSDate (Helper) is continually being used in our own Cocoa projects, and I’m constantly mucking with it and pushing new changes to the github project. It’s probably due for a touch of code clean-up.

I love seeing the change summary after a large and major branch is merged into the master branch. I think this is our best one yet:

282 files changed, 11893 insertions(+), 12129 deletions(-)

We successfully deployed the Tempo update “Teams” last night. There are a few lingering issues that we’re working hard to resolve. If you notice anything funny, please get in touch so we can get to work on it.