Search code examples
fluttersqlitedartdatecomparison

SQLite Local Date(Shamsi) comparison

I've stored fields with the local date(Shamsi) in my SQLite DB. I want to get a list of DB where its date is between date_1 and date_2. It works when using a const value as below: db!.rawQuery(("SELECT * FROM $tblVisit WHERE $colVisitDate BETWEEN '1401-10-01' AND '1401-12-29' ")

but I like to use variables from function input instead of const value. How can I write something like follows:

String? date_1, String? date_2

db!.rawQuery("SELECT * FROM $tblVisit WHERE $colVisitDate BETWEEN $date_1  AND $date_2 ")

Now, the last statement returns no list but the first one works well. Thanks

Solution

  • Text/String values must be enclosed in single quotes so

    db!.rawQuery("SELECT * FROM $tblVisit WHERE $colVisitDate BETWEEN '$date_1'  AND '$date_2' ")

    Otherwise the dates, as they only contain numerics and operators, are considered an expression so 1401-10-01 becomes 1390 and thus unusual results.

    However, this is susceptible to SQL injection and thus it is considered better practice by the SQL Injection Police to use parameter binding. That is, code ? placeholders instead of the values, e.g. BETWEEN ? AND ? and then use the selectionArgs parameter of rawQuery for the 2 date values (note that you cannot bind component names and keywords).

    See rawQuery