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.

Spoken

try using selectionArgs, for e.g. : rawQuery("select * from table where chapters = ?", new String[] { difficulty } ); – Conqueror

Answer 1

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

Domett

What about dash, hyphen "-" character? – Subordinary

The edit looks OK, but the first part of the answer is obviously wrong. "'" is the same string as "\'" (the latter is just alternative notation for the same character sequence), so the replace() statement is effectively an identity function. Funny it's the accepted answer. – Mendes

Franz is right, this will not work, did not work for me. – Contaminate

Answer 2

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

Shira

this is horrible. Use selectionArgs. don't try to sanitize the sql inputs yourself. – Boondocks

@Boondocks Hey Thanks for pointing. But it would be better if you add details about "why is it horrible?". This would help other users :). By the way I did not get your points here, because this answer was for escaping special characters. – Shira

This question is targeted at SQLite in Android. The OP asks how to input characters such as ' in a query. The proper answer is to use selectArgs, which leaves the escaping/sanitizing/... work to the SQLite module itself, which it does well, and comprehensively. What you suggests only works for '. – Boondocks

Comments of this sort are silly. There's absolutely no reason someone can't successfully implement their own string sanitizer. It's one of the simplest ways to build your own methods for dynamically building queries, which isn't the easiest thing to do with argument processors. – Arne

I need to export a result of combining tables into a textfile which I can include in a SQlite browser. I dump a few hundred lines in it and voila. If it is sanitized by me, nog selectionArgs :) – Cadmium

Answer 3

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.

Mureil

I ran into trouble with queries, after using this function, since it definitely surrounds your string with single quotes, which are then part of your string and you have to adapt your queries accordingly or use another method to escape strings. More details at #31335223 – Driveway

@Carsten this question relates to the use of raw query strings. For obvious reasons you would not use it in conjunction with the other helper methods, they do this internally already. Hence the issue you and the other poster were experiencing. – Mureil

The source code of that utils method suggests that it only escapes ' characters and surrounds the input strng with two quotes as well ' – Foreside

Answer 4

What worked for me was

if (columnvalue.contains("'")) {
    columnvalue = columnvalue.replaceAll("'", "''");
}

Ardatharde

This worked for me as well. I also added a null check before it to be safe. I can't believe GOOG's own DatabaseUtils.sqlEscapeString() method is such junk. – Chymotrypsin

String.replaceAll creates a Pattern. I'm not sure it's very optimal just to replace ' – Outsole

Answer 5

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

Jonellejones

It is Work For Me. Thanks jaredzhang. It is Just Easy to Solve this special character problem. – Zwick

Answer 6

What DatabaseUtils.sqlEscapeString(s) essentially does, is replace all single quotes (') with two single quotes ('') and append one single quote at the beginning and one at the end of the String.
So if you just want to escape the String this function should work:

private static String escape(String s) {
    return s != null ? s.replaceAll("\'", "\'\'") : null;
}

Cosimo

Answer 7

This will work the same:

if (columnValue.contains("'")) 
    columnValue = columnValue.replaceAll("'", "[']");

or

if (columnValue.contains("'")) 
    columnValue = columnValue.replaceAll("'", "\\\'");

but in actuality we use next symbols % and _

Claudication

Answer 8

As per my understanding there are two approcaches,

String test= "Android's vaersion" test= test.replace("'","''");

This scenario is absolutely fine but i would definitely suggest to use following approach.

String test= "Android's vaersion" test= test.replaceAll("'","\'");

We faced the issue because of first approach when we were trying to update and select SQLite Table Value with '.

Changteh

Answer 9

don't you need to escape the \ as well? so it would be replaceAll("'", "\\'") Or am I wrong about that?

Ronnaronnholm