SQL查询在Workbench中工作，但在Delphi中使用完全相同的查询“get not convert convert type”错误 [英] SQL Query works in Workbench but get 'Could not convert variant type' error with the exact same query in Delphi

问题描述

我得到的确切错误是

无法将类型（UnicodeString）的变体转换为类型（Date）。 p>

Could not convert variant of type (UnicodeString) into type(Date).

我用于日期的变量是一个字符串，我必须在其周围放置引号，否则返回null，但是当我放在这个引号周围我收到这个错误。

The variable I am using for the date is a string and I have to place quotation marks around it or else it returns null but when I put the quotation marks around it I get this error.

这是我的SQL查询代码和变量 TodaysDate 。 （此代码不会复制和粘贴，因为它在不同的机器上没有互联网功能，所以请忽略可能导致编译错误的任何东西）

Here is my code for the SQL Query and the variable TodaysDate. (This code isn't copy and pasted due to it being on a different machine without internet capabilities so please ignore anything that might cause a compiling error)

    if MidStr(DateToStr(Date),2,1) ='/' then
         TodaysDate := MidStr(DateToStr(Date),6,4) + '-' + '0' + 
         MidStr(DateToStr(Date),1,1) + '-' + MidStr(DateToStr(Date),3,2)
    else
      TodaysDate := MidStr(DateToStr(Date),7,4) + '-' 
      + MidStr(DateToStr(Date),1,2) + '-' + MidStr(DateToStr(Date),4,2);

   ADOQuery1.SQL.Clear;
   ADOQuery1.SQL.Add('SELECT tbl.emailAddress, tbljob.Time FROM '+
   'dbwindowwash.tblclient, dbwindowwash.tbljob, dbwindowash.tbljobclientworker '+
   'WHERE tbljobclientworker.jobID = tbljob.jobID AND '+
   'tbljobclientworker.clientID = tblclient.clientID AND tbljob.Date = ' +
   QuotedStr(TodaysDate));
   ADOQuery1.Open

   // More Code using the email addresses and time

直接复制并粘贴到工作台中，该查询给出了我所需要的所有数据，但在Delphi中给出了我和错误，而没有 QuotedStr（）它返回null Delphi和Workbench。

Copy and pasted straight into the Workbench this query gives me all the data I want but in Delphi gives me and error and with no QuotedStr() it returns null in both Delphi and Workbench.

我在我的程序中有一个类似的查询，其中使用日期作为一个字符串 QuotedStr（）它工作正常，所以我绝对不知道这是什么问题。

I have a similar query in my program elsewhere which uses the date as a string with QuotedStr() and it works fine so I have absolutely no idea what is wrong with this.

推荐答案

同意SirRufo的意见。对我该怎么做的正确答案？是不要这样做，这是错误的做法。

Agreeing with SirRufo's comment here. The correct answer to "how do I do this?" is "don't do that; that's the wrong way to do it."

如果您直接将值直接粘贴到查询中，黑客可以找到一种方式来<一个href =http://xkcd.com/327/>将您的查询中的东西放入SQL命令解释。这被称为 SQL注入，它是负责任的数十亿美元的损失在过去几十年。 （不夸张）。

If you stick values directly into the query like that, hackers can find a way to place things into your query that get interpreted as SQL commands. This is known as SQL injection, and it's been responsible for billions of dollars' worth of damage in the last few decades. (Not exaggerating.)

正确的方法是通过使用参数将数据清理干净地分离出来：

The right way to do it is by cleanly separating your SQL code from your data, by using parameters, like so:

ADOQuery1.SQL.Clear;

//: before an identifier specifies a parameter
ADOQuery1.SQL.Add('SELECT tbl.emailAddress, tbljob.Time FROM '+
  'dbwindowwash.tblclient, dbwindowwash.tbljob, dbwindowash.tbljobclientworker '+
  'WHERE tbljobclientworker.jobID = tbljob.jobID AND '+
  'tbljobclientworker.clientID = tblclient.clientID AND tbljob.Date = :date';

//parse the query and find parameter declarations
ADOQuery1.Prepare;

//set a value for the parameter
ADOQuery1.ParamByName['date'].AsDateTime := TodaysDate;

ADOQuery1.Open

如何设置的确切语法一个参数的值可能不同于一个数据集类型到另一个数据集类型，但这应该给您一个基本的想法。

The exact syntax for how to set the value of a parameter may differ from one dataset type to another, but that should give you the basic idea.

这篇关于SQL查询在Workbench中工作，但在Delphi中使用完全相同的查询“get not convert convert type”错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！