PHP PDO:如何处理bindValue()和保留关键字? [英] PHP PDO: How to deal with bindValue() and reserved keywords?

问题描述

在我的数据库中，我有诸如"status" 之类的字段，它们是保留关键字.这段代码对我来说很好(状态被``转义了):

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'";

但是现在我只想使用准备好的语句！我的Database.class:

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, ", ");
    }
}

无法使用的更新示例:

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

我认为，这是因为reserverd关键字"status".但是我不知道如何逃避它.我试图将_prepare_update_string($ data)中的占位符转义为:

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)

但没有结果.

我希望解决方案非常简单，这只是我脑海中的卡住现象. ;-)在此先感谢大家！

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

推荐答案

在构造SQL字符串(我认为是prepare_update_string)时，以及在两个绑定数据的foreach循环中，都要运行一个递增计数并将其附加到绑定值.因此，:status"成为:status1".

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".

类似的东西:

$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.

例如而不是以下内容，这是由于:status占位符上的两次绑定而引发错误

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

享受.

这篇关于PHP PDO:如何处理bindValue()和保留关键字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！