为什么当query()是时，这个准备好的PHP PDO语句不能工作？

Question

我可能很愚蠢，但我有一个函数，它根据输入计算需要的页数，然后计算需要的页数并返回。

function get_total_pages($field, $table, $page_size = 20, $where_something = "", $equals_something = ""){
    global $dbh;
    try {
        if(empty($where_something)){
        // I deleted irrelevant code here
        }
        elseif(!empty($where_something) && !empty($equals_something)){
            $count_query = $dbh->prepare("SELECT COUNT(:field) FROM :table WHERE :where=:equals");
            $count_query->bindParam(":field", $field);
            $count_query->bindParam(":table", $table);
            $count_query->bindParam(":where", $where_something);
            $count_query->bindParam(":equals", $equals_something);
            $count_query->execute();
            $count = $count_query->fetch();
            $total_records = $count[0];                 // calculating number of records in history table
            $total_pages = ceil($total_records / $page_size);   // calculating number of pages necessary
            return $total_pages;
        }
        return false;
    }
    catch(PDOException $e){
        echo $e->getMessage();
    }

我用来调用它

$total_pages = get_total_pages("username", "comments", $page_size, "username", $_GET['user']);

下面是我得到的错误：

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 ''comments' WHERE 'username'='redbot'' at line 1

但是，如果我将所有函数的代码替换为更简单的query()而不是准备好的语句，只要我在用户名后面加上引号，它就可以工作：

function get_total_pages($field, $table, $page_size = 20, $where_something = "", $equals_something = ""){
    global $dbh;
    try {
        if(empty){
          //   irrelevant code
        }
        elseif(!empty($where_something) && !empty($equals_something)){
            $count_query = $dbh->query("SELECT COUNT({$field}) FROM {$table} WHERE {$where_something}={$equals_something}");
            $count = $count_query->fetch();
            $total_records = $count[0];                 // calculating number of records in history table
            $total_pages = ceil($total_records / $page_size);   // calculating number of pages necessary
            return $total_pages;
        }
        return false;
    }
    catch(PDOException $e){
        echo $e->getMessage();
    }
}

$total_pages = get_total_pages("username", "comments", $page_size, "username", "\"" . $_GET['user'] . "\"");

Answer 1

You can't use dynamic field and table names in prepared statements.

您必须自己检查它们(理想情况下，对照现有的和允许的表名和列名的白名单)，并将它们放入查询字符串中。

Here是一些代码片段，展示了如何做到这一点。

Answer 2

您不能使用占位符定义列名，还可以看看bindParam和bindValue之间的区别

通常，参数只在数据操作语言(DML)语句中是合法的，而在数据定义语言(DDL)语句中不合法。