Issues binding variable types using executeQuery in Doctrine DBAL

Question

I'm looking to use the Doctrine DBAL function executeQuery as follows:

$conn = DBAL\DriverManager::getConnection($connectionParams, $config);

$sql = "SELECT count(*) FROM clients WHERE client_id = :id";

$results = $conn->executeQuery($sql, ['id' => 'foo'], ['id' => \PDO::PARAM_STR]);

var_dump($results->fetchAll());
var_dump($results->rowCount());

Which works fine returning:

array (size=1)
  0 => 
    array (size=1)
      'count(*)' => string '1' (length=1)
int 1

However the code also works using the following lines (where the types parameters is declared incorrectly or not declared at all):

$results = $conn->executeQuery($sql, ['id' => 'foo'], ['id' => \PDO::PARAM_INT]);

$results = $conn->executeQuery($sql, ['id' => 'foo'], ['notatag' => \PDO::PARAM_STR]);

$results = $conn->executeQuery($sql, ['id' => 'foo']);

Suggesting the declaring the bound variable data type isn't being used, raising concerns as to if this is protected against SQL injection.

Am I doing something wrong here? How can I be sure my code is secure?

score 2 · Accepted Answer · answered Mar 25 '15 at 21:13

Having reread the DBAL documentation I came across this gem:

If you don’t specify an integer (through a PDO::PARAM* constant) to any of the parameter binding methods but a string, Doctrine DBAL will ask the type abstraction layer to convert the passed value from its PHP to a database representation.

So by not defining the $types parameter you leave it to Doctrine to explicitly convert the type.

But how secure this? Doctrine has this to say when describing the "right" way to include "user input in your queries":

Besides binding parameters you can also pass the type of the variable. This allows Doctrine or the underlying vendor to not only escape but also cast the value to the correct type.

Suggesting from a security standpoint the $types parameter is optional.

Issues binding variable types using executeQuery in Doctrine DBAL

1 Answers1