LIKE SQL queries in rails

Rails
1

I’m trying to do something like:

SELECT * FROM attachment WHERE filename LIKE ‘%whatever%’;

so my code is:

@search = params[:search]
@attachments = Attachment.find(:all, :conditions => [“filename LIKE
‘%?%’”, @search.to_s])

but that’s converting to:

SELECT * FROM attachments WHERE (filename LIKE ‘%‘whatever’%’);

how do i prevent those extra single quotes from being inserted?
thanks!
steve

2

Stephen Karsch wrote:

I’m trying to do something like:

SELECT * FROM attachment WHERE filename LIKE ‘%whatever%’;

so my code is:

@search = params[:search]
@attachments = Attachment.find(:all, :conditions => [“filename LIKE
‘%?%’”, @search.to_s])

Try
Attachment.find(:all, :conditions => [“filename LIKE ‘?’”, ‘%’ +
@search.to_s + ‘%’])

3

±le 11/12/2005 17:47 +0100, Andreas S. écrivait :
| Stephen Karsch wrote:
|> I’m trying to do something like:
|>
|> SELECT * FROM attachment WHERE filename LIKE ‘%whatever%’;
|>
|> so my code is:
|>
|> @search = params[:search]
|> @attachments = Attachment.find(:all, :conditions => [“filename LIKE
|> ‘%?%’”, @search.to_s])
|
| Try
| Attachment.find(:all, :conditions => [“filename LIKE ‘?’”, ‘%’ +
| @search.to_s + ‘%’])

‘…LIKE ?’

4

On Apr 17, 2006, at 6:47 AM, Tim P. wrote:

Dan that way leaves you open for SQL injection attacks :slight_smile:

using the [x=?], var way is much safer as rails protects you.

But…Dan is using placeholders.

Would it be different if he said:

@attachments = Attachment.find(:all, :conditions => [“filename
LIKE %?%”, @search}])

I don’t think that would work with true placeholders, but
it might work with Rails.

–
– Tom M.

5

On Apr 17, 2006, at 11:30 AM, Tom M. wrote:

@attachments = Attachment.find(:all, :conditions => [“filename
LIKE %?%”, @search}])

I don’t think that would work with true placeholders, but
it might work with Rails.

What about:

@attachments = Attachment.find(:all, :conditions => [“filename
LIKE ?”, ‘%’ + @search + ‘%’])

You like? (pun intended only sub-consciously)

-Derrick S.

6

On Apr 17, 2006, at 8:53 AM, Derrick S. wrote:

Would it be different if he said:
LIKE ?", ‘%’ + @search + ‘%’])

You like? (pun intended only sub-consciously)

Identical end result to this from above:

@attachments = Attachment.find(:all, :conditions => [“filename
LIKE ?”,
“%#{@search}%”])

Color me prejudiced, but I do prefer your version. Ruby string
interpolation
is painfully hard to look at IMHO.

–
– Tom M.

7

Dan F. wrote:

I’m not a rails expert but I think you could do:

@attachments = Attachment.find(:all, :conditions => [“filename LIKE ?”,
“%#{@search}%”])

Not sure if this is the best way but it should work.

Dan

Dan that way leaves you open for SQL injection attacks :slight_smile:

using the [x=?], var way is much safer as rails protects you.

Tim

8

I’m not a rails expert but I think you could do:

@attachments = Attachment.find(:all, :conditions => [“filename LIKE ?”,
“%#{@search}%”])

Not sure if this is the best way but it should work.

Dan

9

Mathieu A. wrote:

±le 11/12/2005 17:47 +0100, Andreas S. �crivait :
| Stephen Karsch wrote:
|> I’m trying to do something like:
|>
|> SELECT * FROM attachment WHERE filename LIKE ‘%whatever%’;
|>
|> so my code is:
|>
|> @search = params[:search]
|> @attachments = Attachment.find(:all, :conditions => [“filename LIKE
|> ‘%?%’”, @search.to_s])
|
| Try
| Attachment.find(:all, :conditions => [“filename LIKE ‘?’”, ‘%’ +
| @search.to_s + ‘%’])

‘…LIKE ?’

Yes, that’s what I meant.