ObjectiveC sqlite3 issue

Question

I'm finding that I have an issue when updating/inserting into my table in my iPhone app because I have a TEXT column, and when that text includes a ' symbol, things get messed up. What is the best way to handle this?

Should I check before I use a string that has an apostrophe? Is there a quick way to add formatting that will add an escape character in front of each apostrophe?

Does this question even make sense? lol.

Answer 1

sqlite requires the ' symbol to be escape by two ''.

Look at this from the official sqlite FAQ:

(14) How do I use a string literal that contains an embedded single-quote (') character?

The SQL standard specifies that single-quotes in strings are escaped by putting two single quotes in a row. SQL works like the Pascal programming language in the regard. SQLite follows this standard. Example:

    INSERT INTO xyz VALUES('5 O''clock');

Answer 2

hey forget all this stuff. If you want your db to contain ' . Just replace your string with %27 & when fetching it back convert it back . You will get what you want. Check below :

// while Inserting into db
    str = [str stringByReplacingOccurrencesOfString:@"'" withString:@"%27"];

// while fetching it back
        text = [text stringByReplacingOccurrencesOfString:@"%27" withString:@"'"];

Enjoy programming :) :)

Answer 3

There's three ways to solve this:

1. Do the formatting yourself. Don't do this. (Well, not unless this string is part of your code rather than user input. In that case, this approach is fine.)
2. Use sqlite3_mprintf("%Q") to have SQLite do this. (%q does quote replacement; %Q does quote replacement and inserts NULL for a null pointer.)
3. Use bindings in your statement that you fill in with sqlite3_bind_text. This is the best way to do this, since it doesn't require recompiling the statement for every string and doesn't open you to SQL Injection.

Using a binding would look like this:

sqlite3_prepare(db, "INSERT INTO Table(Column) VALUES(?);", -1, &stmt, NULL);
sqlite3_bind_text(stmt, 1, [str cStringUsingEncoding:NSUTF8StringEncoding],
                  -1, SQLITE_TRANSIENT);
// stepping, etc

(Don't forget to do error checking.)