Forum: Ruby on Rails LIKE SQL queries in rails

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Stephen Karsch (Guest)
on 2005-12-11 18:46
(Received via mailing list)
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
Andreas S. (Guest)
on 2005-12-11 18:47
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 + '%'])
Mathieu A. (Guest)
on 2005-12-11 18:53
(Received via mailing list)
+-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 ?'
Andreas S. (Guest)
on 2005-12-11 18:54
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.
Dan F. (Guest)
on 2005-12-12 03:57
(Received via mailing list)
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
Tim P. (Guest)
on 2006-04-17 17:47
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 :)

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

Tim
Tom M. (Guest)
on 2006-04-17 19:33
(Received via mailing list)
On Apr 17, 2006, at 6:47 AM, Tim P. wrote:

>
> Dan that way leaves you open for SQL injection attacks :)
>
> 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.
Derrick S. (Guest)
on 2006-04-17 19:54
(Received via mailing list)
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.
Tom M. (Guest)
on 2006-04-17 22:14
(Received via mailing list)
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.
This topic is locked and can not be replied to.