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 -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060223/1dfa6d22/attachment.html
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''
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
On 2/23/06, Steve Price <steve@havk.org> wrote:> > 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).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. -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060223/fb2df54e/attachment.html
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)
On 2/23/06, Jeremy Evans <jeremyevans0@gmail.com> wrote:> > 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)This approach works for me. Thanks much. -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060223/1087d574/attachment.html
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
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. -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060223/1758e85c/attachment.html
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 -- Posted with http://DevLists.com. Sign up and save your time!