Workaround for sql functions

INTONE

I am using the php sql server driver and want to use the standard editor server side script for users to do searches. My standard server script looks something like this:

$data = Editor::inst( $db, 'BeneficialOwner', 'ID' )
->fields(
Field::inst( 'BeneficialOwner.MIDDLE_NAMES' ),//comma seperated list
)->->join(
        Mjoin::inst( 'Countries' )
            ->name( 'Countries1' )
            ->link( 'BeneficialOwner.ID', 'BoCountriesLink.BENEFICIALOWNER_ID' )
            ->link( 'Countries.COUNTRY_CODE', 'BoCountriesLink.COUNTRY_CODE' )
            ->order( 'COUNTRY_NAME asc' )
            ->fields(
                Field::inst( 'COUNTRY_CODE' )
                    ->validator( 'Validate::required' )
                    ->options( Options::inst()
                        ->table( 'Countries' )
                        ->value( 'COUNTRY_CODE' )
                        ->label( 'COUNTRY_NAME' )
                    ),
                Field::inst( 'COUNTRY_NAME' )
            )
    )

Now I want to run a search using the MIDDLE_NAMES column which has comma seperated values. In regular sql that would be:

SELECT *
FROM [GOABOS].[dbo].[BeneficialOwner]
WHERE CONCAT(',',MIDDLE_NAMES,',') LIKE CONCAT('%','joe','%');

However from searches on this forum, I was told that sql functions are not supported for the Editor::inst. So doing something like this would not work:

if(!empty($_POST['MIDDLE_NAMES'])){
    $data->where( function ( $q ) use ($db) {   
    $q->where(  CONCAT(',',BeneficialOwner.MIDDLE_NAMES,','), 'CONCAT('%','joe','%')', 'LIKE', false );
    });
}

Is there a workaround for something like this?

allan

Not yet I'm afraid. As you mention SQL functions are not yet supported in the Editor PHP libraries. The one exception to that is the second parameter of the $q->where() method, when the fourth parameter is false (i.e. don't bind). But the first parameter would still be escaped, so it wouldn't work in this case.

Support for SQL functions is something that I plan to introduce in future.

Regards,
Allan

INTONE

Ok, so I did some changes. Instead of storing values in a comma separated string, each item is stored in table MiddleNames and a column called names. No I want to search, not a problem for unsecured queries:

$data->where( function ( $q ) { 
 $mn = explode(",", $_POST['MIDDLE_NAMES']);//comma separated
 $mns = "'" . implode ( "', '", $mn ) . "'";//comma separated with single quotes
 $q->where( 'MiddleName.NAME',"(".$mns.")" , 'IN', false );//only works with false
})

however if I try to make it secured by doing:

$data->where( function ( $q ) { 
 $mn = explode(",", $_POST['MIDDLE_NAMES']);//comma separated
 $mns = "'" . implode ( "', '", $mn ) . "'";//comma separated with single quotes
 $q->where( 'MiddleName.NAME',"(:MIDDLE_NAMES)" , 'IN', false );//only works with false
$q ->bind( ':MIDDLE_NAMES', $mns );
})

I get no result and no error. What am I doing wrong.

allan

The binding is causing it to be quoted. If you enable the debug mode you'll see the SQL isn't why you want which is why you are getting no results.

You would need to loop over the $mn array with an OR condition I think, binding each input individually. Otherwise there is no way for it to know that the commas are different values - at the moment it is just taking it as one big single value.

Allan

INTONE

Hello Allan,

When I output the value of $mns I see for example: 'joe', 'fill'. This means that it is not "one big single value" as you suggested. I will take a look at doing it as you suggested though.

allan

The value there isn't, no. But once it has been passed through the binding libraries it will be.

$q ->bind( ':MIDDLE_NAMES', $mns );

That is still doing a binding and its doing it with a string of comma separated values.

Allan

INTONE

Hi Allan,

I have tried as you suggested using something similar to this:

->where( function ( $q ) {
    $q->or_where( function ( $r ) {
            $r->where( 'MiddleName.NAME', 'joe' );
            $r->where( 'MiddleName.NAME', 'phil' );
        } );
})

Well this does not work because when I check the sql debugger it is showing this:

WHERE  ( [MiddleName].[name] = 'joe' AND [MiddleName].[name] = 'dad' )

If I were to replace the AND with and OR like this:

WHERE  ( [MiddleName].[name] = 'joe' OR [MiddleName].[name] = 'dad' ) 

it does work as expected. The question now is how do i make the api do this:

WHERE  ( [MiddleName].[name] = 'joe' OR [MiddleName].[name] = 'dad' ) 

INTONE

Hello Allan,

I have figured out the problem. It was simple using:

$data->where( function ( $q ) {     
         $q->or_where('MiddleName.NAME', 'joe' );
         $q->or_where( 'MiddleName.NAME', 'Dad' );
});

now i get all the benefits of binding plus multiple search terms in one go. Thanks for your help.

allan

Nice one. Thanks for posting back - great to hear you've got it working as needed.

Allan