SitePoint Sponsor |
|
User Tag List
Results 1 to 22 of 22
-
Jul 24, 2007, 05:59 #1
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?
-
Jul 24, 2007, 09:02 #2
- Join Date
- Aug 2005
- Posts
- 986
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Can you post the sql you want to generate? Examples?
-
Jul 24, 2007, 13:03 #3
-
Jul 24, 2007, 15:15 #4
- 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...
-
Jul 25, 2007, 00:15 #5
-
Jul 25, 2007, 01:54 #6
- 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'])
You can also add multiple entries:
Code:things = Thing.find(:all, :conditions => ["a_field = ? AND another_field = ?", some_value, another_value])
Code:values = [some_value, another_value] things = Thing.find(:all, :conditions => ["a_field = ? AND another_field = ?", values])
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
-
Jul 25, 2007, 02:11 #7
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.
-
Jul 25, 2007, 02:36 #8
- 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 << "%#{value}%"
Code:condition_text << "#{field} = ?" values << value
Code:search_fields = {'field1' => "=", 'field2' => "LIKE", 'field3' => "LIKE"}
Code:condition_text << "#{hash_key} #{hash_value} ?"
Code:if hash_value == "=" values << value else values << "%#{value}%" end
Code:search_fields = {'field1' => "=", 'field2' => "STARTS", 'field3' => "CONTAINS"}
-
Jul 25, 2007, 03:17 #9
ok thank you very much i've done my code and it work well
-
Jul 25, 2007, 03:30 #10
- Join Date
- Feb 2006
- Location
- Worcs. UK
- Posts
- 404
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Splendid. Glad to have been of help.
-
Jul 25, 2007, 03:34 #11
- 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.
-
Jul 25, 2007, 04:51 #12
- 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
-
Jul 25, 2007, 07:10 #13
-
Jul 25, 2007, 09:40 #14
- Join Date
- Aug 2005
- Posts
- 986
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Try first result of google "rails searching".
-
Jul 25, 2007, 11:49 #15
-
Jul 25, 2007, 13:20 #16
- 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.
-
Jul 25, 2007, 13:30 #17
-
Jul 26, 2007, 00:29 #18
- Join Date
- Aug 2005
- Posts
- 986
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Jul 26, 2007, 00:58 #19
-
Jul 26, 2007, 02:45 #20
- 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
-
Jul 26, 2007, 05:27 #21
-
Jul 26, 2007, 06:59 #22
- Join Date
- Aug 2005
- Posts
- 986
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Cool
Bookmarks