Why is the `LIKE` operator not working with integer columns?

Question

I'm trying to receive some ids from my database for an autocomplete search on my CAKEPHP 3.3 site. But my problem is that its only returning the id if I type in the exact id and not part of it.

Here is my function to search the data. The name variable is what is being passed from input.

public function search()
{
    if ($this->request->is('ajax')) 
    {
        $name = $this->request->query['term'];
        $resultArr = $this->Invoices->find('all', [
            'conditions' => ['Invoices.id LIKE' => ($name . '%')]
        ]);
        $resultsArr = [];
        foreach ($resultArr as $result) 
        {
             $resultsArr[] = ($result['id']);
        }

        $this->set('resultsArr', $resultsArr);
        // This line is what handles converting your array into json
        // To get this to work you must load the request handler
        $this->set('_serialize', ['resultsArr']);
    }
}

For example there is a id in the table '5254' and I type in part of the id '52' nothing is returned but when I type in the whole id '5254' the id is returned.

I'm unsure why this is the case because in my sql query i'm using the percent sign to say any characters after what has been typed into the input.

Here is part of my table

SQL debug when 52 is entered.

object(Cake\ORM\Query) {

    '(help)' => 'This is a Query object, to get the results execute or iterate it.',
    'sql' => 'SELECT Invoices.id AS `Invoices__id`, Invoices.start_date AS `Invoices__start_date`, Invoices.close_date AS `Invoices__close_date`, Invoices.customer_id AS `Invoices__customer_id`, Invoices.invoice_to_address AS `Invoices__invoice_to_address`, Invoices.ship_to_address AS `Invoices__ship_to_address`, Invoices.customer_contact_id AS `Invoices__customer_contact_id`, Invoices.aircraft_registration_id AS `Invoices__aircraft_registration_id`, Invoices.shipping_company_id AS `Invoices__shipping_company_id`, Invoices.notes AS `Invoices__notes`, Invoices.worksheet_notes AS `Invoices__worksheet_notes`, Invoices.closed AS `Invoices__closed`, Invoices.times_printed AS `Invoices__times_printed`, Invoices.payment_due AS `Invoices__payment_due`, Invoices.GST_rate AS `Invoices__GST_rate`, Invoices.opening_notes AS `Invoices__opening_notes`, Invoices.courier_ticket AS `Invoices__courier_ticket`, Invoices.job_description AS `Invoices__job_description`, Invoices.worksheets_printed AS `Invoices__worksheets_printed`, Invoices.supervising_engineer_id AS `Invoices__supervising_engineer_id`, Invoices.job_type_id AS `Invoices__job_type_id`, Invoices.opened_by_id AS `Invoices__opened_by_id`, Invoices.assigned_to_id AS `Invoices__assigned_to_id`, Invoices.certification_required AS `Invoices__certification_required`, Invoices.currency_id AS `Invoices__currency_id`, Invoices.xero_batch_number AS `Invoices__xero_batch_number`, Invoices.xero_amount AS `Invoices__xero_amount`, Invoices.exchange_rate AS `Invoices__exchange_rate`, Invoices.payment_instructions AS `Invoices__payment_instructions`, Invoices.email AS `Invoices__email`, Invoices.inv_email AS `Invoices__inv_email` FROM invoices Invoices WHERE Invoices.id like :c0',
    'params' => [
        ':c0' => [
            'value' => '52%',
            'type' => 'integer',
            'placeholder' => 'c0'
        ]

It working for me. Check this documentation for reference https://book.cakephp.org/3.0/en/orm/query-builder.html — , Jul 05 '17 at 05:19
I added the code produced from debug if that's what you meant. — mark.nz, Jul 05 '17 at 07:53

score 3 · Accepted Answer · answered Jul 05 '17 at 12:44

The id column is of type INTEGER, and therefore the value is being bound as such, as can be seen in your Query dump, it says 'type' => 'integer'. Being bound as an integer will cause it to be casted, and you'll end up with a comparison against 52 only.

You can workaround that by telling the query builder to treat the column as a string type. This can be done via the second argument ($types) of the query builders *where() methods:

$this->Invoices
    ->find()
    ->where(
        ['Invoices.id LIKE' => ($name . '%')],
        ['Invoices.id' => 'string']
    );

See also

API > \Cake\ORM\Query::where()

score 0 · Answer 2 · 2017-07-05T17:43:50.883

0

Try it like this:

'conditions' => ['Invoices.id LIKE' => '"' . $name . '%"']

edited Jul 05 '17 at 17:43

answered Jul 05 '17 at 05:27

None of these fixed the problem the 1st, 2nd, and 4th made it not work with the whole id and the 3rd one produced the same output to my original code. – mark.nz Jul 05 '17 at 08:05

score 0 · Answer 3 · answered Jul 05 '17 at 16:14

In this case You can "inject" plain query - array values with numeric index in conditions are treated as plain query, and it will not be parametrized. Be carefull: Typecast to integer is necessary in this case to prevent SQL Injection:

    $result = $this->Invoinces->find('all' , [
            'conditions' => [
                'id LIKE "'.(int)$input.'%" '
            ]
        ])
    ->toArray();

score 0 · Answer 4 · answered Jun 23 '19 at 18:19

you can still do it like this in cakephp 3

$results = $clients->find()->select(['id','email','name','accountid','created','status'])
            ->Where(function (QueryExp $exp, Query $q) use ($requestData) {
                $orCond = $exp->or_([
                    new Comparison('accountid',$requestData['search']['value'],null,'LIKE'),
                    new Comparison('email',$requestData['search']['value'],null,'LIKE'),
                    new Comparison('name',$requestData['search']['value'],null,'LIKE'),
                    new Comparison('created',$requestData['search']['value'],null,'LIKE'),
                    new Comparison('status',$requestData['search']['value'],null,'LIKE'),
                ]);
                return $exp->add($orCond);
            });

Why is the `LIKE` operator not working with integer columns?

4 Answers4