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=%@", record.name, record.id];

What’s the problem here? Good ol’ Bobby Tables. Depending on what record.name 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, [record.name 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.

3 Comments

  1. This article is awesome ..

    I am an infoSec professional in the beginning of my career . To understand the objective C application issues this article helped me a lot ..

    Reply

  2. Excelent! Thanks. I think that even with a small app in a personal device, every programmer must apply all good practices. It’s part of the job.
    Thank you

    Reply

Leave a Reply to Carlos B Cancel reply

Your email address will not be published. Required fields are marked *