Geek Answers Handbook

Laravel 5.1 escape string parameter (having the form)

I get a string parameter that would like to use hasRaw with:

the line I get:

$searchString = Input::get('q');//example:party beach

I found in another question that I should use this to prevent sql injection:

 $searchStringEsc = DB::connection()->getPdo()->quote($searchString);

The problem I am facing is that when I insert into my query using Raw, since my string is now escaped by `party beach`, it returns null, but when I insert the string without escaping, it works fine.

 ->havingRaw('search rlike replace("'.$searchStringEsc.'", " ", "|")')

is there any other way to avoid the initial parameters? thanks

EDIT-- Full query (I make a search query in which the user can specify the name of the city, the name of the institution, any tags marked in the installation, etc.)

  $results = DB::table('events') ->leftJoin('event_tag', 'events.id', '=', 'event_tag.event_id') ->join('tags', 'tags.id', '=', 'event_tag.tag_id') ->join('establishments', 'establishments.id', '=', 'events.establishment_id') ->join('cities', 'establishments.city_id', '=', 'cities.id') ->leftJoin('artist_event', 'events.id', '=', 'artist_event.event_id') ->join('artists', 'artist_event.artist_id', '=', 'artists.id') ->leftJoin('event_music', 'events.id', '=', 'event_music.event_id') ->join('musics', 'musics.id', '=', 'event_music.music_id') ->select('events.id as evId', 'events.slug as evSlug', 'events.name as evName', 'events.cover_path as estPath','establishments.establishment_type_id as estType', 'establishments.name as estName', 'events.start_date as evStart', 'events.end_date as evEnd', 'cities.name as ciName', DB::raw('CONCAT_WS(",", GROUP_CONCAT(distinct tags.name), GROUP_CONCAT(distinct artists.name), GROUP_CONCAT(distinct cities.name), GROUP_CONCAT(distinct events.name), GROUP_CONCAT(distinct musics.name) ) as search')) ->where('events.end_date','>=', DB::raw('NOW()')) ->where('establishments.is_active','=',1) ->groupBy('events.id') ->havingRaw('search rlike replace("'.$searchString.'", " ", "|")') ->orderBy('events.total_visited', 'desc') ->take(5)->get();

if I leave it as $ searchString (unscaped string), it works fine. if I change it to $ searchStringEsc (escaped string), it returns null

+6
source share
1 answer

You can just use request binding, so replace

  ->havingRaw('search rlike replace("'.$searchString.'", " ", "|")')

with

 ->havingRaw('search rlike replace(?, " ", "|")', [$searchString])

which handles all your escaping capabilities ( whereRaw can do this too). ? means you have an associated parameter (namely $searchString ).

+2
source

More articles:


All Articles