SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Guru Skyblaze's Avatar
    Join Date
    Jul 2005
    Location
    Italy
    Posts
    734
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  2. #2
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you post the sql you want to generate? Examples?

  3. #3
    SitePoint Guru Skyblaze's Avatar
    Join Date
    Jul 2005
    Location
    Italy
    Posts
    734
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Fenrir2 View Post
    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.

  4. #4
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's not good enough ;-) If you want answers you should put in some effort...

  5. #5
    SitePoint Guru Skyblaze's Avatar
    Join Date
    Jul 2005
    Location
    Italy
    Posts
    734
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Fenrir2 View Post
    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

  6. #6
    SitePoint Evangelist
    Join Date
    Feb 2006
    Location
    Worcs. UK
    Posts
    404
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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:
    Code:
    things = Thing.find(:all, 
             :conditions => ["a_field = ? AND another_field = ?", some_value, another_value])
    Or put the values into an array:
    Code:
    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:
    Code:
    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.

  7. #7
    SitePoint Guru Skyblaze's Avatar
    Join Date
    Jul 2005
    Location
    Italy
    Posts
    734
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ReggieB View Post
    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:
    Code:
    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:
    Code:
    things = Thing.find(:all, 
             :conditions => ["a_field = ? AND another_field = ?", some_value, another_value])
    Or put the values into an array:
    Code:
    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:
    Code:
    def search
        search_fields = &#37;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.

  8. #8
    SitePoint Evangelist
    Join Date
    Feb 2006
    Location
    Worcs. UK
    Posts
    404
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you need to use the = form instead of the LIKE form, you simply replace:
    Code:
            condition_text << "#{field} LIKE ?"
            values << "&#37;#{value}%"
    with
    Code:
            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:
    Code:
    search_fields = {'field1' => "=", 'field2' => "LIKE", 'field3' => "LIKE"}
    Then substitute the keys and values into the condition_text:
    Code:
    condition_text << "#{hash_key} #{hash_value} ?"
    You'd also need to alter the value depending on the current hash_value.
    Code:
    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.
    Code:
    search_fields = {'field1' => "=", 'field2' => "STARTS", 'field3' => "CONTAINS"}
    and then use case to alter the behaviour depending on the hash value.

  9. #9
    SitePoint Guru Skyblaze's Avatar
    Join Date
    Jul 2005
    Location
    Italy
    Posts
    734
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok thank you very much i've done my code and it work well

  10. #10
    SitePoint Evangelist
    Join Date
    Feb 2006
    Location
    Worcs. UK
    Posts
    404
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Splendid. Glad to have been of help.

  11. #11
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is a lot of example code & plugins on the net that can help you do this without manual sql mangling.

  12. #12
    SitePoint Evangelist
    Join Date
    Feb 2006
    Location
    Worcs. UK
    Posts
    404
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nothing like a bit of sql mangling to brighten the soul and put a spring in the step

  13. #13
    SitePoint Guru Skyblaze's Avatar
    Join Date
    Jul 2005
    Location
    Italy
    Posts
    734
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Fenrir2 View Post
    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

  14. #14
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try first result of google "rails searching".

  15. #15
    SitePoint Guru Skyblaze's Avatar
    Join Date
    Jul 2005
    Location
    Italy
    Posts
    734
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Fenrir2 View Post
    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

  16. #16
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please read the first result. It's not an external library.

  17. #17
    SitePoint Guru Skyblaze's Avatar
    Join Date
    Jul 2005
    Location
    Italy
    Posts
    734
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Fenrir2 View Post
    Please read the first result. It's not an external library.
    Put the link

  18. #18
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  19. #19
    SitePoint Guru Skyblaze's Avatar
    Join Date
    Jul 2005
    Location
    Italy
    Posts
    734
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Fenrir2 View Post
    and i say "LOL!" you can't read maybe 'cause IT IS a library!!

  20. #20
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  21. #21
    SitePoint Guru Skyblaze's Avatar
    Join Date
    Jul 2005
    Location
    Italy
    Posts
    734
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Fenrir2 View Post
    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

  22. #22
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •