7

I'm calling SQLite like this

String[] args = new String[]{"(A,B)"}
Cursor cur = db.query("tab1", null, "name in ?", args, null, null, null);

and receiving Exception:

android.database.sqlite.SQLiteException: near "?": syntax error: , while compiling: SELECT * FROM tab1 WHERE name in ?

How to use in operator in query() ?

I already tried

String[] args = new String[]{"('A','B')"}
PeterMmm
  • 24,152
  • 13
  • 73
  • 111
  • possible duplicate of [Android/SQLite IN clause and placeholders](http://stackoverflow.com/questions/7418849/android-sqlite-in-clause-and-placeholders) – Aleks G Apr 30 '13 at 08:37

4 Answers4

5
String[] args = new String[]{A,B} // if A, B are variables
String[] args = new String[]{"A","B"}    
Cursor cur = db.query("tab1", null, "name in (?,?)", args, null, null, null);
Lion789
  • 4,402
  • 12
  • 58
  • 96
Hoan Nguyen
  • 18,033
  • 3
  • 50
  • 54
4

While working on a project and struggling with this very same question I found these other questions (and answers) helpful:

  1. Sqlite Query for multiple values in one columm
  2. Android/SQLite IN clause and placeholders

Here is what I've found works:

String[] args = new String[] {"A", "B"};
Cursor cur = db.query("tab1", null, "name in(?,?)", args, null, null, null);

As will:

String args = "A, B";
Cursor cur = db.query("tab1", null, "name in(" + args + ")", null, null, null, null);



So you can either use multiple ? with the IN() statement and match each with an element in selectionArgs array (like the first example). If you have multiple conditions in your WHERE clause, make sure to match the ? with the correct element in selectionArgs:

String[] args = new String[] {"Current", "A", "B"};
Cursor cur = db.query("tab1", null, "IsCurrent=? AND name in(?,?)", args, null, null, null);


Or you can just use a string made up of comma-delineated arguments directly in the IN() statement in the selection string itself (like the second example).



The referenced questions seemed to indicate that you could use a single ? in the IN() and somehow expand the associated parameter(s), but I was not able to get that to work.

Community
  • 1
  • 1
Ryan Manes
  • 59
  • 4
  • 1
    Never ever construct any sql parts from strings. It is vulnerable to sql injection. – PeterMmm May 03 '13 at 06:13
  • @PeterMmm I am ignorant on the topic of sql injection (aside from the rule to always using parameterized SQL calls), so even if all the data were from within the app, it is still possible to have an injection attack from a query? ...Or is it that the query string could be corrupted if sent to a SQLite db outside the app? – Ryan Manes May 03 '13 at 16:26
  • The point is, that all requests (should) going thru the same method (query() in this case). If query() is vulnerable and fixed it will be no more (or less) vulnerable. – PeterMmm May 03 '13 at 18:52
  • Your point "if all the data were from within the app" has given big surprises to developers when they discover "upss, someone may inject here uncontrolled data". We are smart, but hackers are even smarter, – PeterMmm May 03 '13 at 18:53
  • Indeed. The approach with string concatenation should **NOT** be used due to SQL injection vulnerabilities. As the Python's psycopg2 docs usually say: **"Never, never, NEVER use string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint."** This is true on Android and SQLite too. The correct approach is the first one, i.e. `String[] args = new String[] {"A", "B"}; Cursor cur = db.query("tab1", null, "name in(?,?)", args, null, null, null);` which makes use of the selection and selectionArgs parameters – Alex Bitek Jun 29 '15 at 15:22
0

If I am not mistaken, arg parsing does not work with IN clause. So you can't use '?' in your WHERE clause.

You should do something like this :

String args = "A, B";
Cursor cur = db.query("tab1", null, "name in (" + args + ")", null, null, null, null);

and construct your args with a loop if needed.

vdechef
  • 257
  • 3
  • 10
  • 1
    This is vulnerable to SQL injection. See http://stackoverflow.com/questions/16295432/sqlite-in-operator-in-query#comment50252085_16349334 If you have many elements, you can construct the selection programatically with the placeholders in it and pass the values as the selectionArgs parameter. You shouldn't, under any circumstances, build the query with the values using string concatenation or formatting – Alex Bitek Jun 29 '15 at 15:28
0

If i understand right, you can solve your problem like this:

List<String> exercise =  new ArrayList<>();
exercise.add(parameter1);
exercise.add(parameter2);
exercise.add(parameter3);

Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM exersisesenglish WHERE sex " + " 
=?" + " AND level" + "=?" + " AND wish" + "=?" + " AND place" + "=?" + " AND lang" + 
"=?" +" AND stay_exersise IN (" + exercise.get(0) + "," + exercise.get(1) + "," + 
exercise.get(2) + "," + exercise.get(3) + ")", new String[]{String.valueOf(sex), 
String.valueOf(level), String.valueOf(wish), String.valueOf(place), 
String.valueOf(loc)});
Miramax Mars
  • 147
  • 2
  • 3