Custom CakePHP order by clause for jQuery autocomplete

Question

I have a database of locations the user can select from by typing and autocompletion. In my CakePHP controller, I do this:

    $locations = $this->Location->find('all', array(
        'conditions' => array('Location.name like' => '%'.$term.'%'),
        'fields' => array('Location.id', 'Location.name', 'Region.name'),
        'order' => array(
            array('Location.name = "'.mysql_real_escape_string($term).'"'
                => 'desc'),
            'Location.name'
        ),
        'limit' => 10,
        'recursive' => 1,
    ));

It works perfectly fine, but it feels like a hack, and I'd rather not escape SQL literals myself.

The first order by clause is needed since a perfect match might otherwise not make it to the top of the alphabetically sorted list.

I considered moving the equality-test into a virtual field, but I don't feel it's a very elegant solution when the $term is dynamic.

How do I implement this in a better way?

Answer 1

As far as the structure of your query is written this is fine.

In regards to having to escape SQL yourself, this is from the Cookbook:

CakePHP already protects you against SQL Injection if you use CakePHP's ORM methods (such as find() and save()) and proper array notation (ie. array('field' => $value)) instead of raw SQL. For sanitization against XSS its generally better to save raw HTML in database without modification and sanitize at the time of output/display.