MySql Query:: How to solve the problem of 's in data, when query is fired

Question

I am facing such problem in which 's is present in data. while searching it does not shows data. I wanna remove SQL injection issue Code ::

@search_condition = ""
          if !search_text.nil?
            search_field = search_text.split("-")
            @search_condition  =  "( address_books.organization_name like '#{search_text}%' or  address_books.business_name like '#{search_text}%' or address_books.federal_tax_id like '#{search_text}%' or address_books.city like '#{search_text}%' or address_books.zip like '#{search_text}%' ) "  if search_field.length == 1

            if search_text.include? "-"
              if search_field.length <= 1
                @search_condition  = " ( address_books.organization_name like '%" + search_field[0] + "%'  "
                @search_condition  += " or address_books.business_name like '%" + search_field[1] + "%'  "
                @search_condition  += " or address_books.federal_tax_id like '%" + search_field[2] + "%'  "
                @search_condition  += " or address_books.city like '%" + search_field[3] + "%'  "
                @search_condition  += " or address_books.zip like '%" +  search_field[4] + "%' ) "

Answer 1

You need replace all data insert by ? and save each data to replace this ? in an Array

@search_condition = ""
          if !search_text.nil?
            search_field = search_text.split("-")
            if search_field.length == 1
              @search_condition  =  "( address_books.organization_name like ? or  address_books.business_name like ? or address_books.federal_tax_id like ? or address_books.city like ? or address_books.zip like ? ) "  
              @search_condition_datas = ["#{search_text}%", "#{search_text}%", "#{search_text}%", "#{search_text}%", , "#{search_text}%"]
            if search_text.include? "-"
              if search_field.length <= 1
                @search_condition  = " ( address_books.organization_name like ? "
                @search_condition  += " or address_books.business_name like ?"
                @search_condition  += " or address_books.federal_tax_id like ?"
                @search_condition  += " or address_books.city like ?"
                @search_condition  += " or address_books.zip like ?"
                @search_condition_datas = ["%#{search_text[0]}%", "%#{search_text[1]}%", "%#{search_text[2]}%", "%#{search_text[3]}%", , "%#{search_text[4]}%"]

And after you can search with

User.find(:all, :conditions => [@search_condition] | @search_conditions_datas)

This code can be refactor after. It's really ugly.

Answer 2

Here's a possible refactoring using Arel / Rails 3 / REE 2010-02

class AddressBook < ActiveRecord::Base

  def self.search(search_text)
    unless search_text.nil?
      t = arel_table
      results = scoped

      search_fields = search_text.split("-")
      search_fields.map! {|f| "%#{f}" } unless search_fields.length == 1
      results = results.where(
        t[:organization_name].matches("#{search_fields[0] || search_text}%").
        or(t[:business_name].matches("#{search_fields[1] || search_text}%")).
        or(t[:federal_tax_id].matches("#{search_fields[2] || search_text}%")).
        or(t[:city].matches("#{search_fields[3] || search_text}%")).
        or(t[:zip].matches("#{search_fields[4] || search_text}%"))
      )
    end
    results
  end

end

Here are the SQLs generated:

ree-1.8.7-2010.02 > AddressBook.search("something")
  AddressBook Load (0.1ms)  SELECT "address_books".* FROM "address_books" WHERE ((((("address_books"."organization_name" LIKE 'something%' OR "address_books"."business_name" LIKE 'something%') OR "address_books"."federal_tax_id" LIKE 'something%') OR "address_books"."city" LIKE 'something%') OR "address_books"."zip" LIKE 'something%'))
 => [] 


ree-1.8.7-2010.02 > AddressBook.search("1-2-3-4-5")
  AddressBook Load (0.2ms)  SELECT "address_books".* FROM "address_books" WHERE ((((("address_books"."organization_name" LIKE '%1%' OR "address_books"."business_name" LIKE '%2%') OR "address_books"."federal_tax_id" LIKE '%3%') OR "address_books"."city" LIKE '%4%') OR "address_books"."zip" LIKE '%5%'))
 => [] 

Obviously, depending on your needs and how you want to search, you can update this. The main point is that with Arel you can just keep chaining clauses to the relation until the point where it's actually queried. This is a lot cleaner than building up a conditions string or array, I think.