'SQLSTATE [HY093]：无效的参数编号：参数未定义'

Question

我不断收到上述错误。这是我设立在那里发生的事情：

$InsertSQL = "insert into reports.NonVarassetInvoices(State, CLLI, Type, Vendor, DateReceived, InvoiceNumber, InvoiceDate, TotalInvoiceAmount, ProjectWONumber, CAF, SentForApprovalDate, Approver 
      , ApprovalReceivedDate, ReleaseDate, ReleaseNumber, SentToAPDate, InvoicerName, Status, HoldReason, Notes) 
select ':State', ':CLLI', (select Id from pmdb.PicklistChild where ParentId in(select Id from pmdb.PicklistParent where Value = ':Type') order by Sequence) 
    , ':Vendor', ':DateReceived', ':InvoiceNumber', ':InvoiceDate', :TotalInvoiceAmount, ':ProjectWONumber', ':CAF', ':SentForApprovalDate', ':Approver' 
    , ':ApprovalReceivedDate', ':ReleaseDate', ':ReleaseNumber', ':SentToAPDate', ':InvoicerName' 
    , (select Id from pmdb.PicklistChild where ParentId in(select Id from pmdb.PicklistParent where Value = ':Status') order by Sequence) 
    , (select Id from pmdb.PicklistChild where ParentId in(select Id from pmdb.PicklistParent where Value = ':HoldReason') order by Sequence), ':Notes'"; 

$stmt = $conn->prepare($InsertSQL); 

$stmt->bindParam(':State', $State, PDO::PARAM_STR); 
$stmt->bindParam(':CLLI', $CLLI, PDO::PARAM_STR); 
$stmt->bindParam(':Type', $Type, PDO::PARAM_INT); 
$stmt->bindParam(':Vendor', $Vendor, PDO::PARAM_STR); 
$stmt->bindParam(':DateReceived', $DateReceived, PDO::PARAM_STR); 
$stmt->bindParam(':InvoiceNumber', $InvoiceNumber, PDO::PARAM_STR); 
$stmt->bindParam(':InvoiceDate', $InvoiceDate, PDO::PARAM_STR); 
$stmt->bindParam(':TotalInvoiceAmount', $TotalInvoiceAmount, PDO::PARAM_INT); 
$stmt->bindParam(':ProjectWONumber', $ProjectWONumber, PDO::PARAM_STR); 
$stmt->bindParam(':CAF', $CAF, PDO::PARAM_STR); 
$stmt->bindParam(':SentForApprovalDate', $SentForApprovalDate, PDO::PARAM_STR); 
$stmt->bindParam(':Approver', $Approver, PDO::PARAM_STR); 
$stmt->bindParam(':ApprovalReceivedDate', $ApprovalReceivedDate, PDO::PARAM_STR); 
$stmt->bindParam(':ReleaseDate', $ReleaseDate, PDO::PARAM_STR); 
$stmt->bindParam(':ReleaseNumber', $ReleaseNumber, PDO::PARAM_STR); 
$stmt->bindParam(':SentToAPDate', $SentToAPDate, PDO::PARAM_STR); 
$stmt->bindParam(':InvoicerName', $InvoicerName, PDO::PARAM_STR); 
$stmt->bindParam(':Status', $Status, PDO::PARAM_INT); 
$stmt->bindParam(':HoldReason', $HoldReason, PDO::PARAM_INT); 
$stmt->bindParam(':Notes', $Notes, PDO::PARAM_STR); 

$stmt->execute(); 

我也曾尝试做execute(array(':State => $State ...));我得到同样的错误。

我不知道这是什么意思，但我看过其他几个类似名称的问题。据我所知，他们不回答我确切的问题。

我错过了什么吗？我该如何解决？

UPDATE

我已经基于以下答案更新了我的插入SQL：

$InsertSQL = "insert into reports.NonVarassetInvoices(State, CLLI, Type, Vendor, DateReceived, InvoiceNumber, InvoiceDate, TotalInvoiceAmount, ProjectWONumber, CAF, SentForApprovalDate, Approver 
                   , ApprovalReceivedDate, ReleaseDate, ReleaseNumber, SentToAPDate, InvoicerName, Status, HoldReason, Notes) 
        select :State, :CLLI, (select Id from pmdb.PicklistChild where ParentId in(select Id from pmdb.PicklistParent where Name = 'NonVInvoiceType') Value = :Type and IsActive = 1) 
         , :Vendor, :DateReceived, :InvoiceNumber, :InvoiceDate, :TotalInvoiceAmount, :ProjectWONumber, :CAF, :SentForApprovalDate, :Approver 
         , :ApprovalReceivedDate, :ReleaseDate, :ReleaseNumber, :SentToAPDate, :InvoicerName 
         , (select Id from pmdb.PicklistChild where ParentId in(select Id from pmdb.PicklistParent where Name = 'NonVStatus') and Value = :Status and IsActive = 1) 
         , (select Id from pmdb.PicklistChild where ParentId in(select Id from pmdb.PicklistParent where Name = 'NonVHoldReason') and Value = :HoldReason and IsActive = 1), :Notes"; 

现在，我得到了500 Internal Server Error消息和空白屏幕。如果我把Params到执行语句是这样的：

$stmt->execute(array(':State'=>$State,':CLLI'=>$CLLI,':Type'=>$Type,':Vendor'=>$Vendor,':DateReceived'=>$DateReceived,':InvoiceNumber'=>$InvoiceNumber,':InvoiceDate'=>$InvoiceDate 
         ,':TotalInvoiceAmount'=>$TotalInvoiceAmount,':ProjectWONumber'=>$ProjectWONumber 
         ,':CAF'=>$CAF,':SentForApprovalDate'=>$SentForApprovalDate,':Approver'=>$Approver,':ApprovalReceivedDate'=>$ApprovalReceivedDate,':ReleaseDate'=>$ReleaseDate 
         ,':ReleaseNumber'=>$ReleaseNumber,':SentToAPDate'=>$SentToAPDate 
         ,':InvoicerName'=>$InvoicerName,':Status'=>$Status,':HoldReason'=>$HoldReason,':Notes'=>$Notes)); 

然后我得到这个错误：

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near 'Value'

Answer 1

你居然没有在SQL语句中的任何参数;他们都是文字字符串作为你引述他们：

... where Value = ':Type' ... 
       ^ ^these need to go 

所以，你需要删除所有这些单引号包围的占位符。

除此之外，你只能使用值的占位符，而不是列名，表名等

所以这不会不带引号工作，要么：

... select ':State', ':CLLI' ... 

在变量的情况下，列名称，您需要将它们插入字符串中，并且为了避免sql注入，您需要首先检查它们是否在白名单中。

// check all column names agains a white-list 
... 
// insert them into your string 
... select `{$State}`, `{$CLLI}` ... 
// etc.