CodeIgniter's $this->db->like() method is automatically escaping percent symbols in the input string

Question

I've written a simple query for searching a keyword in the database.

$keyword = "keyword sample"; 
$keyword = str_replace(" ", "%", $keyword);   

$this->db->select('*')->from('table')
           ->like('column', "%".$keyword."%")->get();

Now the query generated by Codeigniter is like this:

SELECT * FROM (`table`) WHERE `column` LIKE '%keyword\%sample%'

Where is the trailing \ coming from in the query? This is making an erroneous search and not returning the data that is actually in the db. I've checked everything and nothing seems to be wrong with the code I've written.

Afrit

Answer 1

If you dig a bit into CodeIgniter's internals, you'll notice that the $this->db->like() function escapes special characters it contains - including, of course, %.

I don't think like() will help you much with your particular needs. Your best bet, I guess, would be to bypass the problem and use a where function containing your LIKE clause:

$this->db->select('*')->from('table')->where("column LIKE '%$keyword%'")->get()->result_array();

Ikon

...("column LIKE %$keyword%")... , don't forget to insert single quotation, so it should be ...("column LIKE '%$keyword%'")... – Vilberg

You're absolutely right, I fixed the query. My bad. – Ikon

Answer 2

You just try it like this:

$this->db->select('*')
$this->db->from('table');
$this->db->like('column', $keyword);
return $this->db->get()->result_array();

If you want to control where the wildcard (%) is placed, you can use an optional third argument. Your options are 'before', 'after' and 'both' (which is the default).

example:

$this->db->select('*')
$this->db->from('table');
$this->db->like('column', $keyword, 'before');
return $this->db->get()->result_array();

If you do not want to use the wildcard (%) you can pass to the optional third argument the option 'none'.

example:

$this->db->select('*')
$this->db->from('table');
$this->db->like('column', $keyword, 'none');
return $this->db->get()->result_array();

BUT, for your example you must need to search like "%keyword sample%", or like "%keyword%" OR "%simple%";

For example:

$this->db->like('column', 'keyword simple');
// Produces: WHERE column LIKE '%keyword simple%' 

OR

$this->db->like('column', 'keyword');
$this->db->or_like('column', 'simple');
// Produces: WHERE column LIKE '%keyword%' OR column LIKE '%simple%'  

More, you can read CodeIgniter User Guide

Cusick

Answer 3

Use the escape_like_str() method.

The escape_like_str() method should be used when strings are to be used in LIKE conditions so that LIKE wildcards %, _ in the string are also properly escaped. It cannot automatically add the ESCAPE ! condition for you, and so you’ll have to manually do that.

Hope it helps.

$keyword = "keyword sample"; 
$sql = "SELECT id FROM table WHERE column LIKE '%" .
$this->db->escape_like_str($keyword)."%' ESCAPE '!'";

Source:- https://www.codeigniter.com/userguide3/database/queries.html

Unconscionable

Escaping the input manually will not solve the asked question -- the don't want the passed in percent symbol to be escaped. – Vincenza

Answer 4

I don't know when it entered the framework, but I know that at least CodeIgniter3 has a 4th parameter to control whether escaping is implemented.

$keyword = 'keyword sample';
return $this->db
    ->like('my_column', str_replace(" ", "%", $keyword), 'both', false)
    ->get('my_table');

(notice that ->select('*') was not needed)

Depending on your database dialect, the rendered query will resemble:

SELECT * FROM "my_table" WHERE my_column LIKE '%keyword%sample%'

The querybuilder method has the following signature:

/**
 * LIKE
 *
 * Generates a %LIKE% portion of the query.
 * Separates multiple calls with 'AND'.
 *
 * @param   mixed   $field
 * @param   string  $match
 * @param   string  $side
 * @param   bool    $escape
 * @return  CI_DB_query_builder
 */
public function like($field, $match = '', $side = 'both', $escape = NULL)
{
    return $this->_like($field, $match, 'AND ', $side, '', $escape);
}

In the same family, related public methods include:

• not_like($field, $match = '', $side = 'both', $escape = NULL)
• or_not_like($field, $match = '', $side = 'both', $escape = NULL)

Vincenza