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

Question

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 android.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

score 11 · Accepted Answer · edited May 23 '17 at 11:45

11

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...

edited May 23 '17 at 11:45

Community

1
1

answered Sep 21 '11 at 21:00

Jack

9,156
4
50
75

Thanks for the link. I searched everywhere for a similar question, but I was searching for "apostrophe" instead of "single quote". – John Leehey Sep 21 '11 at 21:11
2

DatabaseUtils.sqlEscapeString was the easiest solution for me, FTW – John Leehey Sep 22 '11 at 06:16

David · Answer 2 · 2011-09-21T21:09:10.227

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):

Use parmeterized stored procedures
Use parameterized queries if the DB Engine supports it.
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.

Has it been confirmed that two single-quotes would work on the android SQLite engine? I could use that as a viable solution. — John Leehey, Sep 21 '11 at 21:12
I tried `\'` and it didn't work. `''` worked on Android SQLite. — Thomas, Nov 01 '20 at 23:35

score 1 · Answer 3 · answered Mar 14 '13 at 07:04

1

the one line simplest code :

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

answered Mar 14 '13 at 07:04

Prakash

449
2
4
15

score 0 · Answer 4 · answered Jan 30 '17 at 14:10

0

Just use android standard API as

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

It will solve your problem.

answered Jan 30 '17 at 14:10

Absar Alam

102
2
9

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

4 Answers4