phpmysqlcodeigniterwhere-clausesql-function

CodeIgniter where() parameter containing SQL function is being incorrectly quoted


I have here my sample query using codeigniter. I'm using this to get data satisfying my where condition.

MODEL:

function quarterly1_sales($year_date, $q1_start)
{
    $where = array(
     'status'   => 'purchased',
     'payment.paymentdate >=' => $q1_start,
     'payment.paymentdate <=' => 'DATE_ADD('.$q1_start.',INTERVAL 1 QUARTER)'
    );
    return $this->db
    ->select('COUNT(payment.keyid) AS rec_count')
    ->select('product_key.client_name, product_key.contact_email, product_key.status, product_key.id, payment.paymentdate, (payment.id) as pid,payment.subscription_type')
    ->from('product_key')
    ->where($where)
    ->join('payment', 'payment.keyid=product_key.id', 'left outer')
    ->group_by('product_key.id')
    ->get()
    ->result(); 
}

My problem is, there shouldn't be any result since my data starts with 2012 and 2013 yet it outputs everything.


Solution

  • Codeigniter's active record class automagically escapes everything passed to the where() function. Because of this your call to the MySQL function DATE_ADD() is being passed in as a string and treated literally by MySQL instead of being evaluated. To fix this, move that where clause to a separate function call and set the third parameter to FALSE, turning off the escaping.

    $where = array(
         'status'   => 'purchased',
         'payment.paymentdate >=' => $q1_start);
        return $this->db
        ->select('COUNT(payment.keyid) AS rec_count')
        ->select('product_key.client_name, product_key.contact_email, product_key.status, product_key.id, payment.paymentdate, (payment.id) as pid,payment.subscription_type')
        ->from('product_key')
        ->where($where)
        ->where('payment.paymentdate <=', 'DATE_ADD('.$this->db->escape($q1_start).',INTERVAL 1 QUARTER)', FALSE)
        ->join('payment', 'payment.keyid=product_key.id', 'left outer')
        ->group_by('product_key.id')
        ->get()
        ->result();