Rails Brakeman SQL injection warning while accessing an oracle view/function

Question

I have rails code that is consuming an oracle view/function. This is my code:

 def run_query
    connection.exec_query(
      "SELECT * FROM TABLE(FN_REQ(#{demo_type_param},#{demo_tid_param}}))")
  end

When run Brakeman analyzer it warns of possible "sql injection attack"

I need to understand if this is a valid warning, if so, how do I remediate it?

Since this is a function & not an actual table, I am not sure what's the right way. If it was a normal model, i would have just followed this pattern:

Model.where("mycolumn1= ? AND mycolumn2= ?", demo_type_param, demo_tid_param).first

Answer 1

Yes, it is real. Almost every time, you build any SQL query from simply concatenating variables, you are vulnerable to SQL injection. Generally, an SQL injection happens each time when data inserted into the query can look like valid SQL and can result in additional queries executed.

The only solution is to manually enforce appropriate escaping or to use prepared statements, with the latter being the preferred solution.

With ActiveRecord / Rails, you can use exec_query with binds directly

sql = 'SELECT * FROM TABLE(FN_REQ(?,?))'
connection.exec_query(sql, 'my query', [demo_type_param, demo_tid_param])

Here, Rails will prepare the statement on the database and add the parameters to it on execution, ensuring that everything is correctly escaped and save from SQL injection.