SQL Sub Query is being appended as a string instead of a proper query

Question

I have been trying to use Rails find_by_sql functionality and pass a Sub Query as a parameter. The sub query is being appended to the parent query as a string instead of a proper query. Below is my SQL.

sql =  <<~SQL
    SELECT
      column_1,
      column_2
    FROM (
        ?
      ) AS sample_table
    GROUP BY
      column_1,
      column_2
SQL

somemodel = SomeModel.find_by_sql [sql, someotherquery.to_sql]

The someotherquery.to_sql is as below

SELECT * FROM someothertable WHERE column_1 in ('test') and column_2 in ('new test')

This when append to the parent query results in the below SQL which has the subquery as a String instead of a proper query.

SELECT
      column_1,
      column_2
    FROM (
        'SELECT * FROM someothertable WHERE column_1 in (''test'') and column_2 in (''new test'')'
      ) AS sample_table
    GROUP BY
      column_1,
      column_2

Answer 1

The thing is that, that the syntax:

sql  = "select * from model where column = ?"
data = Model.find_by_sql [sql, 'some value']

was designed to prevent SQL injection. What you are trying to do here is exactly the thing that this syntax is trying to prevent. That is why it doesn't work.

You have to replace the question mark manually in your SQL before you send it to the find_by_sql method.

eg during the creation of the SQL string:

sql =  <<~SQL
    SELECT
      column_1,
      column_2
    FROM (
        #{someotherquery.to_sql}
      ) AS sample_table
    GROUP BY
      column_1,
      column_2
SQL

or by doing replace:

sql.sub!('?', someotherquery.to_sql)

Finally, now you can send it to find_by_sql to execute the query

somemodel = SomeModel.find_by_sql(sql)