Injection attacks

rubyonrails-talk
1

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.

2

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)

3

> 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

4

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)

5

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

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

Ciao, Sheldon.