Hi, I''m using in my project ActiveRecord::Base.connection.select_value and ActiveRecord::Base.connection.select_rows several times to execute complex queries. This basically works, but I need to check the params in order to avoid sql-injection by myself. Even worse I''ve to handle basic datatype conversions, e.g. choose 0/1 or ''t''/''f'' as appropiate boolean for sqlite or MySQL. Is there any way to execute a model-unrelated sql-query by passing an array (["select <whatever> from model where <something>=?","nice- value"]) instead of a string?!
Gregory Mazurek
2009-Oct-30 14:00 UTC
Re: Native sql query with array-like, prepared statement
On Fri, Oct 30, 2009 at 3:57 AM, neomizer <hthpsycho-Mmb7MZpHnFY@public.gmane.org> wrote:> > Hi, > > I''m using in my project ActiveRecord::Base.connection.select_value and > ActiveRecord::Base.connection.select_rows several times to execute > complex queries. This basically works, but I need to check the params > in order to avoid sql-injection by myself. Even worse I''ve to handle > basic datatype conversions, e.g. choose 0/1 or ''t''/''f'' as appropiate > boolean for sqlite or MySQL. > > Is there any way to execute a model-unrelated sql-query by passing an > array (["select <whatever> from model where <something>=?","nice- > value"]) instead of a string?! >Why can''t you use find_by_sql instead of ActiveRecord::Base.connection.select_rows? You can start with one Model and query from another. For example, Apple.find_by_sql(["SELECT apples.flavor as apple_flavor, bananas.flavor as banana_flavor FROM apples, bananas WHERE....", something])> >-- www.abetaday.com --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 30 Okt., 15:00, Gregory Mazurek <gregory.mazu...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Fri, Oct 30, 2009 at 3:57 AM, neomizer <hthpsy...-Mmb7MZpHnFY@public.gmane.org> wrote: > > > Hi, > > > I''m using in my project ActiveRecord::Base.connection.select_value and > > ActiveRecord::Base.connection.select_rows several times to execute > > complex queries. This basically works, but I need to check the params > > in order to avoid sql-injection by myself. Even worse I''ve to handle > > basic datatype conversions, e.g. choose 0/1 or ''t''/''f'' as appropiate > > boolean for sqlite or MySQL. > > > Is there any way to execute a model-unrelated sql-query by passing an > > array (["select <whatever> from model where <something>=?","nice- > > value"]) instead of a string?! > > Why can''t you use find_by_sql instead > of ActiveRecord::Base.connection.select_rows? You can start with one Model > and query from another. For example, Apple.find_by_sql(["SELECT > apples.flavor as apple_flavor, bananas.flavor as banana_flavor FROM apples, > bananas WHERE....", something])Because the values I want to retrieve are not part of any of my models (e.g. aggregated stats). IIRC find_by_sql can only return instances of the related model, therefore in the sql-statement I need to select existing model-attributes, which is not possible in my case.
Gregory Mazurek
2009-Oct-30 16:34 UTC
Re: Native sql query with array-like, prepared statement
On Fri, Oct 30, 2009 at 12:10 PM, neomizer <hthpsycho-Mmb7MZpHnFY@public.gmane.org> wrote:> > > > On 30 Okt., 15:00, Gregory Mazurek <gregory.mazu...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > On Fri, Oct 30, 2009 at 3:57 AM, neomizer <hthpsy...-Mmb7MZpHnFY@public.gmane.org> wrote: > > > > > Hi, > > > > > I''m using in my project ActiveRecord::Base.connection.select_value and > > > ActiveRecord::Base.connection.select_rows several times to execute > > > complex queries. This basically works, but I need to check the params > > > in order to avoid sql-injection by myself. Even worse I''ve to handle > > > basic datatype conversions, e.g. choose 0/1 or ''t''/''f'' as appropiate > > > boolean for sqlite or MySQL. > > > > > Is there any way to execute a model-unrelated sql-query by passing an > > > array (["select <whatever> from model where <something>=?","nice- > > > value"]) instead of a string?! > > > > Why can''t you use find_by_sql instead > > of ActiveRecord::Base.connection.select_rows? You can start with one > Model > > and query from another. For example, Apple.find_by_sql(["SELECT > > apples.flavor as apple_flavor, bananas.flavor as banana_flavor FROM > apples, > > bananas WHERE....", something]) > > Because the values I want to retrieve are not part of any of my models > (e.g. aggregated stats). IIRC find_by_sql can only return instances of > the related model, therefore in the sql-statement I need to select > existing model-attributes, which is not possible in my case.You should be able to query any of the tables in your db *_production whether it has a corresponding model or not. In the trite example I cited above, bananas could be a table in your db and not have a model dedicated to it. It''s not good practice. Just because you do Apple.find_by_sql doesn''t mean all your results have to be returned from the columns of an Apple table. -- www.abetaday.com --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Marnen Laibow-Koser
2009-Oct-30 17:17 UTC
Re: Native sql query with array-like, prepared statement
neomizer wrote:> On 30 Okt., 15:00, Gregory Mazurek <gregory.mazu...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> >> > Is there any way to execute a model-unrelated sql-query by passing an >> > array (["select <whatever> from model where <something>=?","nice- >> > value"]) instead of a string?! >> >> Why can''t you use find_by_sql instead >> of ActiveRecord::Base.connection.select_rows? �You can start with one Model >> and query from another. �For example, Apple.find_by_sql(["SELECT >> apples.flavor as apple_flavor, bananas.flavor as banana_flavor FROM apples, >> bananas WHERE....", something]) > > Because the values I want to retrieve are not part of any of my models > (e.g. aggregated stats). IIRC find_by_sql can only return instances of > the related model, therefore in the sql-statement I need to select > existing model-attributes, which is not possible in my case.Well, you can use the Calculations module for aggregate functions. But as a matter of application design, it''s probably best to declare an ActiveRecord model for any table your Rails app needs to know about. Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
On 30 Okt., 17:34, Gregory Mazurek <gregory.mazu...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Fri, Oct 30, 2009 at 12:10 PM, neomizer <hthpsy...-Mmb7MZpHnFY@public.gmane.org> wrote: > > > On 30 Okt., 15:00, Gregory Mazurek <gregory.mazu...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > On Fri, Oct 30, 2009 at 3:57 AM, neomizer <hthpsy...-Mmb7MZpHnFY@public.gmane.org> wrote: > > > > > Hi, > > > > > I''m using in my project ActiveRecord::Base.connection.select_value and > > > > ActiveRecord::Base.connection.select_rows several times to execute > > > > complex queries. This basically works, but I need to check the params > > > > in order to avoid sql-injection by myself. Even worse I''ve to handle > > > > basic datatype conversions, e.g. choose 0/1 or ''t''/''f'' as appropiate > > > > boolean for sqlite or MySQL. > > > > > Is there any way to execute a model-unrelated sql-query by passing an > > > > array (["select <whatever> from model where <something>=?","nice- > > > > value"]) instead of a string?! > > > > Why can''t you use find_by_sql instead > > > of ActiveRecord::Base.connection.select_rows? You can start with one > > Model > > > and query from another. For example, Apple.find_by_sql(["SELECT > > > apples.flavor as apple_flavor, bananas.flavor as banana_flavor FROM > > apples, > > > bananas WHERE....", something]) > > > Because the values I want to retrieve are not part of any of my models > > (e.g. aggregated stats). IIRC find_by_sql can only return instances of > > the related model, therefore in the sql-statement I need to select > > existing model-attributes, which is not possible in my case. > > You should be able to query any of the tables in your db *_production > whether it has a corresponding model or not. In the trite example I cited > above, bananas could be a table in your db and not have a model dedicated to > it. It''s not good practice. Just because you do Apple.find_by_sql doesn''t > mean all your results have to be returned from the columns of an Apple > table.Interesting, I tried that before with something similar to the following (the countries table has a column name, the orders table doesn''t):>> Order.find_by_sql("select name from countries")=> [#<Order >, #<Order >] which looks like two not fully instantiated Order objects to me. But you''re totally right, I can actually call name on the resulting objects:>> Order.find_by_sql("select name from countries").first.name=> "Germany" Apart from being a bit confused about the behaviour, it solves my problem. Thx Gregory!