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'

njzk2 · Accepted Answer · 2013-06-06 13:19:41Z

10

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.

answered Jun 6, 2013 at 13:19

njzk2

39.4k7 gold badges72 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

njzk2 Over a year ago

however, @WarrenFaith is right, you should use query whenever possible.

WarrenFaith · Accepted Answer · 2013-06-06 13:14:40Z

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 6, 2013 at 13:14

WarrenFaith

57.7k25 gold badges139 silver badges152 bronze badges

2 Comments

menu_on_top Over a year ago

if i do the check without the title that returns the error,and only with the date,the code works..

WarrenFaith Over a year ago

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

Philip Sheard · Accepted Answer · 2013-06-06 13:11:37Z

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 6, 2013 at 13:11

Philip Sheard

5,8335 gold badges30 silver badges42 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:34:18Z

1

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?

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered Jun 6, 2013 at 13:08

Ken Wolf

23.3k6 gold badges68 silver badges90 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related