Codeigniter query with symbols in condition string

Question

I am trying to have filters dropdown in my CMS my model looks like

public function load($sort,$order,$key,$value)
{ //        $key='listening'; //        $value="1";
            //configure pagination
    $config=array(
      'base_url'=>base_url().'/index.php/companies/index',
      'total_rows'=>$this->db->get('company')->num_rows(),
      'per_page'=>$this->settings_model->get_per_page(),
      'num_links'=>20
    );

    $this->pagination->initialize($config);



    $this->db->select('company.id, 
                       company.name, 
                       company.logo, 
                       company.status_id, 
                       company.listening',FALSE);
    $this->db->select('company_category.name as category,
                       company_category.id as category_id',FALSE);

    $this->db->select('complain_status.cs_status as status',false);
    $this->db->from('company');
    $this->db->join('company_category','company_category.id = company.category_id');
    $this->db->join('complain_stastus', 'complain_status.cs_id = company.status_id');



    if(isset($_POST['key']))
    {
       $value=  str_replace('&nbsp', ' ', $_POST['value']);
        var_dump($value);
        if($value!='0')
            $this->db->having ($_POST['key'], mysql_real_escape_string($value) );

    }
    if($sort!='' || $sort!=NULL)
        $this->db->order_by ($sort, $order);

    $this->db->limit($config['per_page'], $this->uri->segment(3));

    $result=$this->db->get();
    if(!isset($_POST['key']))
        $this->filter->set_filters_list($result->result_array());

    return $result->result();
}

that generates the below query

SELECT company.id, company.name, company.logo, company.status_id, company.listening, company_category.name as category, company_category.id as category_id, complain_status.cs_status as status
FROM (`company`)
JOIN `company_category` ON `company_category`.`id` = `company`.`category_id`
JOIN `complain_status`  ON `complain_status`.`cs_id` = `company`.`status_id`
HAVING `category` =  'Health & recreation'
LIMIT 20

as you can see here is the problem when category equals some string with special character like Health & recreation it fails and even if i tried the query generated by CI it works normally on MYSQL and gets me the result

Note : I m replacing the space $value= str_replace('&nbsp', ' ', $_POST['value']); as this data comes from select html element that fails when it has spaces in options so i had to parse and remove it later in the back-end code

Thanks in advance

How do you know that's the query that's getting generated? Did you use `echo $this->db->last_query()` to make sure that's the query that's getting generated? — Catfish, Nov 14 '12 at 19:21
No actually i just misspell the anything in the query like wrong table name and i get it from the error generated on ajax response.. — Ahmed Gaber, Nov 14 '12 at 19:27

Rooster · Accepted Answer · 2012-11-14T20:08:33.157

2

Code igniter is probably html_encoding the ampersand so that it reads as its html value. YOu can comfirm this by turning on the profiler by adding this line to the constructor of whatever controller or model your runnning the query in:

$this->output->enable_profiler(TRUE);

if I'm right your query will have substituted something like & where the & should be.

Note the profiler reveals the & while using a $this->db->last_query() still shows a &

edited Nov 14 '12 at 20:08

answered Nov 14 '12 at 19:29

Rooster

9,954
8
44
71

i used the profiler and i got HAVING `category` = 'Health & recreation', it's really strange the same query is running on mysql and working ok @jJohn B – Ahmed Gaber Nov 14 '12 at 20:43
hmmm. Could you try running the query through sql except using amp; and & in place of &. Se if either of those work. – Rooster Nov 14 '12 at 21:14

score 0 · Answer 2 · edited May 23 '17 at 12:19

To insert symbols into the database, you need to escape the values first. In PHP, you would normally use: mysql_real_escape_string()

How to insert special characters into a database?

However, as you're doing this in CodeIgniter, you have to use query binding for the data to automatically be escaped,

$category = $this->input->post('category');
$status = $category = $this->input->post('status');
$status_id = $category = $this->input->post('status_id');

$sql = "SELECT * FROM company WHERE category = ? AND status = ?"; 

$this->db->query($sql, array($category, $status));

http://ellislab.com/codeigniter/user_guide/database/queries.html (under Query Binding)

Does CodeIgniter automatically prevent SQL injection?

Although its not part of the original question, your code has flaws in that your using $_POST['value'] without any sort of filtering. There would be nothing stopping someone from SQL injecting your form.

How can I prevent SQL injection in PHP?

Thanks Jarret for your response, but my problem with selecting not inserting values and i have used escape character string function but same behavior this is just filter in the CMS that add one more condition in the query — Ahmed Gaber, Nov 14 '12 at 19:36
Try adding a forward slash before the ampersand "&" so that it reads: `category` = 'Health \& recreation' — Jarrett Barnett, Nov 14 '12 at 19:38

Codeigniter query with symbols in condition string

2 Answers2