wpdb - get_results query with accent

admin管理员组

文章数量:1317909

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?

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?

• wpdb

Share Improve this question edited Nov 4, 2020 at 11:30 bueltge 17.1k77 gold badges6262 silver badges9797 bronze badges asked Oct 24, 2020 at 17:04 user7734861user7734861 5333 bronze badges 2

• 2 As an aside you probably ought to $wpdb->esc_like() and esc_sql() your $_POST['value'] when assembling the search string. – Rup Commented Nov 4, 2020 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 Commented Nov 4, 2020 at 9:42

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 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.

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 ;

}

本文标签： wpdbgetresults query with accent