How to safely pass parameters to ActiveRecord#select() when using PostgreSQL

Question

I have a method that takes a column name and defines a scope on my model. The scope makes use of PostgreSQL window functions (like rank() and row_number()), adding an additional column to the resulting dataset. For example, User.ranked_on_score should add a rank() column aliased as score_ranking.

Currently, I achieved it with simple string interpolation (pseudocode):

window_alias = "#{attr}_rankings"
result_alias = "#{attr}_ranking"

select("users.*, #{window_alias}.#{result_alias}")
.join("JOIN (SELECT id, rank() OVER (ORDER BY #{attr} ASC) as #{result_alias} ...")

Considering, the scope should be defined be a macro beforehand and doesn't take input from users, this approach should be safe; nevertheless, I don't like it.

I know about the sanitize_sql method, but it doesn't work in my case. It wraps parameters in single quotes which is fine in the WHERE clause, but raises syntax errors if used in the SELECT clause (PG requires identifiers to be wrapped in double quotes).

Is there any built-in method for identifier sanitization? Or should I just leave it like that?

PinnyM · Accepted Answer · 2013-07-03 16:15:57Z

3

Have you tried quote_column_name? It's undocumented for some reason, but should do what you need.

window_alias = quote_column_name("#{attr}_rankings")
result_alias = quote_column_name("#{attr}_ranking")

Under the hood, it's equivalent to calling PGconn.quote_ident when using PostgreSQL.

answered Jul 3, 2013 at 16:15

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

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

Collectives™ on Stack Overflow

How to safely pass parameters to ActiveRecord#select() when using PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related