Binding Error with PHP8, SSP class, Complex method

Binding Error with PHP8, SSP class, Complex method

jfadejfade Posts: 5Questions: 2Answers: 0

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!

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    Answer ✓

    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

  • jfadejfade Posts: 5Questions: 2Answers: 0
    edited November 2021

    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. :D So I appreciate the simpler SSP class for most other cases. :smile: I'll keep my eye out for an official solution down the road.

  • GeMaGeMa Posts: 7Questions: 2Answers: 0

    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.

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    Are you using $whereAll?

    Allan

  • GeMaGeMa Posts: 7Questions: 2Answers: 0
    edited November 2021

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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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

  • GeMaGeMa Posts: 7Questions: 2Answers: 0

    Thanks in advance Alan. Looking forward to it.

  • karl.w.aukarl.w.au Posts: 1Questions: 0Answers: 0
    edited April 2022

    @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.

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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

Sign In or Register to comment.