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
– Catfish, Commented Nov 14, 2012 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
– Ahmed Gaber, Commented Nov 14, 2012 at 19:27

Rooster · Accepted Answer · 2012-11-14 20:08:33Z

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, 2012 at 20:08

answered Nov 14, 2012 at 19:29

Rooster

10.1k8 gold badges49 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ahmed Gaber Over a year ago

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

Rooster Over a year ago

hmmm. Could you try running the query through sql except using &#amp; and & in place of &. Se if either of those work.

Community · Accepted Answer · 2017-05-23 12:19:17Z

0

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?

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Nov 14, 2012 at 19:33

Jarrett Barnett

8135 silver badges12 bronze badges

2 Comments

Ahmed Gaber Over a year ago

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

Jarrett Barnett Over a year ago

Try adding a forward slash before the ampersand "&" so that it reads: category = 'Health \& recreation'

Collectives™ on Stack Overflow

Codeigniter query with symbols in condition string

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related