PDO 参数化查询的工作方式
请仔细阅读问题。这不是通常的愚蠢“我的代码不起作用！！！”问题。
当我运行此代码并出现预期错误时，
try {
$sth = $dbh->prepare("SELECT id FROM users WHERE name INN(?,?) ");
$sth->execute(array("I'm","d'Artagnan"));
} catch (PDOException $e) {
echo $e->getMessage();
}
我收到此错误消息
您的 SQL 语法有错误...第 1 行的 'INN('I\'m','d\'Artagnan')' 附近
但我多年来一直认为查询和数据会单独发送到服务器，但从来没有干扰。因此我有一些问题（尽管我怀疑有人得到答案......）
- 它从哪里得到如此熟悉的字符串表示形式 - 引用和转义？它是专门为了报告错误还是实际查询的一部分？
- 实际情况如何？它是否用数据替换占位符？
- 有没有办法获取整个查询，而不仅仅是其中的一小部分，以进行调试？
更新
mysqli 按预期执行：它抛出一个错误，显示 near 'INN(?,?)'
PLEASE READ THE QUESTION CAREFULLY. It is not usual silly "my code doesn't work!!!" question.
When I run this code with intended error
try {
$sth = $dbh->prepare("SELECT id FROM users WHERE name INN(?,?) ");
$sth->execute(array("I'm","d'Artagnan"));
} catch (PDOException $e) {
echo $e->getMessage();
}
I get this error message
You have an error in your SQL syntax ... near 'INN('I\'m','d\'Artagnan')' at line 1
But I thought for years that query and data being sent to the server separately and never interfere. Thus I have some questions (though I doubt anyone got an answer...)
- Where does it get such a familiar string representation - quoted and escaped? Is it being made especially to report an error or is it a part of actual query?
- How does it work in real? Does it substitute a placeholder with data or not?
- Is there a way to get whole query, not only little bit of it, for debugging purposes?
Update
mysqli does it as expected: it throws an error says near 'INN(?,?)'
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我不确定所有细节，但我会尽力回答。
引用发生在数据库端。数据库转义并清理它接收到的所有值（参见项目符号 2），以便正确解释。
抛出错误时，数据库（在本例中为 MySQL）会打印出它尝试运行的查询。如果它只是显示准备好的部分，那么这不会有太大帮助。
不，事实并非如此。在准备时，查询在服务器端进行编译。当使用值执行查询时，仅传输值。这与直接在数据库上调用 PREPARE 和 EXECUTE 几乎相同。
这取决于您使用的数据库。例如，MySQL 可以将所有查询记录到日志文件中（检查 my.cnf 设置）。但您也可以在 PHP 端使用 debugDumpParams() 。
我希望这有点帮助。
I'm not sure about all the details, but I will try to answer.
The quotation happens on the database side. The database escapes and sanitizes all values (see bullet 2) it receives so that it gets interpreted correctly.
The moment the error is thrown, the database (in this case MySQL) prints out the query it tried to run. This wouldn't be so helpful if it just showed the prepared part.
No, it doesn't. At preparation time the query gets compiled on the server side. When a query is executed with values, only the values are transmitted. This is pretty much the same as calling PREPARE and EXECUTE on the database directly.
This depends on the database you're using. MySQL for example can log all queries to a log file (check my.cnf settings for that). But you can also use debugDumpParams() on PHP side.
I hope this was a bit helpful.
尝试添加
；）
try adding
;)