PostgreSQL+PHP：“从选项卡中选择 *，其中 col in (?)”
以下简短的测试脚本
<?php
define('DBHOST', '/tmp');
define('DBNAME', 'XXX');
define('DBUSER', 'XXX');
define('DBPASS', 'XXX');
$ids = array('OK251562715876', 'OK178469380239');
try {
$db = new PDO('pgsql:host=' . DBHOST . '; dbname=' . DBNAME, DBUSER, DBPASS, $options);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sth = $db->prepare('select * from pref_money where id in ( ? )');
$sth->execute($ids);
while ($row = $sth->fetch(PDO::FETCH_ASSOC))
print_r($row);
} catch (Exception $e) {
exit('Database problem: ' . $e->getMessage());
}
?>
失败并出现错误：
Database problem: SQLSTATE[42P18]:
Indeterminate datatype: 7
ERROR: could not determine data type of parameter $2
如果我删除数组的第二个元素，则它可以正常工作。
有没有办法使绑定适用于具有多个元素的数组？
我知道我可以通过使用 join() 等并省略问号来准备完整的 SQL 语句字符串，但随后我需要额外的努力来防止我的 Web 脚本中的 SQL 注入...
使用 PostgreSQL 8.4.6 和 PHP 5.1。 CentOS 5.5下6
the following short test script
<?php
define('DBHOST', '/tmp');
define('DBNAME', 'XXX');
define('DBUSER', 'XXX');
define('DBPASS', 'XXX');
$ids = array('OK251562715876', 'OK178469380239');
try {
$db = new PDO('pgsql:host=' . DBHOST . '; dbname=' . DBNAME, DBUSER, DBPASS, $options);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sth = $db->prepare('select * from pref_money where id in ( ? )');
$sth->execute($ids);
while ($row = $sth->fetch(PDO::FETCH_ASSOC))
print_r($row);
} catch (Exception $e) {
exit('Database problem: ' . $e->getMessage());
}
?>
fails with the error:
Database problem: SQLSTATE[42P18]:
Indeterminate datatype: 7
ERROR: could not determine data type of parameter $2
If I remove the 2nd element of the array, then it works ok.
Is there maybe a way to make the binding work for array with several elements?
I know that I can prepare a complete SQL statement string by using join() etc. and omiting the question mark, but then I need extra effort to prevent SQL injection in my web script...
Using PostgreSQL 8.4.6 with PHP 5.1.6 under CentOS 5.5
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
PDOStatement::execute()期望单个参数是参数数组。该数组的每个元素都被视为查询参数。因此，现在 PDO“认为”您传递了两个参数，而您的查询仅包含一个参数。解决方案：传递一个只有一个元素（参数）的数组，即一个数组：
...->execute(array($ids));PDOStatement::execute()expects a single argument that is an array of parameters. Each element of that array is treated as a query parameter. So right now PDO "thinks" that you pass two parameters while your query contains only one.Solution: pass an array with only one element (parameter), which is an array:
...->execute(array($ids));使用正确数量的问号构建查询（以匹配数组的长度），然后将数组的内容作为值传递给
执行。Build the query with the right number of question marks- to match the length of the array- then pass the contents of the array as the values to
execute.如果绑定参数以这种方式工作，那就很方便了，但遗憾的是，事实并非如此。你必须做类似的事情：
丑陋，可能不是你想要的，但你可以编写一些代码让它稍微友好一些（例如，自动放入正确数量的问号）。
It would be convenient if bound parameters worked this way, but alas, they do not. You'd have to do something like:
Ugly, and probably not what you want, but you can write some code to make it slightly friendlier (automatically put in the right number of question marks, for example).