PostgreSql + Searching Issue for "'" present in string

Question

Here, I have a problem in searching record in Postgresql DB in RoR Application. Name of table :: address_books, name of attributes :: organization_name, federal_tax_id, city, zip , business_name. In search, organization name contain :: Claire's Inc as record. At the time of searching, it does not show the data while we select Claire's Inc in search box. Because "'" breaks the string and gives no result. So I have used "?" replace "'" at time of search in mysql and it works. But I am getting appropriate conversion to make search of this words.

Query :: SELECT * FROM "address_books" 
WHERE ( address_books.organization_name = 'Claire?s Inc' 
and address_books.federal_tax_id = '59-0940416' 
and address_books.city = 'Hoffman Estates' 
and address_books.zip = '60192' and address_books.business_name ='' ) 
ORDER BY address_books.organization_name , city LIMIT 100

Please suggest any other way to make successful search.

Thanks in Advance

Answer 1

You're messing up your data to deal with a matter of query syntax. Put a correctly escaped apostrophe in the place where the apostrophe should be.

One way is to escape it to 'Claire''s Inc'. Another is to use a library that lets you pass parameters and handles the escaping for you. Another is to enter the string as $$Claire's Inc$$ though that syntax allows for other things that may not be appropriate here.

Answer 2

I think you can use RoR parameter substituion, than RoR will escape your dangerous strings for you. something like:

AddressBook.find(:all, :conditions => { "organization_name => ?", "Claire's Inc" }) 

or

AddressBook.find(:all, :conditions => { :organization_name => "Claire's Inc" })