我试图创建一个搜索功能,能够搜索产品,也过滤的相关性,但我得到了查询后语法错误的结果。下面是我犯的错误
sql error
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mens', 'winter', 'jacket'%',6,0) + if (title LIKE '%'mens'%',5,0) + if (title LI' at line 5
The SQL being executed was: SELECT p.product_id,p.title,p.price,p.unit_sold,
p.profile_img,p.store_name,p.item_number,
(
(-- Title score
if (title LIKE '%'mens', 'winter', 'jacket'%',6,0) + if (title LIKE '%'mens'%',5,0) + if (title LIKE '%'winter'%',5,0) + if (title LIKE '%'jacket'%',5,0)
)+
(-- description
if (description LIKE '%'mens', 'winter', 'jacket'%',5,0) + if (description LIKE '%'mens'%',4,0) + if (description LIKE '%'winter'%',4,0) + if (description LIKE '%'jacket'%',4,0)
)
) as relevance
FROM products p
WHERE p.is_active = '1'
HAVING relevance > 0
ORDER BY relevance DESC,p.unit_sold DESC
LIMIT 10
以及搜索功能
function search($q){
if (mb_strlen(trim($q))===0){
// no need for empty search
return false;
}
$query = $this->limitChars(trim($q));
// Weighing scores
$scoreFullTitle = 6;
$scoreTitleKeyword = 5;
$scoreFullDescription = 5;
$scoreDescriptionKeyword = 4;
$keywords = $this->filterSearchKeys($query);
$escQuery = $this->escape($keywords);
$titleSQL = array();
$descSQL = array();
/**Matching full occurences**/
if (count($keywords) > 1){
$titleSQL[] = "if (title LIKE '%".$escQuery."%',{$scoreFullTitle},0)";
$descSQL[] = "if (description LIKE '%".$escQuery."%',{$scoreFullDescription},0)";
/**Matching Keywords**/
foreach($keywords as $key){
$titleSQL[] = "if (title LIKE '%".Yii::$app->db->quoteValue($key)."%',{$scoreTitleKeyword},0)";
$descSQL[] = "if (description LIKE '%".Yii::$app->db->quoteValue($key)."%',{$scoreDescriptionKeyword},0)";
}
//add 0 is query string is empty to avoid error
if (empty($titleSQL)){
$titleSQL[] = 0;
}
if (empty($descSQL)){
$descSQL[] = 0;
}
$sql = "SELECT p.product_id,p.title,p.price,p.unit_sold,
p.profile_img,p.store_name,p.item_number,
(
(-- Title score
".implode(" + ", $titleSQL)."
)+
(-- description
".implode(" + ", $descSQL)."
)
) as relevance
FROM products p
WHERE p.is_active = '1'
HAVING relevance > 0
ORDER BY relevance DESC,p.unit_sold DESC
LIMIT 10";
$results = Yii::$app->db->createCommand($sql)->queryAll();
if (!$results){
return false;
}
return $results;
}
我还使用escape()方法对查询字符串进行转义,以避免sql注入,但我不太相信这是最佳做法,因为转义方法在字符串周围添加单引号,这反过来甚至不会返回表中的任何匹配项,我也尝试使用mysqli\u escape\u string(),但也无法使其工作,所以我想知道yii2中的最佳实践是什么来转义查询字符串和避免sql注入攻击。
function escape($values)
{
$values = (array)$values;
$escaped = array();
foreach($values as $value) {
if(!is_scalar($value)) {
throw new CException('One of the values passed to values() is not a scalar.');
}
$escaped[] = Yii::$app->db->quoteValue($value);
}
return implode(', ', $escaped);
}
1条答案
按热度按时间8aqjt8rx1#
你应该避开整个表情
LIKE
,包括%
通配符:这将产生如下结果: