A way to escape/sanitize an sql string from a controller?

[Skyblaze]

In a project I use find_by_sql method and i pass to it, as the sql string argument, a sql string that i build "dinamically" based on the type and number of form parameters that the user send back to the controller.(those form parameter will be the variuos argument to the sql string "where" clause that as i said i build dinamically. So i can't use the standard built in way of the find_by_sql method to sanitize the sql string 'cause i don't know in advance how many arguments to the sql where clause i will have. I need a way to pass that sql custom string to a method that sanitize it. I found in the rail api the "sanitize" method but it can be used only in the model but i need it in the controller. Does anyone have an idea?

[Fenrir2]

Can you post the sql you want to generate? Examples?

[Skyblaze]

Can you post the sql you want to generate? Examples?

a simply construct a string from form parameters that i get from user inputs. That string is the "dinamically" generated sql string to escape.

[Fenrir2]

That's not good enough ;-) If you want answers you should put in some effort...

[Skyblaze]

That's not good enough ;-) If you want answers you should put in some effort...

sorry but what do you want? You want this?
"select * from some_table where column1 = this and column2 = that and column3 like %sometext%"
That is an example of a dinamically generates sql string

[ReggieB]

If all you are doing are simple "SELECT * FROM THING WHERE a_field = 'some_value'" you can use ActiveRecord's find method to generate the SQL query and therefore the security built into that.

In its simplest form you'd do this:

things = Thing.find(:all, :conditions => ["a_field = ?", 'some_value'])

If you use this array form of the conditions statement, the find method will automatically escape out dangerous characters and sanitize your data.

You can also add multiple entries:

things = Thing.find(:all, 
         :conditions => ["a_field = ? AND another_field = ?", some_value, another_value])

Or put the values into an array:

values = [some_value, another_value]
things = Thing.find(:all, 
         :conditions => ["a_field = ? AND another_field = ?", values])

So you could create a search method for people like this for example:

def search
    search_fields = %w(first_name last_name age gender location)
    condition_text = Array.new
    values = Array.new
    params.each do |field, value|
      if search_fields.include?(field) and value.length > 0
        condition_text << "#{field} LIKE ?"
        values << "%#{value}%"
      end
    end
    unless values.empty?
      @people = Person.find(:all, 
              :conditions => [condition_text.join(" AND "), values])
    else
      @people = Person.find(:all)
    end
end

Note that I have predefined which field names are acceptable with search_fields. This will prevent a user using the query to search any field they choose. Also I have used the "field LIKE '%value%' form of WHERE statement which means that only part of a value has to be submitted. This in effect says "where field includes value" which is usually more useful in a search query.

[Skyblaze]

If all you are doing are simple "SELECT * FROM THING WHERE a_field = 'some_value'" you can use ActiveRecord's find method to generate the SQL query and therefore the security built into that.

In its simplest form you'd do this:

things = Thing.find(:all, :conditions => ["a_field = ?", 'some_value'])

If you use this array form of the conditions statement, the find method will automatically escape out dangerous characters and sanitize your data.

You can also add multiple entries:

things = Thing.find(:all, 
         :conditions => ["a_field = ? AND another_field = ?", some_value, another_value])

Or put the values into an array:

values = [some_value, another_value]
things = Thing.find(:all, 
         :conditions => ["a_field = ? AND another_field = ?", values])

So you could create a search method for people like this for example:

def search
    search_fields = %w(first_name last_name age gender location)
    condition_text = Array.new
    values = Array.new
    params.each do |field, value|
      if search_fields.include?(field) and value.length > 0
        condition_text << "#{field} LIKE ?"
        values << "%#{value}%"
      end
    end
    unless values.empty?
      @people = Person.find(:all, 
              :conditions => [condition_text.join(" AND "), values])
    else
      @people = Person.find(:all)
    end
end

Note that I have predefined which field names are acceptable with search_fields. This will prevent a user using the query to search any field they choose. Also I have used the "field LIKE '%value%' form of WHERE statement which means that only part of a value has to be submitted. This in effect says "where field includes value" which is usually more useful in a search query.

That would be great except that you use only "LIKE" searches in the where clause and i would like to choose which fields/form parameters are to be searched with "like" or exact match "=". Maybe that is not a problem to use only like searches 'cause at least a user can enter a value that correspond to the exact value in the database field right?
Now i remember that i have a field/form parameter that i need to search only at the beginning of the field value with "some_text%" in the sql.

[ReggieB]

If you need to use the = form instead of the LIKE form, you simply replace:

        condition_text << "#{field} LIKE ?"
        values << "&#37;#{value}%"

with

        condition_text << "#{field} = ?"
        values << value

If you want to pick or choose your operator between LIKE or = then you will have to specify which fields to use which. For example you could use a hash for your search_fields:

search_fields = {'field1' => "=", 'field2' => "LIKE", 'field3' => "LIKE"}

Then substitute the keys and values into the condition_text:

condition_text << "#{hash_key} #{hash_value} ?"

You'd also need to alter the value depending on the current hash_value.

if hash_value == "="
  values << value
else
  values << "%#{value}%"
end

For more complicated options such as "field LIKE 'something%'" you could create a set of possible hash values and use these to determine how to construct the conditional text and array.

search_fields = {'field1' => "=", 'field2' => "STARTS", 'field3' => "CONTAINS"}

and then use case to alter the behaviour depending on the hash value.

[Skyblaze]

ok thank you very much i've done my code and it work well

[ReggieB]

Splendid. Glad to have been of help.

[Fenrir2]

There is a lot of example code & plugins on the net that can help you do this without manual sql mangling.

[ReggieB]

Nothing like a bit of sql mangling to brighten the soul and put a spring in the step

[Skyblaze]

There is a lot of example code & plugins on the net that can help you do this without manual sql mangling.

All the find/sql examples i saw on books and internet suppose that you know in advance how many search parameters you will have

[Fenrir2]

Try first result of google "rails searching".

[Skyblaze]

Try first result of google "rails searching".

I can search and i already knew that but if i have to use an external library then i use act_as_ferret plugin but i simply don't need all that functionality

[Fenrir2]

Please read the first result. It's not an external library.

[Skyblaze]

Please read the first result. It's not an external library.

Put the link

[Fenrir2]

lol. http://wiki.rubyonrails.org/rails/pages/TextSearch

[Skyblaze]

lol. http://wiki.rubyonrails.org/rails/pages/TextSearch

and i say "LOL!" you can't read maybe 'cause IT IS a library!!

[Fenrir2]

An internal one though. Just one file in your lib dir

I can search and i already knew that but if i have to use an external library then i use act_as_ferret plugin but i simply don't need all that functionality

I understand you don't want dependency hell, but this is just the functionality you need.

[Skyblaze]

An internal one though. Just one file in your lib dir



I understand you don't want dependency hell, but this is just the functionality you need.

Yeah i know but now i already found my solution and it is only some lines of code in the action. Maybe next time i will try it

[Fenrir2]

Cool