Binding Error with PHP8, SSP class, Complex method

jfade

Hello there!

I'm using the SSP class on a simple DataTable with 4 columns, very similar to the base examples. The code in question is this:

json_encode( SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, "`company` = '$searchValue'") );

Now on this project I'm using PHP 8 rather than PHP 7.4. I've confirmed that if I switch back to 7.4 the issue goes away. On 8, however, I receive this error:

An SQL error occurred: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Examining the SSP class and doing some var_dumping etc to figure out where the issue is, it's here:

// Total data set length $resTotalLength = self::sql_exec( $db, $bindings, "SELECT COUNT(`{$primaryKey}`) FROM `$table` ". $whereAllSql );

It appears that since $bindings is included in this totaling function, it's causing the error message shown above. By changing $bindings to null it prevents the error. So far as I can tell, there is no danger in doing this. In the sql_exec function, the block to add bindings will be skipped since it's not an array, the SQL statement is still prepared, and since the statement is simply select count(id) from jobs ($whereAllSql is null in this case) I cannot see any reason this would be a problem.

However, perhaps I'm under-thinking it. Is there a reason this could cause problems, or am I safe in making this adjustment to the class? Thanks!

allan

Interesting - thank you for posting this.

If your "where all" is not used, then yes, that looks like a good workaround. I'm going to need to have a bit of a think about it for other cases though. Probably it needs to be split into two arrays.

Allan

jfade

No problem, thanks for the confirmation! Honestly I've never used the "where all" parameter, to my recollection, in the simpler cases that I use the SSP class. For more complex reports/tables I have a slightly crazy and convoluted function, but it works for me. So I appreciate the simpler SSP class for most other cases. I'll keep my eye out for an official solution down the road.

GeMa

I have the exact same error. The issue appeared with PHP 8.

In another forum post, someone mentioned the sollution :
$bindings = (!empty($whereAll) ? $bindings : []); //This line fixes the issue

That did not fix the issue for me. Changing $bindings to null does the trick but I'm unsure if it would cause other issues.

This error prevents me from migrating my project to PHP 8.

Thanks in advance.

allan

Are you using $whereAll?

Allan

GeMa

Yes.
I'm using $whereAll to filter the records by YEAR.
I'm NOT using $whereResult.

allan

Thank you - yup it sounds like a little upgrade is needed to the script to use two different binding arrays. I'll get that done.

Allan

GeMa

Thanks in advance Alan. Looking forward to it.

karl.w.au

@GeMa

I found that if you add
$bindings = (!empty($whereAll) ? $bindings : []); //This line fixes the issue
just below the comment

/// Total data set length
$bindings = (!empty($whereAll) ? $bindings : []); //This line fixes the issue
$resTotalLength = self::sql_exec( $db, $bindings,
"SELECT COUNT({$primaryKey}) FROM $table ". $whereAllSql
);
$recordsTotal = $resTotalLength[0][0];/

    /*
     * Output
     */ 

I did try placing it before

// Data set length after filtering $resFilterLength = self::sql_exec( $db, $bindings, "SELECT COUNT(`{$primaryKey}`) FROM `$table` $where" ); $recordsFiltered = $resFilterLength[0][0];

but it didn't work. The sweet spot is around the line 366 in SSP. I did try some SQL injection attempts and it seems fine so far.

allan

There were a couple of other threads about this (1 2) and it looks like I missed this one when I committed a fix, apologies for that oversight.

DataTables 1.11.5 includes the fix, or use the very latest script from here which should address this error.

Allan