Zend/DB invalid params

[TakeoO]

Version: Zend-db version: 2.9.*

Issue: When binding named params into sql query, the sql trows error: "SQLSTATE[HY093]: Invalid parameter number: parameter was not defined".

Sql:

$sql = "SELECT Version FROM `SystemInfo` WHERE `Name` = :infoName ORDER BY `SystemInfoID` DESC LIMIT 1";

PHP:

$params = [':infoName' => 'version' ];

$this->getAdapter() ->createStatement($sql) ->execute($params) ->next()

Explained:

This works on any of previous versions before 2.9.*, the fix was to rollback to version 2.8.2

[michalbundyra]

@TakeoO Can you confirm please that you've tried version 2.9.1? I think it was resolved yesterday. Please see: #288 and https://github.com/zendframework/zend-db/releases/tag/release-2.9.1

[TakeoO]

Ok, I updated to 2.9.1:
Got new issue:

Simplified Sql:

SELECT * FROM Navigation N LEFT JOIN NavigationUser NU ON (N.NavigationID = NU.NavigationID AND NU.UserID = :userID) WHERE (N.IsActive = 1 AND (NU.NavigationID IS NULL OR NU.IsActive = 1)) AND N.Module = :module ORDER BY Sort

Exception:

Fatal error: Uncaught Zend\Db\Adapter\Exception\RuntimeException: The PDO param :userID contains invalid characters. Only alphabetic characters, digits, and underscores (_) are allowed.

[ChipZ91]

Got the same error after updating to 2.9.1.

Seems like the bugfix from 2.9.0 to 2.9.1 is containing a new bug.

2.8.* is fine.

[michalbundyra]

I bet you bind params with ::

$stm->bindParam([
    ':param' => 'value',
]);

but anyway, it should be fixed... Working on it.

ping @ezimuel

[ChipZ91]

Yes, you're right. Binding parameters without ":" is still working.

[TakeoO]

Yes, we bind params with ":" in $params.
Is there a better way?

[michalbundyra]

I wouldn't say "better" but you can bind params without ":" and it will be fine (":" is not mandatory), but as you noted with ":" it breaks... Working on fix, should be ready soon, not sure when it will be released. I'll ping you to test my hotfix with your code.

[michalbundyra]

See:
https://stackoverflow.com/questions/17386469/pdo-prepared-statement-what-are-colons-in-parameter-names-used-for
https://stackoverflow.com/questions/9778887/is-the-leading-colon-for-parameter-names-passed-to-pdostatementbindparam-opt

I can't see any word in PHP docs that : is mandatory or optional on binding. I just know that it works with and without (on binding !).

[werc]

In PDO documentation there are lot of examples with named parameters in format :name. So I would stay close to docs as possible.