CODEWITHSUNDEEP
phpmysqlpdo
Marlorn
Marlorn

Reputation: 529

How do I set ORDER BY params using prepared PDO statement?

I'm having problems using params in the ORDER BY section of my SQL. It doesn't issue any warnings, but prints out nothing. 

$order = 'columnName';
$direction = 'ASC';

$stmt = $db->prepare("SELECT field from table WHERE column = :my_param ORDER BY :order :direction");
$stmt->bindParam(':my_param', $is_live, PDO::PARAM_STR);
$stmt->bindParam(':order', $order, PDO::PARAM_STR);
$stmt->bindParam(':direction', $direction, PDO::PARAM_STR);
$stmt->execute();

The :my_param works, but not :order or :direction. Is it not being internally escaped correctly? Am I stuck inserting it directly in the SQL? Like so:

$order = 'columnName';
$direction = 'ASC';

$stmt = $db->prepare("SELECT * from table WHERE column = :my_param ORDER BY $order $direction");

Is there a PDO::PARAM_COLUMN_NAME constant or some equivalent?

Thanks!

Upvotes: 50

Views: 69636

Answers (7)

Your Common Sense
Your Common Sense

Reputation: 157870

Yes, you're stuck inserting it directly in the SQL. With some precautions, of course. Every operator/identifier must be hardcoded in your script, like this:

$orders=array("name","price","qty");
$key=array_search($_GET['sort'],$orders);
$order=$orders[$key];
$query="SELECT * from table WHERE is_live = :is_live ORDER BY $order";

Same for the direction.

I wrote a whitelisting helper function to be used in such cases, it greatly reduces the amount of code that needs to be written:

$order = white_list($order, ["name","price","qty"], "Invalid field name");
$direction = white_list($direction, ["ASC","DESC"], "Invalid ORDER BY direction");

$sql = "SELECT field from table WHERE column = ? ORDER BY $order $direction";
$stmt = $db->prepare($sql);
$stmt->execute([$is_live]);

The idea here is to check the value and raise an error in case it is not correct.

Upvotes: 69

goat
goat

Reputation: 31813

I don't think you can get ASC/DESC as part of the prepared statement, but the column you can if you list them all in the sql query like so:

// Validate between 2 possible values:
$sortDir = isset($_GET['sortDir']) && $_GET['sortDir'] === 'ASC' ? 'ASC' : 'DESC';
$sql = "
...
     order 
        by 
           case :orderByCol
               when 'email' then email
               when 'age' then age
               else surname
           end
           $sortDir
";
$stmt = $db->prepare($sql);
$stmt->bindParam(':orderByCol', $someColumn);
$stmt->execute();

Since ASC/DESC is only two possible values, you can easily validate and select between them as hardcoded values using php code.

You could also make use of the ELT(FIELD(,,,,,),,,,,) functions for this, but then ordering will always be done as a string, even if the column is a numeric data type that should be sorted using numeric semantics / collation.

Upvotes: 7

bithom
bithom

Reputation: 186

It is possible . You can use number instead of field name in the 'order by' clause. This is a number starting from 1 and is in the order of field names in the query. And you can concatenate a string in for ASC or DESC. For example "Select col1,col2,col3 from tab1 order by ? " + strDesc + " limit 10,5". strDesc=" ASC" / " DESC".

Upvotes: 0

rray
rray

Reputation: 2556

It's possible use prepared statements in ORDER BY clause, unfortunately you need pass the order of column insted of the name and is required set PDO_PARAM_INT with type.

In MySQL you can get the order of columns with this query:

SELECT column_name, ordinal_position FROM information_schema.columns 
WHERE table_name = 'table' and table_schema = 'database'

PHP code:

$order = 2;

$stmt = $db->prepare("SELECT field from table WHERE column = :param ORDER BY :order DESC");
$stmt->bindParam(':param', $is_live, PDO::PARAM_STR);
$stmt->bindParam(':order', $order, PDO::PARAM_INT);
$stmt->execute();

Upvotes: 10

cavila
cavila

Reputation: 7942

Unfortunely I guess you could not make it with prepared statements. It would make it no cacheable since different columns may have values that could be sorted with special sorting strategies.

Create query by using standard escapes and execute it directly.

Upvotes: -1

Turbotoast
Turbotoast

Reputation: 139

If I'm not entirely mistaken, Pascal is right.
The only binding possible in PDO is the binding of values, as you did with the ':my_param' parameter.
However, there's no harm done in: 

$stmt = $db->prepare("SELECT field from table WHERE column = :my_param ORDER BY ".$order ." ".$direction);
$stmt->bindParam(':my_param', $is_live, PDO::PARAM_STR);
$stmt->execute();

The only thing to take notice of would be the correct escaping of $order and $direction, but since you set them manually and didn't set them via user input, I think you're all set.

Upvotes: -4

Pascal MARTIN
Pascal MARTIN

Reputation: 400982

I don't think you can :

  • Use placeholders in an order by clause
  • Bind column names : you can only bind values -- or variables, and have their value injected in the prepared statement.

Upvotes: 15

Related Questions