Matching "curly" quotes against standard quotes in DB query

Question

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?

Answer 1

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.