如何检查当前所选日期是否位于数据库表中的日期存储之间 [英] How to check the current selected date whether lies between the dates store in database table

问题描述

我正在处理休假管理系统。假设Empid = 00001，他从19-05-05-2019到06-06-2019休假8天，这些数据保存在数据库表中。现在再次如果相同的Empid = 0001，尝试再次申请30-05-2019至06-06-2019之间的休假，假设他正试图申请一天休假03-06-2019至03-06-2019介于两者之间以上日期。因此，应首先检查该员工记录是否已在数据库中保留从30-05-2019到06-06-2019的假期。因此，不应允许员工在此日期申请任何其他休假。



LeavefromDate - 表格栏目

StartDate- - 保存当前休假开始日期的变量。

EndDate - 保存当前休假结束日期的变量。



我的数据库是sqlite3。



寻找完美的SQLite查询工作



我尝试过：



从empleave中选择*，其中Empcode ='+ txtLeaveempcode.Text +'和IsUploaded ='0'和（date（substr（LeavefromDate， 7,4）||' - '|| substr（LeavefromDate，4,2）||' - '|| substr（LeavefromDate，1,2））'+ StartDate +'和'+ EndDate +之间'）OR（date（subsfrom（LeavefromDate，7,4）||' - '|| substr（LeavefromDate，4,2）||' - '|| substr（LeavefromDate，1,2））'+ StartDate +'和'+ EndDate +'）;

I am working on the Leave management system. Suppose Empid=00001, and he had taken leave for 8 days from 30-05-2019 to 06-06-2019 and these data saved in a database table. Now again if the same Empid=0001, try to apply leave between 30-05-2019 to 06-06-2019 again, suppose he is trying to apply one day leave 03-06-2019 to 03-06-2019 which lies between above date. So it should check first that for this employee record there is a leave from 30-05-2019 to 06-06-2019 already in the database. So it shouldn't allow the employee to apply for any other leave on this date duration.

LeavefromDate -- table column
StartDate-- a variable that holds current leave start date.
EndDate--a variable that holds current leave End date.

My database is in sqlite3.

Looking for the perfect SQLite query to work

What I have tried:

Select * from empleave where Empcode ='" + txtLeaveempcode.Text + "' and IsUploaded='0' and (date(substr(LeavefromDate, 7, 4) || '-' || substr(LeavefromDate, 4, 2) || '-' || substr(LeavefromDate, 1, 2) ) between '" + StartDate + "' and '" + EndDate + "') OR (date(substr(LeavefromDate, 7, 4) || '-' || substr(LeavefromDate, 4, 2) || '-' || substr(LeavefromDate, 1, 2) ) between '" + StartDate + "' and '" + EndDate + "');

推荐答案

不是那样的！永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。总是使用参数化查询。



连接字符串时会导致问题，因为SQL会收到如下命令：

Not like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

哪个SQL看作三个单独的命令：

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

完全有效的SELECT

A perfectly valid SELECT

DROP TABLE MyTable;

完全有效的删除表格通讯和

A perfectly valid "delete the table" command

--'

其他一切都是评论。

所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。



所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。你定期做备份，不是吗？



然后有你的数据库设计......哦亲爱的。你的查询如此丑陋和难以解决的部分原因是你将所有内容都存储为字符串。这很糟糕 - 非常糟糕 - 你需要在整个应用程序中解决这个问题。始终以适当的格式存储数据：数字输入INT，DECIMAL或FLOAT列，日期输入DATE，DATETIME或DATETIME2。如果你没有;你已经看到了这项任务，你的工作变得更加复杂。



当你通过整个应用程序解决了这个问题时，你可以开始研究如何解决你已经注意到的问题 - 但是在你有这个问题之前没有意义！

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

And then there is your DB design ... oh dear. Part of why your query is so ugly and difficult to work out is that you are storing everything as strings. That's bad - very bad - and you need to fix that throughout your app as well. Always store data in the appropriate formats: numbers go in INT, DECIMAL, or FLOAT columns, Dates go in DATE, DATETIME, or DATETIME2 for example. If you don;t you make your job a whole load more complex as you have already seen with this task.

When you've fixed that through your whole app, you can start looking at how to fix the problem you have noticed - but there is no point in trying until you have!

这篇关于如何检查当前所选日期是否位于数据库表中的日期存储之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！