As a New Yorker and a firm believer in mass transit and open data policies, I am happy to see the big news that came with yesterday’s unveiling of the new MTA website:

The agency’s new policy does away with time-consuming data-sharing procedures and burdensome licensing requirements. As of today, schedule, route and fare information are available directly from the MTA website in a standard format, and the data will be updated whenever service changes. Similar set-ups have allowed developers to create scores of transit applications for cell phones and web browsers in other cities, giving riders convenient access to up-to-date information.

Amen! Visit the new developer access page to sign up for accessing to the MTA’s data. Looking forward to seeing what evolves, as I’m sure that apps like Across Air and Exit Strategy are only the beginning. I’ve come to use Exit Strategy a lot, more often for the built-in Subway map than for the nearest door to the NQRW stairway. Maybe I’m a touch old-skool, but I prefer a well-designed map to the augmented reality thing. Get off my lawn! City Transit apparently provides you with a lot of similar getting-around features, plus service advisories, which is handy.

I’m guessing the developers at iTrans really love dynamic programming, because the shortest-path problem is not an easy one, but they’re not content to leave it up to Google, as their app provides this offline, running its calculations on your phone. I need to give that a shot. This particular app is nice because it provides you further info outside the Subway, like PATH and NJTransit (although how accurate that data can be is questionable, as there has been no sign of opening up coming from either NJTransit or the Port Authority).

We’ve posted a new project to Github: SQLCipher Manager.

I’ve got to make this brief because I have a ton of work to do, but we’ve recently split out a few chunks of re-usable code from our iPhone application Strip in order to facilitate sharing that code across multiple projects, both for the iPhone and for Mac OS X development. Most of that stuff will be staying internal, and wouldn’t be of much use to others anyway, but we decided to make SQLCipher Manager available as open source software under the same MIT-style license as SQLCipher itself.

The project on Github is an XCode project that builds a static library. You can use cross-project references to utilize this code from other XCode projects and to link against the static lib (required by Apple for us iPhone developers). There’s a great reference on doing this over at Mobile Orchard, so I’ll refrain fro going into the process here.

In short, it provides a singleton that handles your database connection to SQLCipher, keying, re-keying, notifying your app when the database has been created or opened, etc. This sounds like a no-brainer, but you end up re-using this code quite a bit, so it only made sense to define it and a delegate protocol:

@protocol SQLCipherManagerDelegate <NSObject>
@optional
- (void)didOpenDatabase;
- (void)didCreateDatabase;
- (void)didEncounterRekeyError;
- (void)didEncounterDatabaseError:(NSString *)error;
@end

This means you’d do something like this in your app delegate, to initialize the manager for your application, and handle schema events:

- (void)applicationDidFinishLaunching:(UIApplication *)application {
    NSLog(@"Setting up sqlcipher manager, and ourself as delegate...");
    // setup the db manager register as the delegate
    SQLCipherManager *dm = [SQLCipherManager sharedManager];
    [dm setDelegate:self];
    // set the databasePath property (required), use default iphone app documents directory
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *saveDirectory = [paths objectAtIndex:0];
    NSString *databasePath = [saveDirectory stringByAppendingPathComponent:CONFIG_DBNAME];
    NSLog(@"setting db path to %@", databasePath);
    [dm setDatabasePath:databasePath];

    [self launchLoginView];
}

# pragma mark -
# pragma mark SQLCipherManagerDelegate

- (void)didOpenDatabase {
    NSLog(@"Database opened");
    SQLCipherManager *dm = [SQLCipherManager sharedManager];
    NSUInteger schemaVersion = [dm getSchemaVersion];
    if (schemaVersion != CONFIG_SCHEMAVERSION) {
        NSLog(@"handing off to schema manager, found old version %d", schemaVersion);
        StripSchemaManager *schemaManager = [[StripSchemaManager alloc] initWithSQLCipherManager:dm];
        [schemaManager migrate:CONFIG_SCHEMAVERSION];
        [schemaManager release];
    }
}

- (void)didCreateDatabase {
    NSLog(@"Database created, migrating");
    SQLCipherManager *dm = [SQLCipherManager sharedManager];
    StripSchemaManager *schemaManager = [[StripSchemaManager alloc] initWithSQLCipherManager:dm];
    [schemaManager migrate:CONFIG_SCHEMAVERSION];
}

