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? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
On 9/7/06, Andy Koch <andy.koch-sBIqA0PYact54TAoqtyWWQ@public.gmane.org> wrote:> > 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Max Muermann schrieb:> On 9/7/06, Andy Koch <andy.koch-sBIqA0PYact54TAoqtyWWQ@public.gmane.org> wrote: >> 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 >Sorry, not quite sure I understand your intent. Do you mean the users should type that into the search field? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
> > > > 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---