Zend/DB invalid params
TakeoO opened this issue · 9 comments
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
@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
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.
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.
I bet you bind params with :
:
$stm->bindParam([
':param' => 'value',
]);
but anyway, it should be fixed... Working on it.
ping @ezimuel
Yes, you're right. Binding parameters without ":" is still working.
Yes, we bind params with ":" in $params.
Is there a better way?
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.
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 !).