Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[BUG]: Model::find() cannot with 'limit' param when param val has referenced #15034

Closed
Vanni-Fan opened this issue May 12, 2020 · 8 comments
Closed
Assignees
Labels
5.0 The issues we want to solve in the 5.0 release bug A bug report discussion Request for comments and discussion external dependency This issue depends on external issue to be resolved. wontfix The issue will not be fixed or implemented

Comments

@Vanni-Fan
Copy link

Describe the bug

# init a query val
$where = ['conditions'=>''];

# fill 'conditions' and 'bind' by $_POST to $where val
# Delete this row and it's normal,But it's just filling in the data use refer
ModelHelper::parseWhere($_POST, $where); # the function defined is: parseWhere(array $query, array &$where)

$where['order'] = 'examine_id desc';
$where['limit'] = 10;

##### code A ####
$rs = MyModel::find($where); # code A:  throw exception
$rs = MyModel::find(json_decode(json_encode($where),1));   #  okey
###############


##### code B ####
$a = [ // Same data
            'conditions' => '(task_id like ?0)',
            'bind' =>['%4x5alNJj%'],
            'order' => 'examine_id asc',
            'limit' => '10',
];
var_dump($where === $a); // true
var_dump(json_encode($where) === json_encode($a)); // true
$rs = MyModel::find($where); # throw exception
$rs = MyModel::find($a);         # okey
###############


##### code C ####
$nothing = $where;
reset($where);
$rs = MyModel::find($where); # okey
###############


##### code D ####
//$nothing = $where;
reset($where);
$rs = MyModel::find($where); # throw exception
###############


##### code E ####
$nothing = &$where;
reset($where);
$rs = MyModel::find($where); # throw exception
###############


##### code F ####
$nothing = $where;
unset($where);
$rs = MyModel::find($nothing); # throw exception
###############

Output

#0 | PDOStatement->execute()
-- | --
#1 | Phalcon\Db\Adapter\Pdo\AbstractPdo->executePrepared(Object(PDOStatement), Array([:0] => %4x5alNJj%, [APL0] => 10), Array([APL0] => 1))
#2 | Phalcon\Db\Adapter\Pdo\AbstractPdo->query(SELECT `wy_content_examine`.`examine_id`, `wy_content_examine`.`task_id`, `wy_content_examine`.`status`, `wy_content_examine`.`errmsg`, `wy_content_examine`.`rate`, `wy_content_examine`.`content`, `wy_content_examine`.`type`, `wy_content_examine`.`callback`, `wy_content_examine`.`create_time`, `wy_content_examine`.`code`, `wy_content_examine`.`is_pass` FROM `wy_content_examine` ORDER BY `wy_content_examine`.`examine_id` ASC LIMIT :APL0, Array([:0] => %4x5alNJj%, [APL0] => 10), Array([APL0] => 1))
#3 | Phalcon\Mvc\Model\Query->_executeSelect(Array([models] => Array([0] => content\Models\ContentExamine), [tables] => Array([0] => wy_content_examine), [columns] => Array([content\Models\ContentExamine] => Array([type] => object, [model] => content\Models\ContentExamine, [column] => wy_content_examine, [balias] => content\Models\ContentExamine)), [order] => Array([0] => Array([0] => Array(), [1] => ASC)), [limit] => Array([number] => Array([type] => placeholder, [value] => :APL0))), Array([0] => %4x5alNJj%, [APL0] => 10), Array([APL0] => 1))
#4 | Phalcon\Mvc\Model\Query->execute()
#5 | Phalcon\Mvc\Model::find(Array([conditions] => (task_id like ?0), [bind] => Array([0] => %4x5alNJj%), [order] => examine_id asc, [limit] => 10))

Screenshots
If applicable, add screenshots to help explain your problem.

Details

  • Phalcon version: (php --ri phalcon)
phalcon


Phalcon is a full stack PHP framework, delivered as a PHP extension, offering lower resource consumption and high performance.
phalcon => enabled
Author => Phalcon Team and contributors
Version => 4.0.5
Build Date => May  2 2020 14:17:53
Powered by Zephir => Version 0.12.17-6724dbf

