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.
D6c13e82448f753fde1a51d79e59d92c?d=identicon&s=25 Stephen Karsch (Guest)
on 2005-12-11 17: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
3a83969376c805ef5b6042191fdb0ff3?d=identicon&s=25 Andreas S. (andreas)
on 2005-12-11 17: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 + '%'])
2ad1c05e8fa25d4ca9dc218fa33d2d14?d=identicon&s=25 Mathieu Arnold (Guest)
on 2005-12-11 17:53
(Received via mailing list)
+-le 11/12/2005 17:47 +0100, Andreas Schwarz é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 ?'
3a83969376c805ef5b6042191fdb0ff3?d=identicon&s=25 Andreas S. (andreas)
on 2005-12-11 17:54
Mathieu Arnold wrote:
> +-le 11/12/2005 17:47 +0100, Andreas Schwarz �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.
637e16a0a94b259b10c3d3691c281dfe?d=identicon&s=25 Dan Fitzpatrick (Guest)
on 2005-12-12 02: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
A049c3597983fdaaa2af2b0010c49abc?d=identicon&s=25 Tim Perrett (timperrett)
on 2006-04-17 15:47
Dan Fitzpatrick 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
59de94a56fd2c198f33d9515d1c05961?d=identicon&s=25 Tom Mornini (Guest)
on 2006-04-17 17:33
(Received via mailing list)
On Apr 17, 2006, at 6:47 AM, Tim Perrett 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 Mornini
Bc80625db60e9db4394c51d6c1892b49?d=identicon&s=25 Derrick Spell (Guest)
on 2006-04-17 17:54
(Received via mailing list)
On Apr 17, 2006, at 11:30 AM, Tom Mornini 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 Spell
59de94a56fd2c198f33d9515d1c05961?d=identicon&s=25 Tom Mornini (Guest)
on 2006-04-17 20:14
(Received via mailing list)
On Apr 17, 2006, at 8:53 AM, Derrick Spell 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 Mornini
This topic is locked and can not be replied to.