PHP PDO: How to deal with bindValue() and reserved keywords?

Question

In my database I've fields like "status", which are reserved keywords. This code works fine for me (status is escaped by ``):

$sql = "UPDATE $table SET `status`='$status' WHERE `id`='123'";

But now I want to use prepared statements only! My Database.class:

class Database extends \PDO {
    private $_sth; // statement
    private $_sql;

    public function update($tbl, $data, $where, $where_params = array()) {
        // prepare update string and query
        $update_str = $this->_prepare_update_string($data);
        $this->_sql = "UPDATE $tbl SET $update_str WHERE $where";
        $this->_sth = $this->prepare($this->_sql);

        // bind values to update
        foreach ($data as $k => $v) {
            $this->_sth->bindValue(":{$k}", $v);
        }

        // bind values for the where-clause
        foreach ($where_params as $k => $v) {
            $this->_sth->bindValue(":{$k}", $v);
        }

        return $this->_sth->execute();
    }

    private function _prepare_update_string($data) {
        $fields = "";
        foreach ($data as $k => $v) {
            $fields .= "`$k`=:{$k}, ";
        }
        return rtrim($fields, ", ");
    }
}

Update example that won't work:

$DB = new Database();
$DB->update("tablename",
        array("status" => "active"),
        "`username`=:username AND `status`=:status",
        array("username" => "foofoo", "status" => "waiting"));

I think, its because of the reserverd keyword "status". But I don't know how to escape it. I tried to escape the placeholder in _prepare_update_string($data) to:

bindValue("`:{$k}`", $v)

but no result.

I hope the solution is very simple and it's just a stuck overflow in my brain. ;-) Thanks in advance people!

score 3 · Accepted Answer · edited Nov 02 '12 at 00:37

3

When you construct the SQL string (prepare_update_string i think), as well as in both the foreach loops where you bind data, run an incrementing count and append it to the bind value. So ":status" become ":status1".

Something like:

$i = 1;
foreach ($data as $k => $v) {
    $this->_sth->bindValue(":{$k.$i}", $v);
    $i++;
}

This will solve the problem of any reserved keywords.

It also solves the problem (which I'm sure you'll encounter in the future) where you need to bind to the same placeholder more than once.

e.g. instead of the following, which throws an error due to two binds on the :status placeholder

SELECT * from table WHERE `status` = :status AND `otherfield` = :status

With an incrementing count, this becomes:

SELECT * from table WHERE `status` = :status1 AND `otherfield` = :status2

Enjoy.

edited Nov 02 '12 at 00:37

Robbie

17,605
4
35
72

answered Nov 02 '12 at 00:19

Jonathan Spiller

1,885
16
25

1

This fixes the problem. The OP was using the `:status` placeholder in multiple places – Phil Nov 02 '12 at 00:26
1

+1 as nice solution, but I edited as your last two examples had fields bound, not values. As Alex says above, you can't bind field names (or table names) as they are used for pre-omtimising the query. – Robbie Nov 02 '12 at 00:39
@Jonathan: Thanks for this simple solution. I just added the iterator and it works fine for me. Shame on me! :) – Mr. B. Nov 02 '12 at 00:47

Roberto Messa · Answer 2 · 2021-05-25T00:15:06.063

I had a similar problem and solved by passing the parameter by reference

I have a varchar(3) field and a pointer was been passed instead of 'aaa' value

This works ($val by reference):

<?php
foreach ($params as $key => &$val) {
    $sth->bindParam($key, $val);
}
?>

This will fail ($val by value, because bindParam needs &$variable):

<?php
foreach ($params as $key => $val) {
    $sth->bindParam($key, $val);
}
?>

reference: Vili's comment at

https://www.php.net/manual/pt_BR/pdostatement.bindparam.php

PHP PDO: How to deal with bindValue() and reserved keywords?

2 Answers2

Linked