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.

It sounds like you're building a SQL query string yourself. Don't do that. Use a wrapper like FMDB instead, which will handle this for you. — Dave DeLong
– Dave DeLong, Commented Jun 30, 2011 at 20:08

viggio24 · Accepted Answer · 2011-06-30 18:46:55Z

12

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');

answered Jun 30, 2011 at 18:46

viggio24

12.4k5 gold badges43 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CodeGuy Over a year ago

is there a quick way to say "newString = oldString + double quotes where needed"

viggio24 Over a year ago

use: newString=[oldString stringByReplacingOccurrencesOfString:@"'" withString:@"''"];

Niru Mukund Shah · Accepted Answer · 2012-10-15 11:04:30Z

5

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 :) :)

answered Oct 15, 2012 at 11:04

Niru Mukund Shah

4,7532 gold badges22 silver badges35 bronze badges

Comments

Steven Fisher · Accepted Answer · 2014-07-10 18:14:18Z

5

There's three ways to solve this:

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.)
Use sqlite3_mprintf("%Q") to have SQLite do this. (%q does quote replacement; %Q does quote replacement and inserts NULL for a null pointer.)
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.)

edited Jul 10, 2014 at 18:14

answered Jun 30, 2011 at 19:43

Steven Fisher

44.9k21 gold badges142 silver badges194 bronze badges

1 Comment

Chris Schiffhauer Over a year ago

I believe you reversed the order of the last two arguments in sqlite3_prepare. I believe it should be sqlite3_prepare(db, "INSERT INTO Table(Column) VALUES(?);", -1, &stmt, NULL); +1 for the great solution.

xyzzycoder · Accepted Answer · 2011-06-30 19:03:27Z

3

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

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

answered Jun 30, 2011 at 19:03

xyzzycoder

1,83113 silver badges21 bronze badges

Collectives™ on Stack Overflow

ObjectiveC sqlite3 issue

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related