如何转义mysql“搜索/喜欢”查询正确? [英] How do I escape a mysql "search / like" query properly?
问题描述
我目前正在使用$ search_field LIKE'$ this-> db-> escape_like_str($ search_string)%' ;
用于转义动态创建的搜索查询。生成的SQL语句不会产生任何错误,也不会产生任何结果。下面是我在做什么的详细描述。
I am currently using "$search_field LIKE '$this->db->escape_like_str($search_string)%'";
to escape dynamically created search queries. The resulting SQL statements created do not produce any errors but also do not produce any results. Below is a detailed description of what I am doing.
我使用jqGrid及其搜索功能。当用户输入搜索字词时,它将 $ filters
json对象发布到我的服务器。然后我解析它并创建一个SQL语句来获取请求的数据。
I am using jqGrid and its search feature. When the user enters search terms it posts the $filters
json object to my server. I then parse it and create an SQL statement to get the data requested.
这是转义传入搜索数据的代码(这也是问题区域):
Here is the code for escaping the incoming search data (this is also the problem area):
$search_string_like = $this->CI->db->escape_like_str($search_string);
$operator['bw'] = "$search_field LIKE '$search_string_like%'"; //begins with
以下是产生的SQL语句:
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
不会抛出任何错误,但它也不工作。当我直接在phpmyadmin中运行类似的查询时,我得到, MySQL返回一个空结果集(即零行)。
即使我知道有结果。如果我只是删除 first_name LIKE'\'zech\'%'
的反斜杠和单引号,使它 first_name LIKE'zech% '
然后我得到预期的结果。我担心的是,这不再正确逃脱,对吧?
This query doesn't throw any errors but it also doesn't work. When I run a similar query directly in phpmyadmin I get, MySQL returned an empty result set (i.e. zero rows).
even though I know there are results to be found. If I simply remove the backslashes and single-quotes from first_name LIKE '\'zech\'%'
to make it first_name LIKE 'zech%'
I then get the expected results. My concern is that this is no longer properly escaped, right?
摘要
变量 $ filters
,其中包含此 {groupOp:AND,rules :[{field:first_name,op:bw,data:zech}]}
被传递到 build_where_clause )
。 build_where_clause
返回完整的 $ where
语句,然后在模型中用于创建最终的SQL搜索语句。
A variable, $filters
, with data such as this {"groupOp":"AND","rules":[{"field":"first_name","op":"bw","data":"zech"}]}
is passed into 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];
}
}
}
strong>
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:
看起来我已解决问题。在模型中我删除了手工制作的$ sql语句并替换为
UPDATE / Answer:
It looks like I fixed the issue. In the model I removed the hand made $sql statement 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);
这似乎正确地转义了所有变量,包括$ where语句中的列名。
That seemed to properly escape all the variables including my column names in the $where statement.
推荐答案
手册:
$ this-> db-> escape_like_str()当在LIKE中使用字符串时,应使用此方法条件,以便字符串中的LIKE通配符('%','_')也正确转义。
$this->db->escape_like_str() This method should be used when strings are to be used in LIKE conditions so that LIKE wildcards ('%', '_') in the string are also properly escaped.
$search = '20% raise';
$sql = "SELECT id FROM table WHERE column LIKE '%".$this->db->escape_like_str($search)."%'";
所以你在最后两行代码中正确。
So you're 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%'";
这篇关于如何转义mysql“搜索/喜欢”查询正确?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!