CODEWITHSUNDEEP
android
kolek
kolek

Reputation: 3940

Android - Crash when i have apostrophe in database and select data

I select data from my sqlite database. My problem is that when in database are apostrophes ("I' m John" - for example) and i try to select data, then my application crash.

If i don' t have apostrophes in database, then everything is all right.

My select data function:

String query = "SELECT * FROM " + mainCollumn  + " WHERE used=0 " + " AND season <= " + seasons + " ORDER BY RANDOM() LIMIT " + count ;

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(query, null);

    if(cursor.moveToFirst()){
        do {
            Question question = new Question(Integer.parseInt(cursor.getString(0)),
                    cursor.getString(1),cursor.getString(2),cursor.getString(3),cursor.getString(4),cursor.getString(5),
                    Integer.parseInt(cursor.getString(6)));

            questionList.add(question);
        }while(cursor.moveToNext());
    }

Thanks for any help.

Upvotes: 1

Views: 917

Answers (2)

Simon
Simon

Reputation: 14472

This is an issue with any SQL as single quote (apostrophe) is the text delimiter character. In your data, you must either replace apostrophe with another character or replace single apostrophes with double apostrophes.

Daniel O'Neil

becomes

Daniel O''Neil

[EDIT] UgglyNoodles's recommendation is better.

Upvotes: 3

UgglyNoodle
UgglyNoodle

Reputation: 3047

You can specially handle apostrophes by replacing them with double apostrophes, but the best way is to use query() with selectionArgs instead of rawQuery().

SQLiteDatabase db = this.getReadableDatabase();
String[] columns = new String[] {column1, column2}; // the columns you want
Cursor cursor = db.query(mainCollumn, String[] columns, "used=0 and season<=?", new String[] {seasons}, null, null, "RANDOM() LIMIT " + count);

Upvotes: 6

Related Questions