Reputation: 1725
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
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
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
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
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