从以下两个sql命令获得不同的结果，但是需要输出1171行。解决方案将不胜感激。提前致谢。 [英] getting different results from below two sql commands however output of 1171 rows required. Solution will be appreciated. Thanks in advance.

问题描述

 声明  @ FromDate   varchar （ 20 ）= '  01-02-2010'， @ ToDate   varchar （ 20 ）= '  31-12-2012' 
 < span class =code-keyword> set   dateformat  dmy 
 
  1 < /跨度>。 
 选择 * 从 View_getReportFullDataDump 
 其中 cast（强制转换（转换（ datetime ，[日期   Intimation  to 警惕]） as   float ） as   int ）之间强制转换（强制转换（CAST（ @ FromDate   as   datetime ） as   float ） as   int ）和 cast（ Cast（CAST（ @ ToDate   as   datetime ） as   float ） as   int ）
 订单 按 CaseUIDNo 
 
 输出  -  < span class =code-digit> 1171 行
 
  2 。 
 执行（'  select * From View_getReportFullDataDump'  + 
 '  where cast（cast（转换（datetime，[Intimation of Intimation to Vigilance] ）作为浮点数）int）在强制转换之间（Cast（CAST（' +  @ FromDate  + '  as datetime）as float）as int）and cast（Cast（CAST（' +  @ ToDate  + '  as datetime）as float）as int）' + 
 ' 按CaseUIDNo排序'）
 
  output   -   0 行

解决方案

您的连锁执行没有您的日期字符串的单引号。



更改

 '  cast（Cast（CAST（' +  @ FromDate  + '  as datetime）as float）as int）' 

< br $>
要

 '  cast（强制转换（CAST（''' +  @ FromDate  + ' ''as datetime）as float）as int）' 

并重复约会



您目前正在尝试执行的是：



< pre lang =SQL> cast（演员（CAST（31-12-2012 as datetime ）< span class =code-keyword> as float ） as < span class =code-keyword> int ）

我很惊讶你没有收到错误。



但是这种类型的SQL执行很危险。



我可以使用你的申请提交：

 ' ; DELETE FROM ReportData;' 

半冒号会终止语句的第一部分，这会出错，然后它会运行我的删除命令。



这个攻击类型称为SQL注入攻击。



使用sp_ExecuteSQL可以安全地将参数传递到动态SQL中。



SQL注入信息



https://www.owasp.org/index.php/SQL_Injection [ ^ ]



关于sp_ExecuteSQL的信息vs执行和使用参数



< a href =http://www.codeproject.com/Articles/20815/Building-Dynamic -SQL-in-a-Stored-Procedure>在存储过程中构建动态SQL [ ^ ]

declare @FromDate varchar(20)='01-02-2010',@ToDate varchar(20)='31-12-2012'
set dateformat dmy

1.
select * From View_getReportFullDataDump
where cast(cast(Convert(datetime,[Date of Intimation to Vigilance]) as float) as int) Between cast(Cast(CAST(@FromDate   as datetime) as float) as int) and  cast(Cast(CAST(  @ToDate   as datetime) as float) as int)
Order By CaseUIDNo

output - 1171 rows

2.
execute('select * From View_getReportFullDataDump' +
			' where cast(cast(Convert(datetime,[Date of Intimation to Vigilance]) as float) as int) Between cast(Cast(CAST(' + @FromDate + ' as datetime) as float) as int) and  cast(Cast(CAST(' + @ToDate + ' as datetime) as float) as int)' + 
			 ' Order By CaseUIDNo')

output - 0 rows

解决方案

Your concatenated execute doesn't have single quotes for your date strings.

Change

'cast(Cast(CAST(' + @FromDate + ' as datetime) as float) as int)'

To

'cast(Cast(CAST(''' + @FromDate + ''' as datetime) as float) as int)'

And repeat for to date

What you're currently trying to execute is:

cast(Cast(CAST(31-12-2012 as datetime) as float) as int)

I'm surprised you're not getting an error.

But this type of SQL execution is dangerous.

I could potentially use your application to submit this:

';DELETE FROM ReportData;'

The semi colon would terminate the first part of the statement, which would error, and then it would run my delete command.

This type of attack is known as an SQL Injection attack.

Using sp_ExecuteSQL would allow you to safely pass your parameters into your dynamic SQL.

Info on SQL Injections

https://www.owasp.org/index.php/SQL_Injection[^]

Info on sp_ExecuteSQL vs Execute and using parameters

Building Dynamic SQL In a Stored Procedure[^]

这篇关于从以下两个sql命令获得不同的结果，但是需要输出1171行。解决方案将不胜感激。提前致谢。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！