ManyToManyPersister::loadCriteria() incorrectly load Criteria with "IN" clause, and crash with SQL Syntax error

[Fedik]

Bug Report

Q	A
BC Break	no
Version	2.6.x

Summary

I got an error while try to use ->matching($criteria); within Many2Many association, when criteria contains IN clause.

Notice: Array to string conversion in <path>/dbal/lib/Doctrine/DBAL/Connection.php on line 905

And crash with:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Array'' at line 1

I suspect it happen because ManyToManyPersister::loadCriteria() does not extract a parameters types.

How to reproduce

I have made a little TestCase:

<?php

use Doctrine\Common\Collections\Criteria;
use Doctrine\Tests\Models\CMS\CmsTag;
use Doctrine\Tests\Models\CMS\CmsUser;
use Doctrine\Tests\OrmFunctionalTestCase;

class ManyToManyCriteriaWithInTestCase extends OrmFunctionalTestCase
{
    protected function setUp()
    {
        $this->useModelSet('cms');
        parent::setUp();
    }

    public function testCriteriaWithIn()
    {
        $user = new CmsUser;
        $user->name = 'Foo';
        $user->username = 'foo';

        $tag1 = new CmsTag;
        $tag2 = new CmsTag;
        $tag3 = new CmsTag;

        $tag1->name = 'Tomato';
        $tag2->name = 'Lemon';
        $tag3->name = 'Apple';

        $user->addTag($tag1);
        $user->addTag($tag2);
        $user->addTag($tag3);

        $this->_em->persist($user);
        $this->_em->flush();
        $this->_em->clear();

        $userInDB = $this->_em->find(get_class($user), $user->id);

        $criteria = Criteria::create()
            ->where(Criteria::expr()->in('name', ['Lemon', 'Apple']))
            ->orderBy(['name' => Criteria::ASC])
        ;

        $tagsInDB = $userInDB->getTags()->matching($criteria);

        $this->assertEquals(2, count($tagsInDB));
        $this->assertEquals('Apple', $tagsInDB[0]->name);
        $this->assertEquals('Lemon', $tagsInDB[1]->name);
    }
}

Expected behavior

All should work without crash.

[sztyup]

Hi, check out https://github.com/doctrine/orm/pull/9010 it probably fixed this issue

[Fedik]

@sztyup I just checked on 2.10.1, the bug still persists

[qnixdev]

doctrine/orm 3.2.2, the bug still persists

If you specify it like this, everything works. Yes, this condition inserts the value directly into the sql query, but it works.

foreach ($parameters as $parameter) {
    [$name, $value, $operator] = $parameter;

    $field = $this->quoteStrategy->getColumnName($name, $targetClass, $this->platform);

    if ($value === null && ($operator === Comparison::EQ || $operator === Comparison::NEQ)) {
        $whereClauses[] = sprintf('te.%s %s NULL', $field, $operator === Comparison::EQ ? 'IS' : 'IS NOT');
    } elseif ($operator === Comparison::IN) {
        $whereClauses[] = sprintf('te.%s %s (%s)', $field, $operator, "'".implode("','", $value)."'");
    } else {
        $whereClauses[] = sprintf('te.%s %s ?', $field, $operator);
        $params[]       = $value;
        $paramTypes[]   = PersisterHelper::getTypeOfField($name, $targetClass, $this->em)[0];
    }
}