[Rails] Bind Variables in Active Record

[Rick Bradley]

* David Heinemeier Hansson (david at loudthinking.com) [041213 16:51]:
> >The option that came to mind that might make this work would be to
> >detect the sprintf formats and replace with a question mark while in 
> >the
> >background actually applying the sprintf format to the variable being
> >used.
> 
> Or just have the replacement work on %s in isolation. As soon as you're 
> doing %d, or something else, you're out of harms way, security wise. So 
> it's only the unadorned %s that's a problem.

It might be worth noting somewhere, as an additional reason people might
want to move away from the deprecated %-notation, that if individual
database handlers start doing compilation of queries for efficiency,
that non-'%s' %-specifiers will effectively be recompiled each time
since the non-'%s' specifiers don't merge down to bind markers ('?'),
and so the queries vary with the values supplied at execution time.

Of course, I think not converting '%d' and friends to '?' is a feature
since it multiplies the reasons to move to the new '?' binding notation.


On a slightly different note (having seen the issue of how to parse a
query with quoting in it raising its head already), before the '%s'->'?'
quoting discrimination problem becomes too hairy (i.e., noone wants to
take the time to untangle the meanings of the various quoting and
escaping conventions) it's probably worth noting that sprintf itself
includes the interpretation machinery sufficient to sort this out.  The
trick is that we can have '%d', '%f', etc., interspersed with the '%s',
among various bits of quotery.


Here is a hack which perhaps (hopefully) can be streamlined but which
solves the problem of converting %-specifiers to '?' specifier without
having to reinvent the sprintf parser.  I had to resort to defining a
wrapper class because my ruby-fu is not strong enough to make this work
directly on the values themselves:

class QueryCleanser
  def initialize(value, capture)
    @value = value
        @capture = capture
  end
  def to_i; @value.to_i; end
  def to_f; @value.to_f; end
  def to_s
        @capture << @value
        '?'
  end
end
 
def convert_query(query, *args)
  newargs = []
  wrapped = []
  args.each { |x| wrapped << QueryCleanser.new(x, newargs) }
  [ sprintf(query, *wrapped), newargs ]
end


----

irb> convert_query("foo '%s'', %d, %2.3f %s", 1, 2, 3, 4)
=> ["foo '?'', 2, 3.000 ?", [1, 4]]
irb> convert_query("select * from table where a='%s' and b < %d and c > %d and abs(d-%f) < 0.01 and x like '%%%s'", "bob", 3, 5, 24.24, "wins") 
=> ["select * from table where a='?' and b < 3 and c > 5 and abs(d-24.240000) < 0.01 and x like '%?'", ["bob", "wins"]]

(please forgive any line-wrap)

Of course, this won't work with combinations of %-specifiers and
'?'-specifiers for placeholders, but that shouldn't be a big issue.

Rick
-- 
 http://www.rickbradley.com    MUPRN: 703
                       |  obtain a PC
   random email haiku  |  and NICs, this sounds like pretty
                       |  much of a black box.