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

Question

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

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

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.

edited May 23 '17 at 12:05

Community

1
1

answered Jan 28 '13 at 10:10

Pekka

442,112
142
972
1,088

Thank you so much, I probably should have known this already but the error made me think it was to do with quotation marks somehow. – emik Jan 28 '13 at 10:29
Should this be working (with one bound parameter)? $count_query = $dbh->prepare("SELECT COUNT({$field}) FROM {$table} WHERE {$where_something}=:equals"); because its result is empty – emik Jan 28 '13 at 11:10
@emik I guess that depends on what `$field`, `$table` etc. contain? Have you tried outputting it? – Pekka Jan 28 '13 at 11:12
Okay now it's working but I don't appear to have changed anything, probably something to do with the files being sent to the host server. Bizarre. Anyway, thanks again for your help, and sorry to bother you again. – emik Jan 28 '13 at 11:23

score 0 · Answer 2 · answered Jan 28 '13 at 10:11

0

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.

answered Jan 28 '13 at 10:11

hank

3,748
1
24
37

Thanks very much! Was scratching my head over this for far too long. – emik Jan 28 '13 at 10:27

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

2 Answers2