Database Class: $this->db->like($field, $value, 'both'); with Wildcards (Underscore '_')

[eluser]Unknown[/eluser]
Hey everyone,


I'm using the Active Record Class to get data from my DB (MySQL).
I've set up a form where a User can submit a search query which will call a Model with this line of code:

Code:

$this->db->like($field, $value, 'both');

Everything works fine, but now, I my client wants to submit Wildcards (especially the underscore) as well.

E.g. if the DB contains:
'aa1aa', 'aa2aa', 'aa3aa', aaXbc'

Code:

$this->db->like($field, 'aa_', 'both');

Should return every four rows. But because of using DB's like() method, the underscore is escaped, so the query is

[code]WHERE field LIKE "

[eluser]theprodigy[/eluser]
I don't think there is a real good way around it without extending the DB class to add in a "protect submission" boolean.
The mysql_driver class contains a "escape_str" method that is used by the DB class method. The escape_str method uses php's native functions for

1. 'mysql_real_escape_string'
2. 'mysql_escape_string'
3. 'addslashes'

in that order depending on what is available. So really, it's not the DB class Like method doing the escaping. In order to get around it, you would have to extend the mysql_driver class to accept a boolean as to whether or not you want to protect the user submitted value.

NOTES:
* The escape_str method can be found in system/database/drivers/mysql/mysql_driver.php starting on line 285.
* The call to this method can be found in system/database/drivers/DB_driver.php, method escape_like_string line 705
* The call to escape_like_string method can be found in system/database/drivers/DB_active_record.php method _like line 697
* The call to _like method can be found in system/database/drivers/DB_active_record.php method like line 613

[eluser]dakta[/eluser]

Code:

$this->db->like();

Should contain another parameter for whether or not the string should be escaped for "LIKE" wildcards. And so, my patch is attached.

This simply adds a parameter to every LIKE function in the Active Record class to allow you to specify whether or not to escape the LIKE string for wildcards.

Usage:
- Extract the PHP file,
- Back up your original DB_active_rec.php (in ./system/database/)
- Replace DB_active_rec.php contents with the contents of this file.

Code:

$this->db->like("column", "ex%mple", $escape=FALSE); //generates "... `column` LIKE '%ex%mple%'