How to escape special characters like ' in sqlite in android

Question

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.

Answer 1

The SQL standard specifies that single-quotes in strings are escaped by putting two single quotes in a row. SQL works like the Pascal programming language in the regard. SQLite follows this standard. Example:

INSERT INTO xyz VALUES('5 O''clock'); 

Ref : SQLite FAQ

Answer 2

The best way is to use a native Android method designed for exactly this purpose:

DatabaseUtils.sqlEscapeString(String) 

Here is the documentation for it online:

• sqlEscapeString() on Android Developers

The main advantage of using this method, in my opinion, is the self-documentation because of the clear method name.