Hi,
I have this pice of SQL, in my ROR app
I have two models article and category, and I''m trying to do a search
of public articles in a selected category. I am using a find_by_sql,
and this leaves me open to an injection attack.
We get the input parameter #{@search_term} from the user, they type
this in so it can be any thing.
#{@selected_catID}, #{article_per_page}, #{article_offset} will be
int''s
@public_ articles = Article.find_by_sql("SELECT articles.* FROM
articles, articles_categories ac
WHERE articles.is_public = 1 and articles.id=ac.
article_id and ac.category_id=#{@selected_catID} and
(articles.description like ''%#{@search_term}%'' or
article.title like
''%#{@search_term}%'')
order by created_at DESC LIMIT #{article_per_page}
OFFSET #{article_offset}")
What is the correct way to do this?
Kind regards,
Dave.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
DNBrennan-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote:> Hi, > > I have this pice of SQL, in my ROR app > > I have two models article and category, and I''m trying to do a search > of public articles in a selected category. I am using a find_by_sql, > and this leaves me open to an injection attack. > > We get the input parameter #{@search_term} from the user, they type > this in so it can be any thing. > #{@selected_catID}, #{article_per_page}, #{article_offset} will be > int''s > > @public_ articles = Article.find_by_sql("SELECT articles.* FROM > articles, articles_categories ac > WHERE articles.is_public = 1 and articles.id=ac. > article_id and ac.category_id=#{@selected_catID} and > (articles.description like ''%#{@search_term}%'' or article.title like > ''%#{@search_term}%'') > order by created_at DESC LIMIT #{article_per_page} > OFFSET #{article_offset}") > > What is the correct way to do this? > > >I''ll assume that articles_categories is a HABTM table between ''articles'' and ''categories'' tables. Article.find(:all, :include => :categories, :conditions => ["articles.is_public = 1 AND categories.id = ? AND (articles.description LIKE ? OR articles.title LIKE ?",@selected_catID, "%#{search_term}%","%#{search_term}%"], :order => ''created_at DESC'', :limit => articles_per_page, :offset => articles_offset) -- http://www.5valleys.com/ http://www.workingwithrails.com/person/8078 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Jun 9, 8:04 pm, Jon Garvin <jgarvin.li...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> DNBren...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote: > > Hi, > > I''ll assume that articles_categories is a HABTM table between ''articles'' > and ''categories'' tables. > > Article.find(:all, :include => :categories, :conditions =>Or just :joins if you don''t actually want to load the categories. Fred> ["articles.is_public = 1 AND categories.id = ? AND (articles.description > LIKE ? OR articles.title LIKE ?",@selected_catID, > "%#{search_term}%","%#{search_term}%"], :order => ''created_at DESC'', > :limit => articles_per_page, :offset => articles_offset) > > -- > > http://www.5valleys.com/ > > http://www.workingwithrails.com/person/8078--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Jon Garvin wrote:> DNBrennan-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote: >> Hi, >> >> I have this pice of SQL, in my ROR app >> >> I have two models article and category, and I''m trying to do a search >> of public articles in a selected category. I am using a find_by_sql, >> and this leaves me open to an injection attack. >> >> We get the input parameter #{@search_term} from the user, they type >> this in so it can be any thing. >> #{@selected_catID}, #{article_per_page}, #{article_offset} will be >> int''s >> >> @public_ articles = Article.find_by_sql("SELECT articles.* FROM >> articles, articles_categories ac >> WHERE articles.is_public = 1 and articles.id=ac. >> article_id and ac.category_id=#{@selected_catID} and >> (articles.description like ''%#{@search_term}%'' or article.title like >> ''%#{@search_term}%'') >> order by created_at DESC LIMIT #{article_per_page} >> OFFSET #{article_offset}") >> >> What is the correct way to do this? >> >> >> > I''ll assume that articles_categories is a HABTM table between > ''articles'' and ''categories'' tables. > > Article.find(:all, :include => :categories, :conditions => > ["articles.is_public = 1 AND categories.id = ? AND > (articles.description LIKE ? OR articles.title LIKE > ?",@selected_catID, "%#{search_term}%","%#{search_term}%"], :order => > ''created_at DESC'', :limit => articles_per_page, :offset => > articles_offset) >Actully, no, the is much better. @category = Category.find(@selected_catID) @articles = @category.articles.find(:all, :conditions => ["articles.is_public = 1 AND (articles.description LIKE ? OR articles.title LIKE ?", "%#{search_term}%","%#{search_term}%"], :order => ''created_at DESC'', :limit => articles_per_page, :offset => articles_offset) -- http://www.5valleys.com/ http://www.workingwithrails.com/person/8078 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Monday 09 June 2008 21:08:13 Jon Garvin wrote:> @category = Category.find(@selected_catID) > @articles = @category.articles.find(:all, :conditions => > ["articles.is_public = 1 AND (articles.description LIKE ? OR > articles.title LIKE ?", "%#{search_term}%","%#{search_term}%"], > :order => ''created_at DESC'', :limit => articles_per_page, :offset => > articles_offset)I think that if you parametize the is_public value, ActiveRecord copes with various SQL boolean approaches: [ "articles.is_public = ? ...", true, ... ] Ciao, Sheldon. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFITk2GpGJX8XSgas0RAmB+AJ0RodtZH7w7LGFlOhVN4OLmuE33UACfTl0c gLVfrMz/BNpo+TcA/BH83G0=ekpk -----END PGP SIGNATURE----- --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---