Arel and a SafeBuffer?

Hi group.

I've written a patch for Arel to provide a feature similar to the ActiveSupport::SafeBuffer class that allows for by-passing some of Arel's quoting mechanisms when using, say, String#sql_safe, similar to #html_safe. I've come across situations where sometimes I want to perhaps use a native database function or something to that effect where I don't want an entire value quoted and want it to pass through to SQL untouched, i.e.

MyModel.create(:field => 'now()')

Basically, I ported ActiveSupport::SafeBuffer to Arel::SafeBuffer and it would allow for things like

MyModel.create(:field => 'now()'.sql_safe)

I know this sort of feature has the potential to be abused and lead to SQL injection attacks, so as a sanity check, I figured I'd check to see if this sort of feature would be useful or if I'm as crazy as I may potentially be for bringing it up. I think if care were taken with its use, it could be pretty useful, as there have been a good number of occasions where I would have liked to have used this sort of mechanism. (I use PostGIS quite often which provides a bunch of spatial functions for PostgreSQL, for instance.) I'm also thinking someone must have done something similar to this before but I can't seem to track anything down on the subject.

Anyways, comments, anyone? Would this sort of thing be as useful and potentially disastrous as I think it would be?

Cheers.

J

The Arel.sql factory method already enables the easy creation of SqlLiterals without monkeypatching String. Seems to me that this method doesn't really give us anything much more convenient than that, for the times when this sort of thing is needed.

I would agree to using Arel.sql too. I had a patch in recently that made sure that #limit would allow a that value to be passed thru to Arel untouched. I think more relation methods should do this. Just recently I had a user on the sqlserver adapter list wanting to pass a complex sql literal to #order. I could be wrong, but that method too does not allow the Arel::Nodes::SqlLiteral to pass thru unescaped. When I have time later, I may look at submitting a patch there too. But it does raise the point that in places where it is appropriate that ActiveRecord always allow these nodes to pass down unescaped.

- Ken

How would I use Arel.sql relative to the example from earlier, i.e. from the ActiveRecord attributes standpoint? This still seems to be escaped, for instance:

MyModel.create(:field => Arel.sql('now()'))

That's really what I'm looking for, I guess -- a way to bypass quoting and sanitization when I know that it's safe to do so, as with Ken Collins' example with #limit and #order and the like and in my case with attribute assignment. At least in the case of attribute assignment that even SqlLiterals are escaped, unless I'm missing something, which I may very well be.

Any ideas?

This is a bug. You should be able to pass strings tagged using Arel.sql() in and have them remain untouched.

Do you mind filing a ticket in lighthouse and assigning it to me? Or even better, come up with a patch that fixes it? :wink:

And please post a followup here. I'll contribute a patch too for #order if that does not already.

- Ken