SQL ORDER BY using prepared statements

Question

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

what else would I need @YourCommonSense – user2827317 Sep 29 '13 at 22:17 — user2827317, Sep 29 '13 at 22:17

score 2 · Answer 1 · answered Sep 29 '13 at 21:15

2

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

answered Sep 29 '13 at 21:15

Mihai

26,325
7
66
81

score 0 · Accepted Answer · edited May 23 '17 at 12:03

0

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);

edited May 23 '17 at 12:03

Community

1
1

answered Sep 29 '13 at 21:19

paulsm4

114,292
17
138
190

Hi @paulsm4 your comment told me how it's not possible, however the workaround of mysql_real_escape_string requires a direct connection to the mysql database, and does not work with PDO. Are there any other work arounds? – user2827317 Sep 29 '13 at 22:16
Hi - See my addendum above – paulsm4 Sep 29 '13 at 22:36
May I ask you two questions? 1. Why you're trying to answer questions in the area you have no idea in? 2. Would you please completely delete that statement on "mysql_real_escape_char" along with accompanying link? – Your Common Sense Sep 30 '13 at 05:13

SQL ORDER BY using prepared statements

2 Answers2

Linked