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.
D9d1b31a337f46ac5e4dd56b55404f58?d=identicon&s=25 Dylan Markow (Guest)
on 2006-01-21 06: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?
55428cbf149e35dd4b65f1d019d04139?d=identicon&s=25 Matthew Palmer (Guest)
on 2006-01-21 06:55
(Received via mailing list)
On Fri, Jan 20, 2006 at 08:57:39PM -0800, Dylan Markow 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
8f15d89f469605e9e07a8b5ce5d3d5d1?d=identicon&s=25 Sean Wolfe (Guest)
on 2006-02-10 17:02
(Received via mailing list)
On 1/20/06, Matthew Palmer <mpalmer@hezmatt.org> 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 Wolfe
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
8f15d89f469605e9e07a8b5ce5d3d5d1?d=identicon&s=25 Sean Wolfe (Guest)
on 2006-02-10 17:13
(Received via mailing list)
On 2/10/06, Sean Wolfe <sean@iheartsquares.com> 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 Wolfe
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
Eea7ad39737b0dbf3de38874e0a6c7d8?d=identicon&s=25 Justin Forder (Guest)
on 2006-02-11 14:54
(Received via mailing list)
Sean Wolfe 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.