比较存储过程中的日期时间变量 [英] Compare datetime variables in stored procedure

问题描述

我正在尝试比较存储过程sql server中的两个日期时间变量。

在下面的代码片段中，@ createDate被视为用户输入，然后与datetime类型的列进行比较。我无法检查''=''属性

I am trying to compare two datetime variables in a stored procedure sql server.
In the below code snippet @createdDate is taken as user input and then comparing with a column of type datetime. I am unable to check the ''='' property

set @sqlquery = 'Select
            v.*,
            vsc.vidhanSabhaConstituencyName, where 1=1 '

set @sqlquery = @sqlquery +'AND v.createdBy ='''+cast(@createdBy as nvarchar(100))+''''

if(@VoterIdNumber is not null)
set @sqlquery = @sqlquery+'AND v.voterIDNumber= '''+@VoterIdNumber+''''

if(@createdDate is not null)
set @sqlquery = @sqlquery+'AND v.dataIsCreated = '''+cast(@createdDate as varchar(100))+''''
else
set @sqlquery = @sqlquery+'AND v.dataIsCreated= '''+cast(getdate() as varchar(100))+'''' 

Execute sp_Executesql @sqlquery

我尝试过：



我尝试过铸造和转换@createdDate变量但没有成功。

它适用于其他运算符，如> =或< =但不是机智h =。



帮助表示赞赏

What I have tried:

I've tried casting and converting the @createdDate variable without success.
It works with other operators like >= or <= but not with = .

Help is appreciated

推荐答案

不要这样做！



您的代码容易受到 SQL注入 [ ^ ]。 从不使用字符串连接来构建SQL查询。 总是使用参数化查询。



在SQL中，这意味着使用 sp_executesql [ ^ ]正确：

Don't do it like that!

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

In SQL, that means using sp_executesql[^] properly:

DECLARE @sqlquery nvarchar(2000);

SET @sqlquery = N'SELECT v.*, vsc.vidhanSabhaConstituencyName
FROM SomeTable As v
INNER JOIN SomeOtherTable As vsv ON <join conditions>
WHERE 1 = 1';

SET @sqlquery = @sqlquery + N' AND v.createdBy = @createdBy';

If @VoterIdNumber Is Not Null SET @sqlquery = @sqlquery + N' AND v.voterIDNumber = @VoterIDNumber';

If @createdDate Is Null SET @createdDate = GetDate();
SET @sqlquery = @sqlquery + N' AND v.dataIsCreated = @createdDate';

EXEC sp_executesql
    @sqlquery,
    N'@createdBy varchar(50), @VoterIDNumber varchar(50), @createdDate date', -- TODO: Use the correct types here
    @createdBy = @createdBy,
    @VoterIDNumber = @VoterIDNumber,
    @createdDate = @createdDate
;

---

你想知道关于SQL注入的一切（但不敢问）|特洛伊亨特 [ ^ ]

如何在没有技术术语的情况下解释SQL注入？ |信息安全堆栈交换 [ ^ ]

查询参数化备忘单| OWASP [ ^ ]

---

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

为什么要尝试使用基于字符串的日期？

如果您的dataIsCreated列是VARCHAR或NVARCHAR，那么这是一个错误：改为使其成为DATE，DATETIME或DATETIME2值，并进行比较DATE或DATETIME值。



问题是基于字符串的日期取决于插入它们的机器的区域设置，而您几乎无法控制它。并且日期可以以三种主要格式输入：yyyy / MM / dd，MM / dd / yyyy和dd / MM / yyyy - 因此当您将日期转换为字符串时，您绝对无法真正控制您所比较的内容什么！



存储和比较日期作为日期 - 如果你不这样做;你会给自己一大堆问题。

Why are you trying to use string based dates?
If your dataIsCreated column is VARCHAR or NVARCHAR, then that's a mistake: make it a DATE, DATETIME, or DATETIME2 value instead, and compare DATE or DATETIME values.

The problem is that string based dates depend on the locale setting for the machine that inserted them, and you have little control over that. And dates can be entered in three main formats: yyyy/MM/dd, MM/dd/yyyy, and dd/MM/yyyy - so when you cast your date to a string you have absolutely no real control over what you are comparing to what!

Store and compare dates as dates - if you dont; you will give yourself a huge numebr of problems.

这篇关于比较存储过程中的日期时间变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！