Unable to run named placeholder for order by ASC in php pdo

Question

I am new to PHP PDO and trying to use named placeholder at the place of ORDER BY ASC. Sometime in simple query page this run very successfully but unable to run in the following query:

PHP CODE IS:

$price_sort = "ASC";
$keyword = "samsung glaxy";
$limit = 0;
    $query = $db->prepare("SELECT *, MATCH(title) against (:keyword) as 'relevence'
                FROM view_store_items_grid 
                WHERE MATCH(title) against(:keyword) 
                ORDER BY relevence DESC, price :order
                LIMIT :limit,25");
    $query->bindValue(":keyword",$keyword);
    $query->bindValue(":order",$price_sort);
    $query->bindValue(":limit", $limit, PDO::PARAM_INT);
    $query->execute();

When I remove placeholder :order with ASC this run and gives result but when I use this placeholder, I get empty result.

For Fetching data or to show fetched result I am using

while ($row = $query->fetch(PDO::FETCH_ASSOC)) :
       extract($row);
       echo "$name";
endwhile;

What I am doing wrong and How I can use many named placeholder in query at different places?

you cannot use a named parameter marker of the same name more than once in a prepared statement — Saty, Jul 20 '16 at 10:25
You mean I should use two different placeholder at the place of `:keyword` ? — Syed Aqeel, Jul 20 '16 at 10:27
I replaced `keyword` with `keyword_order` but this also does not solved my problem. — Syed Aqeel, Jul 20 '16 at 10:29
Yes `$query->bindValue(":keyword_order",$keyword);` and replaced it with second paramater `keyword` — Syed Aqeel, Jul 20 '16 at 10:30
You can't bind DML. In this case you'll need to choose from a whitelist of `"asc", "desc"` and then concatenate that into the query. — timclutton, Jul 20 '16 at 10:42

score 2 · Accepted Answer · edited May 23 '17 at 12:32

You should not bind ASC/DESC in prepared statement. Parameters are automatically quoted, and ASC/DESC shouldn't be quoted. this is the same reason that table and column names can't be parameters.

Instead you can do like that

$sql_query = "SELECT *, MATCH(title) against (:keyword) as 'relevence'
                FROM view_store_items_grid 
                WHERE MATCH(title) against(:keyword) 
                ORDER BY relevence DESC, price "
if($price_order == 'ASC'){
    $sql_query .= " ASC "
}else{
    $sql_query .= " DESC  "
}
$sql_query .= " LIMIT :limit,25  "
$query = $db->prepare($sql_query);

Refer How bindValue in LIMIT

Refer pdo binding asc/desc order dynamically

Also do not try to use the same named parameter twice in a single SQL statement, for example

<?php 
$sql = 'SELECT * FROM some_table WHERE  some_value > :value OR some_value < :value'; 
$stmt = $dbh->prepare($sql); 
$stmt->execute( array( ':value' => 3 ) ); 
?>

...this will return no rows and no error -- you must use each parameter once and only once. Apparently this is expected behavior (according to this bug report: http://bugs.php.net/bug.php?id=33886) because of portability issues.

Yes. according to you, when I replace with `ASC` then it runs. Thank you for your suggestion. — Syed Aqeel, Jul 20 '16 at 10:46
Hello Tamil, how you going? I got one similar question about PDO Limit Placeholders, is it possible to use the named placeholder to Limit inside the execute? https://stackoverflow.com/questions/72081221/error-to-selecet-when-use-pdo-prepared-limit?noredirect=1#comment127363151_72081221 — Sophie, May 02 '22 at 05:44

score 0 · Answer 2 · answered Jul 20 '16 at 10:56

This one solved this question by replacing the price_sort variable value with price ASC and removing the price before the placehoder :order

as:

$price_sort = "price ASC";

and the query is as:

$query = $db->prepare("SELECT *, MATCH(title) against (:keyword) as 'relevence'
                FROM view_store_items_grid 
                WHERE MATCH(title) against(:keyword) 
                ORDER BY relevence DESC, :order
                LIMIT :limit,25");

this solved my question with the help of answer of @tamil

Thank you

Unable to run named placeholder for order by ASC in php pdo

2 Answers2