ORDER BY $order - Possible security hole?

Question

I have a code like this:

ORDER BY $order

Where '$order' is taken from the url like:

http://mywebsite.com/page.php?order=Bananas DESC

Could someone with wrong intentions add their own code at the end of the URL and thus do whatever they like?

Good question. Here you can find a whitelist example code http://stackoverflow.com/questions/2993027/in-php-when-submitting-strings-to-the-db-should-i-take-care-of-illegal-characters/2995163#2995163 — Your Common Sense, Jan 12 '11 at 12:26

score 2 · Accepted Answer · answered Jan 12 '11 at 12:07

2

If you don't check what's on $order variable, then your code is definitely exposed to potential SQL injection attacks.

So, you need to sanitize your input variables by making sure what you get from the GET command is actually a valid order by clause (you can use a regular expression for that).

Or you can do some kind of encoding for your application to form the order by clauses. Something like making:

http://mywebsite.com/page.php?orderField=1&orderType=DESC

And then mapping in your code 1 to Bananas for orderField parameter, and ASC or DESC for your orderType parameter.

answered Jan 12 '11 at 12:07

Pablo Santa Cruz

176,835
32
241
292

The only check I'm doing is to see if the order field is empty to assign a default order. I don't have anything other than that. How would I go about checking and preventing attacks? I have 9 possible 'order' that should be allowed. – Brian Jan 12 '11 at 12:09
@Michael - You therefore have a "whitelist" which is the proper way of validating input. Just check the variable at the end is on of your 9 allowed fields. Job done! – Jamiec Jan 12 '11 at 12:13
I'm going to give the whitelsit a go then. Cheers guys, I'm blown away by how quickly you get answers on here. – Brian Jan 12 '11 at 12:18

score 2 · Answer 2 · answered Jan 12 '11 at 12:08

2

Probably yes. Depending on your config the attacker could insert something like this:

"Bananas; drop table students"

Subqueries instead of multiple statements might be possible too

I'd either build the order clause myself, or compare it against a whitelist.

answered Jan 12 '11 at 12:08

CodesInChaos

106,488
23
218
262

It is worth noting that vulnerability to query stacking (adding a second query on the end) does depend on the combination of DBMS and DB language library being used. Some combinations are inherently safe from this particular category of vector and some are not. – Cheekysoft Jan 12 '11 at 14:07

score 0 · Answer 3 · answered Nov 26 '14 at 15:50

Example :

SELECT * FROM bugs ORDER BY $column $direction

You must define possible options :

 $column =array('id','name',....);
$direction = array('ASC','DESC');

then :

if(array_key_exists ($_REQUEST['column'],$column){
    $column = $column[  $_REQUEST['column']  ];
}else{
...defaults....
}

ORDER BY $order - Possible security hole?

3 Answers3