Rails 7 MySQL adapter improves the security of untyped bound values


Databases like MySQL, SQLServer have typecasting as a default feature for comparison operations. This opens ActiveRecord up for potential vulnerabilities.

To illustrate, consider the SQL query below.

Example: Typecasting

SELECT "556" = 556;

# Returns 1 for match

In the above case, 556 of the type string is converted to an integer for comparison with the right-hand value 556 of the type integer.

To illustrate clearly, a more explicit typecasting can be done as below.

Example: Explicit Typecasting

SELECT CONVERT( "556", UNSIGNED INTEGER) = 556

# Returns 1 for match

Example: Explicit Typecasting

In case the string is not a valid number, it is type-casted to an integer with the value 0.

SELECT CONVERT( "Not a Number", UNSIGNED INTEGER)
# Converting "Not a Number" to string, returns 0

SELECT "Not a Number" = 0
# Converting "Not a Number" to string and comparing with 0, returns 1

How does it play as a vulnerability in Rails?

Consider a basic API with the following code for authentication.

Before: API

# params[:token] = "X23sxE"
# valid params token

User.where("access_token = ?", params[:token] ).first

# resulting query will be
SELECT `users`.* FROM `users` WHERE (access_token = 'X23sxE') ORDER BY `users`.`id` ASC LIMIT 1

# resulting object
# <User id: 2321, name: "Fred Durst", email: "fred@saeloun.com", access_token: [FILTERED], created_at: "2021-07-07 11:01:09.026716000 +0000", updated_at: "2021-07-07 11:01:09.026716000 +0000">

The above code intends to return a User object on passing a valid token. In case the token does not exist, it returns nil.

Before: API Vulnerable

If an attacker tries to pass the token as 0, it will translate to a query like shown below and can lead to security vulnerabilities.

# params[:token] = 0
# invalid params token as integer

User.where("access_token = ?", params[:token] ).first

# resulting query will be
SELECT `users`.* FROM `users` WHERE (access_token = 0) ORDER BY `users`.`id` ASC LIMIT 1

# resulting object
# <User id: 1, name: "Admin", email: "admin@saeloun.com", access_token: [FILTERED], created_at: "2021-07-07 11:01:09.026716000 +0000", updated_at: "2021-07-07 11:01:09.026716000 +0000">

In the above query, as the access_token column gets compared to an integer(0), the access_token column for existing records is type-casted to an integer. It returns a record that matches the first value that doesn’t contain a valid integer.

After: API Secure

To avoid possible attacks, Rails 7 fixes the above vulnerability.

# params[:token] = 0
# Invalid params token as integer

User.where("access_token = ?", params[:token] ).first

# resulting query will now be secure. Notice the quote on 0
SELECT `users`.* FROM `users` WHERE (access_token = '0') ORDER BY `users`.`id` ASC LIMIT 1

# resulting object
# nil

ActiveRecord will cast numbers to string when comparing a string column to a number. Check out this pull request for more details.