C＃windows形成SQL dateadd等 [英] C# windows form SQL dateadd et like

问题描述

Hello,

原谅我的英语我不懂明白...谢谢你翻译，谢谢。

我无法解决sql搜索

我当前的代码效果很好并且告诉我我想要什么。

Forgive me my English that I do not understand ... thank you translator, thanks.
I can not solve a sql search
My current code works well and tells me what I want.

string RechMstpe18 = "Armoire";
   string RechEmail19 = "NON";
   string date1 = DateTime.Now.ToShortDateString(); 

sql = "Select * from Tble_MES_121 WHERE (Statut LIKE '" + RechMstpe18 + "%')" +
                " AND (Email LIKE '" + RechEmail19 + "%') AND (Date_vente IS NULL)";

我需要添加下面的代码来改善我的搜索...但这不起作用。

I need to add the code below to improve my search ... but that does not work.

AND (DATEadd(dd, -7, CONVERT(Date_fin_vente)) Like '" + date1 + "%')

谁能帮我解决这个问题？





谢谢，



我拥有什么试过：

Who can help me solve this problem ?


thank,

What I have tried:

connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\resources\BDD_Suivi_vente.accdb;Persist Security Info=False";

sql = "Select * from Tble_MES_bds_121 WHERE (Statut LIKE '" + RechMstpe18 + "%')" +
                " AND (Email LIKE '" + RechEmail19 + "%') AND (Date_MES IS NULL) AND (DATEadd(dd, -7, CONVERT(Date_fin_MSTPE)) Like '" + date1 + "%')";

推荐答案

如果日期字段为IS NULL它不能是填充日期。因此，WHERE子句中排除了填充日期

或许：

IF a DATE-field IS NULL it cannot be a filled date. SO the filled dates are excluded in your WHERE clause
Perhaps:

AND (Date_MES IS NULL OR Date_MES BETWEEN (DATEadd(dd, -7, CAST( date1 As DAteTime) AND CAST( date1 As DAteTime))

作为查询的一部分

as part of your query

我没有你的答案，我的 MS Access 有点生疏，日期不是我的强项。

在访问查询中使用日期作为条件的示例 - 访问 [ ^ ]



但是;我有义务指出您使用的是易受SQL注入攻击的代码。你不应该把一个SQL语句从组合字符串组合在一起。

将变量添加到其中的正确方法是在查询中使用占位符然后添加参数到OLE命令。 OLE将占位符限制为问号（？），并且添加的值需要按照准确的顺序填写准备好的语句。



这大致是您的查询应该是什么样子

I do not have the answer for you, my MS Access is a little rusty and Dates in there were not my strong point.
Examples of using dates as criteria in Access queries - Access[^]

However; I am obligated to point out that you are using code that is susceptible to SQL Injection. You should NEVER piece together an SQL statement from combining strings together.
The proper way to add the variables into this would be to use placeholders within your query and then add Parameters to the OLE Command. OLE restricts the placeholders to the question mark (?), and the values that are added need to be in the exact order to fill in the prepared statement.

This is roughly what your query should look like

connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\resources\BDD_Suivi_vente.accdb;Persist Security Info=False";

sql = "Select * from Tble_MES_bds_121 WHERE (Statut LIKE ? + '%') AND (Email LIKE ? + '%') AND (Date_MES IS NULL) AND (DATEadd(dd, -7, CONVERT(Date_fin_MSTPE)) Like ? '%')";

OleDbCommand cmd = new OleDbCommand(sql, connection);
cmd.Parameters.AddWithValue("@Statut", RechMstpe18);
cmd.Parameters.AddWithValue("@Email", RechEmail19);
cmd.Parameters.AddWithValue("@MSTPE", date1);

sql = "Select * from Tble_MES_121 WHERE (Statut LIKE '" + RechMstpe18 + "%')" + " AND (Email LIKE '" + RechEmail19 + "%') AND (Date_vente IS NULL)";

没有必要解决您的问题，但你有另一个问题。

永远不要通过连接字符串来构建SQL查询。迟早，您将使用用户输入来执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易并且容易出错。

名称中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

按示例进行SQL注入攻击 [ ^ ]

PHP：SQL注入 - 手册 [ ^ ]

SQL注入预防备忘单 - OWASP [ ^ ]

我该怎么办？解释没有技术术语的SQL注入？ - 信息安全堆栈交换 [ ^ ]

Not necessary a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

这篇关于C＃windows形成SQL dateadd等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！