Cant seems to avoid the query that I am sending to my sqlite3 db, not sure why

Question

I have a line as such:

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

this way I can insert rows in B and C with just a simple replacement:

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

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

The problem is that the lines end prematurely. I tried adding extra escape characters, but that didn't work either.

Right now I am using sqlite3_exec () to accomplish everything. Is there anything else I should do? Is a prepared statement handling what I'm trying to do?

Should I just try it with prepare_v2 and solve the problems?

How do I approach this?

Answer 1

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

Your request 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 that you use options. So it works with direct calls to SQLite functions (wrappers can 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); 

Answer 2

Each inner string needs single quotes:

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