From: Justin Collins <justincollins@...>
Date: 2013-01-04T01:36:31+09:00
Subject: Re: Escaping SQL queries.

On 01/02/2013 12:22 PM, D. Deryl Downey wrote:
> Could do something like this example:
>
> first = "T'Luth"
> User.where("first_name LIKE ?", first) # This is the parameterized call
> Matma mentioned.
>
> Under ActiveRecord, the 'first' parameter to where() will automatically
> be escaped for you.
>
> Under Rails, calling .html_safe will escape non-alphanumeric characters
> for you, explicitly, before being passed to the calling method, the
> .where() method such as in this example, even though AR will do it for
> you, implicitly.
>
>      User.where("first_name LIKE ?", "#{first.html_safe}") # Explicit
> escaping
>
> The example AR .where() clause is the same regardless if your app is
> straight Ruby or Rails. The .html_safe call is a Rails-only method. bear
> in mind that you can .html_safe variables directly in an SQL query,
> *but*, one still should not get into the habit of directly interpolating
> in a query string. Its an unsafe habit to form. (Lord help you if you
> forget to call .html_safe and it contains something malicious)
>


Whoa, there. First, `html_safe` does NOT escape anything or change the 
string at all, it marks a string as BEING safe. In other words, you are 
explicitly telling Rails NOT to (HTML) escape anything in the string.

Secondly, the HTML escaping in Rails does not escape "non-alphanumeric 
characters", it escapes HTML-specific characters (for latest Rails, 
these characters are <,>,&,', and "). So you do not want to HTML-escape 
input to a SQL query. Use the ORM's escaping methods.

Also, while the first example is correct, it is not accurate to say 
ActiveRecord will escape the first parameter to where(). The first 
parameter is not escaped, the parameters interpolated into the first 
parameter with the `?` syntax are escaped.

For other safe ways of using ActiveRecord, see the guide: 
http://guides.rubyonrails.org/active_record_querying.html#conditions

-Justin