search

phalcon / cphalcon

High performance, full-stack PHP framework delivered as a C extension.
https://phalcon.io
BSD 3-Clause "New" or "Revised" License
10.76k stars 1.96k forks source link

[BUG]: Database manager throws 'Invalid parameter number' when bind parameter used in query more than once #16091

Closed ktsv closed 1 year ago

ktsv commented 1 year ago

when performing queries to database with bind parameters, and query contains same parameter more than once - 'Invalid parameter number' is thrown by DB manager (or medel)

Current behaviour: $this->db->query('select * from test where id=:id or id=:id', ['id' => 1]); throws PDOException: SQLSTATE[HY093]: Invalid parameter number

Expected behaviour: Query runs without exception

niden commented 1 year ago

I believe this is a PDO related issue - having the same name for two or more variables. Honestly your query does not make sense (unless it is just an example).

If you really need to use the same parameter you have to name them differently id1, id2 etc.

https://stackoverflow.com/questions/18028706/php-pdoexception-sqlstatehy093-invalid-parameter-number

ktsv commented 1 year ago

It used to work in our system for years before we try to update from phalcon 4.0.6 to phalcon 5.0.0RC4. This is one most annoying bug we've got.

sinbadxiii commented 1 year ago

there the default pdo attribute has changed to false https://github.com/phalcon/cphalcon/issues/15810

need to edit db config

'database' => [
      'adapter'     => $_ENV['DB_CONNECTION'],
      'host'        => $_ENV['DB_HOST'],
      'username'    => $_ENV['DB_USERNAME'],
      'password'    => $_ENV['DB_PASSWORD'],
      'dbname'      => $_ENV['DB_DATABASE'],
      'charset'     => $_ENV['DB_CHARSET'],
      'options' => [
          \PDO::ATTR_EMULATE_PREPARES => true
      ]
],
niden commented 1 year ago

there the default pdo attribute has changed to false #15810

need to edit db config

'database' => [
      'adapter'     => $_ENV['DB_CONNECTION'],
      'host'        => $_ENV['DB_HOST'],
      'username'    => $_ENV['DB_USERNAME'],
      'password'    => $_ENV['DB_PASSWORD'],
      'dbname'      => $_ENV['DB_DATABASE'],
      'charset'     => $_ENV['DB_CHARSET'],
      'options' => [
          \PDO::ATTR_EMULATE_PREPARES => true
      ]
],

Ack. Thank you @sinbadxiii.

I need to add a warning to the docs about this - I thought I did but just in case.

niden commented 1 year ago

Addressed in: https://github.com/phalcon/docs/commit/efaa70c41e0f3052949f91db0f6aed2447bd1857

Thank you all for bringing this to our attention!