MySQL PDO查询语法错误与？ 参数

Question

as a newbie, I've followed PHP MySQL tutorials advising the use of regular MySQL php functions. However, since I've been told that PDO is the better alternative, I've been converting my code to that. I just ran into the following problem:

    $query = $uspdb->prepare("SELECT post_id, is_approved, reports FROM ? WHERE id=? AND ?");
    $query->bindValue(1, $table, PDO::PARAM_INT);
    $query->bindValue(2, $id, PDO::PARAM_INT);
    $query->bindValue(3, checkPermission("comment_moderation"),PDO::PARAM_BOOL);
    $query->execute;
    $result = $query->fetch(PDO::FETCH_ASSOC);

The first line throws the following PDO exception:

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 '? WHERE id=? AND ?' at line 1

Why is that? I have no idea what could be wrong with the syntax. The tutorial I'm reading tells me that I should be using bindValue or execute(array(stuff)) to add parameters rather than ".$id." and the likes, since it's safer, but this isn't working for whatever reason.

Answer 1

Unfortunately, prepared statement can represent a data literal only. (in case of emulated prepares). So, a developer have to take care of identifiers oneself - PDO offers no help for this matter.

To make a dynamical identifier safe, one have to follow 2 strict rules:

1. To format identifier properly. Means
   • enclose identifier in backticks.
   • escape backticks inside by doubling them.
2. To verify it against a hardcoded whitelist.

After the formatting, it is safe to insert the $table variable into query. So, the code would be:

$field = "`".str_replace("`","``",$field)."`";
$sql   = "SELECT * FROM t ORDER BY $field";

However, although such a formatting would be enough for the cases like ORDER BY, for the most other cases there is a possibility for a different sort of injection: letting a user to choose a table or a field they can see, we may reveal some sensitive information, like password or other personal data. So, it's always better to check dynamical identifiers against a list of allowed values. Here is a brief example:

$allowed = array("name","price","qty");
$key = array_search($_GET['field'], $allowed));
if ($key === false) {
    throw new Exception('Wrong field name');
}
$field = $allowed[$key];
$query   = "SELECT $field FROM t"; //value is safe