Expression created by DBAL query builder for IN clause doesn't set in values as strings?

Question

I have this simple example:

$names = ['a', 'b'];
$query = $dbConnection->createQueryBuilder();
$query->select('*')
      ->from('foo')
      ->where($query->expr()->in('name', $names));

print $query->getSQL();

outputs

SELECT * FROM foo WHERE name IN (a, b)

instead of expected

SELECT * FROM foo WHERE name IN ('a', 'b')

How can I fix it?

Unthrone

Answer 1

Though I am fairly new to the query builder I did think you would be better off using prepared statements etc. unless you know that user input will never be used in this query. After further reading I found this:

http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion

So using the information from there you can change the query you have to:

$query->select('*')
      ->from('foo')
      ->where($query->expr()->in('name', ':names'))
      ->setParameter('names', $names, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY);

I tested it myself and it works as intended, with the added bonus now of being safe from SQL injection attacks. If you are wanting to use different or a mix of variable types the documentation page linked above shows you how you can do it.

Hurter