JDBC to android.sqlite: SELECT/DELETE … WHERE field IN (?)

Question

I am just porting some JDBC code (intended for use with HSQLDB) to Android’s own SQLite implementation. I have a snippet where I delete records based on a particular field matching one of the values in a String[] array in my Java code.

Here is the JDBC code for the DELETE statement:

String[] ids = getIdsSomehow();
PreparedStatement stmtD = db.prepareStatement("delete from message where id in (unnest(?))");
Array delIdArray = stmtD.getConnection().createArrayOf("VARCHAR", ids);
stmtD.setArray(1, delIdArray);
stmtD.execute();
stmtD.close();

Another snippet does a SELECT instead of DELETE and has the values in a List<String> instead of an array.

How would I accomplish this with the methods offered by SQLiteDatabase, preferably in a way that does not open up any SQL injection vulnerabilities?

score 0 · Accepted Answer · answered Jul 04 '20 at 20:10

The main “ingredients” to make this work with HSQLDB over JDBC, namely the unnest() function of the DBMS, and the ability to pass array values to SQL statements, are not available with the android.sqlite stack, making a workaround necessary:

String[] ids = getIdsSomehow();
String whereClause = "id in (" + TextUtils.join(",", Collections.nCopies(ids.size(), "?")) + ")";
db.delete("message", whereClause, ids);

This builds a where clause à la id in (?,?,?,?) with the correct number of question marks.

For the selection, use rawQuery() with a SELECT statement built in the same manner. The List<String> can be converted to an array like this:

ids.toArray(new String[]{})

Input came from this answer and its comments.

JDBC to android.sqlite: SELECT/DELETE … WHERE field IN (?)

1 Answers1