为什么这个 PDO 参数化查询表现得“奇怪"? [英] Why this PDO parametrized query behave "strangely"?
问题描述
在这里我编辑了我原来的问题.我也回答了，在我的下一封邮件中.
Here i have edited my original question. I have alswo answered it, in my next message.
我正在尝试使用参数化的 php PDO 查询从 MySQL 获取结果，但事情表现得很糟糕.我不知道这是一个错误，还是我做错了什么或没有看到明显的东西.
I'm trying to get results from MySQL with parametrized php PDO query, but thing behaves strangley. I dont know if it is a bug, or I am doing something wrong or not seeing something obvious.
假设数据库中有这两个表
Lets suppose there are these two tables in database
CREATE TABLE `users` (
`user_id` int(11) NOT NULL PRIMARY KEY
)
CREATE TABLE `users_contacts` (
`contact_id` int(11) NOT NULL PRIMARY KEY ,
`user_id` int(11) DEFAULT NULL,
`type` varchar(45) DEFAULT NULL,
`value` varchar(255) DEFAULT NULL
)
用最少的数据填充它们:
Fill them with minimal data :
INSERT INTO `users` (`user_id`) VALUES (125);
INSERT INTO `users_contacts` (`contact_id`, `user_id`, `type`, `value`)
VALUES(11432, 125, 'email', 'losleyTyped@offten.stinks'),
(11433, 125, 'phone', '1234567'),
(564, 125, 'unit', '910');
然后你尝试像这样获取数据
And then you try to fetch data like this
$db_name = "";
$db_user = "";
$db_pass = "";
$db_pdo = new pdo("mysql:host=localhost;dbname=$db_name","$db_user","$db_pass");
$user = 125;
$user_unit_btm = 900;
$user_unit_top = $user_unit_btm + 100;
$upload_user = $db_pdo -> prepare("SELECT K.value AS unit
FROM users AS O,
users_contacts AS K
WHERE O.user_id = :user_id AND
K.user_id = O.user_id AND
K.type = 'unit' AND
K.value >= :unit_btm AND
K.value < :unit_top
");
$upload_user -> execute( [":user_id" => $user,
":unit_btm" => $user_unit_btm,
":unit_top" => $user_unit_top
]
);
$upload_user = $upload_user -> fetch(PDO::FETCH_ASSOC);
var_dump($upload_user);
var_dump 会返回 false，但没有错误(err 为 0000)
我已经减少了问题，发现只有一个参数:organization"有问题并且导致了奇怪的行为.
I have reduced the problem, and find that only one parameter ":organization" is problematic and cause for bizare behevior.
但是如果你替换"K.value <:unit_top "带有变量 $user_unit_top
" K.value <$user_unit_top "
然后，查询返回结果！
But if you replace " K.value < :unit_top "
with variable $user_unit_top
" K.value < $user_unit_top "
Then, query returns result!
同样的事情，如果我用文字 1000 替换K.value < :unit_top"，
" K.value <100"
然后查询返回结果！
Same thing if i replace " K.value < :unit_top " with literal 1000,
" K.value < 100"
Then query returns result!
为什么会这样?
推荐答案
如我对您的回答的评论中所述.
As mentioned in my comment to your answer.
关于 PDOStatement::execute 的 PHP 文档说明.
The PHP documentation on PDOStatement::execute states.
一个值数组，其元素与正在执行的 SQL 语句中的绑定参数一样多.所有值都被视为 PDO::PARAM_STR.
来源:https://www.php.net/manual/en/pdostatement.执行.php
An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as
PDO::PARAM_STR.
Source: https://www.php.net/manual/en/pdostatement.execute.php
此外，当没有更多结果或失败时，PDOStatement::fetch() 返回 false.
Additionally PDOStatement::fetch() returns false when there are no more results or upon failure.
此函数成功时的返回值取决于获取类型.在所有情况下，失败时返回 FALSE.
The return value of this function on success depends on the fetch type. In all cases,
FALSEis returned on failure.
$pdo = new \PDO('sqlite::memory:', null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
$pdo->query('CREATE TABLE foo(id INTEGER)');
$stmt = $pdo->prepare('SELECT * FROM foo');
$stmt->execute();
var_dump($stmt->fetch());
//bool(false)
如果您需要为发送到 MySQL 的参数显式定义除 PDO::PARAM_STR 之外的数据类型，您可以使用 PDOStatement::bindParam 或 PDOStatement::bindValue
If you need to explicitly define a data type, other than PDO::PARAM_STR for the parameter being sent to MySQL, you would use PDOStatement::bindParam or PDOStatement::bindValue
示例:
$upload_user = $db_pdo->prepare('SELECT
K.value AS unit
FROM users AS O,
users_contacts AS K
WHERE O.user_id = :user_id
AND K.user_id = O.user_id
AND K.type = \'unit\'
AND K.value >= :unit_btm
AND K.value < :unit_top');
$upload_user->bindValue(':user_id', $user, PDO::PARAM_INT);
$upload_user->bindValue(':unit_btm', $user_unit_btm, PDO::PARAM_INT);
$upload_user->bindValue(':unit_top', $user_unit_top, PDO::PARAM_INT);
$upload_user->execute();
另一种方法是对查询中的参数强制进行数据类型转换.
An alternative would be to force data type casting on the parameter in the query.
$upload_user = $db_pdo->prepare('SELECT
K.value AS unit
FROM users AS O,
users_contacts AS K
WHERE O.user_id = :user_id
AND K.user_id = O.user_id
AND K.type = \'unit\'
AND K.value >= (:unit_btm - 0)
AND K.value < (:unit_top - 0)'); //CAST(:unit_top AS SIGNED)
$upload_user->execute([
':user_id' => $user,
':unit_btm' => $user_unit_btm,
':unit_top' => $user_unit_top
]);
<小时>
您的问题的另一个促成因素是 MySQL 将自动转换为列的数据类型以进行比较.其他 RDMBS，如 PostgreSQL 和 SQLite3 不执行相同的转换.
Another contributing factor to your issue, is that MySQL will perform an automatic conversion to the column's data type for the comparison. Where other RDMBS, like PostgreSQL and SQLite3 do not perform the same conversions.
当一个运算符与不同类型的操作数一起使用时，输入发生转换以使操作数兼容.一些转换隐含地发生.例如，MySQL 自动将字符串转换为根据需要使用数字，反之亦然.
来源:https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html
When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts strings to numbers as necessary, and vice versa.
Source: https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html
由于您的初始列数据类型是 VARCHAR，因此您的测试结果如下.
数据库小提琴
Since your initial column data type was VARCHAR, this resulted in the following from your testing.
DB Fiddle
初始查询为 PDOStatement::execute([1000]).
SELECT IF('910' > '1000', 'fail', 'pass') AS if_str_to_str;
| if_str_to_str |
| ------------- |
| fail |
手动向查询提供整数
SELECT IF('910' > 1000, 'fail', 'pass') AS if_str_to_int;
| if_str_to_int |
| ------------- |
| pass |
更改数据库列数据类型后使用PDOStatement::execute([1000])
After changing the database column data type and using PDOStatement::execute([1000])
SELECT IF(910 > '1000', 'fail', 'pass') AS if_int_to_str;
| if_int_to_str |
| ------------- |
| pass |
使用 PDOStatement::bindValue(':param', '1000', PDO::PARAM_INT) 或 ('1000' - 0)
SELECT IF('910' > CAST('1000' AS SIGNED), 'fail', 'pass') AS if_str_to_typecast_int;
| if_str_to_typecast_int |
| ---------------------- |
| pass |
这篇关于为什么这个 PDO 参数化查询表现得“奇怪"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
