Problem with ObjectiveC sqlite3

Question

I found that I have a problem updating / pasting into my spreadsheet in an iPhone app, because I have a TEXT column, and when this text contains a character, everything becomes messy. What is the best way to handle this?

Should I check before using an apostrophe string? Is there a quick way to add formatting that will add an escape character before each apostrophe?

Does this question make sense? lol.

Answer 1

sqlite requires the "run by two" character.

Take a look at this from sqlite 's official questions :

  (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 it all. If you want your db to contain '. Just replace your line with% 27 and, returning it back, return 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 are three ways to solve this problem:

• Make formatting yourself. Do not do this. (well, unless that line is part of your code, not the user). In this case, this approach is great.)
• Use sqlite3_mprintf("%Q") for SQLite to do this. ( %q quotes the replacement; %q performs the replacement and inserts NULL for the null pointer.)
• Use the bindings in your statement, which you fill out with sqlite3_bind_text . This is the best way to do this because it does not require recompiling the statement for each row and does not open you up for SQL Injection .

Using binding will 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 

(Remember to check for errors.)

Answer 4

There is a feature provided by SQLite that can escape characters as needed. Take a look at: sqlite3_mprintf

http://www.sqlite.org/c3ref/mprintf.html