动态SQL查询出错 [英] Error in dynamic sql query

问题描述

大家好，

我想在我的胜利服务中使用动态查询。这是方法，

 public DataSet GetValidServerGroups（DateTime time）
 {
 string ss =" SELECT Id， Ip FROM dbo.tblStart" + 
" WHERE（dbo.tblStart.Status = 1 OR dbo.tblStart.Status = 0）AND" + 
"（dbo.tblStart.ProcessOn BETWEEN \'" + time +" \'AND \'" + time.AddSeconds（35）+" \'）" ;; 
 
尝试
 {
 sqlCommand.Connection = sqlConnection; 
 sqlCommand.CommandText = ss; 
 
 sqlAdapter.SelectCommand = sqlCommand; 
 DataSet ds = new DataSet（）; 
 sqlAdapter.Fill（ds）; 
 
返回ds; 
} 
 catch（exception ex）
 {
 EventLog.WriteEntry（" RFAutomateWinService"，" GetValidServerGroups（）" + ex.Message +"" + ss ）; 
} 
返回null; 
} 

当我运行上面的代码时，我收到以下错误。

GetValidServerGroups（）将char数据类型转换为datetime数据类型导致超出范围的日期时间值。 SELECT GroupId，InternalIp FROM dbo.tblServersToStart WHERE（dbo.tblServersToStart.Status = 1或dbo.tblServersToStart.Status = 0）AND（dbo.tblServersToStart.ProcessOn BETWEEN'6 / 29/2009 9:17:44 AM'和'6 / 29/2009 9:17:49 AM'）

但是当我在sql查询窗口运行相同的查询时它工作正常。

有人可以帮我解决这个问题。

非常感谢

解决方案

实际上，最好不要使用动态SQL。这是SQL注入攻击和转换问题的直接方法。最好的方法是使用参数化查询，提供程序将自动处理所有类型的格式。在这种情况下，您不需要转换任何内容，只需按原样为参数赋值。以下是一些示例的链接

http://support.microsoft.com/kb/310070

Hi All,

I want to use a dynamic query in one of my win service. Here is the way,

        public DataSet GetValidServerGroups(DateTime time)
        {
            string ss = "SELECT Id, Ip FROM dbo.tblStart " +
                                "WHERE (dbo.tblStart.Status = 1 OR dbo.tblStart.Status = 0) AND " +
                                "(dbo.tblStart.ProcessOn BETWEEN \'" + time + "\' AND \'" + time.AddSeconds(35) + "\')";

            try
            {
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandText = ss;

                sqlAdapter.SelectCommand = sqlCommand;
                DataSet ds = new DataSet();
                sqlAdapter.Fill(ds);

                return ds;
            }
            catch(Exception ex)
            {
                EventLog.WriteEntry("RFAutomateWinService", "GetValidServerGroups() " + ex.Message + " " + ss);
            }
            return null;
        }

When I run the above code, i got the following error.

GetValidServerGroups() The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. SELECT GroupId, InternalIp FROM dbo.tblServersToStart WHERE (dbo.tblServersToStart.Status = 1 OR dbo.tblServersToStart.Status = 0) AND (dbo.tblServersToStart.ProcessOn BETWEEN '6/29/2009 9:17:44 AM' AND '6/29/2009 9:17:49 AM')

But when I run the same query on the sql query window it works fine.

Can someone help me to solve this problem.

Thanks a lot

解决方案

It is, actually, a good idea not to use dynamic SQL at all. It is a straight way to the SQL injection attack and those conversion issues. Best way is to use parameterized query and provider will handle formatting for all the types automatically. In this case you do not need to convert anything and just assign value to the parameter as is. Here is the link with some samples

http://support.microsoft.com/kb/310070

这篇关于动态SQL查询出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！