Prevent Sql Injection In Laravel Full text search

Question

if(!empty($request->search_key)){
                $search = $request->search_key;
                $search_keys = explode(' ', $search);
                $count = 1;
                if(count($search_keys) > 0){
                    foreach($search_keys as $keys){
                        if(trim($keys) != ''){
                            $relevance .= " (MATCH(column_name) AGAINST ( ". "'" . $keys . "'" . ")* " . $count*10 . ") +";
                        }
                        $count++;
                    }
                }
                else{
                    $relevance .= " (MATCH(column_name) AGAINST ( ". "'" . $search . "'" . ")* " . $count*10 . ")";
                }
                $relevance = rtrim($relevance, '+');
                $relevance = $relevance . ' AS relevance';

               DB::table('tbl')->select(DB::raw($relevance))->get();
            }

In this code how we can prevent sql injection, if it's a single statement then I think i can use ,

DB::raw("SELECT * FROM users WHERE name = ?", [$name]));

but in this case I prepare it in a loop. then how to solve this ?

Thank you.

Answer 1

$search_keys = explode(' ', $search);
$terms = [];
$params = [];
$count = 1;
foreach ($search_keys as $key) {
    $terms[] = "(MATCH(column_name) AGAINST(?) * ?)";
    $params[] = $key;
    $params[] = $count * 10;
    $count++;
}
$relevance = implode($terms, " + ") . " AS relevance";

Now you have an array of the query parameters to use when you execute:

DB::table('tbl')->selectRaw($relevance, $params)->get();