PHP mysql fulltext boolean search, how to sanitize searched keyword and match all words?

Question

i am trying to match all words user has entered.

i am using php v7 and mysql v 5.7.9 mysql innodb fulltext search in boolean mode.

but my search keeps breaking when i use special characters like @ in the search.

i have tried many combinations , how can i achieve something like this ?

right now i am doing like this.

for getting records count

$get_count_query = "SELECT count(*) as total_count FROM content WHERE MATCH(title) AGAINST('". mysqli_real_escape_string ($conn, $_GET['q'])."' IN BOOLEAN MODE) AND is_enabled = 1 " ;

but if i search for

Adobe Premier pro -

my query becomes

SELECT count(*) as total_count FROM content WHERE MATCH(title) AGAINST('Adobe Premier pro -' IN BOOLEAN MODE) AND is_enabled = 1  

and i get

syntax error, unexpected $end

same thing happens when there are special charcters are either at the end.

i can match all words., by adding + before every word.,

but lets say someone tries to search for

Adobe Premier pro - tda

them my query becomes

SELECT count(*) as total_count FROM content WHERE MATCH(title) AGAINST('+Adobe +Premier +pro - +tda' IN BOOLEAN MODE) AND is_enabled = 1  

and i get

syntax error, unexpected '+'

what is the workaround for this ?

i only want to allow +, - , * special characters as a search operator.

---

update 1 :

so i tried with prepared statements.,

$query = "SELECT count(*) as total_count FROM content WHERE MATCH(title) AGAINST(? IN BOOLEAN MODE) AND is_enabled = 1" ;

    if ($stmt = mysqli_prepare($conn, $query)) {

        /* bind parameters for markers */
        mysqli_stmt_bind_param($stmt, 's', $_GET['q']);

        /* execute statement */
        mysqli_stmt_execute($stmt);

        /* bind result variables */
        mysqli_stmt_bind_result($stmt, $rrow['total_count']);

        /* store result */
        mysqli_stmt_store_result($stmt);

        /* fetch values */
        while (mysqli_stmt_fetch($stmt)) {
            printr($rrow);
        }

        /* free result */
        mysqli_stmt_free_result($stmt);

        /* close statement */
        mysqli_stmt_close($stmt);
    }

/* close connection */
mysqli_close($conn);

and searched for

Adobe Premier pro -

and output was blank.

what am i missing here ?

---

update 2 :

i was trying to replicate how TPB search works., and so far i have this, and works perfect. i am only allowing - , * mysql parameters., the code is ugly, but it does works as expected

//sanitize user input before sending it to db
function sanitize_keyword($keyword){

    $keyword_array = explode(' ', $keyword) ;

    $keyword_array = array_values(array_filter($keyword_array)) ;

    foreach($keyword_array as $key => $word){

        //pass it to db
/*      if (($key = array_search('(', $keyword_array)) !== false) {
            unset($keyword_array[$key]);
        } */

        //pass it to db
/*      if (($key = array_search(')', $keyword_array)) !== false) {
            unset($keyword_array[$key]);
        } */

        //dont pass it to db
        if (($key = array_search('*', $keyword_array)) !== false) {
            unset($keyword_array[$key]);
        }

        //dont pass it to db
        if (($key = array_search('+', $keyword_array)) !== false) {
            unset($keyword_array[$key]);
        } 

        //pass it to db
/*      if (($key = array_search('-', $keyword_array)) !== false) {
            unset($keyword_array[$key]);
        } */

        //dont pass it to db
        if (($key = array_search('<', $keyword_array)) !== false) {
            unset($keyword_array[$key]);
        }

        //dont pass it to db
        if (($key = array_search('>', $keyword_array)) !== false) {
            unset($keyword_array[$key]);
        }

        //dont pass it to db
        if (($key = array_search('@', $keyword_array)) !== false) {
            unset($keyword_array[$key]);
        }

        //pass it to db
/*      if (($key = array_search('~', $keyword_array)) !== false) {
            unset($keyword_array[$key]);
        } */
    }

    $keyword_array = array_values(array_filter($keyword_array)) ;

    //printr($keyword_array) ;

    $out_array = array();

    //loop through array, using increment, so we can jump to next value
    for($i=0 ; $i <= count($keyword_array); $i++){

        if(isset($keyword_array[$i])){
            //if there is space in - and word then, we get next value
            if($keyword_array[$i] == '-'){
                //get next word 
                if(isset($keyword_array[$i+1]) && strlen($keyword_array[$i+1]) > 0){
                    $out_array[] = '-'.$keyword_array[$i+1];
                    $i = $i + 1;
                }
            }elseif(isset($keyword_array[$i]) && strlen($keyword_array[$i]) > 0 && $keyword_array[$i][0] == '-'){
                //if word starts with -, then dont add + sign
                //add word as it is
                $out_array[] = $keyword_array[$i] ;
            }elseif(strpos($keyword_array[$i], '-') !== false){
                //if word contains - sign in the middle
                //explode and create words of array
                $all_words_array = explode('-', $keyword_array[$i]) ;

                //remove empty values
                $all_words_array = array_values(array_filter($all_words_array)) ;   

                //printr($all_words_array) ;

                //add each word in final array
                foreach($all_words_array as $word_t){
                    $out_array[] = '+'.$word_t;
                }
            }else{
                //its just some regular word, add it in out array
                $out_array[] = '+'.$keyword_array[$i];
            }
        }
    }

    //printr($out_array) ;
    return implode(' ', $out_array) ;
}

Answer 1

you can easily sanitize search string with some special char which are not supported by Mysql full-text search. you can use this function for escape and sanitize.

/**
     * @param string $keyword
     * @return string
     */
    public function sanitizeKeyword($keyword)
    {
        $keywordArray = explode(' ', $keyword);
        $specialChar = ['*', '+', '-', ' ', '(', ')', '~', '@','%', '<', '>'];
        $finalKeywords = [];

        //loop through array, using foreach, so we can prepare proper keywords array
        foreach ($keywordArray as $keyword) {
            if (in_array($keyword, $specialChar) || empty($keyword) || strlen($keyword) < 3) {
                continue;
            }
            if (in_array(substr($keyword, 0, 1), ['+', '-','~','*', '@', '<', '>'])) {
                $keyword = substr($keyword, 1);
            }

            //add each word in final array
            $finalKeywords[] = $keyword;
        }

        // create final string with space and + sign
        $finalString = implode(' +', $finalKeywords);
        if (!empty($finalString)) {
            $finalString = '+'.$finalString;
        }

        return $finalString;
    }