Getting SQL query Syntax error or access violation while running raw SQL in yii2

sam

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);
        }
rob006

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.

edited at
0

Comments

0 comments
Login to comment

Related

Getting sql syntax error while searching value with single and double in my sql through sequelize raw query

Why am I getting a syntax error while running SQL query in PHP?

Undefined variable: error while running the raw sql query

Syntax error or access violation php sql

Getting syntax error while running a SQL script in psql

Update query in SQL, syntax error while running on java

getting syntax error while using LIKE in sql query

Syntax Error running a Visual Basic SQL query in MS Access

Getting error while running SQL query in Procedure in Oracle

Laravel SQL Query Keeps Giving SQLSTATE[42000]: Syntax error or access violation: 1064

Yii2 querybuilder correct syntax for table names with raw sql

Eloquent getting Syntax error or access violation error

SQLSTATE[42000]: Syntax error or access violation: 1064 in sql select statement

SQL return a Syntax error or access violation: 1064 during migration with symfony

why this dynamic sql query getting syntax error

Getting an Odd Syntax Error With SQL Query

getting error in Sql Query incorrect syntax near ')'

Syntax error while calling sql query in JS

Getting syntax error when using raw SQL SELECT command

Syntax error or access violation error while running "php artisan migrate" for migration file containing primary & foreign keys

Laravel: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; change column

Syntax error or access violation: 1064 You have an error in your SQL syntax;

'PDOException' Syntax error or access violation: 1064 You have an error in your SQL syntax; check

Syntax error or access violation: 1064 You have an error in your SQL syntax in alter table

at the time of alter table syntax error or access violation: 1064 you have an error in your sql syntax;

Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax

Uncaught PDOException: Syntax error or access violation: You have an error in your SQL syntax

ERROR 1064 (42000) SQL Syntax error while running ansible playbook