How to escape unsupported character in SQLite on Android?

Question

can anybody tell How to escape or replace not supported character like single quotes in sqlite in android can anybody give example

Thanks

Answer 1

You can utilize the commons-lang utility or you can use a regexp to handle it.

If you're building dynamic SQL, what I would suggest is trying to use a prepared statement which would eliminate the need for escaping single quotes.

Using just a dynamic SQL built using string concatenation:

String value = "one's self";
StringBuilder query= new StringBuilder();
query.append("insert into tname(foo) values (").append(value).append(")");
... execute call with query.toString() ...

Change that to

String value = "one's self";
value= DatabaseUtils.sqlEscapeString(value);
StringBuilder query= new StringBuilder();
query.append("insert into tname(foo) values (").append(value).append(")");
... execute call with query.toString() ...

Ideally, use a prepared statement

String value = "one's self";
StringBuilder query= StringBuilder();
query.append("insert into tname(foo) values (?)");
SQLiteStatement stmt= db.compileStatement(query.toString());
stmt.bindString(1, value);
long rowId= stmt.executeInsert();
// do logic check for > -1 on success

This way you don't run into "SQL injection attacks".

Refer to http://developer.android.com/reference/android/database/sqlite/SQLiteStatement.html for more information.

EDIT I did a little more digging, you can use DatabaseUtils.sqlEscapeString(String) to escape the content of a string so that it is valid for a complete SQL statement with no prepares.