英文:
SQLite Local Date(Shamsi) comparison
问题
我已经在我的SQLite数据库中存储了带有本地日期（Shamsi）的字段。我想获取一个日期在date_1和date_2之间的数据库列表。当使用常量值时，它可以正常工作，如下所示：
db!.rawQuery("SELECT * FROM $tblVisit WHERE $colVisitDate BETWEEN '1401-10-01' AND '1401-12-29'")
但我想使用来自函数输入的变量，而不是常量值。我该如何编写类似以下的内容：
String? date_1, String? date_2
db!.rawQuery("SELECT * FROM $tblVisit WHERE $colVisitDate BETWEEN $date_1 AND $date_2")
现在，最后一条语句返回空列表，但第一条可以正常工作。谢谢。
英文:
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
答案1
得分: 1
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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论