CodeIgniter like query with single quote in search string

Question

I have been working on this for like more than 4 hours. I have select query using active record I am doing: $this->db->like ('items.name',$search); everything works fine but whenever there is single quote (') in the $search string it gives this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's%' OR default_items.short LIKE 'faith\'s%' LIMIT 5' at line 5

I have just checked now that it is adding double back slashes \\ instead of single in my active record for LIKE query. I tried in MySQL bt removing one slash and it is working.

My code:

$q = "faith's";
$query = $this->db->select('items_categories.slug as category_slug, items_categories.name as cat_name, items.name, items.price_value, items.cover_photo, items.slug');
$query->select('default_items.short as short',false);
$query->select('date(default_items.date_created) as date_created',false);
$query->join('items_categories','items_categories.id=items.root_id','inner');
$query->join('users','items.company_id=users.id','inner');
$query->like('items.name',$q);
$query->or_like('items.short',$q);
$query->limit(5);
$result =  $query->get($this->_table);
$both_prod_results = $result->result();

I am using pyrocms 2.x.

This is a possible magic quotes issue (i.e. the quote is being escaped twice, so not at all). They should be turned off in CodeIgniter/PyroCMS, but you never know... http://www.php.net/manual/en/security.magicquotes.whynot.php . If not, show more code. — Nick Pyett, Sep 17 '13 at 22:36
same question here (unsolved) : http://stackoverflow.com/questions/18417441/getting-mysql-syntax-error-using-codeigniter-like-active-record — Alireza, Sep 17 '13 at 22:49
Yes all magic quotes are off. i even tried making a simple function with simple query whenever i use Like function it is adding double backslashes than single. PyroCMS default search is using Match Against which does not look feasible to my system. — Umair Mumtaz, Sep 18 '13 at 07:51
i dont know why it is not working when you are trying. But take a look at [this](http://stackoverflow.com/questions/18883976/active-record-query-failed-escape-quote-from-query) — Muhammad Raheel, Sep 26 '13 at 11:58

score 0 · Answer 1 · answered Sep 17 '13 at 19:53

0

You can try the following code maybe it can help you, but you have to add \ before every' in your requests :

$value = "faith\'s";
$sql_request = "`short`  LIKE '%". $value ."%'";

$query = $this->db
              ->select('*')
              ->where($sql_request, null, false)
              ->get('default_items');

$result = $query->result();

dump($result);

answered Sep 17 '13 at 19:53

Khalil TABBAL

817
6
12

i have done this and WHERE too add slashes itself. so didn't work – Umair Mumtaz Sep 18 '13 at 08:01

score 0 · Answer 2 · edited Sep 19 '13 at 07:42

i think i need to answer my own question. Well this is a hack(don't think if it is secure) I have patched my MYSQLI Driver:

i have replaced this:

return str_replace(array($this->_like_escape_chr, '%', '_'),
                    array($this->_like_escape_chr.$this->_like_escape_chr, $this->_like_escape_chr.'%', $this->_like_escape_chr.'_'),
                    $str);

with this:

return str_replace(array($this->_like_escape_chr, '%', '_'),
                    array($this->_like_escape_chr, $this->_like_escape_chr.'%', $this->_like_escape_chr.'_'),
                    $str);

it was adding extra slash. and also don't think it will allow sql injections etc anything.

if anyone knows this is right then please comment.

Thanks Umair

see [this](http://stackoverflow.com/questions/18883976/active-record-query-failed-escape-quote-from-query/18888350#18888350) answer this might help — Muhammad Raheel, Sep 19 '13 at 09:15

CodeIgniter like query with single quote in search string

2 Answers2