views:

4003

answers:

1

I've got a Doctrine_RawSql query using prepared statements. However, they seem to get ignored when the SQL query is generated. But If I leave out the token values, I get an exception about number of bound variables not matching (so it's at least trying to sub them in).

If I include these values inline, is Doctrine doing anything behind the scenes to prevent SQL injection?

Here's my code:

public function sortedPhotogsByLocation($location)
{
    $q = new Doctrine_RawSql();
    $result = $q->select('{p.*}')
            ->from('photographers p')
            ->addComponent('p', 'Photographer')
            ->where('p.city_id = ?', $location->id)
            ->orderBy('CASE WHEN p.lname < "?%" THEN 1 ELSE 0 END, p.lname ASC', $location->photographer_sort)
            ->execute();
    return $result;
}

This provides the following SQL output:

  SELECT *  
  FROM photographers p 
  WHERE p.city_id = ? 
  ORDER BY 
    CASE WHEN p.lname < "?%" THEN 1 ELSE 0 END, p.lname 
  ASC

EDIT: The properties on $location are being set properly. If I hardcode the parameters:

->where('p.city_id = ?', 5)

I encounter the same problem with the tokens not being replaced.

+3  A: 

I'm not entirely familiar with Doctrine_RawSql, but a placeholder should be by itself, not "?%", just ? and add the % on the variable you are passing. Take a look at example #6.

Charlie
Thanks for the advice. I corrected the placeholder string. However, the original problem still remains.
Bryan M.
Turns out you were correct. Fixing the placeholder string solved the problem. My original question was misguided. Doctrine was generating the SQL correctly. However, the token replacement was happening at the at PDO level, so Doctrine wasn't going to spit out the final SQL string.
Bryan M.