Rails - Mar 2006 - Wilcard search

Hello,

I''m writing a search function for my application, but I am unsure on
how
to search for all results that contain my search string; here''s what 
I''ve got:

    def search
        @products = Product.find(:all,
                    :conditions => "date_available < now()",
                    :conditions => [ "title ilike ?",
@params[:search]],
                    :order => "title desc")
    end

It works, but only if I provide a full match to the title - how can I do 
"title ilike ''%''?''%'' ?

Also, is this method sql-injection safe ?

Last, but not least - how can I make the search method print output to 
the index template ?

Many thanks,

/mich

-- 
Posted via http://www.ruby-forum.com/.

mich,

On Mon, 2006-03-13 at 15:15 +0100, mich wrote:
> Hello,
> 
> I''m writing a search function for my application, but I am unsure
on how
> to search for all results that contain my search string; here''s
what
> I''ve got:
> 
>     def search
>         @products = Product.find(:all,
>                     :conditions => "date_available <
now()",
>                     :conditions => [ "title ilike ?",
@params[:search]],
>                     :order => "title desc")
>     end
> 
> It works, but only if I provide a full match to the title - how can I do 
> "title ilike ''%''?''%'' ?
My solution to this was to append %s to the parameter:

    def search
        @products = Product.find(:all,
                    :conditions => "date_available < now()",
                    :conditions => [ "title ilike ?",
@params[:search] + ''%s'' ],
                    :order => "title desc")
    end

I''ve got no idea if that is the correct solution, but it worked for me.

> Also, is this method sql-injection safe ?
I believe using ? and passing in your params makes it sql-injection
safe.
> Last, but not least - how can I make the search method print output to 
> the index template ?
render :action => ''index''

> Many thanks,
> 
> /mich
Brandon

Brandon Keepers wrote:
> My solution to this was to append %s to the parameter:
>                     :conditions => [ "title ilike ?",
@params[:search] +
> ''%s'' ],
I ended up with the following:

:conditions => [ "title ilike ?", ''%'' +
@params[:search] + ''%'' ]

and it works. However I''d like confirmation, that it is the *correct* 
way, and that it is sql-injection safe ;)
>> Last, but not least - how can I make the search method print output to 
>> the index template ?
> 
> render :action => ''index''
> 
Cheers !


/mich

-- 
Posted via http://www.ruby-forum.com/.

mich-4 wrote:
> 
> Brandon Keepers wrote:
>> My solution to this was to append %s to the parameter:
>>                     :conditions => [ "title ilike ?",
@params[:search] +
>> ''%s'' ],
> 
> I ended up with the following:
> 
> :conditions => [ "title ilike ?", ''%'' +
@params[:search] + ''%'' ]
> 
> and it works. However I''d like confirmation, that it is the
*correct*
> way, and that it is sql-injection safe ;)
Hmm.. I''d go for something like this instead: ":conditions => [
"title ilike
%?%", @params[:search] ]".

Trying to fudge parts of the query into your parameter string isn''t too
neat. Not 100% sure my suggestion works, but they way you''re doing it
_shouldn''t_ work imho..

--
View this message in context:
http://www.nabble.com/Wilcard-search-t1272697.html#a3383067
Sent from the RubyOnRails Users forum at Nabble.com.

Lucifron wrote:
> Hmm.. I''d go for something like this instead: ":conditions
=> [ "title
> ilike
> %?%", @params[:search] ]".
That won''t work !

It is being parsed, like this:
[...] WHERE (title ilike %''1720''% and [...]
> 
> Trying to fudge parts of the query into your parameter string
isn''t too
> neat. Not 100% sure my suggestion works, but they way you''re doing
it
> _shouldn''t_ work imho..
Well, I agree - but I do not see any other way of doing it !

/mich



-- 
Posted via http://www.ruby-forum.com/.

On Mar 14, 2006, at 2:16 AM, mich wrote:
> Lucifron wrote:
>
>> Hmm.. I''d go for something like this instead:
":conditions =>
>> [ "title
>> ilike
>> %?%", @params[:search] ]".
>
> That won''t work !
>
> It is being parsed, like this:
> [...] WHERE (title ilike %''1720''% and [...]
>
>>
>> Trying to fudge parts of the query into your parameter string  
>> isn''t too
>> neat. Not 100% sure my suggestion works, but they way you''re
doing it
>> _shouldn''t_ work imho..
>
> Well, I agree - but I do not see any other way of doing it !
>
> /mich


Do it like this:

  :conditions => ["title LIKE ?", "%#{params[:search]}%"]


Cheers-
-Ezra Zygmuntowicz
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
ezra@yakima-herald.com

Ezra Zygmuntowicz wrote:
> 
> Do it like this:
> 
>   :conditions => ["title LIKE ?",
"%#{params[:search]}%"]
> 
Muy nice  !

I''m assuming this is sql-injection safe !

/mich

-- 
Posted via http://www.ruby-forum.com/.

mich-4 wrote:
> 
> Ezra Zygmuntowicz wrote:
>> 
>> Do it like this:
>> 
>>   :conditions => ["title LIKE ?",
"%#{params[:search]}%"]
>> 
> Muy nice  !
> 
> I''m assuming this is sql-injection safe !
"abc#{some_variable}def" is basically another way to write
"abc" +
some_variable.to_s + "def". It''s plain ruby string handling,
nothing sql
related.

Use the [querystring, *params] approach with ''?'' as a
placeholder for unsafe
stuff, and active record should keep you safe from sql injection, yes.

Not sure what i think about wildcards not being escaped, but I figure this
has it''s advantages as well..
--
View this message in context:
http://www.nabble.com/Wilcard-search-t1272697.html#a3416491
Sent from the RubyOnRails Users forum at Nabble.com.