PDO "ORDER BY" only works when hardcoding

Question

I want to fetch results from a MySQL database with PDO. The user should be able to order them by tablerow by which type (ascending or descending). This seems to only work when you hardcode it.

Does work:

$query = "SELECT * FROM ".$config->dbPrefix."content 
          WHERE cat_id = 2
          ORDER BY id DESC
";
$query = $pdo->prepare($query);
$query->execute();
$result = $query->fetchAll();

Doesn't work:

$orderRow = 'id'; //from $_POST
$orderType = 'DESC' //from $_POST

$query = "SELECT * FROM ".$config->dbPrefix."content 
          WHERE cat_id = 2
          ORDER BY :orderRow :orderType
";
$query = $pdo->prepare($query);
$query->bindValue(':orderRow', $orderRow);
$query->bindValue(':orderType', $orderType);
$query->execute();
$result = $query->fetchAll();

So my question is: what is the best way to do this and why isn't this implemented?

The best way I can think of is using a switch statement and writing the query for every different option which would have like 14 different available cases.

Sadly, the only thing you can prepare in PDO prepared statements are the field values, not the fields names. There's an answer like this somewhere else on here. Let me find it — , Sep 01 '13 at 22:09
PDO idneed can't deal with dynamic field names. Make sure `orderRow` and `orderType` are clean (e.g. by comparing them against a list of columns in your table) and insert them into the query directly — Pekka, Sep 01 '13 at 22:10
http://stackoverflow.com/questions/2542410/how-do-i-set-order-by-params-using-prepared-pdo-statement? There's no decent answer on my question :/ — kalekip1, Sep 01 '13 at 22:11
This is effectively the same as [this question about table names](http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter). This is not a limitation of PDO, but a fundamental point about what binding parameters means - they are *variables* plugged into a *fully planned query*, so cannot be used to change the structure of the query. — IMSoP, Sep 01 '13 at 22:48
@MisterMelancholy This has nothing to do with PDO or any other client library. You simply cannot use parameters for identifiers in prepared statements — Phil, Sep 02 '13 at 00:46

Janick Bernet · Accepted Answer · 2013-09-01T22:14:48.770

1

You can only provide placeholders for values in an SQL statement, not for column names or other kind of identifiers.

So instead of using bindValue, put the values in like you do for #config->dbPrefix, directly into the string. Make sure however that no SQL injection is possible.

edited Sep 01 '13 at 22:14

answered Sep 01 '13 at 22:11

Janick Bernet

20,544
2
29
55

Is that safe against SQLi? – kalekip1 Sep 01 '13 at 22:16
1

Don't forget [$pdo->quote();](http://us3.php.net/manual/en/pdo.quote.php) – Sep 01 '13 at 22:16
2

@Mister Melancholy: for what? – zerkms Sep 01 '13 at 22:17
@kalekip1: he correctly answered your question – zerkms Sep 01 '13 at 22:17
@zerkms to protect against SQL injection – Sep 01 '13 at 22:18
1

@Mister Melancholy: OP already uses `bindValue()` for parameters. What exactly you're proposing to use `PDO::quote()` for? – zerkms Sep 01 '13 at 22:18
I mean for this answer (hence why the comment is on this answer, not the question). – Sep 01 '13 at 22:19
1

@Mister Melancholy: I still don't see the point. What "for this answer" means? OP **already** uses `bindValue()` for parameters. What else do you propose to quote? – zerkms Sep 01 '13 at 22:20
@zerkms Read the answer. "instead of using bindValue ... Make sure however that no SQL injection is possible." – Sep 01 '13 at 22:20
1

@Mister Melancholy: I have. The answer says - to omit `bindValue` for identifiers. And that's correct. What exactly you're proposing to `quote()`? – zerkms Sep 01 '13 at 22:21
1

@Mister Melancholy: you **DON'T NEED** to `PDO::quote()` identifiers, you just whitelist them and put as-is. `PDO::quote()` **IS NOT** suitable for identifiers – zerkms Sep 01 '13 at 22:21
@zerkms Protect against SQL injection with the unbinded string. – Sep 01 '13 at 22:21
4

@Mister Melancholy: it's incorrect, you cannot and shouldn't use `PDO::quote()` for identifiers, it's for string literals **ONLY** – zerkms Sep 01 '13 at 22:22
@Mister Melancholy: "If OP doesn't, they need to use POD::quote " --- there is no "if". The **ONLY** correct way of doing that is whitelisting. Using `PDO::quote()` is as incorrect as quoting manually with `str_replace` or `preg_replace` – zerkms Sep 01 '13 at 22:24

PDO "ORDER BY" only works when hardcoding

1 Answers1