Use wpdb->prepare for `order by` column name

2 Views

In my current plugin, I am picking up the order by column as user input. So I thought of using wpdb->prepare to escape it. 

$wpdb->get_results($wpdb->prepare("Select id from $wpdb->posts order by %s %s", $order_by_col, $order_by);

This is not working because it gets turned into

Read More
select id from wp_posts order by 'post_date' 'asc'

(Note the quotes)

So is there way to remove the quotes? Also is this a proper way to use $wpdb->prepare()? Should I manually escape it myself?

Post Views: 2

Related posts

Leave a Reply

3 comments

  1. You can’t use prepare for column names, and you can’t really use it for the sort order either. prepare will always quote the string. You will need to swap in the values yourself. Rather than try to “sanitize” the data, I’d use a white-list approach.

    $orderby = array(
  'date' => 'post_date',
  // etc
);
$sortorder = array(
  'asc' => 'ASC',
  'desc' => 'DESC',
);
$orderbycol = 'ID'; // just a default
if (isset($_GET['orderby'])
  && isset($allowed[$_GET['orderby']])) {
  $orderbycol = $allowed[$_GET['orderby']];
}
$order = 'ASC';
if (isset($_GET['order'])
  && isset($sortorder[$_GET['order']])) {
  $order = $sortorder[$_GET['order']];
} 

$sql = "Select id from $wpdb->posts order by $orderbycol $order";
echo $sql;

    Though, I wonder why you are not using WP_Query for this.