How to avoid mysql "search / like" query correctly?

Question

Summary

I am currently using "$search_field LIKE '$this->db->escape_like_str($search_string)%'"; to run dynamically generated search queries. The resulting SQL statements do not produce errors, but also do not produce any results. Below is a detailed description of what I am doing.

More details

I am using jqGrid and its search function. When a user enters a search term, he sends the $filters json object to my server. Then I parse it and create an SQL statement to get the requested data.

Here is the code for escaping incoming search data (this is also a problem):

 $search_string_like = $this->CI->db->escape_like_str($search_string); $operator['bw'] = "$search_field LIKE '$search_string_like%'"; //begins with 

Here is the resulting SQL statement:

 SELECT * FROM player_data_temp_table WHERE first_name LIKE '\'zech\'%' AND last_name LIKE '\'camp\'%' ORDER BY date_won desc LIMIT 0 , 15 

This request does not cause any errors, but it also does not work. When I run a similar query directly in phpmyadmin, I get MySQL returned an empty result set (ie zero rows). although I know that there are results that can be found. If I just remove the backslash and single quotes from first_name LIKE '\'zech\'%' to make it first_name LIKE 'zech%' , I get the expected results. My concern is that this was no longer avoided, right?

The code used to create the request

Summary

$filters variable, with data such as {"groupOp":"AND","rules":[{"field":"first_name","op":"bw","data":"zech"}]} passed to build_where_clause($filters) . build_where_clause returns the full $where statement and is then used in the Model to create the final SQL search statement.

jqgrid_lib.php

 class jqgrid_lib { private $CI; public function __construct() { $this->CI =& get_instance(); } /** * Function takes a json string with search rules and turns it into an sql statement. * * To use this function make sure you set stringResult: true, see example below: * $("#list").jqGrid('filterToolbar',{stringResult: true}); * @param json string * @author zechdc */ public function build_where_clause($filters) { $sql_fragments = array(); $filters = json_decode($filters); $rules = $filters->rules; $group_op = $filters->groupOp; //loop through each rule and create an sql statement foreach($rules as $rule) { $temp_sql = $this->create_search_field($rule->field, $rule->data, $rule->op); array_push($sql_fragments, $temp_sql); } //combine all sql fragments with the group_operator $data['sql'] = implode(' ' . $group_op . ' ', $sql_fragments); return $data; } /** * Takes a field, string and search condition and turns it into a sql search statement * * To use this function make sure you set stringResult: true, see example below: * $("#list").jqGrid('filterToolbar',{stringResult: true}); * @param json string * @return string * @author zechdc */ public function create_search_field($search_field, $search_string, $search_operator) { //$search_field = $this->CI->db->escape($search_field); //escaping the column breaks it. $search_string = $this->CI->db->escape($search_string); $search_string_like = $this->CI->db->escape_like_str($search_string); //$search_string_like = $search_string; $operator['eq'] = "$search_field=$search_string"; //equal to $operator['ne'] = "$search_field<>$search_string"; //not equal to $operator['lt'] = "$search_field < $search_string"; //less than $operator['le'] = "$search_field <= $search_string "; //less than or equal to $operator['gt'] = "$search_field > $search_string"; //less than $operator['ge'] = "$search_field >= $search_string "; //less than or equal to $operator['bw'] = "$search_field LIKE '$search_string_like%'"; //begins with $operator['bn'] = "$search_field NOT LIKE '$search_string_like%'"; //not begins with $operator['in'] = "$search_field IN ($search_string)"; //in $operator['ni'] = "$search_field NOT IN ($search_string)"; //not in $operator['ew'] = "$search_field LIKE '%$search_string_like'"; //ends with $operator['en'] = "$search_field NOT LIKE '%$search_string_like%'"; //not ends with $operator['cn'] = "$search_field LIKE '%$search_string_like%'"; //in $operator['nc'] = "$search_field NOT LIKE '%$search_string_like%'"; //not in $operator['nu'] = "$search_field IS NULL"; //is null $operator['nn'] = "$search_field IS NOT NULL"; //is not null if(isset($operator[$search_operator])) { //set the sql search statement return $operator[$search_operator]; } } } 

Model

 /* * Gets all columns from table with limit and sort order set dynamically */ function get_specific($sidx, $sord, $start, $limit, $where = NULL) { $result = FALSE; if($where) { $where = ' WHERE ' . $where; } // usually I dont do select all but since this whole table is temp and only holds the needed data // then just do select all. $sql = "SELECT * FROM player_data_temp_table $where ORDER BY $sidx $sord LIMIT $start , $limit"; $q = $this->db->query($sql); if($this->db->affected_rows() > 0) { $result = $q->result(); } return $result; } 

UPDATE / ANSWER:

Looks like I fixed the problem. In the model, I deleted the $ sql manual operator and replaced it with

  if($where) { $this->db->where($where); } $this->db->order_by($sidx, $sord); $q = $this->db->get('player_data_temp_table', $limit, $start); 

It seemed that all variables, including column names, in the $ where statement were generally avoided.

Answer 1

Guide :

$ this-> db-> escape_like_str () This method should be used when strings should be used in LIKE conditions so that the LIKE characters ('%', '_') in the string are also correctly escaped.

 $search = '20% raise'; $sql = "SELECT id FROM table WHERE column LIKE '%".$this->db->escape_like_str($search)."%'"; 

So, you are doing it right in your last two lines of code.

---

What does the following code do for you?

 $search_string = 'zech'; $search_string_like = $this->CI->db->escape_like_str($search_string); $operator['bw'] = "$search_field LIKE '$search_string_like%'";