EllisLab text mark
Advanced Search
     
order by incorrect escaping
Posted: 23 September 2009 09:54 PM
Avatar
Joined: 2008-12-04
44 posts

My MySQL order by clause contains

$orderby = ‘concat(firstname,lastname) asc’

because I want to sort by the combined first and last names (which works in MySQL) but, when I try to add it with CI active record via $this->db->order_by($orderby) it doesn’t work because CI tries to escape it to ORDER BY CONCAT(firstname, `lastname)` asc which of course throws a MySQL error. How can I add it and not have CI try to escape it? Is there a secret “don’t escape” parameter?

I suppose a change as such needs to be done in the core files.

 
Posted: 25 September 2009 10:09 AM   [ # 1 ]   [ Rating: 0 ]
Avatar
Joined: 2008-12-04
44 posts

What I did in my core file as per my other post.

Add this to the core file DB_active_rec.php around line 885. Then I just add “true” to the function to “not” try to escape the input. Perhaps a final parameter on every Active Record function that trys to auto escape data would fix a lot of SQL escaping problems????

$this->db->order_by($orderby,true);


  /**
  * Sets the ORDER BY value
  *
  * @access   public
  * @param   string
  * @param   string   direction: asc or desc
  * @return   object
  */
  function order_by($orderby, $direction = ‘’)
  {
if ($direction === true) {
// doesn’t handle CI “caching”
$this->ar_orderby[] = $orderby;
return $this;
}

 
Posted: 11 September 2013 02:58 PM   [ # 2 ]   [ Rating: 0 ]
Avatar
Joined: 2008-11-25
63 posts

Just leaving this here for future reference:

// set this to false so that _protect_identifiers skips escaping:
$this->db->_protect_identifiers = FALSE;

// your order_by line:
$this -> db -> order_by(‘FIELD ( products.country_id, 2, 0, 1 )’);

// important to set this back to TRUE or ALL of your queries from now on will be non-escaped:
$this->db->_protect_identifiers = TRUE;