Forum: Ruby on Rails SQL quoting for :order parameter

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Justin J. (Guest)
on 2006-02-23 19:24
(Received via mailing list)
When I construct a query using variables I do something like this:

@users = User.find(:all, :conditions => ["location = ?", location])

I would like to do the same sort of thing in the :order parameter.

 @users = User.find(:all, :order  => ["? ASC", location])

When I try this though I get a SQL error and I see that the items in the
array were concatenated together and ?s weren't replaced.

What is the best way to handle this?

Thanks,
Justin
Anthony DeRobertis (Guest)
on 2006-02-23 22:33
(Received via mailing list)
Justin J. wrote:

> When I construct a query using variables I do something like this:
>
> @users = User.find(:all, :conditions => ["location = ?", location])
>
> I would like to do the same sort of thing in the :order parameter.
>
>  @users = User.find(:all, :order  => ["? ASC", location])

Ordering by a constant makes no sense. I'm pretty sure you just want

        :order => 'location'

What you wrote would get a weird query like:

        SELECT * from tab_name ORDER BY 'some_constant'
unknown (Guest)
on 2006-02-23 22:51
(Received via mailing list)
On Thu, Feb 23, 2006 at 11:22:29AM -0600, Justin J. wrote:
> When I construct a query using variables I do something like this:
>
> @users = User.find(:all, :conditions => ["location = ?", location])
>
> I would like to do the same sort of thing in the :order parameter.
>
> @users = User.find(:all, :order  => ["? ASC", location])

Have you tried this?

@users = User.find :all, :order => "#{location} ASC"

As was pointed out previously you'll want to make sure that
location is the name of a column in the users table or you'll
still get a SQL error (or worse).

-steve
Justin J. (Guest)
on 2006-02-23 22:54
(Received via mailing list)
On 2/23/06, Steve P. <removed_email_address@domain.invalid> wrote:
> Have you tried this?
>
> @users = User.find :all, :order => "#{location} ASC"
>
> As was pointed out previously you'll want to make sure that
> location is the name of a column in the users table or you'll
> still get a SQL error (or worse).


Yes that works, but my understanding is that this is a SQL injection
risk,
which is why I wanted to use variable binding with a ? in the string.
Jeremy E. (Guest)
on 2006-02-23 23:25
(Received via mailing list)
On 2/23/06, Justin J. <removed_email_address@domain.invalid> wrote:
> Yes that works, but my understanding is that this is a SQL injection risk,
> which is why I wanted to use variable binding with a ? in the string.

SQL Injection is mitigating with ? by appropriately quoting input
values.  You are trying to substitute and attribute name (not an
attribute value), which will be unquoted, so using ? doesn't make
sense.  Instead, you should check that your location is valid by
comparing it against known good values:

:order=>(ModelName.column_names.include?(location) ? "#{location} ASC" :
nil)
Justin J. (Guest)
on 2006-02-23 23:37
(Received via mailing list)
On 2/23/06, Jeremy E. <removed_email_address@domain.invalid> wrote:
> comparing it against known good values:
>
> :order=>(ModelName.column_names.include?(location) ? "#{location} ASC" :
> nil)


This approach works for me.  Thanks much.
unknown (Guest)
on 2006-02-23 23:43
(Received via mailing list)
On Thu, Feb 23, 2006 at 02:51:48PM -0600, Justin J. wrote:
>
> Yes that works, but my understanding is that this is a SQL injection risk,
> which is why I wanted to use variable binding with a ? in the string.

Indeed it is *if* you can't trust the source of location's value.
At the very least you have to 'quote' its value otherwise you'll
be wide open to attack.

-steve
Justin J. (Guest)
on 2006-02-23 23:46
(Received via mailing list)
On 2/23/06, Steve P. <removed_email_address@domain.invalid> wrote:
>
> On Thu, Feb 23, 2006 at 02:51:48PM -0600, Justin J. wrote:
> >
> > Yes that works, but my understanding is that this is a SQL injection
> risk,
> > which is why I wanted to use variable binding with a ? in the string.
>
> Indeed it is *if* you can't trust the source of location's value.
> At the very least you have to 'quote' its value otherwise you'll
> be wide open to attack.


I should have clarified in my original post.  The value of location is
set
by a paramater on the URL.  That is why I wanted to quote it to begin
with.
But it does make more sense to just do an explicit check against the
table
columns before including it.  No need for quoting.

Thanks everyone.
Kevin O. (Guest)
on 2006-02-24 01:36
(Received via mailing list)
Take a look at this, I think it adds some functionality that you might
find handy.

http://tech.rufy.com/articles/2005/07/06/
hash-to_sql-hash-to_conditions-hash-to_named_conditions-oh-my

_Kevin
This topic is locked and can not be replied to.