6

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?

3 Answers 3

8

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.

2
  • 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, 2014 at 6:00
  • Would sanitize_text_field() do enough to keep the data safe?
    – Jack
    Jan 12, 2018 at 0:34
3

As of Mar 29, 2023:

As of milestone 6.2, which was released Mar 29, you can now use %i instead of %s as a placeholder inside $wpdb-prepare() statements for table and column names. Here's a dev-note about it with examples.

1
  • The WPCS though still have this as unsupported, "Unsupported placeholder used in $wpdb->prepare(). Found: "%i" (WordPress.DB.PreparedSQLPlaceholders.UnsupportedPlaceholder)
    – GeorgeP
    Feb 13 at 2:10
2

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 and acknowledge you have read our privacy policy.

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