Prevent SQL Injection

[alencarandre]

Please, help me.

I'm searching a way to prevent SQL Injection using Smart Listing.

For instance:

In my view:

<th><%= smart_listing.sortable ScheduledService.human_attribute_name("customer"), :customer_id %></th>

Params generated by Smart Listing:

?scheduled_service_smart_listing[page]=&scheduled_service_smart_listing[per_page]=10&scheduled_service_smart_listing[sort][customer_id]=asc

If I change scheduled_service_smart_listing[sort][customer_id]=asc for scheduled_service_smart_listing[sort][customer_id; delete from schedule_services where id = 1; --]=asc

Give me this error

PG::SyntaxError: ERROR:  cannot insert multiple commands into a prepared statement
: SELECT  "scheduled_services".* FROM "scheduled_services" ORDER BY customer_id; delete from schedule_services where id = 1; -- asc LIMIT $1 OFFSET $2

See that the DELETE instruction was delivered to database. Not executed, but, delivered and it's a problem. Has a way to avoid that?

[sigra]

I faced with the same problem and found a decision that works for me.

So, when you use sort_attributes option then library will use only that attributes and skip bad one. I don't know why this is not said in the official documentation.

Example:

sort_aliases = [
  [:id, "users.id"],
  [:email, "users.email"],
  [:name, "users.metadata->>'name'"] # you can sort JSONB too
]
@users = smart_listing_create(:users, User, sort_attributes: sort_aliases)

# view part
<th><%= smart_listing.sortable 'ID', :id %></th>
<th><%= smart_listing.sortable 'Name', :name %></th>

So, when I change params for "sort"=>{"id;TRUNCATE users;--"=>"asc"}} it just ignored and nothing will happen.

[korun]

Fixed in 0794ed4 (v1.2.3), but it can cause some issues on update for some complicated queries with sort on joined tables (see #158 for more)