Generating dynamic queries and avoiding SQL injection

[Nytz12]

Hi

I have the following function i use to convert user supplied filter objects, into SQL conditionals. The idea is then to join every conditional using AND and placing it inside the SELECT statement

def build_condition(%{"filter" => filter, "field" => field, "value" => value}) when field in @default_values do
    case filter do
      "greater_than"      -> "#{field} > #{value}"
      "less_than"         -> "#{field} < #{value}"
      "is"                -> "cast(#{field} AS text) ILIKE cast(#{value} AS text)"
      "is_not"            -> "cast(#{field} AS text) NOT ILIKE cast(#{value} AS text)"


      "starts_with"       -> "#{field} ILIKE '#{value}' || '%'"
      "ends_with"         -> "#{field} ILIKE '%' || '#{value}'"
      "contains"          -> "#{field} ILIKE '%' || '#{value}' || '%'"
      "does_not_contain"  -> "#{field} NOT ILIKE '%' || '#{value}' || '%'"


      "date_more_than"    -> "#{field} < NOW() - (#{value} || ' days')::interval"
      "date_less_than"    -> "#{field} > NOW() - (#{value} || ' days')::interval"
      "date_exactly"      -> "#{field} = NOW() - (#{value} || ' days')::interval"


      _                   -> raise "Unknown group filter: #{filter}"
    end
  end

Is this safe and is there a better approach?

Thanks

[José Valim]

You need to quote all occurrences of value and field and you need to make sure to escape entries inside the field and value itself. For example, '#{value}' is not enough because someone can convert provide something like: ';DELETE * FROM users.

My suggestion is to use Ecto queries for those components.

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

--
You received this message because you are subscribed to the Google Groups "elixir-lang-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-lang-ta...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-lang-talk/34463107-9043-41fe-ba14-2af3000308c2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.