[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:  (none)
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  6.1
Component:  Database      |     Version:  6.1
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:
--------------------------+---------------------
Changes (by hellofromTonya):

 * keywords:  needs-testing needs-unit-tests =>


Old description:

> I'm experiencing a similar issue and have just tested a like query with
> WP 6.1-RC5. The result is an SQL error and the output from the prepare
> function is like `'%'my search text'%'`. Notice, there is an extra set of
> quote marks around the search text.
>
> In some cases, I prefer not to use `WP_Query` when displaying data. This
> is the code from the plugin:
>
> {{{#!php
> <?php
>       $sql = $wpdb->prepare("(select $wpdb->posts.ID, post_title,
> {$wpdb->prefix}mgmlp_folders.folder_id, pm.meta_value as attached_file,
> 'a' as item_type
> from $wpdb->posts
> LEFT JOIN {$wpdb->prefix}mgmlp_folders ON($wpdb->posts.ID =
> {$wpdb->prefix}mgmlp_folders.post_id)
> LEFT JOIN $wpdb->postmeta AS pm ON (pm.post_id = $wpdb->posts.ID)
> LEFT JOIN $wpdb->users AS us ON ($wpdb->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
> '%%%s%%')
> union all
> (select $wpdb->posts.ID, post_title,
> {$wpdb->prefix}mgmlp_folders.folder_id, pm.meta_value as attached_file,
> 'b' as item_type
> from $wpdb->posts
> LEFT JOIN {$wpdb->prefix}mgmlp_folders ON( $wpdb->posts.ID =
> {$wpdb->prefix}mgmlp_folders.post_id)
> LEFT JOIN $wpdb->postmeta AS pm ON (pm.post_id = $wpdb->posts.ID)
> LEFT JOIN $wpdb->users AS us ON ($wpdb->posts.post_author = us.ID)
> where post_type = 'attachment' and pm.meta_key = '_wp_attached_file' and
> SUBSTRING_INDEX(pm.meta_value, '/', -1) like '%%%s%%') order by
> attached_file", $search_string, $search_string);
>
> }}}
>
> And here is an example of the SQL:
> {{{#!sql
> (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
> '{48bf6209debff2ee81208ffaee83c0ccfe32af6953d915a72a2fd46f1d0be2e1}'my
> search
> text'{48bf6209debff2ee81208ffaee83c0ccfe32af6953d915a72a2fd46f1d0be2e1}')
> 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
> '{48bf6209debff2ee81208ffaee83c0ccfe32af6953d915a72a2fd46f1d0be2e1}'my
> search
> text'{48bf6209debff2ee81208ffaee83c0ccfe32af6953d915a72a2fd46f1d0be2e1}')
> order by attached_file
> }}}
>
> This use to work in versions before 6.1.
>
> Original support topic: https://wordpress.org/support/topic/prepare-
> function-removes-percent-signs-from-like-sql-statement/.

New description:

 I'm experiencing a similar issue and have just tested a like query with WP
 6.1-RC5. The result is an SQL error and the output from the prepare
 function is like `'%'my search text'%'`. Notice, there is an extra set of
 quote marks around the search text.

 In some cases, I prefer not to use `WP_Query` when displaying data. This
 is the code from the plugin:

 {{{#!php
 <?php
       $sql = $wpdb->prepare("(select $wpdb->posts.ID, post_title,
 {$wpdb->prefix}mgmlp_folders.folder_id, pm.meta_value as attached_file,
 'a' as item_type
 from $wpdb->posts
 LEFT JOIN {$wpdb->prefix}mgmlp_folders ON($wpdb->posts.ID =
 {$wpdb->prefix}mgmlp_folders.post_id)
 LEFT JOIN $wpdb->postmeta AS pm ON (pm.post_id = $wpdb->posts.ID)
 LEFT JOIN $wpdb->users AS us ON ($wpdb->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
 '%%%s%%')
 union all
 (select $wpdb->posts.ID, post_title,
 {$wpdb->prefix}mgmlp_folders.folder_id, pm.meta_value as attached_file,
 'b' as item_type
 from $wpdb->posts
 LEFT JOIN {$wpdb->prefix}mgmlp_folders ON( $wpdb->posts.ID =
 {$wpdb->prefix}mgmlp_folders.post_id)
 LEFT JOIN $wpdb->postmeta AS pm ON (pm.post_id = $wpdb->posts.ID)
 LEFT JOIN $wpdb->users AS us ON ($wpdb->posts.post_author = us.ID)
 where post_type = 'attachment' and pm.meta_key = '_wp_attached_file' and
 SUBSTRING_INDEX(pm.meta_value, '/', -1) like '%%%s%%') order by
 attached_file", $search_string, $search_string);

 }}}

 And here is an example of the SQL:
 {{{#!sql
 (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
 '{48bf6209debff2ee81208ffaee83c0ccfe32af6953d915a72a2fd46f1d0be2e1}'my
 search
 text'{48bf6209debff2ee81208ffaee83c0ccfe32af6953d915a72a2fd46f1d0be2e1}')
 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
 '{48bf6209debff2ee81208ffaee83c0ccfe32af6953d915a72a2fd46f1d0be2e1}'my
 search
 text'{48bf6209debff2ee81208ffaee83c0ccfe32af6953d915a72a2fd46f1d0be2e1}')
 order by attached_file
 }}}

 >In WordPress 6.0.3 my SQL query works

 This worked on WP 6.0.3, but doesn't on 6.1-RC5.

 Original support topic: https://wordpress.org/support/topic/prepare-
 function-removes-percent-signs-from-like-sql-statement/.

--

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