Injection attacks

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.

DNBrennan@gmail.com 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)

> 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

Jon Garvin 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)

I think that if you parametize the is_public value, ActiveRecord copes
with various SQL boolean approaches:

[ "articles.is_public = ? ...", true, ... ]

Ciao,
Sheldon.