Search code examples
androidsqlitespecial-characters

How to escape special characters like ' in sqlite in android

I have a function which is executing a query on a table in SQLite database. I declare a constant: public static final String CANADA_HISTORY = "Canada's History";. This is stored in a String variable let's say difficulty,

I have one query:

Cursor c = mDb.rawQuery("select * from Questions_answers where CHAPTERS = '"+difficulty+"'" , null);

It is throwing an exception near the apostrophe.

Logcat output:

I/Database( 1170): sqlite returned: error code = 1, msg = near "s": syntax error
D/AndroidRuntime( 1170): Shutting down VM
W/dalvikvm( 1170): threadid=1: thread exiting with uncaught exception (group=0x40015560)
E/AndroidRuntime( 1170): FATAL EXCEPTION: main
E/AndroidRuntime( 1170): android.database.sqlite.SQLiteException: near "s": syntax error: , while compiling: select * from Questions_answers where CHAPTERS  = 'Canada's History'

I have also tried:

1.  difficulty=difficulty.replaceAll("'","''");
2.  difficulty=difficulty.replaceAll("'","\'");
3.  difficulty = DatabaseUtils.sqlEscapeString(difficulty);

To add to that, it's working me for the single words like Canada History, I mean without the special character word.

Please give me advice for the solve problem Thanks.

Solution

  • First replace char with this

    difficulty=difficulty.replaceAll("'","\\'");

    then pass it in your query

    "select * from Questions_answers where CHAPTERS='"+difficulty+"'";

    Edit :

     q = "select * from Questions_answers where CHAPTERS = ?";
    database.rawQuery(q, new String[] { difficulty});