Forum: Ruby on Rails Single quotes in parameters

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.
Dylan M. (Guest)
on 2006-01-21 07:34
(Received via mailing list)
I have a "search" action for my "projects" controller, which defines a
set of projects as follows

@projects = Project.find(:all, :include => [:user,:clients], :conditions
=> "name like '%" + params[:query] + "%'",:order => 'number')


This works fine, until I type an entry into my search box that has a
single quote, such as "John's Company." ActiveRecord spits back an error
at me along the lines of "Mysql::Error: #42000You have an error in your
SQL syntax;." I know single quotes need to be escaped in MySQL, but I
assumed rails took care of this automatically, just like it does when I
create or update projects. Is there an easy way to do this, or will I
need to run a gsub on my params[:query] variable?
Matthew P. (Guest)
on 2006-01-21 07:55
(Received via mailing list)
On Fri, Jan 20, 2006 at 08:57:39PM -0800, Dylan M. wrote:
> I have a "search" action for my "projects" controller, which defines a
> set of projects as follows
>
> @projects = Project.find(:all, :include => [:user,:clients], :conditions
> => "name like '%" + params[:query] + "%'",:order => 'number')

:conditions => ["name like '%?%'", params[:query]]

Parameterized queries are a *good* thing.  The presence of the ? will
result
in the next parameter being escaped and inserted where the ? is.

- Matt
Sean W. (Guest)
on 2006-02-10 18:02
(Received via mailing list)
On 1/20/06, Matthew P. <removed_email_address@domain.invalid> wrote:
> in the next parameter being escaped and inserted where the ? is.
I've never been able to get this kind of query to work in a
parametrized :conditions argument. Every time I try to do this, the
results would be

"name like '%'John'%'"

As you can see, it puts extra single quotes around the parameter,
making this an invalid SQL statement.

Too bad sanitize_sql isn't available to us without hacking into the
source.

--
Sean W.
master nerd of
i heart squares, Co.

3711 N. Ravenswood Ave. #147 Chicago, IL 60613
Ph. (773) 531-6301  Fx. (773) 529-7041
http://www.iheartsquares.com
Sean W. (Guest)
on 2006-02-10 18:13
(Received via mailing list)
On 2/10/06, Sean W. <removed_email_address@domain.invalid> wrote:
> > Parameterized queries are a *good* thing.  The presence of the ? will result
> > in the next parameter being escaped and inserted where the ? is.
>
> I've never been able to get this kind of query to work in a
> parametrized :conditions argument. Every time I try to do this, the
> results would be
>
> "name like '%'John'%'"
>
> As you can see, it puts extra single quotes around the parameter,
> making this an invalid SQL statement.

But I guess the solution is something like this

:conditions => ["name like :criteria",  { :criteria => '%' <<
params[:query] << '%'} ]


--
Sean W.
master nerd of
i heart squares, Co.

3711 N. Ravenswood Ave. #147 Chicago, IL 60613
Ph. (773) 531-6301  Fx. (773) 529-7041
http://www.iheartsquares.com
Justin F. (Guest)
on 2006-02-11 15:54
(Received via mailing list)
Sean W. wrote:
>>> Parameterized queries are a *good* thing.  The presence of the ? will result
> But I guess the solution is something like this
>
> :conditions => ["name like :criteria",  { :criteria => '%' <<
> params[:query] << '%'} ]

I think so - this is working for me:

   def list
     filter = params[:filter]
     conds = nil
     if filter && !filter.blank?
       conds = ['name like ?', filter + '%']
     end

     @paginator, @items = paginate :pages,
                                   :per_page => 40,
                                   :conditions => conds
   end

regards

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