:conditions => ... formatting


#1

With code like:

  if @params[:pnumber] =~ /\s*p?(\d+)\s*/
    if student = Student.find_first(:conditions => ["pnumber like 

“:pnumber%”",
{:pnumber => $1}])
@borrower = student
elsif emp = Employee.find_first(:condiwions => [“pnumber like
“?””,
{:pnumber => $1}])
@borrower = emp
else
@flash[:note] += “Nobody matching #{@params[:pnumber]}”
end
end

in particular, the second if, then why would I get errors like:

You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near
‘pnumber1234412341) LIMIT 1’ at line 1: SELECT * FROM students WHERE
(conditionspnumber like ":pnumber%"pnumber1234412341) LIMIT 1

which suggests that :pnumber is not being substituted, as per page
214 of “Agile web Development with Rails”, even though it is in a
position where it would match /\b:pnumber\b/ – i.e. there can’t be
ambiguity about what it is [?].

Otherwise, how does one setup ‘select * where :this is like “that%”’
or even ‘like “%:that%”’ in a query?

Incidentally, I’m using MySQL 4.1.13 with Rails 0.14.3

    Thank you,
    Hugh

#2

Well in your second conditions clause you have ‘condiwions’ instead of
conditions.

hgs wrote:

With code like:

  if @params[:pnumber] =~ /\s*p?(\d+)\s*/
    if student = Student.find_first(:conditions => ["pnumber like 

“:pnumber%”",
{:pnumber => $1}])
@borrower = student
elsif emp = Employee.find_first(:condiwions => [“pnumber like
“?””,
{:pnumber => $1}])
@borrower = emp
else
@flash[:note] += “Nobody matching #{@params[:pnumber]}”
end
end


#3

On Wed, 23 Nov 2005, Joe M.ez wrote:

Well in your second conditions clause you have ‘condiwions’ instead of

Oops! … Fixed. But I’m still getting the error

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ‘pnumber1234412341) LIMIT 1’ at line 1: SELECT * FROM students
WHERE (conditionspnumber like ":pnumber%"pnumber1234412341) LIMIT 1

Changing to single quotes (’) didn’t help – the error message takes
the same form

    Hugh

#4

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 23, 2005, at 12:30 PM, Hugh S. wrote:

WHERE (conditionspnumber like ":pnumber%"pnumber1234412341) LIMIT 1

Changing to single quotes (’) didn’t help – the error message takes
the same form

