CODEWITHSUNDEEP
phpcodeigniteractiverecordsql-likequery-builder
CobaltBabyBear
CobaltBabyBear

Reputation: 2218

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

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.

Upvotes: 13

Views: 97296

Answers (4)

mickmackusa
mickmackusa

Reputation: 48031

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)

Upvotes: 0

SolarBear
SolarBear

Reputation: 4629

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();

Upvotes: 14

Ravi Prakash
Ravi Prakash

Reputation: 21

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

Upvotes: 2

Erman Belegu
Erman Belegu

Reputation: 4079

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

Upvotes: 23

Related Questions