Single quotes in parameters

Rails
1

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?

2

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
3

On 1/20/06, Matthew P. [email protected] 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

4

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

5

On 2/10/06, Sean W. [email protected] 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