Objective-C: Do You Have to Escape Single Quotes in Strings Destined fo SQL?

Question

Hi,

I'm an iPhone developer. I'm writing my unit test cases, one of which checks if the app accepts UTF8 strings to be written to a sqlite3 database. I created a test case to generate random UTF8 strings and I can see the ones that fail do so because they contain an apostrophe (') which I need to be accepted.

I'm writing to the database like this:

NSString *query=@"INSERT INTO song (id,title, duration) VALUES (?,?,?);";
sqlite3_stmt *_statement;
insert=sqlite3_prepare_v2(_database, [query UTF8String], -1, &_statement, NULL);
sqlite3_bind_text(_statement, 1, [value1 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(_statement, 2, [value2 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(_statement, 3, [value3 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_step(_statement);

Do I have to manually escape the apostrophes in value1-3 with a replace or something like that? If so, that's kind of lame...

Answer 1

It's not an apostrophe in this context, it is a single quote which the compiler will see as delimiting a string. Since you're building a string to send to SQL an unescaped single quote will disrupt the string where it occurs.

You always have to escape single and double quote characters in any string context. That is true of every language not just C variants.

Heck, if you use syntax coloring the editor will usually show you that you've turned a big chunk of your code into an unterminated string.

Edit:

Okay, I did misunderstand. In general, to format input from the UI you would use a NSFormatter subclass. This basically lets you turn anything to string and back again. It's most commonly used for dates, numbers, formatted strings like phone# etc but it can perform any string related conversion or manipulation. In this case you would need a formatter to automatically escape single quotes in user inputed strings. Somebody might have already written one.

But I have to say I don't think this is the problem because:

1. NSString is very good about handling quotes. You can even take a UTF8String with single ticks and dump it straight through printf which is notoriously fussy about such things. I'm not sure the Objective-C side of things is the problem.
2. I haven't found any other mentions of this problem. SQL is used extensively on the iPhone and on the Mac and if the Apple API failed to address a very common input pattern, I would expect to see hundreds of post about dealing with rouge quotes in SQL queries.

I haven't futzed with SQL in a while so I can't say definitively what the problem is but I wouldn't get to focused on the Objective-C side of things.