:conditions => ["phone like '%:phone%'" , {:phone => "555"}]


#1

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


#2

Le Lundi 20 Mars 2006 11:59, Alain R. a écrit :

Hi all,

Hi,

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

I think it should work :slight_smile:


#3

Thanks Loïc, it worked.

Alain

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


#4

Hi –

On Mon, 20 Mar 2006, Alain R. 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 :slight_smile: 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 (removed_email_address@domain.invalid)
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


#5

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 (removed_email_address@domain.invalid)
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


#6

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.