0

I trying to update my db using a PDO statement, I have had no real problems until I try to update it using variables as the name and field . 

    $real_function = 'top' ;
    $value = 99 ;
    $tableName = "twitter_control" ;

    $stmt = $pdo->prepare("UPDATE ? SET ?=? WHERE id='control' ");
    $stmt->execute(array( $tableName, $real_function, $value ));

If I use this code all works as expected 

  $stmt = $pdo->prepare("UPDATE twitter_control SET top=? WHERE id='control' ");
  $stmt->execute(array( $value ));

How can I make this work ? , any suggestions please ?

Mick
  • 2,840
  • 10
  • 45
  • 61

2 Answers2

3

you can always do:

$stmt = $pdo->prepare("UPDATE {$tableName} SET {$real_function}=? WHERE id='control' ");

in your prepare statement to get it work. As far as I know you can only bind the values to your execute input parameter or bindParam function.. If someone can correct me otherwise..

Dins

Dinesh
  • 3,065
  • 1
  • 18
  • 19
1

When you pass the parameter to execute it treats the value as a string when it is escaped and will wrap it in quotes. This leads to UPDATE 'twitter_control' SET 'top' = '99', which is invalid.

You should probably know the names of your tables and columns ahead of time though. If they do have to be variables you should create a whitelist of values before having them interpolated in the query.

if (!in_array($real_function, array('top')) {
    return;
}
..."UPDATE twitter_control SET $real_function = ?..."
Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
  • When using an actual prepared statement (not emulation), it's not quite correct to say the parameter is escaped and wrapped in quotes. What actually happens is that the string is passed *as a separate variable* to the database, which inserts it directly into a *compiled* query plan, not the SQL statement itself. (The effect is the same, but it explains *why* you cannot use parameters in other contexts.) – IMSoP May 02 '13 at 22:04