CODEWITHSUNDEEP
mysqlsqlruby-on-railsruby
roarfromror
roarfromror

Reputation: 376

Is a given sql statement vulnerable to a sql injection attack?

I'm building a sql statement like the one below in a rails app :- 

bank_ids = params[:bank_ids] # comes from end user or simply, is a user input.  

sql_string = "SELECT * FROM users WHERE bank_id IN (#{bank_ids});"

Is the sql statement above vulnerable to an injection attack, input 'bank_ids' is end user controlled.

Upvotes: 1

Views: 71

Answers (1)

Sebastián Palma
Sebastián Palma

Reputation: 33420

Take as example a table designed to store a boolean value to tell if a user is admin or not (might not happend, but it's an example):

                                                              Table "public.users"
   Column   |              Type              | Collation | Nullable |              Default              | Storage  | Stats target | Description
------------+--------------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
 id         | bigint                         |           | not null | nextval('users_id_seq'::regclass) | plain    |              |
 name       | character varying              |           |          |                                   | extended |              |
 admin      | boolean                        |           |          |                                   | plain    |              |
 bank_id    | integer                        |           |          |                                   | plain    |              |

If you receive something like this:

'1) or id IN (select id from users where admin = true'

And that's interpolated afterward, then the select clause asking for the admin users will retrieve data that otherwise wouldn't appear. The query would be executed as it's built:

select * from users where bank_id IN (1) or id IN (select id from users where admin = true)

Is better for you to rely on the ORM you have at hand and leave it to do the sanitization and proper bindings for you (it's one of the reasons why those tools exist). Using ActiveRecord for example would bind the passed values for you, without having to do much:

User.where(bank_id: '1) or id IN (select id from users where admin = true')
# ... SELECT "users".* FROM "users" WHERE "users"."bank_id" = $1  [["bank_id", 1]]

Upvotes: 4

Related Questions