PHP Version 8.0.6 SSP Class Complex Bug

alien1344

Link to test case: N/A
Debugger code (debug.datatables.net): N/A
Error messages shown: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
Description of problem:

The SSP class has an issue on the complex function when the bindings are not empty but the $whereAll is empty:

$_REQUEST:
array (size=1)
  0 => 
    array (size=3)
      'key' => string ':binding_0' (length=10)
      'val' => string '%test%' (length=3)
      'type' => int 2

$whereAll:
string '' (length=0)

This can happen if someone chooses to use the complex functionality but only use it for a specific column being set:

//Advanced search
$where = '';
if(!empty($_REQUEST['columns'][6]['search']['value'])){
  $dates = array_filter(explode(',', $_REQUEST['columns'][6]['search']['value']));
  if(count($dates) == 2){
    $where = 'fldExample BETWEEN "' . $dates[0] . '" AND "' . $dates[1] . '"';
  }

  $_REQUEST['columns'][6]['search']['value'] = '';
}

// SQL server connection information
$sql_details = array(
  'user' => DB_USER,
  'pass' => DB_PASSWORD,
  'db'   => DB_NAME,
  'host' => DB_HOST
);

require_once( __DIR__ . '/../../ssp.class.php' );
$data = \SSP::complex( $_REQUEST, $sql_details, $table, $primaryKey, $columns, $where );  

The fix is to add the following commented out $bindings line to the SSP Class:

//class SSP

//Line 368 from: https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/ssp.class.php

// 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];

I know this post is a bit all over the place - at some point I would like to revisit it and provide an example but at the moment I simply do not have the time.

Interestingly this doesn't happen in previous versions of PHP (7.2 to 7.4) so it is something that has changed in version 8. Its a pretty unlikely use case I think because most of the time the $whereAll would be set but if I've encountered it, someone else probably will.

Hope this helps!

allan

Interesting - thanks for posting this. The problem with the approach taken, not using bindings, is that you leave yourself open to SQL injection attacks.

I've not seen this one yet, but we'll see if we can reproduce the error.

Thanks,
Allan

radcore

Thanks very much for this post. I just encountered this issue when migrating a customer's PHP app to heroku.

allan

I've committed a fix for this and there is an update on it and the cause in this thread.

Allan