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.
87f3caa6600d95f06fdc00b822ed108d?d=identicon&s=25 Justin Johnson (Guest)
on 2006-02-23 18: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
2d0f17b478aa748b34afec2e5ec07b8d?d=identicon&s=25 Anthony DeRobertis (Guest)
on 2006-02-23 21:33
(Received via mailing list)
Justin Johnson 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'
2df489144211eb464fd0dfb029cf1609?d=identicon&s=25 unknown (Guest)
on 2006-02-23 21:51
(Received via mailing list)
On Thu, Feb 23, 2006 at 11:22:29AM -0600, Justin Johnson 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
87f3caa6600d95f06fdc00b822ed108d?d=identicon&s=25 Justin Johnson (Guest)
on 2006-02-23 21:54
(Received via mailing list)
On 2/23/06, Steve Price <steve@havk.org> 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.
58c44a4a506d878f9a112f1d7b7cb87e?d=identicon&s=25 Jeremy Evans (Guest)
on 2006-02-23 22:25
(Received via mailing list)
On 2/23/06, Justin Johnson <justinj@justinj.org> 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)
87f3caa6600d95f06fdc00b822ed108d?d=identicon&s=25 Justin Johnson (Guest)
on 2006-02-23 22:37
(Received via mailing list)
On 2/23/06, Jeremy Evans <jeremyevans0@gmail.com> wrote:
> comparing it against known good values:
>
> :order=>(ModelName.column_names.include?(location) ? "#{location} ASC" :
> nil)


This approach works for me.  Thanks much.
2df489144211eb464fd0dfb029cf1609?d=identicon&s=25 unknown (Guest)
on 2006-02-23 22:43
(Received via mailing list)
On Thu, Feb 23, 2006 at 02:51:48PM -0600, Justin Johnson 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
87f3caa6600d95f06fdc00b822ed108d?d=identicon&s=25 Justin Johnson (Guest)
on 2006-02-23 22:46
(Received via mailing list)
On 2/23/06, Steve Price <steve@havk.org> wrote:
>
> On Thu, Feb 23, 2006 at 02:51:48PM -0600, Justin Johnson 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.
5d15c6821f3c3054c04b85471824ba7c?d=identicon&s=25 Kevin Olbrich (Guest)
on 2006-02-24 00: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.