Escaping and Backslashes, Oh Joy

Saturday, February 18, 2006, at 08:07AM

By Eric Richardson

Over the past few days I noticed an annoying issue where a by-address search wasn't behaving properly. I would search for, say, "santa fe" and would get only four results even though I knew there were five in the database.

The Rails code that was generating the search was pretty simple:

@buildings = Building.find(:all,
  :conditions => [ 
    'address regexp ?', 
    '^[^\n]*' + building[:address].downcase 
  ], 
  :order => "address ASC")

So for that santa fe search I should get:

SELECT * 
FROM buildings 
WHERE (address regexp '^[^\n]*santa fe') 
ORDER BY address ASC

But that's not what I'm getting. I'm actually getting that regexp as '^[^\\n]*santa fe'. So instead of preventing newlines before the address, I'm actually preventing backslashes or n's. The missing result -- on "N. Santa Fe".

The proper way to write that query from Rails while still getting quoting on the supplied address would be something more like:

conditions = "address regexp '^[^\\\n]*"
conditions << Building.connection.quote_string(building[:address].downcase)
conditions << "'"

@buildings = Building.find(:all,
  :conditions => [ conditions ],
  :order => "address ASC")

(Note that here the \\n is just to get \n to SQL)

Geesh... I had to extra escape backslashes just to get them to show up in this post.