android.database.sqlite.SQLiteException: near "...": syntax error (code 1)

Question

When i open my RSS app, i would like to check if an article exists in my database.If it exists,i would like to delete it (or ignore it),if not to write it in the db.

I have been trying to do it something like this:

            Cursor cursor = ourDatabase.rawQuery("select 1 from "
                    + DBHelper.DATABASE_TABLE + " where " + DBHelper.TITLE + "='"
                    + title + "'" + " AND " + DBHelper.DATE + "='" + date + "'",
                    new String[] {});
            if (cursor.moveToFirst()) {
                boolean exists = (cursor.getCount() > 0);
                cursor.close();
                return exists;
            } else {
    return false;       }

        }

but i get this error:

06-06 16:01:11.341: E/AndroidRuntime(13867): Caused by: android.database.sqlite.SQLiteException: near "όσο": syntax error (code 1): , while compiling: select 1 from all_t where title='Ο ΦΟΒΟΣ και ο ΤΡΟΜΟΣ των διαδηλωτών!!! Αυτές οι γυναίκες είναι πιο σκληρές απ' όσο δείχνουν!' AND date='Wed, 05 Jun 2013 18:12:15'

score 10 · Accepted Answer · answered Jun 06 '13 at 13:19

This is a typical issue with not using the selectionArgs, and it is exactly why one should use them: You are quoting your string with simple quotes, but your string contains single quote, so SQLite detect the end of the string before it actually ends, and try to parse the rest as SQLite keyword.

The proper solution is to leave the escaping to SQLite, by using those selectionArgs rather that trying to do the escaping your self. in you case, that would be :

Cursor cursor = ourDatabase.rawQuery("select 1 from "
            + DBHelper.DATABASE_TABLE + " where " + DBHelper.TITLE + "=?"
            + " AND " + DBHelper.DATE + "=?",
            new String[] {title, date});

plus, it's cleaner because you can make a constant out of your query rather than constructing it every time.

however, @WarrenFaith is right, you should use query whenever possible. — njzk2, Jun 06 '13 at 13:37

score 6 · Answer 2 · answered Jun 06 '13 at 13:14

6

Don't use the raw query unless you have no other choice!

Cursor findEntry = db.query(TABLE, columns, DBHelper.TITLE + "=?", new String[] { title }, null, null, null);

or for multiple stuff

Cursor findEntry = db.query((TABLE, columns, DBHelper.TITLE + "=? and " + DBHelper.DATE + "=?", new String[] { title, date}, null, null, null);

answered Jun 06 '13 at 13:14

WarrenFaith

56,228
24
130
145

if i do the check without the title that returns the error,and only with the date,the code works.. – menu_on_top Jun 06 '13 at 13:18
I would just use `select _id` and than check `cursor.getCount() > 0`. @menu_on_top you can spare the `if moveToFirst()` it should be `if (cursor != null)` instead... – WarrenFaith Jun 06 '13 at 13:39

score 1 · Answer 3 · edited May 23 '17 at 12:34

You have to escape the ' character or it breaks your SQL.

near "όσο": syntax error (code 1): , while compiling: select 1 from all_t where title='Ο ΦΟΒΟΣ και ο ΤΡΟΜΟΣ των διαδηλωτών!!! Αυτές οι γυναίκες είναι πιο σκληρές απ' όσο δείχνουν!'

More info here.

Perhaps instead of directly manipulating strings in a rawQuery you can look into using a preparedStatement?

How do I use prepared statements in SQlite in Android?

score 1 · Answer 4 · answered Jun 06 '13 at 13:11

1

The answers given so far are correct, but you would be much better off using a parameterised query. There is no extra work involved - indeed it is easier that their suggestions of escaping the single quotes.

answered Jun 06 '13 at 13:11

Philip Sheard

5,641
5
25
42

android.database.sqlite.SQLiteException: near "...": syntax error (code 1)

4 Answers4

Linked

Related