Directive => Local Value => Master Value
phalcon.db.escape_identifiers => On => On
phalcon.db.force_casting => Off => Off
phalcon.orm.case_insensitive_column_map => Off => Off
phalcon.orm.cast_last_insert_id_to_int => Off => Off
phalcon.orm.cast_on_hydrate => Off => Off
phalcon.orm.column_renaming => On => On
phalcon.orm.disable_assign_setters => Off => Off
phalcon.orm.enable_implicit_joins => On => On
phalcon.orm.enable_literals => On => On
phalcon.orm.events => On => On
phalcon.orm.exception_on_failed_save => Off => Off
phalcon.orm.exception_on_failed_metadata_save => On => On
phalcon.orm.ignore_unknown_columns => Off => Off
phalcon.orm.late_state_binding => Off => Off
phalcon.orm.not_null_validations => On => On
phalcon.orm.update_snapshot_on_save => On => On
phalcon.orm.virtual_foreign_keys => On => On
phalcon.warning.enable => On => On
  • PHP Version: (php -v)
PHP 7.4.5 (cli) (built: Apr 23 2020 16:44:34) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
  • Operating System: Windows 10 Pro, Docker 2.3.02(45183)
  • Installation type: Compiling from source || installing via package manager
  • Zephir version (if any):
  • Server: Apache
  • Other related info (Database, table schema): MySQL8
@Vanni-Fan Vanni-Fan added bug A bug report status: unverified Unverified labels May 12, 2020
@Vanni-Fan
Copy link
Author

Look at the error stack
#2 no WHERE part!!
#5 find has with 'conditions' param

#0 PDOStatement->execute()
#2 Phalcon\Db\Adapter\Pdo\AbstractPdo->query(SELECT .... FROM wy_content_examine ORDER BY wy_content_examine.examine_id ASC LIMIT :APL0, Array([:0] => %4x5alNJj%, [APL0] => 10), Array([APL0] => 1))
#5 Phalcon\Mvc\Model::find(Array([conditions] => (task_id like ?0), [bind] => Array([0] => %4x5alNJj%), [order] => examine_id asc, [limit] => 10))

@Vanni-Fan
Copy link
Author

Vanni-Fan commented May 13, 2020

Test..

$where = [
    'conditions' => '(task_id like ?0)',
    'bind' =>['%4x5alNJj%'],
    'order' => 'examine_id asc',
    'limit' => '10',
];
$nothing = &$where['conditions'];   // here...

$data = \content\Models\ContentExamine::find($where);   // throw Expection.

@Vanni-Fan
Copy link
Author

with 'limit' param, error.

$where = [
    'conditions' => 'task_id like ?0',
    'bind' =>['%4x5alNJj%'],
    'limit' => 10, // the core code
];

$nothing = &$where['conditions'];  // the core code
// unset($nothing);

$data = \content\Models\ContentExamine::find($where);   // throw Expection.

no 'limit' param, okey.

$where = [
    'conditions' => 'task_id like ?0',
    'bind' =>['%4x5alNJj%'],
];

$nothing = &$where['conditions']; // the core code
// unset($nothing);

$data = \content\Models\ContentExamine::find($where);   // okey

@Vanni-Fan Vanni-Fan changed the title [BUG]: PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens [BUG]: Model::find() cannot with 'limit' param when param val has referenced May 13, 2020
@Jeckerson
Copy link
Member

Can't simulate your error...

If you are unset conditions, then you also need to unset bind.

@Vanni-Fan
Copy link
Author

root@128b1744c124:/var/www/pa/bin# cat run.php

<?php
final class PA{static public $db;static public $di;}

PA::$di = new Phalcon\Di\FactoryDefault();
PA::$db = (new Phalcon\Db\Adapter\PdoFactory())->newInstance('mysql',
    ['adapter'=>'mysql','dbname'=>'pa_db','username'=>'root','password'=>'123456','host'=>'192.168.9.223',]
);
PA::$db->query('CREATE TABLE IF NOT EXISTS `test` (`id` int unsigned NOT NULL AUTO_INCREMENT,`name` varchar(10) NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB');
PA::$db->query('INSERT IGNORE INTO test(id,name) VALUES(1,"A"),(2,"B")');

class Test extends Phalcon\Mvc\Model{
    public function initialize(){
        PA::$di->set('db',PA::$db);
        $this->setConnectionService('db');
        $this->setDi(PA::$di);
    }
}

# case 1
echo 'select * from test where id > 1 limit 10';
try{
    $where = [
        'conditions' => 'id > ?0',
        'bind' =>[1],
        'limit' => 10,  # the core code!!
    ];
    $nothing = &$where['conditions'];  # the core code!!
    print_r(Test::find($where)->toArray());
}catch (Throwable $e){ echo "\n",$e->getTraceAsString(); }


