CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
emik
emik

Reputation: 58

Why is this prepared PHP PDO statement not working when the query() is?

I'm probably being stupid, but I have this function, which calculates the number of pages necessary based on the input, from which it counts how many pages are necessary and returns it.

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();
    }

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

Here is the error I get:

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

However, if I swap all the function's code for a simpler query() instead of a prepared statement, it works, as long as I append quotation marks to the username:

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'] . "\"");

Upvotes: 1

Views: 588

Answers (2)

hank
hank

Reputation: 3768

You can not define column namnes with placeholders, also have a look at the difference between bindParam and bindValue

In general, parameters are legal only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements.

Upvotes: 0

Pekka
Pekka

Reputation: 449803

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

You'll have to check them yourself (ideally, against a whitelist of existing and allowed table and column names) and put them into the query string yourself.

Here are some code snippets showing how to do this.

Upvotes: 1

Related Questions