Performing rails query with mysql with backslash in string

Question

I have a database entry that looks like the following:

name = servername\vs1

We have a search that is looking for this term.

  scope :search, ->(term) {
    if term
      where('name LIKE ?', "%#{term}%")
    else
      all
    end
  }

However, it isn't finding it. When someone searches for severname, of course, it shows up. But when they include the backslash it isn't found.

After doing some research, I found that rails is currently adding a single backslash to the query term prior to search (servername\\vs1) but mysql needs the following format: (servername\\\\vs1).

So, I was hoping there was an easy rails way to add additional backslashes. Looking for any good solution.

Thanks

@MrYoshiji almost! It's adding single quotes around the string. getting the following: "'servername\\\\vs1'" — Ricky Mason
– Ricky Mason, Commented Jun 6, 2014 at 16:14
What about where("name LIKE #{sanitize("%#{term}%")}") (don't worry about SQL injections, sanitize protects you here — MrYoshiji
– MrYoshiji, Commented Jun 6, 2014 at 16:23

Vakiliy · Accepted Answer · 2014-06-06 18:25:15Z

2

Easiest to use Arel, like this:

  scope :search, ->(term){
    t = arel_table
    term ? where( t[:name].matches("%#{term}%") )
         : all
  }

Example:

Simple.search('\a').to_sql
"SELECT \"simples\".* FROM \"posts\"  WHERE (\"simples\".\"title\" LIKE '%\\a%')"

answered Jun 6, 2014 at 18:25

Vakiliy

8711 gold badge6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Performing rails query with mysql with backslash in string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related