Wilcard search

Rails
1

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

2

mich,

On Mon, 2006-03-13 at 15:15 +0100, mich wrote:

                :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

3

Brandon K. 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 :wink:

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

render :action => ‘index’

Cheers !

/mich

4

mich-4 wrote:

and it works. However I’d like confirmation, that it is the correct
way, and that it is sql-injection safe :wink:

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.

5

On Mar 14, 2006, at 2:16 AM, mich wrote:

[…] 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 Z.
Yakima Herald-Republic
WebMaster

509-577-7732
[email protected]

6

Ezra Z. wrote:

Do it like this:

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

Muy nice !

I’m assuming this is sql-injection safe !

/mich

7

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

8

mich-4 wrote:

Ezra Z. 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.