Q:

How to convert hash values into appropriate data types for SQL queries

The problem I am having is the values in a hash may not be the right data type in order to be successful in a where clause (as one example).
For example, there are filter parameters that may be passed on the URL: some may be strings, some numbers, some boolean. The final hash would be something like filter_parms: {id: "1", active: "true", name: "John"} and so on. The problem should be obvious: everything is treated as a string. I am trying to find a way to automatically coerce the values into becoming the correct data types (e.g. "1" => 1, "true" => true) in a nice, elegant way without looping through and creating a coding mess.
The end goal is to be able to use the hash in something like this: Person.where(filter_parms). This works fine for the String fields, but not for boolean and int.
I have tried using sanitize_sql_for_conditions, but its output matches the input data types exactly without making any modifications.
I imagine there is something obvious that I am missing. Thanks for your help.

A:

You can do things to normalize your data like this:
def normalize(value)
  case (value)
  when 'true'
    true
  when 'false'
    false
  when 'null','nil'
    nil
  when /\A-?\d+\z/
    value.to_i
  when /\A-?\d+\.\d+\z/
    value.to_f
  else
    value
  end
end

There's risks here in interpreting the values incorrectly, like if a field could contain the string literal "true" and you want that preserved this will mangle it, and likewise, phone numbers of the form 8889991111 will be coerced into numbers that might not fit in 32-bit forms, so that could be a problem for some systems.
The reason there's no automatic way is because converting it like this isn't hard, plus a lot of the conversion is highly contextual.

