How do we know PDO is escaping SQL Injections?

Question

I am newbie with PDO libraries. I am working on development environment with mysql as my database. I am able to run through my queries using prepare and execute function while using "?" placeholder and also bindParam method while using named placeholders (ex: ":column").

After this I tried to see if PDO does any kind of escaping by putting in any quotes to sanitize the query like mysql_real_escape_string does. I am trying to see what would the query look but all I get is the statement that has been passed into the prepare statement, but not the query that would be executed.

I tried to var_dump the $result->execute(), and $result->fetch() but the execute statement gives me my prepare statement's sql with place holders while fetch statement gives me the result of that query.

Is there a way to look at the find query that would be run, or atleast how the parameters would look before running the query??

I hope I am clear with my question. :|

You worry too much. Prepared statements escape your input at 100%. If you need to see what the final query looks like, you have to setup a database query log. — netcoder
@netcoder Prepared statements do not escape anything (unless in compatible mode). And there is nothing new in the query log — Your Common Sense
@Col. Shrapnel: Well technically yes it does, although you can word it the way you want. As for "there is nothing new in the query log", I simply don't understand what that means... — netcoder
@netcoder Nope, it doesn't. Technically or literally or whatever. There is a link down below on how prepared statements works, you'd find it very enlightening. Please do not comment until you learn it carefully and tried to run the code from it — Your Common Sense
@Col. Shrapnel: This is a PDO question, and yes prepared statements in PDO escapes the input. Please do not comment until you learn some diplomacy. — netcoder

Crozin Crozin · Accepted Answer · 2011-01-07T16:34:31

When you write something like:

$stmt = $pdo->prepare('SELECT * FROM tbl_name WHERE col_name = :col_name;');
$stmt->bindValue('col_name', 'some \' value');
$stmt->execute();

The actual query is... SELECT * FROM tbl_name WHERE col_name = :col_name;. That's called prepared statement. Firstly, you send query to the database, later you send query parameters. PDO doesn't merge query and parameters.

You've probably thought that PDOStatement::bindValue() does something like:

public function bindValue($placeholer, $value, $valueType = PDO::PARAM_STR) {
    $this->query = str_replace($placeholder, $this->quote($value, $valueType), $this->query);
}

But it doesn't.

It does something more like that:

public function execute() {
    try {
        $this->sendQueryToDatabase($this->query);

        // Query is valid
        $this->sendParametersToDatabase($this->parameters);

        return $this->fetchResultSet();
    } catch (... $e) {
        // Query is invalid (eg. syntax error)
        throw ...;
    }
}

How do we know PDO is escaping SQL Injections?

4 Answers