Qfyilyi

I have text stored in post_title column:

you’re

Using this query:

function ma_post_title_filter($where, &$wp_query) {

    global $wpdb;

    if ( $search_term = $wp_query->get( 'ma_search_post_title' ) ) {

        $search_term = sanitize_text_field($search_term);

        $where .= ' AND ' . $wpdb->posts . '.post_title LIKE '%' . $wpdb->esc_like( $search_term ) . '%'';

    }

    return $where;

}

If $search_term is you’re, it works and finds the column. But when it is you're, it does not work (because it is a different single quotation mark).

How can I improve the query so both you’re and you're matches with you’re?

What I've tried so far

1. Use str_replace() on $search_term to replace ' with ’. Example: str_replace(''','’',$wpdb->esc_like( $search_term ))... the problem with this approach is that we will never match if the stored column is you're.


2. Do AND REPLACE on '.$wpdb->posts.'.post_title to replace ' with ’... but doesn't seem to work.

I'm aware the ideal solution is to adjust all the database instances of the single quotation to a standard one but this is not possible in my case.

What's the proper solution to this?

I wanted to point out that % is not the only placeholder that can be used in LIKE queries. You can use the underscore _ to indicate a single character as well. One approach could be to replace every punctuation character in a certain class with an underscore. This would allow "you're" to match "you’re" and vice-versa. (Granted, it would also allow "youvre" to match as well!)

<?php

function ma_post_title_filter($where, &$wp_query) {

    global $wpdb;

    $punct = ["’", "‘", "”", "“"];

    if ( $search_term = $wp_query->get( 'ma_search_post_title' ) ) {

        // escape any % or _ characters

        $search_term = $wpdb->esc_like($search_term);

        // replace desired punctuation characters with _

        $search_term = str_replace($punct, "_", $search_term);

        // do final escaping for safe queries

        $search_term = $wpdb->_real_escape($search_term);

        // this looks much neater with string interpolation vs concatenation

        $where .= " AND $wpdb->posts.post_title LIKE '%$search_term%'";

    }

    return $where;

}

You should, when passing values to a LIKE clause, use wpdb::esc_like() to ensure any user-supplied percent signs or underscores don't get in the way of things. sanitize_text_field() is meant to be used before output to HTML, and does nothing to make text safe for a database query. That's what wpdb::_real_escape() is for.

You can use MySQL RLIKE :

$where .= ' AND '. $wpdb->posts .'.post_title RLIKE '.*'. $wpdb->esc_like(str_replace(["’","'"],"['’]", $search_term) ) .'.*' ';

I would be using twice a LIKE statement, one with the original search term, the second one with a replacement of the ' by ’

You can use parameterized queries. In example, using wpdb::prepare() :

$searchTerm = "You're"; //Don't sanitize using a function, this will by done with parameters.

// query

$where .= ' AND ('. $wpdb->posts .'.post_title LIKE %s OR '. $wpdb->posts .'.post_title LIKE %s)'; //Notice the double %s

//I suppose $where contains your whole query

$sth = $wpdb->prepare($where, '%' . $searchTerm . '%', '%' . str_replace('’', ''', $search_term) . '%'); //This will associate the %s (a string) in the query by the value indicated in the function

$wpdb->query($sth);

The %s is a placeholder in the query to indicate to $wpdb->prepare() that there will be a string at this place.