CODEWITHSUNDEEP
androidsqlsqlite
John Leehey
John Leehey

Reputation: 22240

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

Upvotes: 4

Views: 6122

Answers (4)

Absar Alam
Absar Alam

Reputation: 96

Just use android standard API as

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

It will solve your problem.

Upvotes: 0

Prakash
Prakash

Reputation: 449

the one line simplest code :

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

Upvotes: 1

David
David

Reputation: 73564

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.

Upvotes: 4

Jack
Jack

Reputation: 9242

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

Upvotes: 11

Related Questions