[wp-trac] [WordPress Trac] #56802: Query: Post IDs cached for search and other 'LIKE' queries are unreachable

[WordPress Trac]

#56802: Query: Post IDs cached for search and other 'LIKE' queries are unreachable
--------------------------+------------------------------
 Reporter:  dlh           |       Owner:  (none)
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Query         |     Version:  trunk
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:
--------------------------+------------------------------
Description changed by dlh:

Old description:

> [53941] added caching for the post ID database query in `WP_Query`. The
> cache key for the post IDs is determined by hashing (among other details)
> the `$request` property of the query, which contains the generated SQL.
>
> At the time that this cache key is generated, the SQL in `$request` still
> contains escaped placeholders from `wpdb::add_placeholder_escape()`. For
> example:
>
> {{{
>         SELECT SQL_CALC_FOUND_ROWS  wp_posts.*
>         FROM wp_posts
>         WHERE 1=1  AND (((wp_posts.post_title LIKE
> '{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}hello{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}')
> OR (wp_posts.post_excerpt LIKE
> '{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}hello{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}')
> OR (wp_posts.post_content LIKE
> '{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}hello{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}')))
> 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')))
>
>         ORDER BY wp_posts.post_title LIKE
> '{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}hello{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}'
> DESC, wp_posts.post_date DESC
>         LIMIT 0, 10
> }}}
>
> `wpdb` generates this placeholder once per request using `uniqid()`,
> making it unlikely that the placeholder will be the same across multiple
> requests.
>
> As a result, the same search will generate different cache keys on each
> request, since the SQL will differ along with the placeholder. The IDs
> will be cached, but that cache will probably never be found.
>
> From what I can tell, this issue affects search queries (with `s`) and
> meta queries that use `'compare' => 'LIKE'` or `'compare_key' => 'LIKE'`
> (or `NOT LIKE`). Presumably, any other queries that include placeholders
> and use `wpdb::prepare()` would also be affected.
>
> I'm not quite sure what the best course for the issue would. My first
> thought was to expand, in `WP_Query`,
>
> `$id_query_is_cacheable = ! str_contains( strtoupper( $orderby ), '
> RAND(' );`
>
> to include
>
> `&& ! str_contains( $this->request, $wpdb->placeholder_escape() )`
>
> However, calling `wpdb::placeholder_escape()` has a side-effect of adding
> a filter to `query`, and that might not be desirable.
>
> Perhaps a naive check like `! str_contains( $this->request, " LIKE '{" )`
> would do the trick, but it's possible that that would catch unrelated
> queries.

New description:

 [53941] added caching for the post ID database query in `WP_Query`. The
 cache key for the post IDs is determined by hashing (among other details)
 the `$request` property of the query, which contains the generated SQL.

 At the time that this cache key is generated, the SQL in `$request` still
 contains escaped placeholders from `wpdb::add_placeholder_escape()`. For
 example:

 {{{
         SELECT SQL_CALC_FOUND_ROWS  wp_posts.*
         FROM wp_posts
         WHERE 1=1  AND (((wp_posts.post_title LIKE
 '{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}hello{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}')
 OR (wp_posts.post_excerpt LIKE
 '{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}hello{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}')
 OR (wp_posts.post_content LIKE
 '{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}hello{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}')))
 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')))

         ORDER BY wp_posts.post_title LIKE
 '{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}hello{6bf47a1ba3fa507a5e63620d433b08dd0a8c3664854a451088b2fd023cb1d0f0}'
 DESC, wp_posts.post_date DESC
         LIMIT 0, 10
 }}}

 `wpdb` generates this placeholder once per request using `uniqid()`,
 making it unlikely that the placeholder will be the same across multiple
 requests.

 As a result, the same search will generate different cache keys on each
 request, since the SQL will differ along with the placeholder. The IDs
 will be cached, but that cache will probably never be found.

 From what I can tell, this issue affects search queries (with `'s'`) and
 meta queries that use `'compare' => 'LIKE'` or `'compare_key' => 'LIKE'`
 (or `NOT LIKE`). Presumably, any other queries that include placeholders
 and use `wpdb::prepare()` would also be affected.

 I'm not quite sure what the best course for the issue would be. My first
 thought was to expand, in `WP_Query`,

 `$id_query_is_cacheable = ! str_contains( strtoupper( $orderby ), ' RAND('
 );`

 to include

 `&& ! str_contains( $this->request, $wpdb->placeholder_escape() )`

 However, calling `wpdb::placeholder_escape()` has a side-effect of adding
 a filter to `query`, and that might not be desirable.

 Perhaps a naive check like `! str_contains( $this->request, " LIKE '{" )`
 would do the trick, but it's possible that that would catch unrelated
 queries.

--

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