Topic: Escaping Single Quotes and Using gsub

I need to generate something like this:

SELECT * FROM foo ORDER BY find_in_set(col_name, 'a,b,c')

and I'm using this statement to do it

sql = "SELECT * from foo ORDER BY find_in_set(#{column}, '#{values}')"

The problem is that the MySQL needs the value(s) that contain embedded single quotes to have them escaped, like this:

SELECT * FROM foo ORDER BY find_in_set(col_name, 'Don\'t,go,there')

So, I figured I'd just do this to values before I get to the statement above:

values.gsub!("'",'\'')

Well, that's a problem because \' is recognized by gsub as a special variable. Is there some easy way to do this. I can imagine some way to iterate and do it, but it seems like too much code to do what ought to be simple.

Re: Escaping Single Quotes and Using gsub

Ruby strings is interpreting this \' escaping here. The end result is a gsub which just replaces a single quote with another single quote. To do what you want you'll have to escape it twice:

values.gsub!("'",'\\'')

However, you really shouldn't need to do this at all. How are you executing the SQL?

Railscasts - Free Ruby on Rails Screencasts

Re: Escaping Single Quotes and Using gsub

That doesn't work because Ruby thinks there are an unbalanced number of quotes. In IRB it looks like this:

irb(main):013:0> "can't get nothin' to work".gsub("'",'\\'')
irb(main):014:1'

There has to be a way to substitute with the string \' but I am still stumped.

Regarding your other point, in fact, this is going into a Rails app as

:order => "find_in_set(#{column}, '#{values}')"

If you have a better idea how I should be doing this, fire away. It sounds from your last sentence that I'm missing a more obvious solution.

Re: Escaping Single Quotes and Using gsub

I got it to work using this

values.gsub('\'') {|s| %q{\'} }

ryanb, if you have a better way to do this, let me know.

Re: Escaping Single Quotes and Using gsub

bobf wrote:

That doesn't work because Ruby thinks there are an unbalanced number of quotes. In IRB it looks like this:

Whoops, 3 backslashes are necessary:

"can't get nothin' to work".gsub("'",'\\\'')

bobf wrote:

If you have a better idea how I should be doing this, fire away. It sounds from your last sentence that I'm missing a more obvious solution.

There is

ActiveRecord::Base.quote(str) # or you can use any model class

But I believe that adds quotes around it as well as sanitizing it, so it may not work for the column name.

Railscasts - Free Ruby on Rails Screencasts

Re: Escaping Single Quotes and Using gsub

Thanks. That will do the trick.

That code eventually calls quote_string (which invokes gsub). This provides the answer for how to do the escaping I originally asked about -- for anyone who's curious (or using Ruby but not Rails).