Student.find(:first, :conditions => [‘pnumber like :pnumber’,
{ :pnumber => “%#{$1}%” }])

Employee.find(:first, :conditions => [‘pnumber like ?’, “%#{$1}%”])

jeremy

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDhNhRAQHALep9HFYRAqI0AJ9uZyJKWbpaosQ81fNskoX2O+MSKgCgzNIk
Kz6KF8J1mf0u0NhYvVYPh3s=
=Eb8t
-----END PGP SIGNATURE-----


#5

On Wed, 23 Nov 2005, Jeremy K. wrote:

You have an error in your SQL syntax; check the manual that

Employee.find(:first, :conditions => [‘pnumber like ?’, “%#{$1}%”])

The book says specifically NOT to do that because of SQL injection
attacks.

jeremy

    Hugh

#6

On Wed, 23 Nov 2005, Hugh S. wrote:

Student.find(:first, :conditions => [‘pnumber like :pnumber’, { :pnumber =>
“%#{$1}%” }])

Employee.find(:first, :conditions => [‘pnumber like ?’, “%#{$1}%”])

The book says specifically NOT to do that because of SQL injection
attacks.

I was too quick with that!

No, that’s somewhat different. I’m not too clear on what advantage
is imparted by this form over the straight expansion of #{…} in
the query string itself, and whether that advantage still applies
when the expansion is done in the second arrray element. I will
have to look at the code I suppose.

jeremy

    Thank you,
    Hugh

#7

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 23, 2005, at 2:26 PM, Hugh S. wrote:

I was too quick with that!

No, that’s somewhat different. I’m not too clear on what advantage
is imparted by this form over the straight expansion of #{…} in
the query string itself, and whether that advantage still applies
when the expansion is done in the second arrray element. I will
have to look at the code I suppose.

The substituted parameter is quoted. You want the % surrounding the
parameter before it’s quoted. Therefore, add the % to the parameter
before binding it to :conditions.

jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDhO6cAQHALep9HFYRAlESAJ4yEyapRRL8lQ3aILgS8g80eMqzaQCZAfpK
rrsQ4+3c1qcL337EkehZsX4=
=K3u8
-----END PGP SIGNATURE-----


#8

Hugh S. wrote:

near ‘pnumber1234412341) LIMIT 1’ at line 1: SELECT * FROM students

hgs wrote:

{:pnumber => $1}])


Try this instead: You are escape’ing the ? that are to be replaced by
the value.

emp = Employee.find_first(:conditions => [“pnumber like ?”,{:pnumber =>
$1} ]

// JoNtE


#9

On Wed, 23 Nov 2005, Jeremy K. wrote:

have to look at the code I suppose.

The substituted parameter is quoted. You want the % surrounding the parameter
before it’s quoted. Therefore, add the % to the parameter before binding it
to :conditions.

Ah, I see. Thank you, that’s what I needed.

jeremy

    Hugh

#10

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 24, 2005, at 5:29 AM, Hugh S. wrote:

That’s the form I went with:
Did you try using Student.find(:first, …)

near ‘?%31313412%) LIMIT 1’ at line 1: SELECT * FROM students WHERE
(conditionspnumber like ?%31313412%) LIMIT 1

That’s because Student.find_first expects a string for conditions.

Use Student.find(:first, :conditions => …)

jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDhho7AQHALep9HFYRAp7bAJ9KmZ/9VOghjVAkNuQ1LkZ7S6E7zQCgjFuQ
XSOwldgKf2mATZyoZfaP3dM=
=AqQr
-----END PGP SIGNATURE-----


#11

On Wed, 23 Nov 2005, Jeremy K. wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 23, 2005, at 2:26 PM, Hugh S. wrote:

On Wed, 23 Nov 2005, Hugh S. wrote:

Student.find(:first, :conditions => [‘pnumber like :pnumber’, { :pnumber
=>
“%#{$1}%” }])

That’s the form I went with:

  if @params[:pnumber] =~ /\s*p?(\d+)\s*/
    if student = Student.find_first(:conditions => ["pnumber like 

?", “%#{$1}%”])
@borrower = student
[…]

Now I get:

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ‘?%31313412%) LIMIT 1’ at line 1: SELECT * FROM students WHERE
(conditionspnumber like ?%31313412%) LIMIT 1

So I change it to this

  if @params[:pnumber] =~ /\s*p?(\d+)\s*/
    if student = Student.find_first(:conditions => ["pnumber like 

:pn", {:pn =>"%#{$1}%"}])
@borrower = student

and I get

You have an error in your SQL syntax; check the manual that corresponds
to your
MySQL server version for the right syntax to use near ‘:pnpn%31313412%)
LIMIT
1’ at line 1: SELECT * FROM students WHERE (conditionspnumber like
:pnpn%31313412%) LIMIT 1

This doesn’t make any sense to me. It seems to be leaving the first
operand of
the substitution in the query. This is still in development mode, I
should
(perhaps) add w/ rails 0.14.3, ruby 1.8.2, Solaris9.

    Thank you,
    Hugh

#12

On Thu, 24 Nov 2005, Jeremy K. wrote:

=>
“%#{$1}%” }])

That’s the form I went with:

Did you try using Student.find(:first, …)

Yes, afterwards, by which time I’d written it the WRONG way just to
make some pogress.

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ‘?%31313412%) LIMIT 1’ at line 1: SELECT * FROM students WHERE
(conditionspnumber like ?%31313412%) LIMIT 1

That’s because Student.find_first expects a string for conditions.

Why? I thought the whole point of find_first(…) was that it was a
shorthand, mentally at least, for find(:first,…). IMHO if that is
not true, and the method will only accept a string, then it should
complain bitterly about getting an array.

OTOH, maybe there is something about this difference that I don’t
know which justifies this design.

Use Student.find(:first, :conditions => …)

That worked. I changed the logic a bit (as I get towards doing what I
need done):

  if @params[:pnumber] =~ /\s*p?(\d+)\s*/
    student = Student.find(:first, :conditions => ["pnumber like 

:pn", {:pn =>"%%#{$1}%%"}])
if student.nil?
emp = Employee.find(:first,:conditions => [“pnumber like :pn”,
{:pn =>"%%#{$1}%%"}])
if emp.nil?
flash[:borrower_error] = “No student or staff with pnumber
#{$1}”

Note, I do need the double % signs for it to work correctly.

jeremy

    Thank you,
    Hugh

#13

On Thu, 24 Nov 2005, Jeremy K. wrote:

know which justifies this design.

find_first is the old, deprecated method. It is not a shorthand.

Ah, that would be it.

It should complain loudly; sorry.

OK, next question: Is there a std way to reference threads on the
rails list given that unlike all the other ruby lists it doesn’t
have a /Message-count: \d+/ header? I’m wondering how to pass this
whole topic over to dev.rails… for the bug tracker.

jeremy

Thank you,
Hugh


#14

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 24, 2005, at 12:47 PM, Hugh S. wrote:

OK, next question: Is there a std way to reference threads on the
rails list given that unlike all the other ruby lists it doesn’t
have a /Message-count: \d+/ header? I’m wondering how to pass this
whole topic over to dev.rails… for the bug tracker.

I use the links from gmane:
http://news.gmane.org/gmane.comp.lang.ruby.rails

There is already a ticket re. deprecated method warnings:
http://dev.rubyonrails.org/ticket/2710

jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDhigxAQHALep9HFYRAghXAJwJcDVeyuUe/83rA3TrtXrlo/jkZQCgxRtm
xt/WcH9Rq0x4ZVdUIMqkPJg=
=0upN
-----END PGP SIGNATURE-----


#15

On Thu, 24 Nov 2005, Jeremy K. wrote:

http://dev.rubyonrails.org/ticket/2710
OK, thanks.

jeremy
Hugh


#16

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 24, 2005, at 12:18 PM, Hugh S. wrote:

Why? I thought the whole point of find_first(…) was that it was a
shorthand, mentally at least, for find(:first,…). IMHO if that is
not true, and the method will only accept a string, then it should
complain bitterly about getting an array.

OTOH, maybe there is something about this difference that I don’t
know which justifies this design.

find_first is the old, deprecated method. It is not a shorthand.

It should complain loudly; sorry.

jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDhiWWAQHALep9HFYRAtFEAKCajZ4Vk0IWkKBMw6KcQzWFP0YkEgCfYmtn
VnjE1aNn3IyBUnLckdktRJE=
=xTuM
-----END PGP SIGNATURE-----