2

I'm currently writing a query to look in a table some grouped results in order to build an alphabetical pagination index.

The query I have looks something like the following (based on this answer):

criteria = "substr(%s, 1, 1)" % field_name
Posts.select(criteria).group(criteria).order(criteria).count(:id)

Now I would like to improve this query in order to avoid any SQL injection but I couldn't really find a way to sanitise it.

As far as I see there's no way to use LIKE for this particular query - or at least I haven't found it - and use a where clause.

Now the example is using a Posts entity, but in production it will be used with many kind of objects and I's like to have a generic system to specify a field name dynamically.

The only idea I had was to get the table column name and validate the field against that list before proceeding, although this may be overkilling in a production scenario.

The environment I have is Ruby 1.9.3, Rails 3.2.17, and a MySQL db.

Update 1

I'll define better the context: in the query above I'm using Post but what I really have it's an ActiveRecord query where I'm appending the select and group criteria.

Something like:

query = Post.where(:condition => something)
# .. do more stuff here

criteria = "substr(%s, 1, 1)" % field_name
query.select(criteria).group(criteria).order(criteria).count(:id)
MarcoL
  • 9,829
  • 3
  • 37
  • 50

1 Answers1

0

Use Post.attribute_names to take columns of Post model then just check that columns contains your field_name and raise error if it doesn't contains.

Maxim
  • 9,701
  • 5
  • 60
  • 108
  • The problem is that I have not the model on my hand when I append the `select` and `group` methods. Unless there's a way to find out the model having the ActiveRecord query I think this is not fit what I'm looking for. – MarcoL Mar 06 '15 at 18:10
  • At the end `attribute_names` did work also for the `ActiveRecord::Relation`. I'll mark this as correct answer. – MarcoL Mar 29 '15 at 10:21