1

I am using Ruby on Rails and I have a location in my database with the name:

1A J@ck$on & S0n's #{10}

I am receiving this name via a webhook and then searching my database with it however it does not find the location name ( it is instead searching for the interpolated name:

1A J@ck$on & S0n's 10

How can I receive this string via a webhook like this:

@location = inbound_webhook_request['location']

And then put it in a pg "like" query as shown below:

Location.where("name ~* ?", @location['name'])

Without it being interpolated along the way?

Ben Richardson
  • 27
  • 4
  • 1
    That string will not be interpolated unless you are typing it into a double quoted String literal. Receiving this through a "webhook" will not cause interpolation as the `#{}` will already be escaped e.g. `'1A J@ck$on & S0n\'s #{10}' #=> "1A J@ck$on & S0n's \#{10}"`. Your issue is that you are using pattern matching `~*` and in regex `{}` has special meaning. "{" ...when followed by a digit, it is the beginning of a bound. "{m}" a sequence of exactly m matches of the atom, so it is actually searching for `"1A J@ck$on & S0n's ##########"` (10 # in row) – engineersmnky Apr 06 '21 at 13:36
  • 1
    Please note this is not the only special character (or character set) in regular expressions, or in your specific example. For instance `$` will match the end of a String – engineersmnky Apr 06 '21 at 13:39

1 Answers1

3

The string is not being interpolated. I'm not sure what led you to that assumption. However:

Location.where("name ~* ?", @location['name'])

This is not a LIKE operation, it's a POSIX regexp (case insensitive) operation.

Assuming you actually did want to perform a LIKE operation, not a regular expression search, you can do this:

Location.where("name LIKE ?", "%#{@location['name']}%")

or, using the shorthand syntax from the above linked documentation:

Location.where("name ~~ ?", "%#{@location['name']}%")

For a case-insensitive LIKE, you can use ILIKE or ~~*.

If the user input needs to be further sanitised, see this answer.

Tom Lord
  • 27,404
  • 4
  • 50
  • 77