sqflite IN operator with AND operator

Question

I have following query which select from employee table where name is "max" and ID not in 123 and 444. Not in IDs can grow in future. But I am receiving error as

Error

( 8023): [ERROR:flutter/lib/ui/ui_dart_state.cc(148)] Unhandled Exception: DatabaseException(near "?": syntax error (code 1 SQLITE_ERROR): , while compiling:

Query

     List<String> a = [];
        a.add("123");
        a.add("444");

        var table = await mydb.rawQuery(
            "SELECT value from employee  WHERE employeename = ? AND id NOT IN ? ORDER BY timestamp DESC",
            ["max", a]);

Yes i got that. Is the `LIST` value fixed? I mean is it 2 always? If so,one way is that the parameter (`?`) should match no of values. something like `..NOT IN (?,?) ...` and put the values in `whereArgs: a` or you have to join the values with `,`..something like `id IN (${a.join(', ')})` — Arun Palanisamy, Dec 09 '19 at 06:50
List of values will be changing, Now 2 but in future can be 5 , 6. depend on scenario — max, Dec 09 '19 at 07:11

score 8 · Accepted Answer · answered Dec 09 '19 at 08:13

8

If the LIST is unpredictable, one way is that you can use JOIN to create your select statement with required value of NOT IN. Below is one sample.

void main() {
  List<String> a = [];
  a.add("123");
  a.add("444");

  var select =
      'SELECT value from employee  WHERE employeename = ? AND id NOT IN (\'' +
          (a.join('\',\'')).toString() +
          '\') ORDER BY timestamp DESC';

  var table = await mydb.rawQuery(select, ["max"]);
}

If you print the variable select you will get

SELECT value from employee  WHERE employeename = ? AND id NOT IN ('123','444') 
ORDER BY timestamp DESC.

Then you can pass the above statement to rawquery and get your result.

P.S:Use your single quote and double quote accordingly.

answered Dec 09 '19 at 08:13

Arun Palanisamy

5,281
6
28
53

Solution worked. this is pro level. I could optimize the code due to this. – max Dec 09 '19 at 10:39
1

You're just asking for a Bobby Tables SQL Injection attack if any of your SQL is done by producing a string at runtime. Please don't do that. That's what placeholders are for! https://bobby-tables.com – Randal Schwartz Dec 13 '19 at 14:21
@Randal Schwartz, I agree, This code is prone to SQL injection. I was trying to solve OP's existing problem. I will add a word of caution to the answer and will try to provide an alternate Query avoiding SQL injection. – Arun Palanisamy Dec 13 '19 at 15:34
This is just asking for SQL injection. Dont do this!!!!!!!! – Hemil Nov 10 '21 at 04:49
1

Ah! After hours I find your solution ! – Noor Hossain Sep 25 '22 at 13:23

score 2 · Answer 2 · answered Dec 09 '19 at 08:14

I'd go for @arun-palanisamy 's solution, see his comment. Props go to him. I just tried the following -- with Groovy/Postgres, but the error seems to be the same, so you might want to give it a try:

String[] a = ['123', '444']

// your code, throws 'ERROR: syntax error at or near "$2"':
// def table = sql.execute("SELECT value from employee WHERE employeename = ? AND id NOT IN ? ORDER BY timestamp DESC", ["max", a])

// code of arun-palanisamy, compiles:
def table = sql.execute("SELECT value from employee WHERE employeename = ? AND id NOT IN (${a.join(', ')}) ORDER BY timestamp DESC", ["max", a])

Side notes:

You might want to try a different type for a, such as Array in my code, or even a HashMap.
There are examples (like here) where the number of ? are generated dynamically.

Update: Go for this answer, we posted simultaneously

This is a possible SQL Injection attack. DO NOT DO THIS. Use placeholders. Do not generate SQL at runtime from program data. https://bobby-tables.com — Randal Schwartz, Dec 13 '19 at 14:22

sqflite IN operator with AND operator

2 Answers2