0

I'm trying to do an SQL query and I don't understand something. I get a value with $ _POST, this value is equal to 'définition'. I made this request: $sql = "SELECT DISTINCT * FROM". $ wpdb-> prefix. "posts WHERE post_title LIKE '%". $ _POST ['value']. "% '";.

A var_dump($sql) gives "SELECT DISTINCT * FROM datatablename.posts WHERE post_title LIKE '% definition%'";.

If I do $res = $wpdb->get_results($sql);, I get an empty array

But, if in my code I put directly $sql = "SELECT DISTINCT * FROM datatablename.posts WHERE post_title LIKE '% definition%'"; (I immediately replace $_POST with my value), $res is an array with a post.

The problem stems from the accent, because if $_POST['value'] = 'finition' it's okay

My data table is in utf8mb4_unicode_ci.

What can be done to solve this problem?

2
  • 2
    As an aside you probably ought to $wpdb->esc_like() and esc_sql() your $_POST['value'] when assembling the search string.
    – Rup
    Nov 4 '20 at 9:40
  • But if you can't get MySQL to match definition with and without the accent then you possibly need some other search mechanism set up. Either add extra stripped-diacritics versions of titles and content to search, or use some external search mechanism that will handle this automatically.
    – Rup
    Nov 4 '20 at 9:42
1
+50

Your SQL command is highly insecure and open to security issues like SQL injection, so even if this may not answer the question, I strongly suggest you to use $wpdb->prepare() and $wpdb->esc_like() — the latter is used to escape the % character in SQL.

Additionally, you can simply use $wpdb->posts to output the table name for WordPress posts such as wp_posts.

And I noticed that in your SQL command:

  • The table name is incorrect because the FROM and $wpdb->prefix is concatenated as one word like FROMwp_posts.

  • There's a whitespace after the second % in the LIKE clause: %". $_POST['value']. "% ' — so that whitespace is probably not needed? Or that it could be the reason why the query did not return any results.

  • The var_dump() actually contains no accent — you used definition and not définition. Same goes with the direct one.

Now here's how your query or SQL command should be generated:

$value = $_POST['value'] ?? '';

// wrapped for brevity
$sql = $wpdb->prepare( "
SELECT DISTINCT *
FROM {$wpdb->posts}
WHERE post_title LIKE %s
", '%' . $wpdb->esc_like( $value ) . '%' );

$res = $wpdb->get_results( $sql );

And I actually tested the above code with a post with the title containing the word définition, and the query returned one result (which is a test post).

If my code doesn't work for you, you can try sanitize_text_field(), but that will strip HTML tags, among other things.

0

Here is a function to query posts with title "like" - returning either IDs or specified columns - escaping both the passed title and any requested column values:

/**
* Get post with title %like% search term
*
* @param       $title          Post title to search for
* @param       $method         wpdb method to use to retrieve results
* @param       $columns        Array of column rows to retrieve
*
* @since       0.3
* @return      Mixed           Array || False
*/
function posts_with_title_like( $title = null, $method = 'get_col', $columns = array ( 'ID' ) ){

    // sanity check ##
    if ( ! $title ) { return false; }

    // global $wpdb ##
    global $wpdb;

    // First escape the $columns, since we don't use it with $wpdb->prepare() ##
    $columns = \esc_sql( $columns );

    // now implode the values, if it's an array ##
    if( is_array( $columns ) ){
        $columns = implode( ', ', $columns ); // e.g. "ID, post_title" ##
    }

    // run query ##
    $results = $wpdb->$method (
            $wpdb->prepare (
            "
                SELECT $columns
                FROM $wpdb->posts
                WHERE {$wpdb->posts}.post_title LIKE %s
            "
            ,   \esc_sql( '%'.$wpdb->esc_like( trim( $title )  ).'%' )
            )
        );

    #var_dump( $results );

    // return results or false ##
    return $results ? $results : false ;

}

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.