This project is archived and is in readonly mode.

#1309 ✓invalid
Lars Christensen

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

    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

    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

    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

    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

    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>

Pages