pdo 是否转义语句? [英] Does pdo escape statements or not?

问题描述

我有这个 PDO 包装器

I have this PDO wrapper

private function cleanup($bind) {
    if(!is_array($bind)) {
        if(!empty($bind))
            $bind = array($bind);
        else
            $bind = array();
    }
    return $bind;
}

public function run($sql, $bind="") {
    $this->sql = trim($sql);
    $this->bind = $this->cleanup($bind);
    $this->error = "";
    array_push($this->qs, $sql);

    try {
        $pdostmt = $this->prepare($this->sql);
        if($pdostmt->execute($this->bind) !== false) {
            if(preg_match("/^(" . implode("|", array("select", "describe", "pragma")) . ") /i", $this->sql))
                return $pdostmt->fetchAll(PDO::FETCH_ASSOC);
            elseif(preg_match("/^(" . implode("|", array("delete", "insert", "update")) . ") /i", $this->sql))
                return $pdostmt->rowCount();
        }
    } catch (PDOException $e) {
        $this->error = $e->getMessage();
        $this->debug();
        return false;
    }
}

自从几年前我开始使用它以来，我没有遇到任何问题，现在我收到一条错误消息，因为一个字符串没有被转义.也许我从来没有处理过这样的场景.

I had no problems with this since I started using it a couple of years back and now I'm getting an error message because a string is not escaped. Maybe I never worked with a scenario like this.

这是导致问题的 SQL 语句

Here's the SQL statement that is causing the problem

$db->run("SELECT region_id FROM region WHERE name = '$name'");

其中 $name 是霍克斯湾.我的印象是 PDO 转义字符串，似乎我错了.有什么想法可以解决这个问题吗?

where $name is Hawke's Bay. I was under the impression that PDO escapes strings, seems like I was wrong. Any ideas how I can fix this issue?

推荐答案

有 2 个错误假设导致您提出这个问题

There are 2 false assumptions that led you to this question

1. 转义是使您的查询正确的一件事.
2. PDO 以任何神奇"的方式逃避"，知道要逃避什么.

不幸的是，这两个假设都是错误的.

Unfortunately, both assumptions are wrong.

事实上，转义需要仅用于 SQL 字符串.它与 PDO、准备好的声明、安全等无关.一旦您要将字符串文字放入查询中 - 它必须转义特殊字符.
但一旦你不是 - 没有逃避会很好.

As a matter of fact, escaping required for the SQL strings only. It has nothing to do with PDO, prepared statements, safety and such. Once you are going to put a string literal into query - it must have special characters escaped.
But once you aren't - no escaping would be good.

关于 PDO，您不希望它转义"而是处理查询中的占位符.这就是整个事情的运作方式.使用占位符，您告诉 PDO 正确格式化相应的值.虽然这种格式不仅涉及转义，还涉及更多不同的措施.

Regarding PDO, you want it not to "escape" but to process placeholders in your query. This is how the whole thing works. Using placeholders you are telling PDO to format corresponding values properly. While such a formatting involves not just escaping but many more different measures.

所以，它必须是这样的

$db->run("SELECT region_id FROM region WHERE name = :name", array(':name' => $name));

这样 PDO 会将 $name 视为字符串并相应地对其进行格式化.

this way PDO will treat $name as a string and format it accordingly.

虽然我不确定清理"功能是否正常工作以及为什么要使用它.

Though I am not sure about "cleanup" function if it works properly and why it is used at all.

这篇关于pdo 是否转义语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！