[wp-trac] [WordPress Trac] #54836: Huge error logs filled with "WordPress database error Illegal mix of collations" errors caused by spammers

[WordPress Trac]

#54836: Huge error logs filled with "WordPress database error Illegal mix of
collations" errors caused by spammers
--------------------------+------------------------------
 Reporter:  jh20001       |       Owner:  (none)
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Database      |     Version:
 Severity:  critical      |  Resolution:
 Keywords:                |     Focuses:
--------------------------+------------------------------

Comment (by domainsupport):

 We've just found this ticket when we found similar errors in our logs.

 You can replicate the issue by performing a site search for an emoji and
 text such as "🥳 test" or as a query string `/?s=🥳+test` when your site's
 database's `wp_posts` table are using a `utf8mb3_%` collation.

 This will create the following error ...

 {{{
 [02-May-2024 08:21:37 UTC] WordPress database error Illegal mix of
 collations (utf8mb3_general_ci,IMPLICIT) and
 (utf8mb4_unicode_520_ci,COERCIBLE) for operation 'like' for query SELECT
 SQL_CALC_FOUND_ROWS  wp_posts.ID
                                          FROM wp_posts
                                          WHERE 1=1  AND
 (((wp_posts.post_title LIKE '%🥳%') OR (wp_posts.post_excerpt LIKE '%🥳%')
 OR (wp_posts.post_content LIKE '%🥳%')) AND ((wp_posts.post_title LIKE
 '%test%') OR (wp_posts.post_excerpt LIKE '%test%') OR
 (wp_posts.post_content LIKE '%test%')))  AND ((wp_posts.post_type = 'post'
 AND (wp_posts.post_status = 'publish'
 OR wp_posts.post_status = 'private')) OR (wp_posts.post_type = 'page' AND
 (wp_posts.post_status = 'publish'
 OR wp_posts.post_status = 'private')) OR (wp_posts.post_type =
 'attachment' AND (wp_posts.post_status = 'publish'
 OR wp_posts.post_status = 'private')) OR (wp_posts.post_type = 'product'
 AND (wp_posts.post_status = 'publish'
 OR wp_posts.post_status = 'private')))

                                          ORDER BY (CASE WHEN
 wp_posts.post_title LIKE '%🥳 test%' THEN 1 WHEN wp_posts.post_title LIKE
 '%🥳%' AND wp_posts.post_title LIKE '%test%' THEN 2 WHEN
 wp_posts.post_title LIKE '%🥳%' OR wp_posts.post_title LIKE '%test%' THEN
 3 WHEN wp_posts.post_excerpt LIKE '%🥳 test%' THEN 4 WHEN
 wp_posts.post_content LIKE '%🥳 test%' THEN 5 ELSE 6 END),
 wp_posts.post_date DESC
                                          LIMIT 0, 5 made by require('wp-
 blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query,
 WP_Query->get_posts
 }}}

 Should emoji's even be allowed / necessary in site searches? We have only
 ever seen this from bot / spam activity.

 At the very least can we add a filter to the site search to strip out
 emoji's etc from search queries if database tables are using a `utf8mb3_%`
 collation?

 Has anyone confirmed that having a `wp_posts` table with `utf8mb4_`
 collation fixes this issue?

 Oliver

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/54836#comment:7>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform