[wp-trac] [WordPress Trac] #56933: Unexpected quotes around search text in custom LIKE queries

[WordPress Trac]

#56933: Unexpected quotes around search text in custom LIKE queries
-------------------------------------------------+-------------------------
 Reporter:  AlanP57                              |       Owner:
                                                 |  hellofromTonya
     Type:  defect (bug)                         |      Status:  closed
 Priority:  normal                               |   Milestone:  6.1
Component:  Database                             |     Version:  6.1
 Severity:  normal                               |  Resolution:  fixed
 Keywords:  has-testing-info commit has-patch    |     Focuses:
  has-unit-tests dev-reviewed                    |
-------------------------------------------------+-------------------------

Comment (by hellofromTonya):

 = Test Report

 == Env

 * Plugins: none active
 * Must-use plugin:
 https://gist.github.com/hellofromtonya/de5d59a9c42820f3395667883fac3f19
 * Theme: TT2
 * WP: 6.0.3 and current 6.1-branch
 * OS: macOS
 * localhost: WP Local and wp-env

 == Test Instructions

 Note: The hex codes will be different in your testing.

 * In your test site, create a `mu-plugins` folder inside of `wp-content`.
 * Copy and paste
 [https://gist.github.com/hellofromtonya/de5d59a9c42820f3395667883fac3f19
 this file] into that new folder. This code is the sample code provided in
 the ticket's description but made into a must-use plugin for testing
 purposes.
 * Using WordPress 6.0.3, open the site in the front-end and note what is
 rendered on the screen:

 {{{#!php
 (select wp_posts.ID, post_title, wp_mgmlp_folders.folder_id, pm.meta_value
 as attached_file, 'a' as item_type
 from wp_posts
 LEFT JOIN wp_mgmlp_folders ON(wp_posts.ID = wp_mgmlp_folders.post_id)
 LEFT JOIN wp_postmeta AS pm ON (pm.post_id = wp_posts.ID)
 LEFT JOIN wp_users AS us ON (wp_posts.post_author = us.ID)
 where post_type = 'mgmlp_media_folder' and pm.meta_key =
 '_wp_attached_file' and SUBSTRING_INDEX(pm.meta_value, '/', -1) like
 '{ec3ef6eec1ef785abece889c0e9046e9297b06b512e407f3f726977705f81079}hello{ec3ef6eec1ef785abece889c0e9046e9297b06b512e407f3f726977705f81079}')
 union all
 (select wp_posts.ID, post_title, wp_mgmlp_folders.folder_id, pm.meta_value
 as attached_file, 'b' as item_type
 from wp_posts
 LEFT JOIN wp_mgmlp_folders ON( wp_posts.ID = wp_mgmlp_folders.post_id)
 LEFT JOIN wp_postmeta AS pm ON (pm.post_id = wp_posts.ID)
 LEFT JOIN wp_users AS us ON (wp_posts.post_author = us.ID)
 where post_type = 'attachment' and pm.meta_key = '_wp_attached_file' and
 SUBSTRING_INDEX(pm.meta_value, '/', -1) like
 '{ec3ef6eec1ef785abece889c0e9046e9297b06b512e407f3f726977705f81079}hello{ec3ef6eec1ef785abece889c0e9046e9297b06b512e407f3f726977705f81079}')
 order by attached_file
 }}}

 The hex codes will be different. Note that the `hello` inserted between
 the hex codes is not wrapped in single quotation marks `}hello{`.

 * Switch to 6.1-RC5.
 * Refresh the home page on the front-end. Note that the `hello` string is
 wrapped in single quotation marks `}'hello'{` 🐞

 {{{#!php
 (select wp_posts.ID, post_title, wp_mgmlp_folders.folder_id, pm.meta_value
 as attached_file, 'a' as item_type
 from wp_posts
 LEFT JOIN wp_mgmlp_folders ON(wp_posts.ID = wp_mgmlp_folders.post_id)
 LEFT JOIN wp_postmeta AS pm ON (pm.post_id = wp_posts.ID)
 LEFT JOIN wp_users AS us ON (wp_posts.post_author = us.ID)
 where post_type = 'mgmlp_media_folder' and pm.meta_key =
 '_wp_attached_file' and SUBSTRING_INDEX(pm.meta_value, '/', -1) like
 '{b11657509e9e1a989804104976342f19d25c708d637da20f8beca38a8de081be}'hello'{b11657509e9e1a989804104976342f19d25c708d637da20f8beca38a8de081be}')
 union all
 (select wp_posts.ID, post_title, wp_mgmlp_folders.folder_id, pm.meta_value
 as attached_file, 'b' as item_type
 from wp_posts
 LEFT JOIN wp_mgmlp_folders ON( wp_posts.ID = wp_mgmlp_folders.post_id)
 LEFT JOIN wp_postmeta AS pm ON (pm.post_id = wp_posts.ID)
 LEFT JOIN wp_users AS us ON (wp_posts.post_author = us.ID)
 where post_type = 'attachment' and pm.meta_key = '_wp_attached_file' and
 SUBSTRING_INDEX(pm.meta_value, '/', -1) like
 '{b11657509e9e1a989804104976342f19d25c708d637da20f8beca38a8de081be}'hello'{b11657509e9e1a989804104976342f19d25c708d637da20f8beca38a8de081be}')
 order by attached_file
 }}}


 * Switch to the current 6.1 branch (which has the revert).
 * Refresh the home page on the front-end. Note the `hello` is no longer
 wrapped in single quotation marks (`}hello{`) which is the same behavior
 in 6.0.3.

 {{{#!php
 (select wp_posts.ID, post_title, wp_mgmlp_folders.folder_id, pm.meta_value
 as attached_file, 'a' as item_type
 from wp_posts
 LEFT JOIN wp_mgmlp_folders ON(wp_posts.ID = wp_mgmlp_folders.post_id)
 LEFT JOIN wp_postmeta AS pm ON (pm.post_id = wp_posts.ID)
 LEFT JOIN wp_users AS us ON (wp_posts.post_author = us.ID)
 where post_type = 'mgmlp_media_folder' and pm.meta_key =
 '_wp_attached_file' and SUBSTRING_INDEX(pm.meta_value, '/', -1) like
 '{5322ec299a9c19e0eef425bd8c57a795cdf1cde3421a119c904b36219a4d6651}hello{5322ec299a9c19e0eef425bd8c57a795cdf1cde3421a119c904b36219a4d6651}')
 union all
 (select wp_posts.ID, post_title, wp_mgmlp_folders.folder_id, pm.meta_value
 as attached_file, 'b' as item_type
 from wp_posts
 LEFT JOIN wp_mgmlp_folders ON( wp_posts.ID = wp_mgmlp_folders.post_id)
 LEFT JOIN wp_postmeta AS pm ON (pm.post_id = wp_posts.ID)
 LEFT JOIN wp_users AS us ON (wp_posts.post_author = us.ID)
 where post_type = 'attachment' and pm.meta_key = '_wp_attached_file' and
 SUBSTRING_INDEX(pm.meta_value, '/', -1) like
 '{5322ec299a9c19e0eef425bd8c57a795cdf1cde3421a119c904b36219a4d6651}hello{5322ec299a9c19e0eef425bd8c57a795cdf1cde3421a119c904b36219a4d6651}')
 order by attached_file
 }}}

 == Test Results

 * Able to reproduce the reported issue 🐞 ✅
 * Confirmed the current version of 6.1-branch with the revert PR restores
 the previous behavior ✅

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