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?
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.
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.
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?
And please post a followup here. I'll contribute a patch too for #order if that does not already.