Bind on Where Parameter

INTONE

I have this code that I want to increase the security of it using parameterized query:

->leftJoin( 'CompanyQuery', 'CompanyQuery.COMPANY_ID', '=', 'BeneficialOwner.COMPANY_ID' )
  ->where( 'BeneficialOwner.COMPANY_ID', $_POST['COMPANY_ID'] )

so I did this:

->leftJoin( 'CompanyQuery', 'CompanyQuery.COMPANY_ID', '=', 'BeneficialOwner.COMPANY_ID' )
  ->where( function ( $q ) {
    $q->where( 'BeneficialOwner.COMPANY_ID',$_POST['COMPANY_ID'],'=', false );
    $q->bind( ':COMPANY_ID', '%'. $_POST['COMPANY_ID'] .'%' );
} )

I cannot find an example with the query I want. Please help.

allan

Change:

$q->where( 'BeneficialOwner.COMPANY_ID',$_POST['COMPANY_ID'],'=', false );

To be:

$q->where( 'BeneficialOwner.COMPANY_ID', ':COMPANY_ID', '=' );

Allan

INTONE

Hi Allan,

Thank again for the quick answer.

INTONE

Hi Allan,

I tried what you said but got this error: "Uncaught Error: DataTables warning: table id=BeneficialOwner - SQLSTATE[HY093]: Invalid parameter number: parameter was not defined." My code now looks like this:

->where( function ( $q ) {
    $q->where( 'BeneficialOwner.COMPANY_ID',':COMPANY_ID', '=' );
    $q->bind( ':COMPANY_ID', '%'. $_POST['COMPANY_ID'] .'%' );
})

Remember when I use the simple unsecured form:

  ->where( 'BeneficialOwner.COMPANY_ID', $_POST['COMPANY_ID'] )

it works. It if help I am using the php pdo sqlsvr driver on iis. Any help is appreciated.

rf1234

In my opinion you don't need to bind $_POST['COMPANY_ID'] yourself because it is bound by default by datatables. I found this here: https://editor.datatables.net/manual/php/conditions

"Where things start to get interesting is the fourth parameter of the Query->where() method (note that it is different from Editor->where()!) - the fourth parameter is an optional boolean parameter and will instruct the query as to whether the value should be bound (i.e. escaped) or not. By default it will be, but disabling the binding means we can pass in SQL statements, arrays and other complex expressions."

Since you have not disabled binding in your query it does not have to be bound by you!

In your latest response you have a syntax error you need to remove the '%' signs and the periods after the first and before the second one because they are only required in a LIKE comparison which you don't have. But again: you don't need to bind this yourself.

Here is an example were binding is needed because a) I pass in an SQL-statement and b) I set the fourth parameter to false.

$q ->where('user.id',
    '( SELECT DISTINCT contract_has_rfa.approver_id  
         FROM contract_has_rfa  
        WHERE contract_has_rfa.contract_id = :contract_id
    )', 'NOT IN', false);        
$q ->bind( ':contract_id', $_POST['contract_id'] );

allan

Could you try removing the _ from :COMPANY_ID in both instances? I've got a feeling some drivers don't like that.

@rf1234 is right that anything passed as the second parameter will get bound by default - so:

->where( 'BeneficialOwner.COMPANY_ID', $_POST['COMPANY_ID'] )

is the same as:

->where( function ( $q ) {
    $q->where( 'BeneficialOwner.COMPANY_ID',':COMPANY_ID', '=' );
    $q->bind( ':COMPANY_ID', $_POST['COMPANY_ID'] );
})

The difference in your code is that you put wild cards around the POSTed value (%%). For that you would need to use a LIKE rather than =.

Allan