Sqlite IN clause not working using values from an array in android?

Question

I want to execute a sqlite query:

c =  db.rawQuery("SELECT " + KEY_BOOK_ID+ " , " + KEY_BOOK_CODE  + " , " + KEY_ISBN + " , " +
                 KEY_VOL_NO  + " , " + KEY_BRAND  + " , " + KEY_SKU + " , " + KEY_TITLE +
                 " , " + KEY_PRICE + " , " + KEY_LANG + " , " + KEY_STATUS + " FROM " +
                 BOOKS_TABLE + " WHERE " + KEY_SKU + " IN " + inClause + ";", null);

The values in the in clause need to be taken from an array of strings:

String values[] = {"Singles","5 in 1 series","Childrens Book"};

Following is my error log.

Caused by: android.database.sqlite.SQLiteException: near "1": syntax error (code 1): , while compiling: SELECT book_id , book_code , ISBN , vol_no , brand , sku , title , price , lang , status FROM books_table WHERE sku IN (Singles, 5 in 1 series, Childrens Book); at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1112) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:689) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:37) at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44) at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1433) at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1372)

This is the method that I call to get the Json Object.

public JSONObject searchForBooks( )
{
    String values[] = {"Singles","5 in 1 series","Childrens Book"};
    String inClause = Arrays.toString(values);

    //replace the brackets with parentheses
    inClause = inClause.replace("[","(");
    inClause = inClause.replace("]",")");

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c ;
    c =  db.rawQuery("SELECT " +KEY_BOOK_ID+ " , " + KEY_BOOK_CODE  + " , " + KEY_ISBN +
                " , " + KEY_VOL_NO  + " , " + KEY_BRAND  + " , " + KEY_SKU + " , " +
                KEY_TITLE + " , " + KEY_PRICE+ " , " + KEY_LANG+ " , " + KEY_STATUS
                + " FROM " + BOOKS_TABLE + " WHERE " + KEY_SKU + " IN " + inClause + ";",
                null);

    if (c.moveToFirst()) {
        do {
            JSONObject jobj = new JSONObject();
            try {
                jobj.put("book_id", c.getString(0));
                jobj.put("book_code", c.getString(1));
                jobj.put("ISBN", c.getString(2));
                jobj.put("vol_no", c.getString(3));
                jobj.put("brand", c.getString(4));
                jobj.put("sku", c.getString(5));
                jobj.put("title", c.getString(6));
                jobj.put("price", c.getString(7));
                jobj.put("lang", c.getString(8));
                jobj.put("status", c.getString(9));

                jarr1.put(0, jobj);
                jm1.put("books_info", jarr1);

                Log.d("Selected BOOKS info", jm1.toString());

            } catch (JSONException e) {
                e.printStackTrace();
            }
        } while (c.moveToNext());
    }
    c.close();
    db.close();

    return jm1;
}

Any help or suggestion is appreciated.Thank you.

MySQL or SQLite? Remove unrelated databases tags please

Stefano Zanini
– Stefano Zanini

2017-05-10 07:19:38 +00:00
Commented May 10, 2017 at 7:19 — Stefano Zanini
– Stefano Zanini, Commented May 10, 2017 at 7:19

Stefano Zanini · Accepted Answer · 2017-05-10 13:07:56Z

2

You're missing the quotes around those strings.

I would build the in clause in a slightly different way though

String values[] = {"Singles","5 in 1 series","Childrens Book"};
boolean first = true;
String inClause = "(";

for(String v : values){
    if(first){
        first = false;
    } else {
        inClause += ","
    }
    inClause += "'" + v + "'";
}
inClause += ")";

Edit

To avoid what CL. describes in his comment, you can replace

inClause += "'" + v.replaceAll("'", "''") + "'";

with

inClause += "'" + v + "'";

This way if you have single quotes inside your strings they will be escaped and won't mess the final query up.

edited May 10, 2017 at 13:07

answered May 10, 2017 at 7:15

Stefano Zanini

5,9362 gold badges15 silver badges33 bronze badges

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

3 Comments

CL. Over a year ago

This will blow up when a string contains a quote.

Stefano Zanini Over a year ago

@CL. You're right, and if that array comes from user input it will open other serious issues too (like SQL injection), but from how the existing code is presented I'm assuming OP has some degree of control over that array. I fixed it though

sirzento Over a year ago

How can one do this but with SQL injection protection?

Collectives™ on Stack Overflow

Sqlite IN clause not working using values from an array in android?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related