Cant seem to escape a query i am sending to my sqlite3 db, not sure why

Question

I have a string as such:

string query;
query = "insert or replace into TABLEA (a,b,c) values (@a,\"@b\",\"@c\");";

that way i can insert strings into B and C with just a simple replace:

string instring("I have a 3\" gauge");
string instring2("I am looking for 1/8\" thickness");
Replace(&query, "@a", to_string(1));
Replace(&query, "@b", instring);
Replace(&query, "@c", instring2);

So now my query string is:

"insert or replace into TABLEA (a,b,c) values (1,\"I have a 3\" gauge\",\"I am looking for 1/8\" thickness\");";

SQLITE3 gets it and it looks like:

insert or replace into TABLEA (a,b,c) values (1,"I have a 3" gauge","I am looking for 1/8" thickness");

The issue is that the strings end prematurely. I tried to add additional escape characters but that wasnt seeming to work either.

Right now i am using sqlite3_exec() to carry out everything. Is there something else i should do? Does a prepared statement handle what i am trying to do?

Should i just try it with prepared_v2 and that might resolve issues?

How should i be approaching this?

http://stackoverflow.com/questions/603572/how-to-properly-escape-a-single-quote-for-a-sqlite-database helps understand that i should want to remove the " that i have, and just have ', and then search the input strings for ' and insert an additional, so that way the DB language will understand that the '' is 1 quote — Fallenreaper, Nov 29 '12 at 21:09

score 3 · Accepted Answer · answered Nov 30 '12 at 11:07

In SQL, strings use single quotes, and are escaped by using two single quotes. (Double quotes are accepted for compatibility with MySQL, but should not be used.)

Your query should look like this:

INSERT OR REPLACE INTO TableA(a, b, c)
VALUES (1, 'I have a 3" gauge', 'I am looking for 3/8" thickness')

or like this:

INSERT OR REPLACE INTO TableA(a, b, c)
VALUES (1, "I have a 3"" gauge", "I am looking for 3/8"" thickness")

However, to avoid string formatting problems, it is recommended to use parameters. This is how it works with direct SQLite function calls (wrappers might work differently):

const char *sql = "INSERT OR REPLACE INTO TableA(a, b, c) VALUES (1, ?, ?)";
sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
sqlite3_bind_text(stmt, 1, "I have a 3\" gauge", -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 2, "I am looking for 3/8\" thickness", -1, SQLITE_TRANSIENT);

Thank you CL, it helped alot – Fallenreaper Nov 30 '12 at 14:16 — Fallenreaper, Nov 30 '12 at 14:16

Kevin · Answer 2 · 2012-11-30T15:33:22.357

2

You need single quotes around the each inner string:

string query;
query = "insert or replace into TABLEA (a,b,c) values (@a,'\"@b\"','\"@c\"');";

edited Nov 30 '12 at 15:33

answered Nov 29 '12 at 19:28

Kevin

7,162
11
46
70

1

does it have to be around the whole inner string? Or can it be like ......'\"@b\"','\"@c\"');"; around individual components? – Fallenreaper Nov 29 '12 at 19:57
what are you thinking? I wasnt sure if i should be putting single quote around instead of the Double quotes that i had. – Fallenreaper Nov 29 '12 at 20:21
I tried to wrap it around the entire thing, and it would call an error saying 2 entries for 3 columns or whatnot, so it would recognize the entire single quoted string as 1 entry. Also, the strings can have a single quote, and when i was testing it this was, it would error on \' because it still recognized the ' as the end of the string. – Fallenreaper Nov 29 '12 at 20:51
@Fallenreaper, My initial entry was wrong. You need single quotes around each string. CL has what I was trying to convey, only much better written. – Kevin Nov 30 '12 at 15:34
1

Yea, i tried it your way, and i was like "That isnt right." So, i realized that when i was doing \" around the stuff, i shouldve actually just been doing single quotes. Now that it was modified by you, ill give you +1 – Fallenreaper Nov 30 '12 at 15:49

Cant seem to escape a query i am sending to my sqlite3 db, not sure why

2 Answers2