Android Single Quote In SQL With a LIKE StateMent

I am having problem querying single quote while using the sql LIKE statement

this is my SQL query for searching the MUSIC file in the SD CARD.

final Uri uri = MediaStore.Audio.Media.EXTERNAL_CONTENT_URI;
final String[] cursor_cols = {
   MediaStore.Audio.Media.TITLE
};
where = MediaStore.Audio.Media.TITLE + " like ('%"+SomeSongTitle+"%')";
cursor = getContentResolver().query(uri, cursor_cols, where, null, null);

SomeSongTitle is some arbitrary input text that the a user input.

My Question is why when SomeSongTitle contai开发者_如何转开发ns a single Quote(for example SomeSongTitle=don't), it crashes.

And How to fix it?

thankz for reading and hope to hear some solution from you guys =D. hehe

If you don't want to do String substitution you can use SQLiteDatabase.rawQuery to get your Cursor object. And then do something like:

String query = "select * from your_table_name where" + MediaStore.Audio.Media.TITLE + " like ('%?%')";
cursor = yourDB.rawQuery(query, new String[] {SomeSongTitle});

That should get around the quoting issue.

To fix it you need to replace the single quote with two single quotes. Try using something like...

SomeSongTitle = SomeSongTitle.replace("'", "''");

If you use bindings (?) for the argument(s) in the where clause, then you do not need and should not use any single quotes because the binding already takes care of that. In particular, the second argument in a binding is an array of strings, String[], providing one String for each ?. In the binding process, each of those Strings is treated by sql as if it has single quotes around it. Binding creates a compiled sql statement with variable substitution, so it is efficient to write your sql as a fixed String and binding rather than make a different statement each call.

You'll need to escape the single quote. There are much more sophisticated methods to do this, but an easy way to start is to simply to a find and replace in order to add a slash (\) before the quote mark so that it looks like this: (\').

You can read more about it SQL Injection. Specifically, look at the section on Mitigation.

Android's database API sits on top of sqlite. In its FAQ, you can see that to "escape" a single quote, you just use two single quotes. See here.