Why does my PDO statement -> execute return false?

somewhatsapient picture somewhatsapient · Jun 21, 2011 · Viewed 10.9k times · Source

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.

Code:

$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;
}

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...

UPDATE

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;
}

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.

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

Answer

Asaph picture Asaph · Jun 21, 2011

When you have a parameterized query that looks like this:

SELECT * FROM :table WHERE :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'

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.