- (void)didEncounterDatabaseError:(NSString *)error {
    NSLog(@"Database error! %@", error);
    SQLCipherManager *dm = [SQLCipherManager sharedManager];
    [dm closeDatabase];
    abort(); // ouch, yo, but it leaves us a crash log for later
}

- (void)didEncounterRekeyError {
    [self lockApplication];
    [self presentError:[NSError errorUsingDatabase:@"An error occured rekeying the database. Please login again using your original password" reason:@""]];
}

Your login view controller (or whatever you are passing control to as the application sets up) would actually do the opening, or creating, of the database. Ours looks something like this:

- (IBAction)submit {
  SQLCipherManager *dm = [SQLCipherManager sharedManager];
  BOOL databaseExists = [dm databaseExists];
  
    // just bail if the submitted password is invalid
    if (![dm passwordIsValid:passwordField.text]) {
        errorLabel.text = @"Invalid password";
        self.passwordConfirm = nil;
        passwordField.text = nil;
        return;
    }
    
    // if this is the first time through, require confirmation
  if(databaseExists == NO && !self.passwordConfirm) {
        self.passwordConfirm = passwordField.text;
        passwordField.text = nil;
        passwordField.placeholder = @"Confirm your password";
        [passwordField becomeFirstResponder];
        return;
    }
    
    loadingImage.hidden = NO;
  CFRunLoopRunInMode (kCFRunLoopDefaultMode, 0, true);

    // seed OpenSSL RAND (removed)
    
    SecureAppDelegate *appDelegate = (SecureAppDelegate *) [[UIApplication sharedApplication] delegate];
    if (databaseExists) { // what mode are we in?
        // attempt login, we only have passwordField present on screen
        if ([dm openDatabaseWithPassword:passwordField.text]) {
            [passwordField resignFirstResponder];
            [appDelegate launchAppView]; // push new view controller, etc... wait we have no navigation controller...
        } else {
            errorLabel.text = @"Incorrect password";
            [passwordField becomeFirstResponder];
        }
    } else {
        if ([self passwordIsConfirmed]) {
            [passwordField resignFirstResponder];
            [dm createDatabaseWithPassword:passwordField.text];
            [appDelegate launchAppView]; // here again we need to get our navigation controller going
        } else {
            errorLabel.text = @"Passwords didn't match";
            [passwordField becomeFirstResponder];
        }
    }   
    passwordField.placeholder = @"Access password";
    self.passwordConfirm = nil; // wipe fields
    passwordField.text = nil;
    loadingImage.hidden = YES;
}

Those are the basics of using SQLCipher in a Cocoa Touch application, and the approach is easily ported to Mac OS X. Can’t go into the various details right now, hopefully it’s explanatory enough. Questions should be posed on the SQLCipher-Users mailing list. Bugs and enhancement requests should be filed using the Issues tracker on the Github project.

Back in 1997 we released the first version of our mobile password manager and data vault Strip (Secure Tool for Recalling Important Passwords) for PalmOS. It quickly grew to be one of the best and most widely recognized security applications for the platform, gathering accolades from the likes of ZDNet and CNN. A few years ago we dropped support for PalmOS Strip after the decline of the Palm platform. Then, last year, we resurrected Strip by bringing a completely redesigned system to the iPhone sporting a flexible data model and new open-source encrypted database engine.

Yet, many former PalmOS Strip users have let us know that they are still trapped on their old devices, in dire need of a way to export information from their Palm databases. Some people have switched to Strip for iPhone, others just want to back up their information in case of device failure.

Today we are pleased to release a free utility that can export your legacy PalmOS Strip databases out to a simple Comma Separated Value file. Once your databases are exported you can print them, open them in Excel or import them into a separate program. Most importantly, these export files will be directly importable to the iPhone when we release our first Strip desktop sync tool in the coming months.

How To Export

Note: This exporter only works with Palm Strip 1.0 or 2.0 Databases. If you are not running one of these versions you should upgrade first. This utility is not intended for use with the iPhone version of Strip (we will be releasing a iPhone sync package soon that will provide similar functionality).

Start off by downloading the exporter program:

• Download Palm Strip Export for Windows
• Download Palm Strip Export for Mac OS X

Save the program to your computer and then run it:

1. Enter the password for your Palm-Strip database. This is the same password you would enter into the program normally.
2. Click the “Choose Directory” button, and navigate to the location of your Strip Databases. These will usually be in your Palm desktop install folder, usually something like C:\Program Files\palm\<username>\backups.
3. Click the “Save As” button to choose the location to export the file.
4. Click the “Export To File” button.

