Why does my PDO statement -> execute return false?

Question

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! :)

Asaph · Accepted Answer · 2018-04-09T20:08:00.753

8

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.

edited Apr 09 '18 at 20:08

answered Jun 21 '11 at 04:00

Asaph

159,146
25
197
199

so if I concatenate a string with the `:table` and `:field` (which aren't open to user input and should be safe when concatenated) and substitute `:value`, I might be in the clear? – somewhatsapient Jun 21 '11 at 04:03
+1 except for the "treated as strings and quoted". They are in fact passed as parameters for the prepared statement though for illustrative purposes, your example is correct – Phil Jun 21 '11 at 04:03
2

Awesome Asaph - that fixed it. Easy enough to change it to concatenate `$table` and `$field`, and bind `$value` – somewhatsapient Jun 21 '11 at 04:06
@Phil: Yes. You are correct. I tried to be careful in my wording, especially when I said "you get something *similar* to". But I slipped up. I will edit out the "and quoted" part. Thank you for pointing it out. – Asaph Jun 21 '11 at 04:06
@somewhatsapient: Yes, the idea you presented in your first comment will work. – Asaph Jun 21 '11 at 04:10
@ArchLinuxTux I rolled back your edit. Changing single quotes to backticks makes the answer wrong. – Asaph Apr 09 '18 at 20:10

Why does my PDO statement -> execute return false?

1 Answers1

Linked