Difficulty in escaping single quote character while SQLite database insertion

Question

I have to insert certain text in database which may contain characters with single quotes and double quotes.

For eg: "Lumix" or something like Vijay's

I have successfully escaped the double quotes like this:

if(fdata.search('"') != -1) {


fdata = fdata.replace(/(['"])/g, "\\$1");



}

But I am not understanding how to escape the single quotes. Without escaping the single quotes SQLite is not accepting the data. How to solve the issue? Thanks in advance!

Doesn't your database API give you bound variables or an SQLite-specific escaping function? — Quentin, Feb 25 '13 at 10:54
It is written in javascript, titanium appcelerator, not java native — kittu88, Feb 25 '13 at 10:57
@kittu88: So? Titanium Appcelerator includes a database API, which _does_ support bound variables (aka parameters). — Martijn, Feb 25 '13 at 18:19

score 4 · Accepted Answer · answered Feb 25 '13 at 12:01

4

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

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

answered Feb 25 '13 at 12:01

CL.

165,803
15
203
239

what about the update query? db.execute("UPDATE formData SET form_xml_id=" + findex + ",dateTime_stamp='" + datetime + "',data='" + "" + "',user_id=" + Ti.App.information.user_id + ",status='"+ DataStatus +"' where unique_id='" + Ti.App.mydata._guid + "'"); – kittu88 Feb 25 '13 at 12:09
2

Same principle: `db.execute("UPDATE formData SET form_xml_id=?, dateTime_stamp=?, data=?, user_id=?, status=?, where unique_id=?", findex, datetime, '', Ti.App.information.user_id, DataStatus, Ti.App.mydata._guid);` :-) – Martijn Feb 25 '13 at 12:14
1

See the documentation here: http://docs.appcelerator.com/titanium/latest/#!/api/Titanium.Database.DB-method-execute – Martijn Feb 25 '13 at 12:17

score 1 · Answer 2 · answered Apr 06 '14 at 14:00

CL.'s answer is the by far best solution, but if you want to make this work without rewriting your code you could add this function:

function removeQuotes(str){
return str.replace(/'/g, "&#39;").replace(/"/g,"&quot;");
}

Usage:

fdata = removeQuotes(fdata);

That worked for me

score 0 · Answer 3 · answered Feb 25 '13 at 10:54

0

Use replaceAll method for replacing '(single quote) with space as below:

     String name= name.replaceAll("'", "''");

answered Feb 25 '13 at 10:54

Avadhani Y

7,476
18
59
90

try fdata.replace(/["']/g, "") – Avadhani Y Feb 25 '13 at 11:06
@Aʌɐpɥɐuı: that would only remove all the (single and double) quote characters, not espace them... – Martijn Feb 25 '13 at 18:18
sqlquery.replace(/'/g, "''"); – Maluvel Nov 16 '15 at 16:36

Difficulty in escaping single quote character while SQLite database insertion

3 Answers3