How to properly sanitize strings without $wpdb->prepare?

by

I’m working on an advanced search (which uses custom sql queries), and the query string is formed using variables. For example, if the person checks some options, only those conditions get added to the WHERE clause.

I can’t use $wpdb->prepare, since I want to be able to add variables to my query string that look something like:

$var = "AND pm.meta_value="%$_POST["val']%'";

I looked at like_escape(), but the documentation says:

Sanitizes $string for use in a LIKE expression of a SQL query. Will still need to be SQL escaped (with one of the above functions).

What would be an appropriate way to escape $val?

2 Answers
2

I can’t use $wpdb->prepare, since I want to be able to add variables
to my query string that look something like:

$var = "AND pm.meta_value="%$_POST["val']%'";

To get a literal % to pass through $wpdb->prepare just double it. You don’t need to be avoiding $wpdb->prepare.

Proof of concept:

var_dump($wpdb->prepare('SELECT * FROM {$wpdb->posts} WHERE post_title LIKE "%%%s%%"','Hello'));

A comment below suggests something a bit more complicated might be in order:

$var[] = 'post_title LIKE "%%%s%%"';
$var_data[] = 'Hello';
$var[] = 'post_name LIKE "%%%s%%"';
$var_data[] = 'Hi';
$var[] = 'post_date LIKE "%%%s%%"';
$var_data[] = 'Howdy';
var_dump($wpdb->prepare('SELECT * FROM {$wpdb->posts} WHERE post_title '.implode(' AND ',$var),$var_data));

Of course, in practice you would probably create $var and $var_data in some kind of loop such as:

foreach ($_POST as $k=>$v) {
  if ('abc' == $k) {
    $var[] = 'post_title LIKE "%%%s%%"';
    $var_data[] = $v; // should probably validate a bit
  } elseif(...) {
    // ...
  } else {
    // ...
  }
}

It is always possible to run prepare on each item as well:

$var[] = $wpdb->prepare('post_title LIKE "%%%s%%"','Hello');

But the method using the array strikes me as more elegant and only runs $wpdb->prepare once, for what that is worth.

Leave a Comment