sanitize_sql and the "%" symbol

Q: I have a case where users have a legitimate reason to search for the percent symbol (%) as implemented using a LIKE clause.

So, I would have something...

SELECT * FROM table WHERE column LIKE '%\%%'

This would be correct. I want all records where the column has a percent symbol (%) anywhere in the string.

However, Rails ActiveRecord function does not escape the %, what it generates is...

SELECT * FROM table WHERE column LIKE '%%%'

which returns all records where column has something.

Is there a Rails method to handle this case? Or do I override the sanitize_sql method to handle this case?

Have you tried "\\\%"?


Max Muermann schrieb:

> > Have you tried "\\\%"? > > Max > Sorry, not quite sure I understand your intent.

Do you mean the users should type that into the search field?

Ah, sorry. I just re-read your post more slowly...

Ignore the answer, I thought it was an issue with the escaping you are doing, I am seeing now that you are still looking for the right way to do the escaping in the first place.

AFAIK, there is nothing in Rails that would help you with this. The method that does the string escaping is not actually sanitize_sql, but quote_string in active_record/connection_adapter/Quoting.rb:

def quote_string(s) s.gsub(/\\/, '\&\&').gsub(/'/, "''") # ' (for ruby-mode) end

You should be able to override that to your purposes.

Cheers, Max