PDO:绑定参数和连接字符串之间的区别 [英] PDO: Difference between binding params and concatenating string
问题描述
我有这个奇怪的问题.为什么这两个实现返回不同的结果?
I have this weird problem. Why do these two implementations return different results?
$db = DbBase::getInstance();
$stmt = $db->prepare('SELECT round(round(9.50 * :amount, 2) * 23 * 0.01, 2)');
$stmt->execute(array(':amount' => 1));
echo $stmt->fetchColumn();
Result: 2.18
$db = DbBase::getInstance();
$stmt = $db->prepare('SELECT round(round(9.50 * 1, 2) * 23 * 0.01, 2)');
$stmt->execute();
echo $stmt->fetchColumn();
Result: 2.19
当我绑定金额时,它会给我不同的结果.由于SQL注入,我宁愿不连接字符串.
When I bind the amount it gives me different result. I'd rather not concatenate the string because of the SQL injections.
推荐答案
使用数组传递数据时,数据将作为字符串传递:
When you are using the array to pass the data, the data is passed as a string:
从文档:
一个值数组,其中元素的数量与正在执行的SQL语句中绑定的参数一样多.所有值都视为PDO :: PARAM_STR.
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.
但是,当您直接在查询中手动输入1
时,它将被视为一个int.让我看看是否可以做进一步的挖掘,以了解将字符串转换为int时内部发生的情况.
However, when you are manually entering in the 1
into the query directly it is being treated as an int. Let me see if I can do some further digging to see what happens internally when a string is converted into an int for you.
这可能是最相似的错误之一.提交并接受:
This is probably one of the most similar bugs that have been submitted and accepted:
1)
SET @a = 1;
SELECT @a;
2)
SET @a = 1.1;
SELECT @a;
.. and this
3)
SET @a = 1.1;
SELECT @a + 7;
returns '8.100000000000000000000000000000'
(probably the addition will convert "1.1" to a double, the result
of the addition is also a DOUBLE and finally the DOUBLE is converted
to a string - that should be OK as well as far as I can understand)
因此,当您将int传递给mysql时,它似乎在内部将mysql转换为double.那会很好地解释您所看到的行为.
So it looks like internally mysql is converting to a double when you pass it an int. That would explain rather nicely the behavior that you are seeing.
以下是您可能感兴趣的其他类似(错误编号)错误的列表:
Here is a list of other similar (numbers not quite right) bugs you might be interested in:
http://bugs.mysql.com/bug.php?id=46037
http://bugs.mysql.com/bug.php?id=35071
http://bugs.mysql.com/bug.php?id=35071 <-表现出Win和Lin之间差异的好人
http://bugs.mysql.com/bug.php?id=35071 <-- Good one showing difference between Win and Lin
And a filtered list of data type bugs that I perused which make for interesting reading.
啊哈!
这是一个非常完美的错误解释了您的问题:
Here is a bug that rather perfectly explains your issue:
Reproduce code:
---------------
CREATE TABLE my_db.my_table (
id int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
<?php
$DB = new PDO('mysql:dbname=my_db;host=localhost', 'user', 'pass');
$stmt = $DB->prepare('select * from my_table where id>?');
$stmt->bindValue(1, 13);
$stmt->execute();
?>
or
<?php
$DB = new PDO('mysql:dbname=my_db;host=localhost', 'user', 'pass');
$stmt = $DB->prepare('select * from my_table where id>?');
$stmt->execute(array(13));
?>
Expected result:
----------------
select * from my_table where id>13
Actual result:
--------------
select * from my_table where id>'13'
这篇关于PDO:绑定参数和连接字符串之间的区别的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!