0

I want to update several tables as below: 

for ($i=0; $i <count($tablesnames); $i++) {

    $update3=$pdo->prepare('UPDATE :surveytable SET `postrecords`=:newrecord WHERE `id`=:id');
//var_dump()here
    $update3->bindValue(':surveytable', $tablesnames[$i],PDO::PARAM_STR);

    $update3->bindValue(':newrecord',$newrecord,PDO::PARAM_STR);

    $update3->bindValue(':id',$id,PDO::PARAM_INT);

    $update3->execute();

}

Check the var_dump result,$tablesnames[$i] and $newrecordare string,$id is int,$update3 is false.
Seemed everything ok but failed,

Warning: PDO::prepare(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? SET postrecords=? WHERE id=?'

What's the problem?

kittygirl
  • 2,255
  • 5
  • 24
  • 52

1 Answers1

0

(Unfortunately) you can't use parameters for your tablename in your prepared statement. You can only use those for data literals. So UPDATE :surveytable is invalid.

As per manual:

Parameter markers can represent a complete data literal only. Neither part of literal, nor keyword, nor identifier, nor whatever arbitrary query part can be bound using parameters.

When you (completely!!!) trust your source of $tablesnames, use 

'UPDATE `' . $tablesnames[i]` . '` SET `postrecords`=:newrecord WHERE `id`=:id'

instead

Peter van der Wal
  • 11,141
  • 2
  • 21
  • 29
  • which mean cannot fully avoid sql injection,sad! – kittygirl Apr 29 '18 at 15:00
  • Depends on the source of `$tablesnames`. When this is user-input (e.g. POST-data, even the names of POST-data), you should definitely validate it before using it within your query. For example using an `in_array` whith a list of your existing tablenames or a `preg_match` with a regular expression that would only allow valid tablenames. (edit:) Although using user-input for tablenames is something I always try to avoid (due to this kind of concerns). – Peter van der Wal Apr 29 '18 at 15:13