SQLITE update fails with error code 1 (SQLITE_ERROR)

Question

Hey sqliters, I am having a strange or maybe not so strange problem with my sqlite db. I have a field of "Text" type and it worked like a charm with any English texts for ages.

The text in the field used to come from an MFC CEdit. Now I switched to CRichEditCtrl to support formatting and UNICODE texts. The CRichEditCtrl dumps the formatted text in hex form like: {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Shell Dlg 2;}} ect.

Once againt the sqlite saves it most of the time just fine. But some time it doesn't giving an error code SQLITE_ERROR /* SQL error or missing database */. the message is not too helpful. the db exists and save most of the time???? Go figure. Here is the code:

error = sqlite3_exec(db,cmd,0,0,0);
if (error != SQLITE_OK){
 errMsg.Format("Unable to save notes to: %s\nSQLite Error: %d",filename,error);
 AfxMessageBox(_T(errMsg));
 sqlite3_close(db);
 return false;
 }

Nothing out of ordinary I think.

Please let me know if you have any ideas. Can it be due to lots of backslashes in the RTF format which somehow confuses sqlite?? Oh, it's not related to size of text saved. thanks a lot. Val

Answer 1

If one backslash works, so should several. But it could be that you're not escaping the text correctly, if the text contains an apostrophe or double quote, which would break the query.

If you didn't escape quotes then that may be the problem. Say you do escape quotes first, like

{blabla\'somethingelse}

and then that string is escaped again to preserve the backslashes (otherwise they would be lost in the communication with the database), which would produce

{blabla\\'somethingelse}

and then voila, you have an apostrophe on the loose...

The solution would be to escape backslashes FIRST, then apostrophes and quotes.

Answer 2

The problem definitely appears to be due to not escaping special characters in your code. I recommend you use the sqlite API for prepared statements to save yourself from a lot of string processing hassle. They will insert values into queries for you and handle escaping automatically.

Instead of this:

error = sqlite3_exec(db,cmd,0,0,0);

You need to do this:

sqlite3_stmt *stmt;
error = sqlite3_prepare_v2(database, "INSERT INTO table (field) VALUES (?)", -1, &stmt, NULL);
if (error != SQLITE_OK) { ...
error = sqlite3_bind_text(stmt, 1, rtfString, -1, SQLITE_STATIC);
if (error != SQLITE_OK) { ...
error = sqlite3_step(stmt);
if (error != SQLITE_DONE) { ...
sqlite3_finalize(stmt);

sqlite will automatically take the value of rtfString, quote it properly, then insert it into the query in place of the question mark.

Another advantage to this way is you can reuse the stmt object. Just call sqlite3_reset() after each use, and remember to call sqlite3_finalize() when you're done for good.