Handling escape sequence in query params in rails app server with Postgres

Question

query_string = params[:q]
model.where("name ILIKE ? ", "#{query_string}").limit(10)

In the controller I have the above lines

params[:q] is the user input.

Whenever the user input ends with \, say police \, Postgres throws this error:

ActionView::Template::Error (PG::InvalidEscapeSequence: ERROR:  LIKE pattern must not end with escape character.

How can we gracefully handle such cases?

Answer 1

Use a different escape character, for example:

WHERE name ILIKE 'pattern\' ESCAPE '/'

If you have no safe escape character, you can double it:

WHERE nane ILIKE replace('pattern\', '\', '\\')