Apostrophe breaking insert query in SQLite in Titanium

Question

I am writing an application in JavaScript using Appcelerator's Titanium development platform to deploy to the Android mobile platform. I am trying to perform an INSERT to an SQLite database.

The strings whenever they have a single quote or an apostrophe entered by a user breaks the insert query. What am I doing wrong?

  var db = Ti.Database.install('db/kewGarden.sqlite', 'kewGarden');
   var driveByData = {
    "notes" : $.row3.getValue() // User entered string
};

driveByData = JSON.stringify(driveByData);

dbLib.saveRecording(saveDriveByDetailsSuccess, saveDriveByDetailsError,  {
    ref_id : newdriveById,
    tableName : tableName,
    data : driveByData
});

saveRecording : function(onSuccessCallback, onErrorCallback, options) {
    var strReplaceData = options.data.replace("'", "\'"); 
    db.execute("INSERT INTO g_temp  (ref_id, table_name, data, site) VALUES (" + options.ref_id + ",'" + options.tableName + "','" + strReplaceData + "','" + options.site + "')");       
},

The docs for this database are here:

http://docs.appcelerator.com/platform/latest/#!/api/Titanium.Database.DB-method-execute

@chris85 that is partly the problem but not the actual problem. OP probably comes from a C/JS background and isn't concatenating correctly in values. Look at the plus signs; unless I'm wrong. — Funk Forty Niner, Feb 08 '16 at 17:03
@Fred-ii- I think this is going from JS to a PHP page so concatenation is correct.. I don't know what `db.execute` does but this `"notes" : $.row3.getValue()` is JS. — chris85, Feb 08 '16 at 17:06
@chris85 yeah, after looking at it again, I feel I was wrong about my comment. My bad. Question closed. — Funk Forty Niner, Feb 08 '16 at 17:07
If you use [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements you will not have these problems. Learn more about prepared statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Feb 08 '16 at 17:08
(This was substantially mis-tagged, now half-fixed. I have asked the OP in comments under an answer to clarify what database this is - it is possibly an HTML5 in-browser SQLite database, but until we see what `db` is, it is rather hard to say. Once this is clarified it _might_ be able to be reopened). — halfer, Feb 08 '16 at 18:08
After a great deal of to-and-fro, we find that this question is not about PHP or MySQL after all, nor an in-browser SQLite database. It is about a JavaScript-based mobile development platform called Titanium, which employs an underlying SQLite database. @Fred-ii-, would you undo your dup hammer? — halfer, Feb 08 '16 at 19:29
@halfer so why the accepted answer below? they've given them a php/mysql answer. — Funk Forty Niner, Feb 08 '16 at 19:34
@Fred-ii-, no, I think that's JavaScript. `db` is a JS object of the type described belatedly by the link in the post, and the dot notation (e.g. `db.execute`, `options.tableName`) suggests this is not PHP. (I don't mind it being closed, since I virtually had to _drag_ sufficient detail out of the OP, but it seems wrong to me for it to be closed as the current dup). — halfer, Feb 08 '16 at 19:46
@halfer something weird happened. I clicked on reopen and it did not reopen in one shot. I think this may have something to do with chris85 marking it as a duplicate in the first place and I closed it. Well, my vote to reopen is there. I just find it strange that it didn't reopen in one go, probably because of the first vote to close. — Funk Forty Niner, Feb 08 '16 at 20:01
Ah no worries @Fred, I thought that too. Never mind, I have added my vote... two more required! — halfer, Feb 08 '16 at 20:06
@halfer Well, I (think) I learned something today lol that only an originally (first) close by a gold tag can only be reopened by a gold. *Cheers* — Funk Forty Niner, Feb 08 '16 at 20:09

Vasim Shaikh · Accepted Answer · 2016-02-08T17:27:28.460

1

Use parameters, then you don't need to escape anything:

db.execute('INSERT INTO MyTable(ID, Name) VALUES(?, ?)', 123, name);

Your Query like this,

db.execute('INSERT INTO g_temp  (ref_id, table_name, data, site) VALUES (?,?,?,?)',options.ref_id,options.tableName,options.data,options.site);

edited Feb 08 '16 at 17:27

answered Feb 08 '16 at 16:58

Vasim Shaikh

4,485
2
23
52

Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Feb 08 '16 at 17:10
@Jay Blanchard Guys this is a local .js file an not php. Sorry for the not making myself clear. its an sqlite query inserting in a local sqlite db. – Adnan Hussein Feb 08 '16 at 17:17
you can perform php in js – Vasim Shaikh Feb 08 '16 at 17:19
var val = " – Vasim Shaikh Feb 08 '16 at 17:20
You cannot go directly to SQLite from JS @AdnanHussein – Jay Blanchard Feb 08 '16 at 17:27
My bad sorry about that, the local db is sqlite, later i sync it when i the device comes online to a mysql backend.. – Adnan Hussein Feb 08 '16 at 17:48
1

Adnan problem solved? – Vasim Shaikh Feb 08 '16 at 18:21
Yes @Vasim. Thanks and also everybody around who tried to help. not sure exactly why your solution works. – Adnan Hussein Feb 08 '16 at 18:33

Apostrophe breaking insert query in SQLite in Titanium

1 Answers1