Bind in Query Builder while using Paginator

Question

Bind in Query Builder while using Paginator

Mohammad Ali Talaghat Jul '16

Created Jul '16	Last Reply Jul '16	Replies 5	Views 830	Votes 0

Mohammad Ali Talaghat 1.5k

Jul '16

Hello,

Is it possible to bind parameters in 'OrderBy' while using Paginator?

Currently i use something like this:

        $orderBy = $this->request->getQuery('order'); // rank/price
        $sortBy = $this->request->getQuery('sort'); // ASC/DESC

        $Domains = $this->modelsManager->createBuilder()
            ->from('Core\Models\Domains')
            ->orderBy($orderBy . ' ' . $sortBy);

     $currentPage = $this->request->getQuery('page', 'int');

        $paginator = new Paginator(
            array(
                "builder" => $Domains,
                "limit" => 10,
                "page" => $currentPage
            )
        );

Thanks.

Nikolay Mihaylov · Answer 1 · 2016-07-18T07:39:12-07:00

Yes, it is possible. What is not working?

Also please notice: Do not use query strings directly into query, at least if you are not 100% sure its safe (restricted access area e.t.c.). You could get injected in your orderBy.

A full working example here: https://forum.phalcon.io/discussion/12057/how-to-use-limit-sql-with-paginator#C36531

Mohammad Ali Talaghat · Answer 2 · 2016-07-18T07:52:50-07:00

I need binding like this to make safe query:

        $Domains = $this->modelsManager->createBuilder()
            ->from('Core\Models\Domains')
            ->orderBy(':order:  :sort:',['order' => $orderBy, 'sort'=> $sortBy]);

But as far as i know 'orderBy' don't support binding like 'where', 'andWhere' and 'orWhere'.

Note: I know i can check the variables manually before using in 'orderBy' but i looking for a better solution.

Yes, it is possible. What is not working?

Also please notice: Do not use query strings directly into query, at least if you are not 100% sure its safe (restricted access area e.t.c.). You could get injected in your orderBy.

A full working example here: https://forum.phalcon.io/discussion/12057/how-to-use-limit-sql-with-paginator#C36531

Nikolay Mihaylov · Answer 3 · 2016-07-18T08:01:43-07:00

Ah sorry I missunderstood you. I don't think its possible to bind parameters inside orderBy like in where().

Usually I do as you do, check parameter and pass to the orderBy. Lets see if someone has another idea.

Wojciech Ślawski · Answer 4 · 2016-07-18T13:09:37-07:00

Well in execute method you can pass binding params. I guess paginator classes need to be changed to accept bind.

mraspor · Answer 5 · 2016-07-21T06:18:02-07:00

@alitalaghat

Filter/Sanitize/Validate the order and sort values before using them in the query.

More on filters: https://docs.phalcon.io/en/latest/reference/filter.html

Or you can validate, for example with InclusionIn:

<?php

use Phalcon\Validation;
use Phalcon\Validation\Validator\InclusionIn;

$validator = new Validation();

$validator->add('sort', new InclusionIn(array(
   'message' => 'SORT must be ASC or DESC',
   'domain' => array('ASC', 'DESC'),
   'allowEmpty => true,
)));

And then check if validation has errors:

<?php

if($validator->validate($this->request->getQuery())->count() > 0) { // or use $_GET
...
}

Wojciech Ślawski · Answer 6 · 2016-07-21T07:06:52-07:00

Yea all you guys write is true, but it's just workaround :) There should be just bind parameter added which will be passed to execute method and that's it :) I already created NFR on github.