User-entered apostrophe crashes android app (SQL-related)

My application currently utilizes a SQLite database to store messages for the user. If a user enters an apostrophe in any of the fields, however, the database crashes. I realize that the issue is due to SQLite using the apostrophe as a quote delimiter (I place single apostrophes around the message text stored), but can't think of any good ways around this. Is there a common practice to storing strings with apostrophes in an SQLite database?

Here's an example of an error I received:

java.lang.RuntimeException: Unable to resume activity {x.x.x.x/x.x.x.x.Main}: android.database.sqlite.SQLiteException: near "m": syntax error: , while compiling: SELECT title, contact_name, contact_number FROM contacts WHERE title='I'm at work'
at android.app.ActivityThread.performResumeActivity(ActivityThread.java:3128)
at android.app.ActivityThread.handleResumeActivity(ActivityThread.java:3143)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2684)
at android.app.ActivityThread.access$2300(ActivityThread.java:125)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2033)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:123)
at android.app.ActivityThread.main(ActivityThread.java:4627)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:521)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:859)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:617)
at dalvik.system.NativeStart.main(Native Method)
Caused by: android.database.sqlite.SQLiteException: near "m": syntax error: , while compiling: SELECT title, contact_name, contact_number FROM contacts WHERE title='I'm at work'
at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:80)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:46)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
at an开发者_Python百科droid.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1454)
at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1338)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1293)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1373)
... 19 more

---

See this question - there are quite a few answers which should help you.

Specifically it talks about using DatabaseUtils.sqlEscapeString or:

uservalue = getText().tostring();
uservalue = uservalue.replace("'","\'");
//execute query...

---

General database guidelines (and secure coding guidelines) help prvent against this because the same thing that's causing your error can be exploited for SQL INjection attacks. The most common defenses are (in order of preference per OWASP guidelines):

1. Use parmeterized stored procedures
2. Use parameterized queries if the DB Engine supports it.
3. As a final resort, escape the strings (implementation dependednt on the database engine.)

SQLLite supports options 2 and 3.

From this page: http://www.sqlite.org/lang_expr.html

A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL.

so O'Brien would be entered as O''Brien in your SQL statement.

And per this page, you can use parameterized queries: http://www.sqlite.org/limits.html

INSERT INTO tab1 VALUES(?,?,?);

Then use the sqlite3_bind_XXXX() functions to bind your large string values to the SQL statement. The use of binding obviates the need to escape quote characters in the string, reducing the risk of SQL injection attacks. It is also runs faster since the large string does not need to be parsed or copied as much.

---

the one line simplest code :

String s = editText.getText().toString().replace("'","\'");

---

Just use android standard API as

userInput = android.database.DatabaseUtils.sqlEscapeString(userInput);

It will solve your problem.