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.

Since Tuesday’s outage on Tempo, we’ve been working hard to not only prevent that from happening again, but to further improve our system’s response time and optimize our resource utilization. This kind of spring cleaning and infrastructure maintenance was long overdue and took a backseat while we were building out the most recent update.

I’m happy to report, and hopefully you’ve noticed, that we’ve managed to not only stabilize our runaway memory consumption, but to dramatically improve response time over all (go Unicorn!). Now that things have stabilized and in fact improved, we’ll be working to further cut down on memory consumption in our application code. It’s very easy to have memory bloat in Ruby code, because Ruby doesn’t really release memory back to the system, so we’re profiling our code to cut back where ever we can.

As an aside, there’s this great Engine Yard post about memory bloat in Rails apps with pointers for pinpointing problem spots and cutting back. However, I take issue the notion that “it’s not a memory leak, it’s bloat.” That’s a lot of garbage, not returning unused memory to the system is a leak, and to pretend otherwise is a laughable bit of delusion. I agree that it’s important to recognize this as a known flaw of the platform, but to start thinking of it as a feature is probably not a good idea.