# case 2, no limit
echo "\n\nselect * from test where id > 1\n";
try{
    $where = [
        'conditions' => 'id = ?0',
        'bind' =>[1],
    ];
    $nothing = &$where['conditions'];
    print_r(Test::find($where)->toArray());
}catch (Throwable $e){ echo $e->getTraceAsString(); }


# case 3, has limit
echo "\n\nselect * from test where id > 1 limit 10\n";
try{
    $where = [
        'conditions' => 'id > ?0',
        'bind' =>[1],
        'limit' => 10,
    ];
    print_r(Test::find($where)->toArray());
}catch (Throwable $e){ echo $e->getTraceAsString(); }

root@128b1744c124:/var/www/pa/bin# php run.php

select * from test where id > 1 limit 10
#0 [internal function]: PDOStatement->execute()
#1 [internal function]: Phalcon\Db\Adapter\Pdo\AbstractPdo->executePrepared(Object(PDOStatement), Array, Array)
#2 [internal function]: Phalcon\Db\Adapter\Pdo\AbstractPdo->query('SELECT `test`.`...', Array, Array)
#3 [internal function]: Phalcon\Mvc\Model\Query->_executeSelect(Array, Array, Array)
#4 [internal function]: Phalcon\Mvc\Model\Query->execute()
#5 /var/www/pa/bin/run.php(28): Phalcon\Mvc\Model::find(Array)
#6 {main}

select * from test where id > 1
Array
(
    [0] => Array
        (
            [id] => 1
            [name] => A
        )

    [1] => Array
        (
            [id] => 2
            [name] => B
        )

)


select * from test where id > 1 limit 10
Array
(
    [0] => Array
        (
            [id] => 2
            [name] => B
        )

)

@zsilbi zsilbi added 5.0 The issues we want to solve in the 5.0 release external dependency This issue depends on external issue to be resolved. and removed status: unverified Unverified labels Oct 17, 2020
@zsilbi
Copy link
Member

zsilbi commented Oct 17, 2020

This is a Zephir issue and will be fixed in the 5.0 release.

@Jeckerson Jeckerson self-assigned this Mar 27, 2021
@niden
Copy link
Member

niden commented Apr 27, 2021

@Vanni-Fan What I do not understand is why are you using references in the $where array?

For instance the code here:

# case 1
echo 'select * from test where id > 1 limit 10';
try{
    $where = [
        'conditions' => 'id > ?0',
        'bind' =>[1],
        'limit' => 10,  # the core code!!
    ];
    $nothing = &$where['conditions'];  # the core code!!
    print_r(Test::find($where)->toArray());
} catch (Throwable $e) { 
    echo "\n",$e->getTraceAsString(); 
}

$nothing has a reference to $where. If you change $nothing you are automatically changing $where. Also you are passing that $where in the find() method. Zephir and Phalcon in general does not support references. It was built that way from the start. I do recall years ago this being asked as a NFR but it was never implemented.

If you change the above code to this:

# case 1
echo 'select * from test where id > 1 limit 10';
try{
    $where = [
        'conditions' => 'id > ?0',
        'bind' =>[1],
        'limit' => 10,  # the core code!!
    ];
    $nothing = $where['conditions'];  # the core code!!
    print_r(Test::find($where)->toArray());
} catch (Throwable $e) { 
    echo "\n",$e->getTraceAsString(); 
}

I believe the same thing happens with your very first example. The ModelHelper accepts the $where as a reference. If you change your code where you just have simple arrays it should work just fine with no exceptions. I have used similar code hundreds of times and nothing like that happens.

What do you get if you make the following change:

// Fill conditions
# the function defined is: parseWhere(array $query, array $where) <- no reference
$where = ModelHelper::parseWhere($_POST, $where); 

....
....

@niden niden added the discussion Request for comments and discussion label Apr 27, 2021
@niden niden added the wontfix The issue will not be fixed or implemented label Aug 26, 2021
@niden
Copy link
Member

niden commented Aug 26, 2021

Closing this - no reply/additional data. If this persists please open another issue.

@niden niden closed this as completed Aug 26, 2021
@niden niden moved this to Released in Phalcon v5 Aug 25, 2022
@niden niden added this to Phalcon v5 Aug 25, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
5.0 The issues we want to solve in the 5.0 release bug A bug report discussion Request for comments and discussion external dependency This issue depends on external issue to be resolved. wontfix The issue will not be fixed or implemented
Projects
Archived in project
Development

No branches or pull requests

4 participants