Two questions about ORDER BY using Prepared Statements

Question

<?php
$what = 'creationdate';

$sql = "SELECT * FROM `accountlist` ORDER BY ? [ASC/DESC]";

$stmt = $conn->stmt_init();

if(!($stmt->prepare($sql)))
{
  echo "Error";
} else {
  $stmt->bind_param("s", $what);
  $check = $stmt->execute();
  $result = $stmt->get_result();
}

for($n=1; $row = $result->fetch_assoc(); $n++)
{
  var_dump($row);echo "<br />";

  echo $n . ':<br />';
  foreach($row as $key => $value) echo $key . ': '. $value . '<br />';
  echo '<br />';
}

echo $check ? 'success' : 'error/no lines'

?>

Why does this return the same result when I use either ASC or DESC in my query?
Is there a way to place a placeholder instead of that *? Using a ? and binding it returns a "?" in the $row array...

You cannot replace an identifier in a prepared statement with a parameter. You need to munge the query string, alas. — Gordon Linoff, Feb 14 '20 at 14:13
I don't want to replace ASC / DESC, the ploblem is that they both return the same result (in particurar DESC behaves like ASC) — , Feb 14 '20 at 14:17
You need to use a whitelist and then if the column name matches insert it as text. You can't bind tables/columns. Also usually `for($n=1; $row = $result->fetch_assoc(); $n++)` is written as `while($row = $result->fetch_assoc()) {` — user3783243, Feb 14 '20 at 14:18
_“the ploblem is that they both return the same result”_ - that problem results from the fact that you used a placeholder in the first place. Placeholders in a prepared statement automatically mean _data_, not SQL keywords / syntax / identifiers. You are not ordering by a _column_ here, but by the static value `creationdate`. You _wanted_ `ORDER BY creationdate ASC`, but what you actually did here, is the equivalent of `ORDER BY 'creationdate' ASC`. — misorude, Feb 14 '20 at 14:24
@user3783243 I've written it as a for to `echo` the *id* number too — , Feb 14 '20 at 14:29
@user3783243 in this case you're right, but this was just an example to solve my problem — , Feb 14 '20 at 15:25

score 0 · Answer 1 · answered Feb 14 '20 at 14:19

0

This is a bit long for a comment.

You cannot replace an identifier in a SQL statement using a parameter. Examples of identifiers are column and table names. You also cannot replace SQL keywords or operators, such as INNER or =.

In your case, you are substituting a parameter with a constant value. It is treated as the value, not as a column reference (or expression). Hence, ASC and DESC do not differentiate among equal values.

answered Feb 14 '20 at 14:19

Gordon Linoff

1,242,037
58
646
786

1

So I would write something like ` $sql = "SELECT $from FROM `accountlist` ORDER BY $what ASC";`, wouldn't I? (obviously after a swith to filter my identifiers) – Feb 14 '20 at 14:28
@stacker Yeah, it should work – Simo Pelle Feb 14 '20 at 14:31

Two questions about ORDER BY using Prepared Statements

1 Answers1