am trying to creating a search function to be able to search for products and also filter the result by relevance but i got Syntax error after the query. below is the error i got too
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
and the search function
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;
}
I'm also using escape() method to escape the query string in other to avoid sql injection but am not so convince this is the best practice as what the escape method does is adding single quote around the string which in turn will not even return any match in the table, I also try to use mysqli_escape_string() but can't get it work either, so i want to know what's the best practice in Yii2 to escape query string and avoid sql injection attack.
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);
}
You should escape the whole expression for LIKE
, including %
wildcards:
$value = Yii::$app->db->quoteValue('%' . implode(', ', (array) $keywords) . '%');
$titleSQL[] = "if (title LIKE $value,$scoreFullTitle,0)";
This will generate something like:
if (title LIKE '%mens, winter, jacket%',6,0)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments