Native sql query with array-like, prepared statement

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?!

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 from model where =?”,"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.

> > > 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!