• Projects
  • Articles
  • About
  • Appointments
  • Services
  • Code

The basics of SQL injections in Ruby on Rails

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):


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]]


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 ?)',
@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:


Interested in working with us?

We’d be happy to provide you with a free 30 minute consultation.

Give us a call: (802) 328 – 0883‬