How to escape? data that is being inserted into sqlite database.

Question

I'm making an apple app using phone gap (which uses sqlite database).

All my inserts work fine except for when I try to insert a weblink. This errors because there are " characters in the weblink.

Using the following:

var content = 'hello, this is my <a href="www.google.com">link</a>'
tx.executeSql('UPDATE PAGES SET content="'+content+'" WHERE id="1"');

Brings back the following error

error code 1
error: "near "http": syntax error"

If I remove the website address, I don't get an error. I have tried: content = escape(content);

but that hasn't worked.

There is no "http" in this code, which means the error is somewhere else. — hamstergene, Sep 30 '11 at 12:20
That's my fault, there is http in the my real version. I just typed that out quickly. Sorry. — Billie, Sep 30 '11 at 13:32

score 11 · Answer 1 · answered Sep 30 '11 at 11:24

11

Use parameter binding. This is the right and the safest way to do what you're trying to do.

tx.executeSql('UPDATE PAGES SET content=? WHERE id=1', [content]);

answered Sep 30 '11 at 11:24

hamstergene

24,039
5
57
72

score 4 · Answer 2 · answered Mar 30 '13 at 02:01

4

To escape quotes in SQLite you'll have to repeat the quotes.

So to insert a 7" screen you'll have to put a 7"" screen in the INSERT statement.

answered Mar 30 '13 at 02:01

Dylan

9,129
20
96
153

score 1 · Answer 3 · answered Jan 25 '12 at 11:49

The problem is the quote marks. They change the meaning of the SQL request and cause the error.

I can't find any information on escaping them properly. I read that you can put \ before them and then add ESCAPE ("\") to the SQL statement, but this doesn't seem to work with PhoneGap's implementation.

In the end, I did a simple replace, where single or double quote marks are replaced with ", like so:

var thisNotes = $('textarea#notes').val().replace(/(["'])/g,'&quot;');

I just need to display quote marks in a text field however - I don't need them as part of a HTML link, so I'm not sure whether the link would still work. You may need to reverse the replace before displaying the link.

Hope this helps.

Then again, it seems a double quote mark may be a better answer, as per this [question](http://stackoverflow.com/questions/603572/how-to-properly-escape-a-single-quote-for-a-sqlite-database) — Stephen Cronin, Jan 25 '12 at 11:54
So I've now revised my code to use .replace(/"/g,'""'). The single quotes seem to be fine, it's just the double ones and the above code fixes that. — Stephen Cronin, Jan 25 '12 at 12:01

score 0 · Answer 4 · answered Sep 30 '11 at 09:41

use javascript replace maybe create a function like so:

function CleanDbData( dirty ){
     var clean = dirty.replace( "/"","''").replace("<script>","[script]");
     return clean;
}

var mydata = CleanDbData( dirtydata );

this is not AS is, you will have to mess with regex etc but you get the idea [hopefully]

How to escape? data that is being inserted into sqlite database.

4 Answers4

Linked