1

I’m experiencing a bizarre issue between PHP and MySQL. I have the following code to get values for an HTML table ($sortfield is set to table1.colb DESC or table1.colb ASC, depending on the test).

if (!($stmt = $db_handle->prepare("SELECT table1.cola, table1.colb ORDER BY ? LIMIT ?,20"))) {
    exit('Prepare failed: (' . $db_handle->errno . ') ' . $db_handle->error);
}
if (!$stmt->bind_param('si', $sortfield, $start)) {
    exit('Binding parameters failed: (' . $stmt->errno . ') ' . $stmt->error);
}
if (!$stmt->execute()) {
    exit('Execute failed: (' . $stmt->errno . ') ' . $stmt->error);
}
$stmt->store_result();

This code executes with no errors, but the ORDER BY clause is totally ignored when its value is given as a variable. The results are sorted by table1.cola, as if ORDER BY were not present. If I replace ORDER BY ? with ORDER BY table1.colb (and modify bind_param accordingly), then the query behaves as expected and sorts by that column.

If I echo the complete statement with variables (i.e., echo "SELECT table1.cola, table1.colb ORDER BY $sortfield LIMIT $start,20") and manually run the result directly against MySQL, then the query works as expected.

$sortfield is taken from a GET method and processed by htmlentities() prior to statement preparation. It’s expected to be a string value, and that’s what it appears to be, so I’m quite confused about the outcome.

AstroPig7
  • 111
  • 4
  • You can't use bind vars for an ORDER BY clause or for LIMIT, only for actual __data values__ – Mark Baker May 28 '14 at 17:47
  • This particular answer is on the question: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/8255054#8255054 – Your Common Sense May 28 '14 at 17:49
  • That’s interesting, because the variable binding for `LIMIT` has been in place for several months and has been working without issues. Should it throw an error, or is it simply unsupported? Regardless, I altered the code so only `$sortfield` would be bound, and I got the same result. – AstroPig7 May 28 '14 at 17:49
  • use bindvalue http://stackoverflow.com/questions/2269840/php-pdo-bindvalue-in-limit – ɹɐqʞɐ zoɹǝɟ May 28 '14 at 17:50
  • It actually DOES work for the limit values, but for the order by it will simply order by passed string, which means unordered – Your Common Sense May 28 '14 at 17:51

0 Answers0