Toggleable sorting useing session variable?

Rails
1

Is there a reason NOT to sort a list using a session variable as in this
function?

–CONTROLLER–
def list
sort_by = params[:sort_by]
if session[:sort_by] == sort_by
sort_by += ’ desc’
end
session[:sort_by] = sort_by
@book_pages, @books = paginate :books, :order => sort_by, :per_page
=> 10
end

–VIEW–

Publisher Title ISBN
2

Yup. SQL injection. params[:sort_by] could contain an SQL fragment.

Vish

3

Hmmm. Point taken. Any suggestions on preventing SQL injection?

4

Whew! That is dangerous! One SQL comment line and that schema is
toast!

http://localhost:3001/admin/book/list?sort_by=title;%20drop%20table
returns:

ActiveRecord::StatementInvalid in Admin/bookController#list

Mysql::Error: #42000You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near ‘; drop table LIMIT 0, 10’ at line 1: SELECT * FROM
books ORDER BY title; drop table LIMIT 0, 10

So is there some way to sanitize the parameter or do I need to add a
whole new layer of abstraction?

5

@books = paginate :books, :order => [’?’, sort_by], :per_page => 10 ?

Vish

6

This thread helped:

http://www.ruby-forum.com/topic/82349#143790

However, I haven’t been able to splice SQL-escaping syntax into the
:order => clause in ‘paginate.’ The API docs are of no help. A few
failures:

@books = paginate :books,
:order => “#{sort_by}”,
:per_page => 10

Does nothing.

@books = paginate :books,
:order => [“:sort_by_criteria”, sort_by_criteria =>
sort_by}],
:per_page => 10

Doesn’t replace the value and tries to order by literally “:sort_by_criteria”

7

Vishnu G. wrote:

@books = paginate :books, :order => [’?’, sort_by], :per_page => 10 ?

We’re getting closer:

@book_pages, @books = paginate :books,
:order => [’?’, sort_by],
:per_page => 10

returns:

SELECT * FROM books ORDER BY ?isbn LIMIT 0, 10 (sort_by = ‘isbn’)

For some reason it is appending the ‘?’ onto sort_by. “” doesn’t work
either. Here is the whole function in case I missed something:

def list
sort_by = params[:sort_by]
unless session[:sort_by].nil?
if session[:sort_by] == sort_by
sort_by += ’ desc’
end
end
session[:sort_by] = sort_by
@book_pages, @books = paginate :books,
:order => [’?’, sort_by],
:per_page => 10
end

8

Vishnu G. wrote:
Hmm weird. Try a space after the ?, as in "? ".

That returns the same error:

SELECT * FROM books ORDER BY ? isbn LIMIT 0, 10

I can’t believe this paginate issue hasn’t been covered somewhere! My
searches only revealed that one thread posted above.

9

What about “? DESC” or “? ASC” ? Trial and error (and script/console) is
a
good friend :slight_smile: Sadly I don’t have irb right now.

Vish

10

Hmm weird. Try a space after the ?, as in "? ".

Vish

11

Vishnu G. wrote:

------=_Part_45211_14470071.1165240866527–

My preferred method for doing this is to pass a hash key in the sort
parameter like…

/object/action?sort=name_up

Then I do a lookup in the action…

sort = { ‘name_up’ => ‘name ASC’, ‘name_down’ =>‘name DESC’ }

Object.find(:all, :order=>sort[params[:sort]])

This way you don’t have to worry about SQL injections and you can make
complicated sort orders.

_Kevin