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

Answer 1

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 ...;
    }
}

Read more about Prepared Statements

Answer 2

To put it straight.

PDO has 2 modes of running prepared statements:

1. Native mode. Query and data being sent to the database se-pa-ra-te-ly. Which means that data never being added to the query. So, no harm could be done. Ever. The query being sent to the database as is, with ? marks (but no named placeholders which being replaced by PDO with ?s)
2. Compatibility mode. PDO do make an old-style query, by substituting placeholders with binded variables depends on variable name. Strings being quoted/escaped, the rest being cast to it's type.

Both methods are perfectly safe.

The real danger begins when you have a variable identifier...