PDO Column Sort Not Sorting

Question

When I click on the Username link below it is not sort and I am not sure why.

 if(isset($_GET['field'])) {

    $orderby = $_GET['field'];
$orderby2 = $_GET['sort'];

}else{
$orderby = "id";
$orderby2 = "ASC";
} if($_GET['sort'] == "ASC") {
    $sortby = "DESC";
}else{
    $sortby = "ASC";

}

Link to sort:

<th style="text-align: center;padding:10px;white-space: nowrap;" width="auto" class="rounded-company" scope="col"><a href="dash.php?field=user_name&sort=<?php echo $sortby;?>">Username</a></th>

if(isset($_REQUEST['txtKey'])) {

   $con = "%".$_REQUEST['txtKey']."%";
}

    $result = $db->dbh->prepare("SELECT * FROM ruj_users WHERE user_name like :textKey ORDER BY :order :order2");
    $result->bindValue(":textKey", isset($con) ? $con : null, PDO::PARAM_STR);
    $result->bindParam(":order", $orderby, PDO::PARAM_STR);
    $result->bindParam(":order2", $orderby2, PDO::PARAM_STR);
    $result->execute();
    $result = $result->fetchAll(PDO::FETCH_ASSOC);

    $result2 = $db->dbh->prepare("SELECT * FROM ruj_users WHERE user_name like :textKey ORDER BY :order :order2");
    $result->bindValue(":textKey", isset($con) ? $con : null, PDO::PARAM_STR);
    $result->bindParam(":order", $orderby, PDO::PARAM_STR);
    $result->bindParam(":order2", $orderby2, PDO::PARAM_STR);
    $result2->execute();
    $resultCount = $result2->rowCount();

    if(isset($_REQUEST['txtKey']))$str='&field='.$_GET['field'].'&sort='.$_GET['sort']."&txtKey=".$_REQUEST['txtKey'];

Please let me know what I am doing or if I am missing something.

Column names (including in ORDER BY clauses) and Table names can't be bound — Mark Baker, Mar 13 '13 at 15:52
You can only use `?` to substitute values, not column names. You need to use string interpolation or concatenation for this. — Barmar, Mar 13 '13 at 16:05
@Barmar I updated as you said (please look at updated code) and it sill doesn't work. — Claude Grecea, Mar 13 '13 at 16:26
Duplicate of [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php/8255054#8255054) — Your Common Sense, Mar 13 '13 at 17:41

Barmar · Accepted Answer · 2013-03-13T17:35:20.027

2

You can't use bindParam() to substitute column names, only expression values. So you have to use string interpolation:

$result = $db->dbh->prepare("SELECT * FROM ruj_users WHERE user_name like :textKey ORDER BY $orderby $orderby2);

Unfortunately, this opens you up to SQL injection, so you need to validate the inputs before doing this. E.g.

if (preg_match('/[^a-z0-1_]/i', $orderby)) {
    // report invalid sort field
}
if (!preg_match('/^(asc|desc)$/i', $orderby2)) {
    // report invalid sort direction
}

edited Mar 13 '13 at 17:35

answered Mar 13 '13 at 16:33

Barmar

741,623
53
500
612

Can you provide me with how to do this? I am still learning and I am not sure what you mean. – Claude Grecea Mar 13 '13 at 17:28
sprint() is a cleaner way of building the select string; and the orderby clause should still be validated against a whitelist of options – Mark Baker Mar 13 '13 at 20:41
@MarkBaker I like to use `sprintf()` when I'm substituting complicated expressions, but interpolation is fine when it's just a variable. And I think validating `$orderby` against a list of columns is overkill, it means you have to keep it in sync with database changes. It's enough to ensure that it's just alphanumeric. – Barmar Mar 13 '13 at 20:59
Thanks, that helped. I remember using this method before but I haven't got the terminology down yet. – Claude Grecea Mar 14 '13 at 02:49

PDO Column Sort Not Sorting

1 Answers1