Passing date parameters to a sql select query in rails (QueryMethods)

Question

I am working with the brakeman gem (which identifies possible security issues in Ruby on Rails code). We have a scope which uses joins, group and select and I need to update the select part of the query. This scope looks something like the below. I am putting content of the scope here on product model.

Product.joins('LEFT JOIN orders ON orders.product_id = products.id')
  .group('products.id')
  .select(
    "SUM(CASE WHEN orders.order_at BETWEEN '#{start_date}' AND '#{end_date}'" \
    " THEN orders.qty ELSE 0 END) as qty, products.*"
  )

Now If i try to change query like this

Product.joins('LEFT JOIN orders ON orders.product_id = products.id')
  .group('products.id')
  .select(
    "SUM(CASE WHEN orders.order_at BETWEEN ? AND ?" \
    " THEN orders.qty ELSE 0 END) as qty, products.*",
    '#{start_date}',
    '#{end_date}'
  )

It gives me syntax error where I have ? I have also tried some other ways with group and having but it didn't worked for me. I am using postgres sql with rails 4.1.8
Is their any way I can achieve this? Thanks in advance.

This is the error

PG::SyntaxError: ERROR: syntax error at or near "?"
LINE 1: SELECT SUM(CASE WHEN orders.order_at BETWEEN ? AND ? AND ord

There's no enough info. Also here there's a similar unattended question. — Sebastián Palma
– Sebastián Palma, Commented Nov 29, 2017 at 3:59

daino3 · Accepted Answer · 2017-11-29 04:20:12Z

1

I believe you can also get rid of the CASE statement with a where clause:

checkout range conditions

Product
  .joins('LEFT JOIN orders ON orders.product_id = products.id')
  .where(orders: {order_at: start_date..end_date})   # range condition
  .group('products.id')
  .select("SUM(orders.qty) as qty, products.*")

answered Nov 29, 2017 at 4:20

daino3

4,58640 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Grant Neufeld · Accepted Answer · 2017-11-29 05:14:39Z

0

You’re right to use the ? syntax, but your call to select needs part of it split out into a call to where intead.

Also, you’re converting your dates to strings (e.g., '#{start_date}'). That prevents the called method from being able to handle, and format, the values as dates. Instead, just pass the raw dates (e.g., start_date).

Product
  .joins('LEFT JOIN orders ON orders.product_id = products.id')
  .where('orders.order_at BETWEEN ? AND ?', start_date, end_date)
  .select("SUM(orders.qty) as qty, products.*")
  .group('products.id')

You could also redo the where call based on daino3’s answer (.where(orders: {order_at: start_date..end_date})).

Reasoning

It’s critical to not embed parameters directly in where (or related) calls. Doing so opens your code up to major security risks, known as SQL Injection because a remote user could potentially put any arbitrary string of data triggering all manner of dangerous calls being performed on your database.

That’s why you should always do where('various sql things ?', parameter) instead of where("various sql things #{parameter}").

For more on the subject of passing in parameters to Rails sql calls like this, check out the official Rails Guide on the Active Record Query Interface.

edited Nov 29, 2017 at 5:14

answered Nov 29, 2017 at 4:02

Grant Neufeld

7301 gold badge11 silver badges24 bronze badges

3 Comments

Manishh Over a year ago

SELECT SUM(CASE WHEN orders.order_at BETWEEN ? AND ? THEN orders.qty  ELSE 0 END) as total_qty, products.*, '2017-11-28 05:00:00.000000', '2017-11-29 04:59:59.999999' FROM "products" LEFT JOIN orders ON orders.product_id = products.id GROUP BY products.id

This is the query which is generated and it has the same error. I think ? should be replaced by date but it is not and I don't know why.

Jeff F. Over a year ago

That's usually the correct thing to do, but unfortunately the select method doesn't take parameters that way, so you need to either sanitize them yourself before embedding them in the string, or find another way to construct your query (such as how @daino's answer shows).

Grant Neufeld Over a year ago

Oh, yeah — using where instead of select should fix that.

Collectives™ on Stack Overflow

Passing date parameters to a sql select query in rails (QueryMethods)

2 Answers 2

Comments

Reasoning

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Reasoning

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related