DataGuy
DataGuy

Reputation: 1725

Variables passed to ORDER BY?

I am using the following query:

$result = "SELECT mgap_ska_id, mgap_ska_id_name, SUM(DISTINCT mgap_growth) as growthtotal, SUM(DISTINCT mgap_recovery) as recovery FROM mgap_orders "
        . "WHERE account_manager_id = '" . $_SESSION['account_manager_id'] . "'  GROUP BY mgap_ska_id ORDER BY '$sort' ";

I am trying to define the ORDER BY with a variable that I can pass through the URL as follows:

$sort = mysql_real_escape_char($_GET['sort'])

How would I escape this properly in the query string? I think I have the quotes done incorrectly.

Thanks!

Upvotes: 0

Views: 88

Answers (4)

Philip Adler
Philip Adler

Reputation: 2206

In terms of 'escaping properly' The only truly correct way to ensure the code is safe is to make sure that the variable you are including is one you specify, e.g:

if('growthTotal' == $_GET['sort']) {
    $sort = 'growthTotal';
}

Then include $sort in your query however you wish, because the value came from your code, and so you can guarantee it's safety.

Upvotes: 1

Marcin Orlowski
Marcin Orlowski

Reputation: 75629

SORT BY expectes column name, not value. So you do not use " there but only (optional, when needed) backticks ` if you use table name which is also reserved word.

Also, better would be not to take column name from GET at all but substitute it in code, based on order sent from user, because currently I can feed your query which what I want (invalid column name, or column I want, not you)

Upvotes: 0

Marc B
Marc B

Reputation: 360602

Don't quote the variable in the order by:

ORDER BY '$sort' 
         ^-----^---

Adding quotes like that forces the DB to treat whatever's in $sort as as STRING, not a field name. This is perfectly valid SQL, as you can sort on any arbitrary expression, but it's not going to sort your records properly.

It should be just

ORDER BY $sort

Upvotes: 1

Fluffeh
Fluffeh

Reputation: 33502

Order by statements aren't surrounded in single quotes:

"... GROUP BY mgap_ska_id ORDER BY $sort ";

Having said that, it is really dangerous to simply accept user data and insert it into a query - real escapes or not.

If you have to allow user selected sorts, I would suggest giving them options, but forcing your OWN code to generate the clause:

switch($userSort)
{
    case 'id':
        $sort=' id asc';
        break;
    case 'dollarValue':
        $sort=' dollarValue desc';
        break;
    default:
        $sort=' dateField desc';
}

then use the variable:

"... GROUP BY mgap_ska_id ORDER BY $sort ";

This way, no matter what the user enters, it won't bork your query.

Upvotes: 3

Related Questions