4

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

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?

5

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.

  • The white-list approach is a good solution and thanks for that. The query I have in the question is just a sample. The one I am using in my code is really complex and went through the documentation of WP_Query and figured out there is no way to do what I want without a custom query. – Sudar Mar 24 '14 at 6:00
  • Would sanitize_text_field() do enough to keep the data safe? – Jack Nicholson Jan 12 '18 at 0:34
1

Whitelisting is a good option for 'ASC' 'DESC', but for table names on your ORDERBY clause, WordPress has a built-in function just for you!

https://codex.wordpress.org/Function_Reference/sanitize_sql_orderby

$order_by_col = sanitize_sql_orderby( $order_by_col );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.