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?


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.

| improve this answer | |
  • The problem is that my query looks like SELECT * FROM {$wpdb->posts} WHERE $val $val1 $val2 Depending on which items the person used in the advanced search, some of those variables are empty, some actually have a condition. – coopersita Apr 26 '13 at 16:53
  • There are at least a couple of ways to deal with that. I'll edit the answer. – s_ha_dum Apr 26 '13 at 16:57
  • Thanks. Would this also work with JOIN clauses? I have another query where depending on the fields chosen, some JOIN statements need to be added (I though adding them this way would make the search more efficient, than doing JOINs I may not even need.) – coopersita Apr 26 '13 at 17:40
  • It should work with any piece of the query so long as you keep the parameters synchronized. You can do very complex stuff with that technique. – s_ha_dum Apr 26 '13 at 17:43

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 );
        return addslashes( $string );

… but you shouldn’t use that anymore.

| improve this answer | |

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.