rails/rails — pull request #14894 by dylanahsmith (2014-04-28 21:07:06+00:00)
Title: active_record: Quote numeric values compared to string columns.

Previously attempted in pull #9207, but that needed pull https://github.com/rails/arel/pull/162 to be merged to implement it properly. Now arel is fixed, and that pull request just provides a regression test.

## Problem

Same as pull #9207:
> The problem is described in [Potential Query Manipulation with Common Rails Practises](https://groups.google.com/forum/?hl=en&fromgroups=#!topic/rubyonrails-security/ZOdH5GH5jCU), which notes that "it is not possible for ActiveRecord to automatically protect against all instances of this attack". However, it is possible to handle the case where the column type is known.
>
> For example:
>
> ```ruby
User.where(:login_token=>params[:token]).first
```
> performs the query
> ```sql
SELECT * FROM `users` WHERE `login_token` = 0 LIMIT 1;
```
> where it will match any login_token that starts with a non-digit character. But since active record can determine that login_token is a string type column, it can quote the integer it is being compared with as follows.
>
> ```sql
SELECT * FROM `users` WHERE `login_token` = '0' LIMIT 1;
```
>
> which will have the desired affect of only matching the exact string '0'.

## Solution

Coerce the numeric values to string values in ActiveRecord::ConnectionAdapters::Quoting's `type_cast` and `quote` methods when a string type column is provided. The `type_cast` method needed to be changed for connection adapters using prepared_statements, and the `quote` method needed to be changed when prepared statements aren't used.

Boolean values are represented as the integers 0 or 1 for some databases, so booleans also need coerced to string when comparing against a string type column.

## Limitations

From pull #9207
> SQL fragments with quoted values have no information about the column type, so the following is still unsafe.
>
> ```ruby
User.where("login_token = ?", params[:token])
```
>
> The parameters to the SQL fragment should still be converted to the correct type before passing it to active record as follows.
>
> ```ruby
User.where("login_token = ?", params[:token].to_s)
```