Does pdo escape statements or not?

Question

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.

Here's the SQL statement that is causing the problem

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

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?

If you are not using PDO::prepare, you need to manually escape strings using PDO::quote. — DevlshOne, Aug 03 '13 at 12:26
@DevlshOne `$pdostmt = $this->prepare($this->sql);` there's the prepare — slash197, Aug 03 '13 at 12:29
@slash197 Have you read the code at least? There is nothing to prepare in the statement — Your Common Sense, Aug 03 '13 at 12:31
@YourCommonSense ok, then what does that 'prepare' do then? Or what did I do wrong? — slash197, Aug 03 '13 at 12:32
I did look at your code and it looks like you're `bind`ing before you `prepare`. I thought I read that this somehow messes with the `prepare` method? Maybe I'm mistaken. That's why this is a comment and not an answer. — DevlshOne, Aug 03 '13 at 12:33
Yes, exactly. the thing that makes your query a prepared statement — Your Common Sense, Aug 03 '13 at 12:34
@YourCommonSense So `$db->run("SELECT region_id FROM region WHERE name = ':name'", array(':name' => $name));` would make this query prepared? — slash197, Aug 03 '13 at 12:35
Duplicate of http://stackoverflow.com/questions/17354412/prepared-statements-escape-variables — Your Common Sense, Aug 03 '13 at 12:36
Nope. this one wouldn't. You need to use correct syntax explained in the manual — Your Common Sense, Aug 03 '13 at 12:36

score 3 · Accepted Answer · answered Aug 03 '13 at 12:49

There are 2 false assumptions that led you to this question

Escaping is a thing that makes your query correct one.
PDO does this "escaping" whatever "magic" way, knowing somehow what to escape.

Unfortunately, both assumptions are wrong.

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.

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.

So, it have to be something like this

$db->run("SELECT region_id FROM region WHERE name = :name", array(':name' => $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.

score 0 · Answer 2 · answered Aug 03 '13 at 12:54

0

By default not PDO doesn't quote anything if you use plain query instead of prepared statements. But you can use PDO::quote() method for escaping individual values. See more http://www.php.net/manual/en/pdo.quote.php

answered Aug 03 '13 at 12:54

Andrej

7,474
1
19
21

You shouldn't use this function but prepared statements. – Your Common Sense Aug 03 '13 at 12:55
This is not an arguement for me. – Andrej Aug 03 '13 at 16:05

Does pdo escape statements or not?

2 Answers2

Linked