CODEWITHSUNDEEP
androidsqlite
Irfan Ahmed
Irfan Ahmed

Reputation: 9374

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

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"

Upvotes: 2

Views: 7589

Answers (5)

Mohammad Asadi
Mohammad Asadi

Reputation: 149

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("\'","\'\'");
}

Upvotes: 0

kalyan pvs
kalyan pvs

Reputation: 14590

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);

Upvotes: 5

laalto
laalto

Reputation: 152807

Multiple options:

  1. Use ContentValues with SQLiteDatabase.insert()

  2. Use variable binding, e.g.

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

  3. 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 \".

Upvotes: 1

Viswanath Lekshmanan
Viswanath Lekshmanan

Reputation: 10083

You can use " for skipping " in a string

Upvotes: -3

Hariharan
Hariharan

Reputation: 24853

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();

Upvotes: 3

Related Questions