The system will then export your entire database out to a CSV file at the selected location. Your data will be left intact, with the exception that line breaks will be removed from Note fields to allow the export. Open your file in a spreadsheet like Excel or in a text editor like Notepad to verify the results.

Credits & Code

This new exporter tool is based on the pioneering work by David Dribin’s perl-strip, an early exporter for Strip databases. We’ve taken his great Palm::Zetetic::Strip module and forked it on GitHub. Our updates include the use of newer digest libraries and updates to support the full Strip 2.0 database format (including service types). We’ve also released the source to the palm-strip-export application on GitHub.

Terralien’s Nathaniel Talbott has an interesting article up on their blog about an activity they’ve termed nomading, or nomadic programming. Basically, Terralien is what you might call a remote company, in that a good number of their people work from geographically distinct locations, often not meeting in person but keeping in touch and coordinating their projects and work over the Internet. We work this way, too, so we’re always interested when other firms write about their experiences.

Talbott’s article is interesting in that Terralien is using this concept of nomading to help mitigate the problem of isolation one can experience remoting:

But no matter how much we appreciate the flexibility of working from wherever, we still often feel the need to spend some time in the same place with fellow geeks. The wife and kids can only take so many explanations of the latest cool hack we pulled off, and a change of scenery can really help get the creative juices flowing. As with so many other remote workers, we started heading out to a coffee shop on occasion, which was fun, but still didn’t quite cut it.

I can tell you from first hand experience that a year and a half of working out of my apartment was a pretty isolating and eventually depressing thing. I’m sure it’s not a problem for some people, but I think most people who try it for any considerable length of time run up against this problem. It comes down to a pretty simple notion — you need to get out of the house, and by the end of your work day, you’d ideally want to enjoy being home.

Terralien’s Matthew Bass, who coined the term nomading, describes how he arrived at his solution:

I don’t mind being alone to a certain extent, but after a few straight weeks it can get pretty lonely. Recently, I’ve started doing what I’ve termed “nomadic programming.” Namely, spending the day roaming between various wi-fi hotspots instead of working from home. This has worked really well for me. So well, in fact, that I think the concept needs to start spreading.

I actually disagree, having tried nomading when I started to get stir-crazy. While I do think it’s a good first step for anybody who’s been working in isolation, you quickly run into some problems doing this that make it a somewhat incomplete solution to the problem. There’s really nothing that comfortable about hanging out in one or more coffee shops all day long, buying obligatory coffees and snacks, using their power outlets under the glare of the manager, not being welcome for quite so long, flakey network connections, and finally, do you really want to leave that $2000 computer alone by itself in Cafe Grumpy while you go to the bathroom? Do you want to take it in there with you, for that matter?

Coworking has been the ideal solution for us here at Zetetic, as we’re now working out of two coworking locations, Williamsburg Coworking in Brooklyn, NY and Indy Hall in Philadelphia. One of our guys is planning to open a new space soon with some partners (so I can’t reveal any details), and our founder Stephen Lombardo has been thinking for some time of starting one in central NJ (if you’re in central NJ and you are interested, get in touch!). It makes us really happy, keeps us extremely productive, and we can’t recommend it enough.

We pay monthly fees to use our spaces — it’s a lot cheaper than renting commercial office space. We have ergonomic chairs, we have personal storage areas where we can keep keyboard trays and the like. We have a fridge, a kitchen, wifi, and a really fantastic social group of thinkers, artists, programmers, writers, you name it. Distractions are low, productivity is high, we have people to bounce ideas off of who’ve become our friends, we even prepare lunches together sometimes to save money (and because it’s fun, Zane’s a great cook). We even have an excellent library of somewhat “alternative” reading materials.

When I started working here, my productivity shot up like woah. In addition, I’ve learned how to screen print and pickle vegetables. I’ve made some great friends and I get to bounce ideas off extremely smart people. And this is a very large community — our space is part of a network of locations all over the world, and any of us is welcome at those spaces if we’re traveling (read more about Coworking Visa). I think the main reason people here get along so well is that there’s no one here who doesn’t want to be here, it’s the total opposite of being trapped in an office. And having done a bit of nomading myself, it’s a lot less stressful.

These coworking spaces are popping up all over the country, the world, and they work. They’re even potentially lucrative businesses, although I prefer our space’s non-profit setup. If you’re considering nomading, we encourage you to try coworking as well, you can find many locations by searching the wiki.

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.