# SQL quoting for :order parameter **URL:** \ **Category:** Rails\ **Created:** [February 23, 2006, 6:24pm UTC](https://www.ruby-forum.com/t/sql-quoting-for-order-parameter/72453 "2006-02-23T18:24:58Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![justinj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b38774/32.png) [@justinj](https://www.ruby-forum.com/u/justinj)\ **Post date:** [February 23, 2006, 6:24pm UTC](https://www.ruby-forum.com/t/sql-quoting-for-order-parameter/72453/1 "2006-02-23T18:24:58Z")
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 ---
**Author:** ![justinj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b38774/32.png) [@justinj](https://www.ruby-forum.com/u/justinj)\ **Post date:** [February 23, 2006, 9:33pm UTC](https://www.ruby-forum.com/t/sql-quoting-for-order-parameter/72453/2 "2006-02-23T21:33:16Z")
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' ``` ---
**Author:** ![justinj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b38774/32.png) [@justinj](https://www.ruby-forum.com/u/justinj)\ **Post date:** [February 23, 2006, 9:51pm UTC](https://www.ruby-forum.com/t/sql-quoting-for-order-parameter/72453/3 "2006-02-23T21:51:41Z")
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 ---
**Author:** ![justinj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b38774/32.png) [@justinj](https://www.ruby-forum.com/u/justinj)\ **Post date:** [February 23, 2006, 9:54pm UTC](https://www.ruby-forum.com/t/sql-quoting-for-order-parameter/72453/4 "2006-02-23T21:54:47Z")
On 2/23/06, Steve P. [removed\_email\_address@domain.invalid](mailto: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. ---
**Author:** ![justinj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b38774/32.png) [@justinj](https://www.ruby-forum.com/u/justinj)\ **Post date:** [February 23, 2006, 10:37pm UTC](https://www.ruby-forum.com/t/sql-quoting-for-order-parameter/72453/5 "2006-02-23T22:37:30Z")
On 2/23/06, Jeremy E. [removed\_email\_address@domain.invalid](mailto: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. ---
**Author:** ![justinj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b38774/32.png) [@justinj](https://www.ruby-forum.com/u/justinj)\ **Post date:** [February 23, 2006, 10:43pm UTC](https://www.ruby-forum.com/t/sql-quoting-for-order-parameter/72453/6 "2006-02-23T22:43:36Z")
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 ---
**Author:** ![justinj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b38774/32.png) [@justinj](https://www.ruby-forum.com/u/justinj)\ **Post date:** [February 23, 2006, 10:46pm UTC](https://www.ruby-forum.com/t/sql-quoting-for-order-parameter/72453/7 "2006-02-23T22:46:41Z")
On 2/23/06, Steve P. [removed\_email\_address@domain.invalid](mailto: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. ---
**Author:** ![justinj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b38774/32.png) [@justinj](https://www.ruby-forum.com/u/justinj)\ **Post date:** [February 23, 2006, 10:25pm UTC](https://www.ruby-forum.com/t/sql-quoting-for-order-parameter/72453/8 "2006-02-23T22:25:21Z")
On 2/23/06, Justin J. [removed\_email\_address@domain.invalid](mailto: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) ---
**Author:** ![justinj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b38774/32.png) [@justinj](https://www.ruby-forum.com/u/justinj)\ **Post date:** [February 24, 2006, 12:36am UTC](https://www.ruby-forum.com/t/sql-quoting-for-order-parameter/72453/9 "2006-02-24T00:36:08Z")
Take a look at this, I think it adds some functionality that you might find handy. [http://tech.rufy.com/articles/2005/07/06/](http://tech.rufy.com/articles/2005/07/06/) hash-to\_sql-hash-to\_conditions-hash-to\_named\_conditions-oh-my \_Kevin