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 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.

iOS Pain Points: Multiselect of UITableViewCell children in Interface Builder

This is a quick one.

Multiselect in a UI designer is a pretty standard operation. If I want to change a property on a number of controls at once, I should be able to simply ctrl-click them (or command-click, or option-click, and so on) to select them all, and change the property.

Unfortunately Interface Builder doesn’t seem to support multiple selection on controls in different UITableViewCells. Attempting to do so always results in the first control being deselected.

However, there is a workaround, which is why this entry is really half ‘iOS Pain Points’, and half ‘iOS Quick Tips’. It turns out you can multiselect across table cels by command-clicking in the Document Outline window on the left.

Not entirely intuitive, but it does the trick!

iOS Quick Tip: Setting the default view controller on a storyboard

Figuring out how to set the default view controller on a storyboard is surprisingly unintuitive, especially as someone new to iOS development. It seemed logical to me that it would be some sort of setting on the storyboard itself. However, it’s actually the exact opposite – it’s a setting on the view controller.

This is somewhat bizzare as it means setting a property on one view controller can affect other view controllers, but nevertheless, it’s quite simple. Here’s what you need to do:

  • Open your storyboard
  • Click on the view controller corresponding to the view that you want to be the default view
  • Open the Attributes Inspector
  • Check the Is Initial View Controller check box in the View Controller section

Voila! Your view controller is now the default view controller for this storyboard.

iOS Quick Tip: Filtering a UITableView with a search bar

Step 1: Add a UISearchBar to your UITableView and create an outlet for it.

Step 2: Add properties for the array of all the table data, and the array of filtered table data.

@property (strong, nonatomic) NSMutableArray* allTableData;
@property (strong, nonatomic) NSMutableArray* filteredTableData;

Step 3: Assign your search bar’s delegate to your controller class.

    // ...Do initialization stuff here...

    searchBar.delegate = (id)self;

Step 4: Implement the searchBar:textDidChange: method from the UISearchBarDelegate protocol. This will let you filter your list as you type. If you want to filter when the search button is clicked, use the searchBarSearchButtonClicked: method instead.

In this example, we are searching through a list of foods with names and descriptions to see if the match the search text. If they do, we add them to our NSMutableArray containing our filtered foods. We also set a flag that indicates whether or not we are currently filtering the list.

-(void)searchBar:(UISearchBar*)searchBar textDidChange:(NSString*)text
    if(text.length == 0)
        isFiltered = FALSE;
        isFiltered = true;
        filteredTableData = [[NSMutableArray alloc] init];

        for (Food* food in allTableData)
            NSRange nameRange = [ rangeOfString:text options:NSCaseInsensitiveSearch];
            NSRange descriptionRange = [food.description rangeOfString:text options:NSCaseInsensitiveSearch];
            if(nameRange.location != NSNotFound || descriptionRange.location != NSNotFound)
                [filteredTableData addObject:food];

    [self.tableView reloadData];

Step 5: Modify our other UITableViewController methods to make them aware of the isFiltering flag, and to use the correct list depending on whether or not we are filtering.

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
    int rowCount;
        rowCount = filteredTableData.count;
        rowCount = allTableData.count;

    return rowCount;

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
    static NSString *CellIdentifier = @"Cell";

    UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
    if (cell == nil)
        cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleSubtitle reuseIdentifier:CellIdentifier];

    Food* food;
        food = [filteredTableData objectAtIndex:indexPath.row];
        food = [allTableData objectAtIndex:indexPath.row];

    // ... Set up the cell here...;

    return cell;

Voila! You now have a UITableView that can be filtered using a UISearchBar.

Download the UITableView Filtering Demo Project (updated July 8, 2012 to show row selection and disclosure indicator handling).