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?

score 6 · Answer 1 · answered Mar 09 '17 at 22:02

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.

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

1 Answers1