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.