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).t

Solution 1:

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

Solution 2:

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

Solution 3:

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

Solution 4:

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

Solution 5:

You can use " for skipping " in a string