Prepared Rails Report with select_all

Question

As far as I know, in Rails it should be possible to do the following:

ActiveRecord::Base.connection.select_all("SELECT MONTH(created) AS month, YEAR(created) AS year FROM orders WHERE created>=$1 AND created<=$2 GROUP BY month ORDER BY month ASC",nil,[['created',1],['created',2]]) 

but unfortunately this doesn't work at all. in any format I'm trying to use, $1 and $2 never replaced with the corresponding values ​​from the bind array.

Is there anything else I should take care of?

Answer 1

You should use sanitize_sql_array in your model, for example:

 r = self.sanitize_sql_array(["SELECT MONTH(created) AS month, YEAR(created) AS year FROM orders WHERE created>=? AND created<=? GROUP BY month ORDER BY month ASC", created1, created2]) self.connection.select_all r 

This protects you from SQL injection.

Answer 2

Since you are not using named bindings, you will do it like this. This works in Rails 4.2.

 ActiveRecord::Base.connection.select_all( "SELECT MONTH(created) AS month, YEAR(created) AS year FROM orders WHERE created>=$1 AND created<=$2 GROUP BY month ORDER BY month ASC", nil, [[nil,'2016-01-01 12:30'],[nil,'2016-01-01 15:30']] ) 

Answer 3

I don’t understand if you are trying to use variables, but yes, it is quite easy to do with variables, you used them incorrectly

Use it as follows:

 ActiveRecord::Base.connection.select_all("SELECT MONTH(created) AS month, YEAR(created) AS year FROM orders WHERE created>=#{v1} AND created<=#{v2} GROUP BY month ORDER BY month ASC",nil,[['created',1],['created',2]]) 

Where v1 and v2 are variables. Let me know if you are trying something else

thanks