Laravel 5.7 selectRaw array binding is not working (in mySQL full text search query)

I am using mySQL full text search and the column "nimi" is indexed accordingly. An hardcoded query, which injects the search term directly into SQL, is working properly: ... $term = '+mysearch*';

    $result = DB::table('register')
            ->select(['nimi', 'kood'])
            ->selectRaw("MATCH(nimi) AGAINST ('" . $term . "' IN BOOLEAN MODE) AS score")
            ->having('score', '>', 0)
            ->orderBy('score', 'desc')
            ->get();

...

However, when I try to do the same with the array binding, the query is not working:

        $result = DB::table('register')
                ->select(['nimi', 'kood'])
                ->selectRaw('MATCH(nimi) AGAINST ("?" IN BOOLEAN MODE) AS score', [$term])
        // or
                // ->selectRaw('MATCH(nimi) AGAINST (":TEST" IN BOOLEAN MODE) AS score', ['TEST' => $term])
        // or without quotes
                // ->selectRaw('MATCH(nimi) AGAINST (? IN BOOLEAN MODE) AS score', [$term])
                ->having('score', '>', 0)
                ->orderBy('score', 'desc')
                ->get();

The last query searches for ? (or for :TEST), not for $term.

How to get it working? $term is entered by users and I don't want to use hardcoded version because of possibility of SQL injection attack.

Nakov

6 years ago

Level 73

And have you tried using single quotes instead of double quotes as single quotes should be used for string values like in the VALUES() list. Double quotes are supported by MySQL for string values as well, but single quotes are more widely accepted by other RDBMS, so it is a good habit to use single quotes instead of double.

Like this:

->selectRaw("MATCH(nimi) AGAINST ('?' IN BOOLEAN MODE) AS score", [$term])

deansatch

6 years ago

Level 24

I'm sure this is the correct syntax (no quotes around ?) but perhaps Nakov is onto something with double vs single quotes but instead around the whole statement?

->selectRaw("MATCH(nimi) AGAINST (? IN BOOLEAN MODE) AS score", [$term])

Nakov

6 years ago

Level 73

@DEANSATCH - I thought the same as you did, but I looked at his code above, and he already seems to tried that.. if you look at one of his commented lines.

deansatch

6 years ago

Level 24

@NAKOV - yeah just thought maybe the double quotes around the whole statement may matter?

Nakov

6 years ago

Level 73

@DEANSATCH - Does not hurt to try. I don't think that it matters as that's php code passed to mysql, so those quotes will be avoided anyway. I use them in my answer in order to avoid concatenation meaning I had to close and open the quotes if I used single quotes at the beginning and as a wrapper on the question mark.

deansatch

6 years ago

Best Answer

Level 24

Ok...how about trying this?

$result = DB::table('register')
            ->select(['nimi', 'kood'])
            ->selectRaw("MATCH(nimi) AGAINST (? IN BOOLEAN MODE) AS score")
            ->having('score', '>', 0)
        ->setBindings([$term])
            ->orderBy('score', 'desc')
            ->get();

1 like

milosh

6 years ago

Level 1

Thank you!

->setBindings([$term])

did the trick!

Please or to participate in this conversation.