CODEWITHSUNDEEP
phpmysqlpdo
user2827317
user2827317

Reputation: 37

SQL ORDER BY using prepared statements

I've tried all different kinds of variations of this code and can't get it to work, can anyone point me in the right direction?

if(isset($_GET['s']) And isset($_GET['o'])) {
    if(strip_tags(htmlspecialchars($_GET['s'])) === 's') $sortingby = 'sender';
    if(strip_tags(htmlspecialchars($_GET['s'])) === 't') $sortingby = 'title';
    if(strip_tags(htmlspecialchars($_GET['s'])) === 'd') $sortingby = 'timestamp';
    if(strip_tags(htmlspecialchars($_GET['o'])) === 'a') $orderingby = 'ASC';
    if(strip_tags(htmlspecialchars($_GET['o'])) === 'd') $orderingby = 'DESC';
    echo '<br />';
    echo $sortingby;
    echo $orderingby;
}
$stmt = $pdo->prepare("SELECT * FROM messages WHERE
                                    receiver = :id AND rhide = 0 
                                    ORDER BY :sortingby :orderingby
                                    ");
$stmt->execute(array(
                                ':id'=>$id,
                                ':sortingby'=>$sortingby,
                                ':orderingby'=>$orderingby
                                ));
$messages = $stmt->fetchAll(PDO::FETCH_ASSOC);

The variables are assigned correctly when I echo them out, it just seems like my content is being ignored

Upvotes: 0

Views: 3568

Answers (2)

paulsm4
paulsm4

Reputation: 121649

Look at these links:

Mysqli Prepare Statements + Binding Order BY

As the php.net link you found states, you cannot use bind variables for identifiers. You'll need a workaround. mysql_real_escape_char would certainly be one way.

... and ...

How do I use pdo's prepared statement for order by and limit clauses?

For this reason the ORDER BY fields should form part of the SQL string passed into the prepare() method, rather than being bound to the query prior to execute().

==========================================================

ADDENDUM:

Since you're already effectively validating the column names and "ASC/DESC" clause before your "prepare", there's no danger of SQL Injection.

I'd just build the string:

$sql = 
  "SELECT * FROM messages WHERE " .
  "receiver = :id AND rhide = 0 " .
  "ORDER BY " . $sortingby . " " . $orderingby;
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
$messages = $stmt->fetchAll(PDO::FETCH_ASSOC);

Upvotes: 0

Mihai
Mihai

Reputation: 26784

Only data can be bound with placeholders,column or table names cannot be bound.

Upvotes: 2

Related Questions