iOS Quick Tips: Avoiding SQL injection attacks with parameterized queries in sqlite3

Since my current app requires a sqlite3 database, I naturally hit up Google for some sqlite3 tutorials. I found plenty of tutorials, but sadly, most of them were poorly written and full of dangerous code. The problem? SQL injection.

Most of the tutorials I saw had code that looked something like this:

NSString* unsafeSqlString = [NSString stringWithFormat:
     @"UPDATE record SET name=\"%@\" WHERE id=%@",,];

What’s the problem here? Good ol’ Bobby Tables. Depending on what is, your SQL statement may do something that you weren’t expecting (you didn’t want to delete all the records in your table? Should’ve thought of that before writing such unsafe code!).

The correct way to do this is with parameterized queries. In sqlite3, you use parameterized queries by replacing your values with ?’s in your SQL statement. So the above unsafe SQL statement becomes this:

NSString* safeSqlString = @"UPDATE record SET name=? where id=?";

You then prepare a SQL statement with your query:

sqlite3_stmt* statement;
if(sqlite3_prepare(database, [safeSqlString UTF8String], -1, &statement, NULL) != SQLITE_OK)
  // Add your error handling code here

Finally, you bind all of your parameters to set the actual values that will be used when your query is executed:

if(sqlite3_bind_text(statement, 1, [ UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK)
  // Add your error handling code here
if(sqlite3_bind_int(statement, 2, record.key) != SQLITE_OK)
  // Add your error handling code here

For more details on all the different sqlite3_bind functions, see the SQLite documentation.

Finally, you execute your safe, injection-free SQL statement:

if (sqlite3_step(statement) != SQLITE_DONE)
  // Add your error handling code here

Sure, avoiding SQL injection attacks in a small iOS app is probably less of an issue than avoiding them in a mission-critical website, but that’s no excuse to do it wrong. And here’s another, even more obvious reason to do it right: If any of your fields have a ” in them, your unsafe SQL statement will fail. The parameterized statement handles quotes correctly.

iOS App Postmortem: BBQ Menu

Now that January’s app – BBQ Menu – is available on the App Store, I thought I would do a quick postmortem for it. Postmortems will likely become a regular feature of this blog, and I suspect that they will normally be a little bigger than this one.

Development Time: ~15 Hours. This is a pretty rough estimate as I wasn’t keeping track of time. I have since started using Thyme to track my hours.

Review Time (time spent in the iOS app submission queue): 7 days

New technologies used: Everything! As this was my first app, everything was new, from UITableViews to Xcode to distribution and deployment.

Challenges: Aside from getting acclimated to an entirely new set of development tools, the hardest part was figuring out where to draw a line in terms of features. It’s a delicate balance between the desire for more features, and the desire for a simple, easy to use application.

Future plans: There are a number of bug fixes coming in the immediate future, and possibly some new features and a prettier UI shortly thereafter. If you have any feedback regarding BBQ Menu, please drop me a line!

iOS Quick Tip – UITableView with static cells not showing up

If you’ve ever tried to create a UITableView with static cells, you may have run across some difficulties in getting your cells to actually show up.

The trick is to remove the following row, cell, and section methods from your UITableViewController subclass. They are automatically added by Xcode, but if you are using static cells, you don’t want them in your class.

– (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView

– (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section

– (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath

Once you have removed these methods, your cells will show up correctly.

January’s Goal – Complete

Sort of.

I submitted January’s app to the app store on the 30th, but it’s still waiting for review. So although it’s not technically in the app store yet, I consider this a pretty successful month. And I’m already hard at work on February’s app – this time using Thyme┬áto keep track of exactly how long it takes to build.

I’ll put up more info on each of the apps as they are released

iOS Pain Points: Renaming folders in Xcode

Xcode, in all its mind-bogglingly incomprehensible wisdom, makes the simple act of renaming a folder a chore. If you’ve ever used any IDE ever released on any other platform in the history of computing, you would think it would be as simple as right-clicking (er, command-clicking) on the folder and selecting ‘Rename’. Sadly, no. There’s no rename option. But wait – there’s a ‘Group Name’ option in the File Inspector. Just change that and… nope. That just changes the name as it appears in Xcode, not the name of the actual folder on disk. What you actually appear to have to do is rename the folder in Finder, then click on the little folder icon in the File Inspector and point the group to the new folder.

Oh, and don’t even think about renaming your project.┬áThat would just be stupid.