PDOException SQLSTATE[HY093] after upgrading to PHP 8.1-CodePudding

today I encounter an issue after upgrading from PHP 7.4 to PHP 8.1. All the time I was using this code to establish an MySQL connection:

<?php

            $kundencode=$_SESSION['kdnr'];
            $i=0;
            $q = $pdo->prepare("SELECT * FROM qi_rechnungen WHERE kdnr='$kundencode' ORDER BY rgnr DESC");
            $q->execute([$_SESSION['id']]);
            $res = $q->fetchAll();
            foreach ($res as $row) {
                $i  ;
                ?>

This worked fine, but when using PHP 8.1 my system throws:

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /home/clients-de/public_html/rechnungen2.php:60 Stack trace: #0 /home/clients-de/public_html/rechnungen2.php(60): PDOStatement->execute(Array) #1 {main} thrown in...

I can't see any mistake!? What's the difference here between PHP 7.4 and 8.1?

Thank you for help...

CodePudding user response：

First of all, you shouldn't inline raw variables in query, use param binding.

Invalid parameter number: number of bound variables does not match number of tokens

This means that count of array passed into PDO::execute() method is different with count of params in query.

For your case, maybe this solution helps you:

// let's assume there is '5'
$kundencode=$_SESSION['kdnr'];
$q = $pdo->prepare("SELECT * FROM qi_rechnungen WHERE kdnr=:kundencode ORDER BY rgnr DESC");
// pass $kundencode into query
$q->bindValue('kundencode', $kundencode); 
// SELECT * FROM qi_rechnungen WHERE kdnr='5' ORDER BY rgnr DESC
$q->execute();

If you want to pass $_SESSION['id'] into query, you must specify it in query template explicitly

CodePudding user response：

The change that you encountered is that before PHP 8, PDO's error mode was set to silent by default. If it encountered any errors, it would just ignore them. This has now changed to the exception mode; every time an error is encountered, an exception will be thrown. So the issue was there before, it just remained unreported.

About the actual issue: you should really read up about prepared statements, they will make your code a lot safer. You're not providing any placeholders for prepared variables in your query, but you are passing them in the following line (which results in the exception):

$q->execute([$_SESSION['id']]);

As far as I see, the session ID is completely unnecessary for your query, but you could pass your customer number as a variable. The following should fix your issue:

$q = $pdo->prepare("SELECT * FROM qi_rechnungen WHERE kdnr=? ORDER BY rgnr DESC");
$q->execute([$kundencode]);