Forum: Ruby on Rails :conditions => ... formatting

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.
457cf540784a12ba2f30e06565a2c189?d=identicon&s=25 hgs (Guest)
on 2005-11-23 20:44
(Received via mailing list)
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
F2a564bc440a0434fbd76354e4778b04?d=identicon&s=25 Joe Martinez (Guest)
on 2005-11-23 21:01
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
>
457cf540784a12ba2f30e06565a2c189?d=identicon&s=25 hgs (Guest)
on 2005-11-23 21:33
(Received via mailing list)
On Wed, 23 Nov 2005, Joe Martinez 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
24d2f8804e6bb4b7ea6bd11e0a586470?d=identicon&s=25 jeremy (Guest)
on 2005-11-23 22:01
(Received via mailing list)
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 23, 2005, at 12:30 PM, Hugh Sasse 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-----
457cf540784a12ba2f30e06565a2c189?d=identicon&s=25 hgs (Guest)
on 2005-11-23 23:22
(Received via mailing list)
On Wed, 23 Nov 2005, Jeremy Kemper 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
457cf540784a12ba2f30e06565a2c189?d=identicon&s=25 hgs (Guest)
on 2005-11-23 23:26
(Received via mailing list)
On Wed, 23 Nov 2005, Hugh Sasse 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
24d2f8804e6bb4b7ea6bd11e0a586470?d=identicon&s=25 jeremy (Guest)
on 2005-11-23 23:38
(Received via mailing list)
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 23, 2005, at 2:26 PM, Hugh Sasse 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-----
E88842bdfe00001492b724febb865865?d=identicon&s=25 jonte (Guest)
on 2005-11-24 07:41
(Received via mailing list)
Hugh Sasse 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
457cf540784a12ba2f30e06565a2c189?d=identicon&s=25 hgs (Guest)
on 2005-11-24 12:07
(Received via mailing list)
On Wed, 23 Nov 2005, Jeremy Kemper 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
457cf540784a12ba2f30e06565a2c189?d=identicon&s=25 hgs (Guest)
on 2005-11-24 14:29
(Received via mailing list)
On Wed, 23 Nov 2005, Jeremy Kemper wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Nov 23, 2005, at 2:26 PM, Hugh Sasse wrote:
> > On Wed, 23 Nov 2005, Hugh Sasse 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
24d2f8804e6bb4b7ea6bd11e0a586470?d=identicon&s=25 jeremy (Guest)
on 2005-11-24 20:56
(Received via mailing list)
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 24, 2005, at 5:29 AM, Hugh Sasse 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-----
457cf540784a12ba2f30e06565a2c189?d=identicon&s=25 hgs (Guest)
on 2005-11-24 21:21
(Received via mailing list)
On Thu, 24 Nov 2005, Jeremy Kemper 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
24d2f8804e6bb4b7ea6bd11e0a586470?d=identicon&s=25 jeremy (Guest)
on 2005-11-24 21:45
(Received via mailing list)
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 24, 2005, at 12:18 PM, Hugh Sasse 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-----
457cf540784a12ba2f30e06565a2c189?d=identicon&s=25 hgs (Guest)
on 2005-11-24 21:49
(Received via mailing list)
On Thu, 24 Nov 2005, Jeremy Kemper 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
24d2f8804e6bb4b7ea6bd11e0a586470?d=identicon&s=25 jeremy (Guest)
on 2005-11-24 21:53
(Received via mailing list)
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 24, 2005, at 12:47 PM, Hugh Sasse 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-----
457cf540784a12ba2f30e06565a2c189?d=identicon&s=25 hgs (Guest)
on 2005-11-24 22:01
(Received via mailing list)
On Thu, 24 Nov 2005, Jeremy Kemper wrote:

>   http://dev.rubyonrails.org/ticket/2710
OK, thanks.
>
> jeremy
        Hugh
This topic is locked and can not be replied to.