This project is archived and is in readonly mode.
Backslashes doesn't work in LIKE queries with PostgreSQL adapter
Reported by Lars Christensen | October 31st, 2008 @ 12:18 PM | in 2.x
For example:
class Resource < ActiveRecord::Base; end Resource.create(:path => "foo\bar\baz") Resource.find(:all, :conditions => [ "resources.path like ?", "%ar\ba%" ] ).each { |rs| puts rs.path }
This does not return any objects. It works with the SQLite3 adapter.
Exact queries does actually work ("resources.path = ?", ...). It is also possible to get the above working by escaping the backslash one more time:
Resource.find(:all, :conditions => [ "resources.path like ?", "%ar\\ba%" ] ).each { |rs| puts rs.path }
PostgreSQL version: 8.3.3. ROR version: 2.1.1
Comments and changes to this ticket
-
Lars Christensen November 5th, 2008 @ 08:28 AM
I have realised that LIKE clauses require special escaping of %, _, and . PostgreSQL defaults to having backslash as the escape operator, while SQLite default to NOT having an escape operator. Furthermore, PostgreSQL requires escaping of backslashes in strings in general, while SQLite does not.
To select strings containing "%_" would in SQLite be:
select ... where name like '\%_\' ESCAPE ''
and in PostgreSQL
select ... where name like E'\%\_\\' ESCAPE '\';
A portable way seems to be:
filter.gsub!(/[\%_]/, "\\\0") Record.find(:all, :conditions => [ "name like ? escape ?", "%#{filter}%", "\" ]
-
Pratik March 12th, 2009 @ 04:05 PM
- State changed from new to incomplete
- Assigned user set to Tarmo Tänav
You should probably try to submit a failing test.
Thanks.
-
CancelProfileIsBroken August 5th, 2009 @ 03:30 PM
- Tag changed from 2.1.1, :conditions, postgresql, sql to 2.1.1, :conditions, bugmash, postgresql, sql
-
Elise Huard August 9th, 2009 @ 11:22 PM
-1 tested with master on postgresql 8.3.5 with ruby1.8.6 and unable to reproduce
>> u = User.create(:name => 'tra/la/la') => #<User id: 3, name: "tra/la/la", created_at: "2009-08-09 22:19:01", updated_at: "2009-08-09 22:19:01"> >> User.find(:all,:conditions => ["users.name like ?","%tra/la%"]) => [#<User id: 3, name: "tra/la/la", created_at: "2009-08-09 22:19:01", updated_at: "2009-08-09 22:19:01">]
-
Rizwan Reza January 20th, 2010 @ 11:30 AM
- State changed from incomplete to invalid
- Tag changed from 2.1.1, :conditions, bugmash, postgresql, sql to 2.1.1, :conditions, postgresql, sql
- Assigned user cleared.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
<h2 style="font-size: 14px">Tickets have moved to Github</h2>
The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>