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?

On 9/7/06, Andy K. [email protected] wrote:

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

Max M. 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?

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