IN and placeholders

Question

I am trying to execute the following SQL query in Android:

String names = "'name1', 'name2"; // in the code this is dynamically generated String query = "SELECT * FROM table WHERE name IN (?)"; Cursor cursor = mDb.rawQuery(query, new String[]{names}); 

However, Android does not replace the question mark with the correct values. I could do the following, however, this does not protect against SQL injection:

  String query = "SELECT * FROM table WHERE name IN (" + names + ")"; Cursor cursor = mDb.rawQuery(query, null); 

How can I get around this problem and be able to use the IN clause?

Answer 1

Form string "?, ?, ..., ?" can be a dynamically created string and safely fit into the original SQL query (since this is a limited form that does not contain external data), and then placeholders can be used as usual.

Consider the String makePlaceholders(int len) function String makePlaceholders(int len) , which returns len question marks separated by commas, and then:

 String[] names = { "name1", "name2" }; // do whatever is needed first String query = "SELECT * FROM table" + " WHERE name IN (" + makePlaceholders(names.length) + ")"; Cursor cursor = mDb.rawQuery(query, names); 

Just be sure to pass as many values ​​as places. The maximum default limit for host parameters in SQLite is 999 - at least in normal build, not sure about Android :)

Happy coding.

---

Here is one implementation:

 String makePlaceholders(int len) { if (len < 1) { // It will lead to an invalid query anyway .. throw new RuntimeException("No placeholders"); } else { StringBuilder sb = new StringBuilder(len * 2 - 1); sb.append("?"); for (int i = 1; i < len; i++) { sb.append(",?"); } return sb.toString(); } } 

Answer 2

A short example based on user response166390:

 public Cursor selectRowsByCodes(String[] codes) { try { SQLiteDatabase db = getReadableDatabase(); SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); String[] sqlSelect = {COLUMN_NAME_ID, COLUMN_NAME_CODE, COLUMN_NAME_NAME, COLUMN_NAME_PURPOSE, COLUMN_NAME_STATUS}; String sqlTables = "Enumbers"; qb.setTables(sqlTables); Cursor c = qb.query(db, sqlSelect, COLUMN_NAME_CODE+" IN (" + TextUtils.join(",", Collections.nCopies(codes.length, "?")) + ")", codes, null, null, null); c.moveToFirst(); return c; } catch (Exception e) { Log.e(this.getClass().getCanonicalName(), e.getMessage() + e.getStackTrace().toString()); } return null; } 

Answer 3

Unfortunately, there is no way to do this (obviously, 'name1', 'name2' not the only value and therefore cannot be used in a prepared statement).

This way, you will have to lower your gaze (for example, by creating very specific rather than repetitive queries such as WHERE name IN (?, ?, ?) ) Or not using stored procedures, and try to prevent SQL injections using some other methods. ..

Answer 4

As suggested in the accepted answer, but without using a user-defined function to generate a comma separated “?”. Please check the code below.

 String[] names = { "name1", "name2" }; // do whatever is needed first String query = "SELECT * FROM table" + " WHERE name IN (" + TextUtils.join(",", Collections.nCopies(names.length, "?")) + ")"; Cursor cursor = mDb.rawQuery(query, names); 

Answer 5

You can use TextUtils.join(",", parameters) to take advantage of sqlite binding parameters, where parameters is a list with "?" placeholders, and the result string is like "?,?,..,?" .

Here is a small example:

 Set<Integer> positionsSet = membersListCursorAdapter.getCurrentCheckedPosition(); List<String> ids = new ArrayList<>(); List<String> parameters = new ArrayList<>(); for (Integer position : positionsSet) { ids.add(String.valueOf(membersListCursorAdapter.getItemId(position))); parameters.add("?"); } getActivity().getContentResolver().delete( SharedUserTable.CONTENT_URI, SharedUserTable._ID + " in (" + TextUtils.join(",", parameters) + ")", ids.toArray(new String[ids.size()]) ); 

Answer 6

In fact, you can use your own query for android instead of rawQuery:

 public int updateContactsByServerIds(ArrayList<Integer> serverIds, final long groupId) { final int serverIdsCount = serverIds.size()-1; // 0 for one and only id, -1 if empty list final StringBuilder ids = new StringBuilder(""); if (serverIdsCount>0) // ambiguous "if" but -1 leads to endless cycle for (int i = 0; i < serverIdsCount; i++) ids.append(String.valueOf(serverIds.get(i))).append(","); // add last (or one and only) id without comma ids.append(String.valueOf(serverIds.get(serverIdsCount))); //-1 throws exception // remove last comma Log.i(this,"whereIdsList: "+ids); final String whereClause = Tables.Contacts.USER_ID + " IN ("+ids+")"; final ContentValues args = new ContentValues(); args.put(Tables.Contacts.GROUP_ID, groupId); int numberOfRowsAffected = 0; SQLiteDatabase db = dbAdapter.getWritableDatabase()); try { numberOfRowsAffected = db.update(Tables.Contacts.TABLE_NAME, args, whereClause, null); } catch (Exception e) { e.printStackTrace(); } dbAdapter.closeWritableDB(); Log.d(TAG, "updateContactsByServerIds() numberOfRowsAffected: " + numberOfRowsAffected); return numberOfRowsAffected; } 

Answer 7

It is unacceptable

 String subQuery = "SELECT _id FROM tnl_partofspeech where part_of_speech = 'noun'"; Cursor cursor = SQLDataBase.rawQuery( "SELECT * FROM table_main where part_of_speech_id IN (" + "?" + ")", new String[]{subQuery});); 

It is permissible

 String subQuery = "SELECT _id FROM tbl_partofspeech where part_of_speech = 'noun'"; Cursor cursor = SQLDataBase.rawQuery( "SELECT * FROM table_main where part_of_speech_id IN (" + subQuery + ")", null); 

Using ContentResolver

 String subQuery = "SELECT _id FROM tbl_partofspeech where part_of_speech = 'noun' "; final String[] selectionArgs = new String[]{"1","2"}; final String selection = "_id IN ( ?,? )) AND part_of_speech_id IN (( " + subQuery + ") "; SQLiteDatabase SQLDataBase = DataBaseManage.getReadableDatabase(this); SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder(); queryBuilder.setTables("tableName"); Cursor cursor = queryBuilder.query(SQLDataBase, null, selection, selectionArgs, null, null, null); 

Answer 8

I had the same problem and I think the accepted answer is really complex. I prefer the following:

 String names = "'name1', 'name2'"; // in the code this is dynamically generated String query = "SELECT * FROM table WHERE name IN (" + names + ")"; Cursor cursor = mDb.rawQuery(query, null);