In this short article we are sharing how SQL injections’ inner bearings work, and how to easily patch them. Let’s start showing a realistic code snippet which is vulnerable to SQL injection in Ruby on Rails:

@account = Account.find(account_id)
account_users = @account.users
@users = account_users.where(id: user_ids).order(Arel.sql("position(id::text in '#{user_ids.join(',')}')"))

One can quickly figure out that “@users” is essentially returning the results of a SQL query which accepts unsanitized input (we are assuming user_ids is an unchecked variable or array the end user controls):

'#{user_ids.join(',')}'

This is happening because the original code expects “user_ids” to be an array such as [2,6,4,3]. If played fair, the results of executing this code with normal input would be like these:

[1] pry(main)> @account = Account.find(2)
ActiveRecord::Base -- Account Load (3.3ms) SELECT "accounts".* FROM "accounts" WHERE
"accounts"."deleted_at" IS NULL AND "accounts"."id" = $1 LIMIT $2 [["id", 2], ["LIMIT", 1]]
=> #<Account:0x0000ffff894abe40

[2] pry(main)> account_users = @account.users
=> ActiveRecord::Base -- User Load (1.6ms) SELECT "users".* FROM "users" WHERE
"users"."deleted_at" IS NULL AND "users"."account_id" = $1 [["account_id", 2]]

[3] pry(main)> user_ids = [2,6,4,3]

=> [2, 6, 4, 3]

Let’s execute the original code in the Rails console and observe how the produced SQL query looks like:

-------------------------------------- Original code ---------------------------------------

[4] pry(main)> @users = account_users.where(id: user_ids).order(Arel.sql("position(id::text
in '#{user_ids.join(',')}')"))

=> ActiveRecord::Base -- User Load (1.1ms)

-------------------------------------- Original SQL query ------------------------------------

SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND "users"."account_id" = $1 AND
"users"."id" IN ($2, $3, $4, $5) ORDER BY position(id::text in '2,6,4,3') [["account_id", 2], ["id",
2], ["id", 6], ["id", 4], ["id", 3]]

But, of course, an attacker would not play fair. Instead, an attacker would send an input string similar to this one:

[8] pry(main)> user_ids = ["2'); DROP test; -- ", 2]
=> ["2'); DROP test; -- ", 2]

[9] pry(main)> @users = account_users.where(id: user_ids).order(Arel.sql("position(id::text
in '#{user_ids.join(',')}')"))

=> 2023-03-31 02:32:59.427120 D [109:59600 log_subscriber.rb:126] ActiveRecord::Base -- User Load (1.7ms)

SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND "users"."account_id" = $1 AND
"users"."id" IN ($2, $3) ORDER BY position(id::text in '2'); DROP test; -- ,2') [["account_id", 2],
["id", 2], ["id", 2]]

ActiveRecord::Base -- User Load (0.9ms)

SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND "users"."account_id" = $1 AND
"users"."id" IN ($2, $3) /* loading for inspect */ ORDER BY position(id::text in '2'); DROP test; --
,2') LIMIT $4 [["account_id", 2], ["id", 2], ["id", 2], ["LIMIT", 11]]

#<User::ActiveRecord_AssociationRelation:0x15c98>

It’s easy to see in the next code snippet how, by inputing a crafted string, the attacker gets to close a SQL statement and issue a new one:

ORDER BY position(id::text in '2'); DROP test; -- ,2')

In this case, Postgres would “order by position”, then drop the table “test”, and turn the rest of the original query into a comment, effectively allowing the attacker to interact with the database in malicious ways.In order to patch these family of vulnerabilities, we can use the method sanitize_sql_array, which builds SQL query fragments and correctly wraps them in quotations:

order_fragment = ActiveRecord::Base.sanitize_sql_array(['position(id::text in ?)',
user_ids.join(',')])
@users = account_users.where(id: user_ids).order(Arel.sql(order_fragment))

In this patch, we are passing an array with all the SQL binding variables (?) and the corresponding values (essentially, one value per ? in the statement). You can click the following link for further reference:

https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/Sanitization/ClassMethods.html