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

Question

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?

Answer 1

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.

Answer 2

Use mysqli_real_escape_string().

The core uses still the deprecated mysql_real_escape_string() or add_slashes() in wpdb::_real_escape() …

/**
 * Real escape, using mysql_real_escape_string() or addslashes()
 *
 * @see mysql_real_escape_string()
 * @see addslashes()
 * @since 2.8.0
 * @access private
 *
 * @param  string $string to escape
 * @return string escaped
 */
function _real_escape( $string ) {
    if ( $this->dbh && $this->real_escape )
        return mysql_real_escape_string( $string, $this->dbh );
    else
        return addslashes( $string );
}

… but you shouldn’t use that anymore.