insert string using sqlite in android containing single and double quotes in it

Question

I am having trouble with inserting a string using sqlite in an android app,
I tried,

query  = "INSERT OR REPLACE into table(_id, text) VALUES ("+data.get(i).id+", '"+data.get(i).text+"')";
MyClass.db.execSQL(query);

If my string looks like,

'I'm an android developer'

App crashes here, here is logcat result,

Caused by: android.database.sqlite.SQLiteException: near "m": syntax error: , while compiling: INSERT OR REPLACE into table (_id, text) VALUES (4, '"I'm an android developer"' )

I think it assumes that, my query ends here

'"I'

please help me to insert any case of string, either it contains single or double quotes like,

"I'm an "android" developer"

http://stackoverflow.com/questions/12615113/how-to-escape-special-characters-like-in-sqlite-in-android — Gooziec, Dec 16 '13 at 10:32

score 5 · Accepted Answer · edited Dec 16 '13 at 10:49

5

Without any hardcoding or anything you can directly insert with using ContentValues like below..

ContentValues values = new ContentValues();
long retvalue = 0;
values.put("_id", id_here);
values.put("text", your_text_here);
retvalue = MyClass.db.insertWithOnConflict(table, null, values, CONFLICT_REPLACE);

edited Dec 16 '13 at 10:49

LS_ᴅᴇᴠ

10,823
1
23
46

answered Dec 16 '13 at 10:37

kalyan pvs

14,486
4
41
59

1

@LS_dev thank you for edit and you make it as a suitable answer. – kalyan pvs Dec 16 '13 at 10:55

score 3 · Answer 2 · answered Dec 16 '13 at 10:31

3

If you are using normal insert statement and if you have any value which contains single quote in it, then you might face a weird issue like this. So,try this..

String insert_info = "INSERT OR REPLACE INTO table(_id,text) VALUES (?,?)";
SQLiteStatement stmt = db.compileStatement(insert_info);
stmt.bindString(1, ""+data.get(i).id);
stmt.bindString(2, ""+data.get(i).text);
stmt.execute();

answered Dec 16 '13 at 10:31

Hariharan

24,741
6
50
54

1

Better to use built-in [insertWithOnConflict](http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html)! – LS_ᴅᴇᴠ Dec 16 '13 at 10:51

laalto · Answer 3 · 2013-12-16T10:46:10.983

1

Multiple options:

Use ContentValues with SQLiteDatabase.insert()

Use variable binding, e.g.

db.execSQL("INSERT INTO table(_id, text) VALUES(?,?)", new String[] { idValue, textValue });

Escape the ' in strings. The SQL way to escape it is '' and you can use DatabaseUtils helpers to do the escaping.

To escape the " in Java strings, use \".

edited Dec 16 '13 at 10:46

answered Dec 16 '13 at 10:36

laalto

150,114
66
286
303

score 0 · Answer 4 · answered Sep 16 '15 at 05:51

you must replace \' with \'\' in query string:

String getQuery(){
    query  = "INSERT OR REPLACE into table(_id, text) VALUES ("+data.get(i).id+", '"+getSqlValue(data.get(i).text)+"')";
    MyClass.db.execSQL(query);
    return query;
}
String getSqlValue(String input){
    return input.replace("\'","\'\'");
}

score -3 · Answer 5 · answered Dec 16 '13 at 10:33

-3

You can use " for skipping " in a string

answered Dec 16 '13 at 10:33

Viswanath Lekshmanan

9,945
1
40
64

This is not an answer to this question. – laalto Dec 16 '13 at 10:37

insert string using sqlite in android containing single and double quotes in it

5 Answers5

Linked