'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined'

Question

I keep getting the above error. Here's my set up where it is happening:

$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();

I have also tried doing the execute(array(':State => $State ...)); I get the same error.

I don't know what this means exactly, but I've looked at several other questions that had similar names. So far as I can tell they don't answer my exact problem.

Am I missing something? How do I fix this?

UPDATE

I have updated my Insert SQL based on answers below:

$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";

Now I get the 500 Internal Server Error message and a blank screen. If I put the Params into the execute statement like this:

$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));

Then I get this error:

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

Answer 1

You actually don't have any parameters in your sql statement; they are all literal strings as you are quoting them:

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

So you need to remove all these single quotes that surround the placeholders.

Apart from that you can only use placeholders for values and not for column names, table names, etc.

So this would not work without the quotes either:

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

In case of variable column names, you need to insert them in the string and to avoid sql injection you need to check them against a white-list first.

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