PDO 不会抛出带有未绑定参数的异常(并且查询中没有变量) [英] PDO not throwing exception with unbound parameters (and no variables in query)
问题描述
所以我不知道这里发生了什么
So I have no idea what's going on here
$link = new PDO('pgsql:dbname=' . $name . ';host=' . $host, $user, $password);
$link->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$stmt = $link->prepare("SELECT s.*, d.invalid_column FROM students s ORDER BY s.student_id");
$stmt->execute(array(1));
}
catch (PDOException $e) {
print $e->getMessage();
}
当我运行这个小代码示例时，我希望抛出一个异常(因为 d.invalid_column 不是一个真正的列，而且我正在传递无法绑定的参数)，但唯一发生的事情是执行返回 false 而没有别的.另外 $stmt->errorInfo() 是空白的，代码是 00000 这使得很难添加一个正确的异常抛出超出一些超级通用的东西，而不是日志当某些最终用户报告错误时，帮助我跟踪错误.
When I run this little code example, I expect an exception to be thrown (as d.invalid_column is not a real column and I'm passing in parameters that cannot be bound), but the only thing that happens is that execute returns false and nothing else. Additionally $stmt->errorInfo() is blank and the code is 00000 which makes it hard to add a proper exception throw beyond something super generic with nothing else for the logs to assist me in tracking errors down when some end user reports an error.
如果我添加一个?"在查询的某个地方，正确的执行被抛出(d.invalid_column 不是一个有效的列)，即使我添加了更多不绑定到任何东西的参数.
If I add a single '?' somewhere to the query, the proper execution is thrown (that d.invalid_column is not a valid column), even if I add more parameters that don't bind to anything.
让这个查询正确出错的方法:
1) 去掉所有参数
2)添加一个?"到查询
So ways to get this query to properly error:
1) Get rid all parameters
2) Add a '?' to the query
这只是 PDO 中的错误还是什么?
Is this just a bug in PDO or what?
将引发异常的设置(无效列):
edit: Setups that will throw an exception (invalid column):
$stmt = $link->prepare("SELECT s.*, d.invalid_column, ? FROM students s ORDER BY s.student_id");
$stmt->execute(array(1));
$stmt = $link->prepare("SELECT s.*, d.invalid_column, ? FROM students s ORDER BY s.student_id");
$stmt->execute(array(1,2,3));
$stmt = $link->prepare("SELECT s.*, d.invalid_column, ? FROM students s ORDER BY s.student_id");
$stmt->execute();
$stmt = $link->prepare("SELECT s.*, d.invalid_column FROM students s ORDER BY s.student_id");
$stmt->execute();
只有当我的查询中没有 ? 并将某些内容传递给 execute() 时，事情才会静默地失败并且 PDO 没有任何解释.
It's only when I have no ? in my query and pass something to execute() that things just fail silently and with no explanation from PDO.
推荐答案
该行为可在当前 PHP (5.6.13) 中重现，并且查询甚至不会发送到服务器.
That behavior is reproducible with the current PHP (5.6.13), and the query is not even sent to the server.
您的案例在文档中描述为:
您不能绑定比指定更多的值；如果存在更多密钥input_parameters 比在 PDO::prepare() 中指定的 SQL 中，然后该语句将失败并发出错误.
You cannot bind more values than specified; if more keys exist in input_parameters than in the SQL specified in the PDO::prepare(), then the statement will fail and an error is emitted.
应为 0 值，给出 1 值，语句失败，返回 false.到目前为止，工作正常.
0 value is expected, 1 value is given, and the statement fails, false being returned. So far, works as documented.
您可能会争辩说出现错误"意味着当 ERRMODE_EXCEPTION 开启时，会抛出异常.这是一个论点，但 PDO 开发人员是否同意这一点并不明显.
You may argue that "an error is emitted" would imply that when ERRMODE_EXCEPTION is on, an exception would be thrown. That's an argument, but it's not obvious that the PDO developers would agree with it.
更新:
为什么没有设置SQLCode?
查看PDO源代码，特别是处理PDO::execute()的static PHP_METHOD(PDOStatement, execute)，可以看到所有的错误都是由一个宏处理的:PDO_HANDLE_STMT_ERR()
Looking at PDO source code, specifically static PHP_METHOD(PDOStatement, execute) that handles PDO::execute(), you can see that all errors are handled by a macro: PDO_HANDLE_STMT_ERR()
#define PDO_HANDLE_STMT_ERR() if (strcmp(stmt->error_code, PDO_ERR_NONE)) { pdo_handle_error(stmt->dbh, stmt TSRMLS_CC); }
重点是，当 PDO 期望没有时传递绑定参数时，查询永远不会将其传递给 SQL 引擎，因此 SQL 引擎永远没有机会报告伴随 SQLSTATE 的错误
The point is that, when passing a bound parameter when PDO expected none, the query never makes it to the SQL engine, so the SQL engine never has the opportunity to report an error accompanied with an SQLSTATE
PDO 本身不会自行创建伪造的 SQLSTATE，至少在那种情况下不会，所以 stmt->error_code 停留在 PDO_ERR_NONEcode> 即 "00000".
PDO itself does not create a fake SQLSTATE on its own, at least no in that case, sostmt->error_code stays at PDO_ERR_NONE which is "00000".
您希望引发异常是可以理解的，但是您应该建议 https://bugs.php.net
It's understandable that you would prefer an exception to be raised, but then you should suggest that to https://bugs.php.net
和 MySQL 一样吗?
是的，除了使用 MySQL 驱动程序之外，root 行为是相同的，prepare 会立即发送到 SQL 引擎，因此如果由于列错误而导致错误，它会更早失败并带有真正的 SQL 错误.另一方面，PgSQL 驱动程序有一个不同的实现，使它推迟服务器端prepare.此特定行为在 PHP Postgres PDO 驱动程序不支持准备好的语句?
Yes, the root behavior is the same except that with the MySQL driver, the prepare is sent immediately to the SQL engine so if it's incorrect because of a bad column, it fails earlier and with a real SQL error. On the other hand, the PgSQL driver has a different implementation that makes it defer the server-side prepare. This particular behavior is discussed in detail at PHP Postgres PDO driver does not support prepared statement?
无论如何，这里有一个 MySQL 的案例来说明我的解释，那就是:
Anyway, here's a case with MySQL that demonstrates my explanation, that is:
- 查询需要 0 个参数，给出 1 个
$stmt->execute返回 false- 不会引发异常
- PDO::errorCode 是
00000
- the query expects 0 parameter, 1 is given
$stmt->executereturns false- no exception gets raised
- PDO::errorCode is
00000
代码:
$link = new PDO('mysql:dbname=' . $name . ';host=' . $host, $user, $password);
$link->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$stmt = $link->prepare("SELECT 1");
$rc=$stmt->execute(array(1));
if ($rc===false)
echo "query failed, errorCode=", $link->errorCode(), "\n";
else
echo "query succeeded, errorCode=", $link->errorCode(), "\n";
}
catch (PDOException $e) {
print "A PDOException has occurred";
print $e->getMessage();
}
结果:
查询失败，errorCode=00000
query failed, errorCode=00000
幕后发生的事情是 prepare 被发送到服务器并成功，但由于参数不匹配，execute 步骤被 PDO 取消.
What happens under the hood is that the prepare is sent to the server and succeeds, but the execute step is cancelled by PDO due to the mismatch in parameters.
这是一个不同的情况，查询引用了一个不存在的列.我正在添加一个打印来显示 $stmt->execute 甚至没有被调用，因为 $stmt->prepare
Here's a case that differs in the fact that the query refers to a non-existing column. I'm adding a print to show that $stmt->execute is not even called, as the exception is raised by $stmt->prepare
代码:
$link = new PDO('mysql:dbname=' . $name . ';host=' . $host, $user, $password);
$link->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$stmt = $link->prepare("SELECT nonexisting");
echo "Executing query\n";
$rc=$stmt->execute(array(1));
if ($rc===false)
echo "query failed, errorCode=", $link->errorCode(), "\n";
else
echo "query succeeded, errorCode=", $link->errorCode(), "\n";
}
catch (PDOException $e) {
print "A PDOException has occurred";
print $e->getMessage();
}
结果:
发生了 PDOExceptionSQLSTATE[42S22]: Column not found: 1054字段列表"中的未知列不存在"
A PDOException has occurredSQLSTATE[42S22]: Column not found: 1054 Unknown column 'nonexisting' in 'field list'
注意执行查询"步骤是如何永远不会发生的，因为它是服务器端的prepare 失败了.
Note how the "Executing query" step never happens, because it's the prepare that fails, server-side.
结论
当查询发送到服务器时，无论是在 prepare() 还是 execute() 中，并且是服务器产生错误，那么我们可以预期会引发 PDOException.
when the query is sent to the server, be it in prepare() or execute(), and it's the server that generates an error, then we can expect a PDOException to be raised.
当查询未发送到服务器执行步骤时，PDO execute() 可能会失败(返回 false)但不会抛出异常并且 errorCode() 保持在 <代码>00000
when the query is not sent to the server for an execution step, then PDO execute() can fail (returns false) but no exception is thrown and errorCode() stays at 00000
这篇关于PDO 不会抛出带有未绑定参数的异常(并且查询中没有变量)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
