Doctrine2: DQL with parameters

Question

I'd like to have sorting type as a parameter. So I wrote function

public function findInterval($pageNumber, $limit, $sortType) {
    $query = $this->_em->createQuery('Select c from Entities\Comment c where c.isremoved=0 ORDER BY c.creationdate ?1');
    $query->setParameter(1, $sortType);  //sortType is either ASC or DESC

    return $users = $query->getResult();
}

But it doesn't work with fatal error Uncaught exception 'Doctrine\ORM\Query\QueryException' with message '[Syntax Error] line 0, col 77: Error: Expected end of string, got '?'' in C:\Users\user\Desktop\projects\interview\application\libraries\Doctrine\ORM\Query\QueryException.php:42 Stack trace: #0 C:\Users\user\Desktop\projects\interview\application\libraries\Doctrine\ORM\Query\Parser.php(380): Doctrine\ORM\Query\QueryException::syntaxError('line 0, col 77:...') #1 C:\Users\user\Desktop\projects\interview\application\libraries\Doctrine\ORM\Query\Parser.php(745): Doctrine\ORM\Query\Parser->syntaxError('end of string') #2 C:\Users\user\Desktop\projects\interview\application\libraries\Doctrine\ORM\Query\Parser.php(213): Doctrine\ORM\Query\Parser->QueryLanguage() #3 C:\Users\user\Desktop\projects\interview\application\libraries\Doctrine\ORM\Query\Parser.php(288): Doctrine\ORM\Query\Parser->getAST() #4 C:\Users\user\Desktop\projects\interview\application\libraries\Doctrine\ORM\Query.php(230): Doctrine\ORM\Query\Parser->parse() #5 C:\Users\user\Deskt in C:\Users\user\Desktop\projects\interview\application\libraries\Doctrine\ORM\Query\QueryException.php on line 42

Is there any other way how to set sort type by parameter?

Answer 1

You only can bind parameters (used in where) in prepared statements. There is no need to anyway to use this in orderBy because there is no possibility for SQL injection on that part.

Just concat using plain PHP:

$sortType = ($sortType == 1) ? 'ASC' : 'DESC';
$query = $this->_em->createQuery('Select c from Entities\Comment c where c.isremoved=0 ORDER BY c.creationdate ' . $sortType);

Answer 2

Firstly, your putting a value directly into your DQL (c.isremoved = 0), which, as rightly pointed out by Bram shouldn't occur. You should only "bind" parameters to your query, these will be correctly escaped and mitigate any potential SQL injection attack.

Secondly, the $sortType parameter your using should contain either ASC or DESC. Not sure what value you're expecting to pass to this function. But as Bram demostrated, this should be tested to ensure you only use one of the two values.

public function findInterval($pageNumber, $limit, $sortType) 
{
    $sortType = ($sortType == 'ASC') ? $sortType : 'DESC';    // <-- this example defaults to descending
    $query = $this->_em->createQuery('SELECT c FROM Entities\Comment c WHERE c.isremoved = :isremoved ORDER BY c.creationdate ' . $sortType);
    $query->setParameter('isremoved', 0);

    return $users = $query->getResult();
}