为什么我的PDO语句->执行返回false? [英] Why does my PDO statement -> execute return false?

问题描述

在经过无休止的测试之后，我确定PDO连接有效(我可以运行一个简单的查询并显示结果)，确定该语句正在成功准备中，并且值绑定正确.由于某种原因，该语句将不会执行.只是为了可爱，我尝试删除所有绑定变量并执行静态查询，但这也不起作用.

After near endless rounds of testing different aspects of this, I've determined that the PDO connection works (I can run a simple query and display results), I've determined that the statement is successfully preparing, and that the values are binding properly. For some reason, the statement won't execute. Just to be cute, I've tried removing all bound variables and executing a static query, and that won't work either.

代码:

$dbh = new PDO( "mysql:host=localhost;dbname=".$GLOBALS['data_name'], $GLOBALS['data_user'], $GLOBALS['data_pass'] );
$dbh->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

$sth = $dbh->prepare( "SELECT * FROM :table WHERE :field = :value" );
if( $sth != false ) TCDebug( 'prepared' );
if( $sth->bindValue( ":table", $table ) ) TCDebug( "table true" );
if( $sth->bindValue( ":field", $field ) ) TCDebug( "field true" );
if( $sth->bindValue( ":value", $value ) ) TCDebug( "value true" );
$flag = $sth->execute();

if( $flag === true ) {
    TCDebug( 'flag = true' );
} else if( $flag === false ) {
    TCDebug( 'flag = false' );
}
$result = $sth->fetchAll();

foreach( $result as $c ) TCDebugArr( $c );
TCDebug( count( $result ) );
if( count( $result ) > 0 ) {
    return $result;
} else {
    return null;
}

始终回显"prepared"，"table true"，"field true"，"value true"，"flag = false"的调试文本，这告诉我准备和绑定工作，但执行没有，$ result为空，并且函数返回null.

Consistently echos debug text of 'prepared' 'table true' 'field true' 'value true' 'flag = false' which tells me that preparing and binding work, but executing doesn't, $result is empty and the function returns null.

我可能已经忽略了一个非常明显的问题，并且我已经做好充分准备，以完全可耻的态度垂下头了.预先谢谢你...

I've probably overlooked something horrendously obvious, and I'm fully prepared to hang my head in utter n00b shame. Thank you in advance...

更新

啊，串联-我今天的朋友.工作代码如下:

Ahh, concatenation -- my friend today. Working code follows:

$dbh = new PDO( "mysql:host=localhost;dbname=".$GLOBALS['data_name'], $GLOBALS['data_user'], $GLOBALS['data_pass'] );
$dbh->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

$prepare_str = "SELECT * FROM ". $table ." WHERE ". $field ." = :value";

$sth = $dbh->prepare( $prepare_str );
if( $sth != false ) TCDebug( 'prepared' );
if( $sth->bindValue( ":value", $value ) ) TCDebug( "value true" );
$flag = $sth->execute();

if( $flag === true ) {
    TCDebug( 'flag = true' );
} else if( $flag === false ) {
    TCDebug( 'flag = false' );
}
$result = $sth->fetchAll();

foreach( $result as $c ) TCDebugArr( $c );
TCDebug( count( $result ) );
if( count( $result ) > 0 ) {
    return $result;
} else {
    return null;
}

在这种情况下这是安全的，因为$table和$field是系统生成的，不能通过用户输入进行访问；仅显示$ value.

This is safe in this instance, since $table and $field are system-generated and in no way accessible via user input; only $value is exposed.

谢谢StackOverflow！你是我的最爱！ :)

Thank you StackOverflow! You're my favorite! :)

推荐答案

当您进行如下所示的参数化查询时:

When you have a parameterized query that looks like this:

SELECT * FROM :table WHERE :field = :value

并用:table，:field和:value的值替代，您将得到与以下内容类似的 (实际上这是一个过分简化的例子，但也说明了这一点):

and you substitute values for :table, :field, and :value, you get something similar to the following (actually this is an oversimplication but illustrates the point):

SELECT * FROM 'sometable' WHERE 'somefield' = 'somevalue'

因为:table和:field得到与:value相同的语义处理，即.它们被视为字符串.通常，您无法使用参数化查询来参数化表名和列名.您将不得不重新考虑自己的方法.您可能会考虑动态构造准备好的语句字符串，以便查询的表名和列名部分是简单的串联，而不是将它们与PDO绑定.但是您必须非常小心地验证/清除表名和列名，因为PDO不会保护您免受该级别的SQL注入.

because :table and :field get the same semantic treatment as :value, ie. they are treated as strings. You generally cannot parameterize table names and column names with parameterized queries. You'll have to rethink your approach a little. You might consider dynamically constructing your prepared statement string so that the table and column name parts of the query are simple concatenations, rather than binding them with PDO. But you must be very careful that you validate/sanitize the table and column names because PDO won't protect you from SQL injection at that level.

这篇关于为什么我的PDO语句->执行返回false?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！