parameter based joins not susceptible for sql injection

Question

I want to do non-standard query with join based on the parameters.

For example I have 2 tables: a_examples and b_examples both with fields: field_x and field_y. I want to join rows when both tables have the same values on field_x(or field_y).

Example query can looks like this:

AExample.joins('INNER JOIN b_examples ON b_examples.field_x = a_examples.field_x')

The problem occurs when I have field name based on parameter. For example I have variable field_name and want to use it for query. I can do it like this:

AExample.joins("INNER JOIN b_examples ON b_examples.#{field_name} = a_examples.#{field_name}")

This query works, but is susceptible for sql injection.

For where clause we have special syntax with ? to avoid sql injection but there isnt any such thing for joins. How can I make this query safe?

Answer 1

Do not attempt this: (explanation below)

You can use the ActiveRecord::Sanitization module and write something like the following, inside your ActiveRecord model:

AExample.joins("INNER JOIN b_examples ON b_examples.#{sanitize_sql(field_name)} = a_examples.#{sanitize_sql(field_name)}")

Or you can include the module somewhere else and use it there (e.g. your controller).

Do use this, instead: (included from another answer)

AExample.joins("INNER JOIN b_examples ON b_examples.#{ActiveRecord::Base.connection.quote_column_name(field_name)} = a_examples.#{ActiveRecord::Base.connection.quote_column_name(field_name)}")

It will raise an error if the column is not found, preventing malicious code from entering your query.

However I wouldn't do this in my app as it looks suspicious, other programers may not understand what is happening, it may be implemented wrong, solid testing should be included, it may have bugs and such. In your problem, you only need to construct two different queries, with that information I would write something like:

case dynamic_field
when 'field_x'
 AExample.joins('INNER JOIN b_examples ON b_examples.field_x = a_examples.field_x')
when 'field_y'
 AExample.joins('INNER JOIN b_examples ON b_examples.field_y = a_examples.field_y')
else
  raise "Some suspicious parameter was sent!: #{dynamic_field}"
end

Or even write scopes on your model and avoid this code to be flying around.

With problems of this nature, as with encryption, try to find a workaround and avoid implementing your own solutions as much as possible.

EDIT:

The method sanitize_sql is intended to sanitize conditions for a WHERE clause (ActiveRecord::Sanitization):

Accepts an array or string of SQL conditions and sanitizes them into a valid SQL fragment for a WHERE clause.

It is not an option as you try to sanitize for an INNER JOIN, or an ON clause.

Note that the ActiveRecord::Sanitization module only has options for WHERE, SET, ORDER and LIKE clauses. I was unable to find a sanitization method for a column name, an INNER JOIN or an ON clause. Perhaps is a useful funcionality that should be added on Rails on further version.

Using sanitize_sql with a string passes it almost unfiltered, so if the field_name variable has some malicious code as:

"field_x = a_examples.field_x; DROP TABLE a_examples; --"

It will be included in your query, without any error being raised.

This solution is not safe, and is for reasons like these that we should avoid writing code of this nature. Perhaps you find something helpful with Arel or other gems, but I would strongly advice not to.

EDIT 2:

Added the working solution to escape a column name. It raises an error if malicious code is being entered, as the column with that name will not be found.