Forum: Ruby on Rails :conditions => ["phone like '%:phone%'" , {:phone => "555"}]

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.
B45eab4f18aa1bb2a44d6e657531a642?d=identicon&s=25 Alain Ravet (aravet)
on 2006-03-20 11:59
Hi all,


What's wrong with the way I specify the condition in :

     Person.find :all, :conditions => ["phone like '%:phone%'" , {:phone
=> "555"}]
?

It translates to (note the two ' between the two ")
    SELECT * FROM people WHERE (phone like "%'555'%" )

instead of
    SELECT * FROM people WHERE (phone like "%555%" )


Another way to produce this problem:

OK :
	model = "555"
        conditions =  "value like '%#{model}%'"

ERROR :
	model = "555"
        conditions =  ["value like '%?%'",model]    ==>  WHERE (phone
like "%'555'%" )

What's the write way to write this query?
TIA

Alain
5892e4bc15f96968740e8a6f70ccc88a?d=identicon&s=25 Loïc Guitaut (Guest)
on 2006-03-20 12:03
(Received via mailing list)
Le Lundi 20 Mars 2006 11:59, Alain Ravet a écrit :
> Hi all,
>
Hi,

Try this :
Person.find :all, :conditions => ["phone like ?", "%" + phone + "%"]

I think it should work :)
B45eab4f18aa1bb2a44d6e657531a642?d=identicon&s=25 Alain Ravet (aravet)
on 2006-03-20 12:24
Thanks Loïc, it worked.

Alain

   > Try this :
   > Person.find :all, :conditions => ["phone like ?", "%" + phone +
"%"]
1fba4539b6cafe2e60a2916fa184fc2f?d=identicon&s=25 unknown (Guest)
on 2006-03-20 14:54
(Received via mailing list)
Hi --

On Mon, 20 Mar 2006, Alain Ravet wrote:

>    SELECT * FROM people WHERE (phone like "%'555'%" )
>
> ERROR :
> 	model = "555"
>        conditions =  ["value like '%?%'",model]    ==>  WHERE (phone
> like "%'555'%" )
>
> What's the write way to write this query?

The way that works :-)  Actually you can do this:

   "value like ?", "%#{model}%"

or equivalent, but I'd rather do "value like '%#{model}%'" in the
first place (unless there's an advantage to the ? technique that I'm
not taking into account).


David

--
David A. Black (dblack@wobblini.net)
Ruby Power and Light, LLC (http://www.rubypowerandlight.com)

"Ruby for Rails" chapters now available
from Manning Early Access Program! http://www.manning.com/books/black
22ff4abebc9bfd299524bb14449d95dc?d=identicon&s=25 Erik van Oosten (Guest)
on 2006-03-20 15:07
(Received via mailing list)
You should always use the form

    "value like ?", "%#{model}%"

to prevent problems. Model might contains a question mark or perhaps
worse: quotes.

Even if you know model never to contain special characters it is better
to get used to the form above. Next time, model is a value entered by a
user, leaving your site open for sql code injection attacks.

Another advantage is performance. Some databases cache compiled queries.
When you put 'model' directly in the query, the query will be different
everytime making caching impossible.

     Erik.
1fba4539b6cafe2e60a2916fa184fc2f?d=identicon&s=25 unknown (Guest)
on 2006-03-20 15:16
(Received via mailing list)
Hi --

On Mon, 20 Mar 2006, Erik van Oosten wrote:

> You should always use the form
>
>   "value like ?", "%#{model}%"
>
> to prevent problems. Model might contains a question mark or perhaps worse:
> quotes.
>
> Even if you know model never to contain special characters it is better to
> get used to the form above. Next time, model is a value entered by a user,
> leaving your site open for sql code injection attacks.

Indeed -- I wasn't factoring in the escape mechanism.


David

--
David A. Black (dblack@wobblini.net)
Ruby Power and Light, LLC (http://www.rubypowerandlight.com)

"Ruby for Rails" chapters now available
from Manning Early Access Program! http://www.manning.com/books/black
This topic is locked and can not be replied to.