2

I have 4 types of last_name:

  • "Camp Bell"
  • "CAMPBELL"
  • "CampBellJr."
  • "camp bell jr."

Now, in rails when an user is searched by it's last name like camp bell, I want to show all the 4 records. So, I tried:

RAILS

stripped_name = params[last_name].gsub(/\W/, '')
#=> "campbell"

User.where("LOWER(REPLACE(last_name, '/\W/', '')) LIKE ?", "#{stripped_name}%")

Give me only 2 records with following last_name:

  • "CAMPBELL"
  • "CampBellJr."

I guess, this is because, the mysql REPLACE is not working correctly with regex.

Any ideas?

EDIT

Guys, sorry for the confusion. My idea is to strip off all special characters including space. So I'm trying to use \W regex.

For example, the input can be: camp~bell... But, it should still fetch result.

Abhi
  • 4,123
  • 6
  • 45
  • 77
  • 4
    **DON'T DO THIS: `LIKE '#{stripped_name}%'`** -- You've just created a [potential SQL injection vulnerability](http://guides.rubyonrails.org/security.html#sql-injection). What happens if a user enters `params[last_name] = '"; DROP TABLE users;--"` In this case, you *might* (?) get away with it, because of the `gsub`... But don't risk it, and don't leave code in a dangerous state where it could easily *become* a critical security hole. – Tom Lord Jul 26 '17 at 08:36
  • 2
    *Always* parameterise your SQL statements. Use: `"... LIKE ?", "#{stripped_name}%"`. – Tom Lord Jul 26 '17 at 08:37
  • https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql – Sajin Jul 26 '17 at 08:56
  • Maybe you could use `ILIKE` to avoid using `LOWER()`, I think it would be simpler to read. (only if you're using postgres, thx @TomLord) – Francois Jul 26 '17 at 09:09
  • 1
    @Francois This question was about `mysql`. `ILIKE` is only available in `postgresql`. – Tom Lord Jul 26 '17 at 09:12
  • Oupsie, my bad! – Francois Jul 26 '17 at 09:13
  • `REPLACE` won’t work with regexp, but what is basically wrong with replacing spaces explicitly `LOWER(REPLACE(last_name, ' ', ''))`?! – Aleksei Matiushkin Jul 26 '17 at 12:06
  • @mudasobwa It's not only spaces. `\W` is expected to remove any special character including spaces. – Abhi Jul 26 '17 at 13:20
  • I sorta know. What do you expect there, save for spaces, dots and probably commas? Do three nested REPLACEs – Aleksei Matiushkin Jul 26 '17 at 13:26
  • @mudasobwa Ok, I get your point. Although the logic would break if it's any special char. other than `.`, `,` and `space`. – Abhi Jul 26 '17 at 13:41
  • @mudasobwa The purpose is to prevail search even if user has accidentally inserted any special char. – Abhi Jul 26 '17 at 13:48

2 Answers2

0

You can check for both stripped_name without space and ones that include both names seperated with space like this.

stripped_name = params[last_name].gsub(/\W/, '')
split_names = params[last_name].split(" ")

User.where('name LIKE ? OR (name LIKE ? AND name LIKE ?)', "%#{stripped_name}%", "%#{split_names[0]}%", "%#{split_names[1]}%")

Next step would to search for complete array of split names not just first two.

meshin
  • 478
  • 2
  • 8
  • `#{%split_names[1]%}` has a couple of errors: 1. it is not valid ruby code and throws an exception, 2. it basically matches “Camp Bell” against “Belly Bellman.” – Aleksei Matiushkin Jul 26 '17 at 12:08
  • I forgott the quotation marks and put `%` in the wrong place, edited the answer. This is correct: `User.where('name LIKE ? OR (name LIKE ? AND name LIKE ?)', "%#{stripped_name}%", "%#{split_names[0]}%", "%#{split_names[1]}%")` – meshin Jul 26 '17 at 12:26
  • Still matches Belly Belltone. – Aleksei Matiushkin Jul 26 '17 at 13:03
  • Everything works fine just checked in the console. Search for “Camp Bell” only returns "Camp Bell” even if "Belly Bellman" exists. You have to have have braces arount this statement just before OR `(name LIKE ? AND name LIKE ?)` that's important – meshin Jul 26 '17 at 13:22
0

Here my solution:

User.where("REPLACE(last_name, ' ', '') ILIKE  CONCAT ('%', REPLACE('?', ' ', ''),'%')", stripped_name)

ILIKE is like LIKE but the I is for insensitive case.

To understand easily step by step:

  • lastname ILIKE '%campbell% you need % because you want lastname contain this string, not necessary at the begin or the end of you string.
    • 'campbell%' => search string who begin by campbell
    • '%campbell' => search string who finish by campbell
  • We need generate '%campbell%, so we use CONCAT for that
  • I just use a simply REPLACE, but maybe you should use a regex.
jpheos
  • 448
  • 5
  • 12
  • Thanks for the answer. There is a misunderstanding. I use `\W` so as to strip any special character including space. It's not only `space`. I have edited the question. – Abhi Jul 26 '17 at 14:01
  • I just saw that you use mysql, so you right to use LOWER instead ILIKE, thanks Tom Lord – jpheos Jul 26 '17 at 14:01