Pdo_Mysql driver integer query parameters can return not exact matches
Closed this issue · 2 comments
When creating queries with the Pdo_Mysql driver, if you supply an integer as parameter, it is automatically cast as type PDO::PARAM_INT (in Zend\Db\Adapter\Driver\Pdo\Statement r273), making it only look at the integer a value starts with.
E.g. it can find a value '123a' when trying to find an exact match for 123.
The Mysqli driver for instance finds the 123 value.
Casting the parameter specifically to a string will make it work correctly.
So unless you always specifically typecast the parameters in your queries for character fields, you can get unexpected results.
PDO defaults to PDO::PARAM_STR if no specific type is supplied while binding.
Viewed on
Zend DB: 2.10.0
OS: Ubuntu 16.04 LTS
PHP: 7.0.33
MySQL: 5.7.26
Code to reproduce the issue
DROP TABLE IF EXISTS `test_table`;
CREATE TABLE `test_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `test_table` (`name`) VALUES
('123456a'), ('123456');
$search = 123456;
$result = $adapter->query('SELECT * FROM `test_table` WHERE `name` = ?', [$search]);
$test = (array)$result->current();
print_r($test);
Expected results
Array
(
[id] => 2
[name] => 123456
)
Actual results
Array
(
[id] => 1
[name] => 123456a
)
@jvangestel Hi, I have verified what you said and I can confirm, that with Mysqli driver it works as expected and with Pdo_Mysql it doesn't. Would you like to submit PR with the hotfix and unit test?
zendframework/zendframework#3198 Seems to be the one that added the PDO::PARSE_INT. I'm unable to test on mssql, and the actual test doesn't seem to exist anymore