Pdo_Mysql driver integer query parameters can return not exact matches

[jvangestel]

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
)

[michalbundyra]

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

[jvangestel]

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