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
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 >-- Posted via http://www.ruby-forum.com/.
On Wed, 23 Nov 2005, Joe Martinez wrote:> Well in your second conditions clause you have ''condiwions'' instead ofOops! ... 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> 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 > > > > > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Nov 23, 2005, at 12:30 PM, Hugh Sasse wrote:> 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 formStudent.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-----
On Wed, 23 Nov 2005, Jeremy Kemper wrote:> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Nov 23, 2005, at 12:30 PM, Hugh Sasse wrote: > > 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 > > > 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.> > jeremy >Hugh
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
-----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}%" }]) >>> >>> 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.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-----
Hugh Sasse wrote:> 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 > > >> 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 >>> >>> >> >> -- >> Posted via http://www.ruby-forum.com/. >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> >> > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > >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 _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
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: > > 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.Ah, I see. Thank you, that''s what I needed.> > jeremyHugh
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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Nov 24, 2005, at 5:29 AM, Hugh Sasse wrote:> On Wed, 23 Nov 2005, Jeremy Kemper wrote: >> 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:Did you try using Student.find(:first, ...)> 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 1That''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-----
On Thu, 24 Nov 2005, Jeremy Kemper wrote:> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Nov 24, 2005, at 5:29 AM, Hugh Sasse wrote: > > On Wed, 23 Nov 2005, Jeremy Kemper wrote: > > > 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: > > Did you try using Student.find(:first, ...)Yes, afterwards, by which time I''d written it the WRONG way just to make some pogress.> > > > 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 > > 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.> > jeremyThank you, Hugh
-----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-----
On Thu, 24 Nov 2005, Jeremy Kemper wrote:> -----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.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.> > jeremyThank you, Hugh
-----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-----
On Thu, 24 Nov 2005, Jeremy Kemper wrote:> -----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 > > 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/2710OK, thanks.> > jeremyHugh