Delete multiple rows using IDs?

Question

How can I delete multiple rows by a list of IDs in Android SQLite database?

I have defined a general delete method in this manner:

protected void deleteWhere(String whereClause, String[] whereArgs) {
    try {
        databaseHelper.getWritableDatabase().delete(
                getTableName(), whereClause, whereArgs);
    } finally {
        databaseHelper.close();
    }
}

And now I'm trying to call it with a list of IDs:

public void deleteAll(Iterable<T> entities) {
    Iterable<Long> ids = Iterables.transform(entities, getId);

    String whereClause = getIdColumn() + " IN (?)";
    String[] whereArgs = { TextUtils.join(",", ids) };
    deleteWhere(whereClause, whereArgs);
}

If the ID list contains for example the values [1, 2, 42], then I assume the resulting SQL should be:

DELETE FROM tableName WHERE _id IN (1,2,42);

But this doesn't seem to work correctly. If the list contains only 1 ID, then it is correctly deleted. However, if I provide multiple values, than zero rows are affected. What am I doing wrong?

Community · Accepted Answer · 2017-05-23 11:53:13Z

9

When you give a single string as whereArgs, a single string ends up in the SQL command, as if you had written this:

... WHERE _id IN ('1,2,42')

This would compare each _id value against the value '1,2,42', which of course does not work.

If you use three parameter markers and give three strings in the whereArgs array, you would end up with three strings, like this:

... WHERE _id in ('1','2','42')

This works only when the _id column has integer affinity, which is true for a column declared as INTEGER PRIMARY KEY, but not in the general case.

The Android database API does not allow query parameters to have any type but string. When using integers, you should just insert them directly (as in ianhanniballake's answer):

String whereClause = getIdColumn() + " IN (" + TextUtils.join(",", ids) + ")";

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Oct 7, 2014 at 7:13

CL.

182k18 gold badges241 silver badges282 bronze badges

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

2 Comments

Natix Over a year ago

Oh, now when I see the way the apostrophes are added to the values, it makes sense. However, isn't putting the values directly into whereClause prone to SQL injection? Wouldn't it be better to manually generate a whereClause with appropriate number of question marks and then pass the IDs into the whereArgs as String array?

CL. Over a year ago

Allowing only strings as parameters is a bug in the design of the Android database API. Anyway, integer values are safe against SQL injection; this problem occurs only with strings.

ianhanniballake · Accepted Answer · 2014-10-07 02:41:56Z

4

You can't use whereArgs for IN statements due to the escaping done by whereArgs (unless you make a separate ? for each value) - instead, you have to embed the ids in your where statement:

String whereClause = getIdColumn() + " IN (" + TextUtils.join(",", ids) + ")";
deleteWhere(whereClause, null);

answered Oct 7, 2014 at 2:41

ianhanniballake

201k31 gold badges509 silver badges483 bronze badges

Comments

eshayne · Accepted Answer · 2014-10-07 01:34:55Z

0

Each "?" will only bind to a single value. So if your id list has three values, your whereClause would need to be "_id IN (?,?,?)"

answered Oct 7, 2014 at 1:34

eshayne

9359 silver badges15 bronze badges

2 Comments

Natix Over a year ago

But I'm passing the IDs as one string consisting of the values joined by commas. That isn't permitted?

eshayne Over a year ago

Good point - your whereArgs would also need to be an array of three values, not a single comma-separated string.

Collectives™ on Stack Overflow

Delete multiple rows using IDs?

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related