Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rails Prepared Statement with select_all

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

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 sadly, this is not working at all. whatever format I try to use, the $1 and $2 are never replaced with the corresponding values from the bind array.

Is there anything more i should take care of?

like image 202
Christoph Brosdau Avatar asked Oct 06 '12 01:10

Christoph Brosdau


2 Answers

You should use sanitize_sql_array in your model, like this:

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 injections.

like image 89
PatrickNLT Avatar answered Oct 20 '22 21:10

PatrickNLT


Since you are not using named binds, you would 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']]
)
like image 30
Urkle Avatar answered Oct 20 '22 21:10

Urkle