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?
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
On 1/20/06, Matthew Palmer <mpalmer@hezmatt.org> wrote:> 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.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
On 2/10/06, Sean Wolfe <sean@iheartsquares.com> wrote:> On 1/20/06, Matthew Palmer <mpalmer@hezmatt.org> wrote: > > 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. > > 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
Sean Wolfe wrote:> On 2/10/06, Sean Wolfe <sean@iheartsquares.com> wrote: >> On 1/20/06, Matthew Palmer <mpalmer@hezmatt.org> wrote: >>> 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. >> 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] << ''%''} ]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