Is it safe to pass a dynamic column name to an ActiveRecord query using Postgres?

Question

I do a lot of time without asking for a date in my application, and I would like to drop some of the requests.

So I have a model with a field DateTime starts_at:

Shift.where('starts_at::time > ?', '20:31:00.00')
-> SELECT "shifts".* FROM "shifts" WHERE (starts_at::time > '20:31:00.00')

This correctly returns all the values ​​of "start_at" more than the time 20:31.

I want to dynamically pass the column name to the query, so I can do something like:

Shift.where('? > ?', "#{column_name}::time", '20:31:00.00').
-> SELECT "shifts".* FROM "shifts" WHERE ('starts_at::time' > '20:31:00.00')

In this example, this does not work, as the search performs starts_at::timeas a row, and not as an application column time.

How can I safely pass column_namein a request with an application ::time? Although this will not accept user input, I would still like the SQL injection to be taken into account.

Answer 1

, , ( , ...) ('pancakes', 6,...) - SQL, ( , SQL, MySQL, SQL-Server,...). , Ruby , , , Ruby, .

:

where('? > ?', ...)

( ) . ? ActiveRecord , ? (, created_at) (, 20:31:00.00).

, , :

> puts ActiveRecord::Base.connection.quote_column_name('pancakes')
"pancakes"
=> nil

, :

quoted_column = Shift.connection.quote_column_name(column_name)
Shift.where("#{quoted_name}::time > ?", '20:31:00.00')

, (, , ) SQL. , quote_column_name - column_name, .

:

quoted_column = "#{Shift.connection.quote_column_name(column_name)}::time"
Shift.where("#{quoted_name} > ?", '20:31:00.00')

time. :

clause = "#{Shift.connection.quote_column_name(column_name)}::time > ?"
Shift.where(clause, '20:31:00.00')

extract , , quote_column_name.

column_name, . column_name :

if(!in_the_whitelist(column_name))
  # Throw a tantrum, hissy fit, or complain in your preferred fashion
end
Shift.where("#{column_name} > ?", '20:31:00.00')

, , "gotta have some breakfast" , . Shift.column_names Shift.columns .

, quote_column_name, , , quote_column_name .