php - 为什么这个准备好的 PHP PDO 语句在 query() 时不起作用？

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("用户名", "评论", $page_size, "用户名", $_GET['user']);

这是我得到的错误:

SQLSTATE[42000]:语法错误或访问冲突:1064 您的 SQL 语法有误；查看与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的“注释”附近使用的正确语法 WHERE 'username'='redbot''

但是，如果我将所有函数的代码换成更简单的 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("用户名", "评论", $page_size, "用户名", "\"". $_GET['user'] . "\"");

Answer 1

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

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

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