1

I'm creating a dynamic search for users where both the field and value are dynamic. I have it working now with the code below, but wanted to prevent possible SQL injection and wondered how to do this.

search_clause = "#{search_criteria.field} LIKE '%#{search_criteria.value}%'"
organizational_users.where(search_clause)

Can I parameterize search_clause even with a dynamic field? How can I do this?

The key to the question is solving the dynamic part of the field, the duplicate question suggestion does not really address that part.

Any help/suggestions would be appreciated!

Mark Kadlec
  • 7,096
  • 15
  • 58
  • 93
  • Where do you get `search_criteria.field` and `search_criteria.value` from ? – Maxence Aug 15 '18 at 17:23
  • 1
    You can use the named parameters like the following: `where(':field LIKE :value', field: search_criteria.field, value: search_criteria.value)`. It will handle the sanitization for you – MrYoshiji Aug 15 '18 at 17:29
  • @Maxence, good question, the search_criteria.field is passed from an array so I'm not too concerned about injection there, but the search_criteria.value is coming from the page. – Mark Kadlec Aug 15 '18 at 17:30
  • 1
    https://stackoverflow.com/a/44664100/525478 Can be adapted, is easy to understand, and is database agnostic – Brad Werth Aug 15 '18 at 17:34
  • 1
    Possible duplicate of [Case-insensitive search in Rails model](https://stackoverflow.com/questions/2220423/case-insensitive-search-in-rails-model) – Brad Werth Aug 15 '18 at 17:34
  • @MrYoshiji, thanks, but using that solution produces SQL that adds apostrophes, it yields 'first_name' like '%'Pat'%' which is invalid – Mark Kadlec Aug 15 '18 at 17:38
  • 1
    @BradWerth I agree that's a good answer for this question, but disagree that makes this a duplicate: that page is full of upvoted (and accepted) answers that in no way address this question. – matthewd Aug 15 '18 at 17:39

1 Answers1

1

With value escaping, the best you can do is to limit SQL injection to referencing any single identifier visible in the query (e.g. every column in the table). That would look like this:

organizational_users.where("#{connection.quote_column_name search_criteria.field} LIKE ?", "%#{search_criteria.value}%")

Unless you're absolutely sure you need to expose every single column to this filtering, you should really apply an allowlist first:

raise "nope" unless search_criteria.field.in? %w(first_name last_name)
organizational_users.where("#{connection.quote_column_name search_criteria.field} LIKE ?", "%#{search_criteria.value}%")

(The above assumes this is happening in a model method. As it involves using the connection to quote the column name, it really should be... but if not, you'll need to use SomeModel.connection.quote_column_name instead.)

matthewd
  • 4,082
  • 13
  • 20