zendframework/zend-db

Zend/DB invalid params

TakeoO opened this issue · 9 comments

TakeoO commented

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 commented

@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 commented

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 commented

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 commented

I bet you bind params with ::

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

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

ping @ezimuel

ChipZ91 commented

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

TakeoO commented

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

michalbundyra commented

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.

werc commented

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

  • 👍1