WordPress.org

Make WordPress Core

Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#42653 closed defect (bug) (duplicate)

% sign is changed to alphanumeric combination of numbers in curly braces, by core.

Reported by: hostingreview Owned by:
Milestone: Priority: normal
Severity: normal Version: 4.8.3
Component: Query Keywords:
Focuses: template Cc:

Description

Here is the WP-Query:

<?php
                        $category        = trim( $options['category'] );
                        $category_object = get_term_by( 'slug', $category, 'hosting_providers_category' );
                        $category_mod    = str_replace( '-', '_', $category );


                        // Getting data from database
                        $args = [
                                'posts_per_page' => $options['limit'],
                                'post_type'      => 'hosting_providers',
                                'orderby'        => $options['orderby'],
                                'order'          => $options['order'],
                                'tax_query'      => [
                                        'relation' => 'AND',
                                        [
                                                'taxonomy' => 'hosting_providers_category',
                                                'field'    => 'slug',
                                                'terms'    => $category,
                                        ]
                                ],
                        ];


                        if ( ! in_array( $options['orderby'], [ 'post_title' ], true ) ) {

                                if ( $options['orderby'] === 'rank' ) {
                                        $args['orderby']    = 'meta_value_num';
                                        $args['meta_key']   = $category_mod . '_rank';
                                        $args['meta_query'] = [
                                                'key'     => $category_mod . '_rank',
                                                'value'   => [ '', null, 0 ],
                                                'compare' => 'NOT IN',
                                                'type'    => 'NUMERIC'
                                        ];

                                        if ( $options['rank_per_category'] == 'yes' ) {

                                                $args['meta_query'] = [
                                                        'relation'    => 'AND',
                                                        'rank_clause' => [
                                                                'key'     => "ranking_%_rank_in_category",
                                                                'value'   => 0,
                                                                'compare' => '>',
                                                                'type'    => 'NUMERIC'
                                                        ],
                                                        [
                                                                'key'   => "ranking_%_rank_category",
                                                                'value' => $category_object->term_id
                                                        ],
                                                ];

                                                $args['meta_key'] = 'ranking_%_rank_in_category';
                                                $args['orderby']  = [
                                                        'rank_clause' => 'ASC'
                                                ];
                                        }

                                } else {
                                        $args['orderby']  = 'meta_value';
                                        $args['meta_key'] = $options['orderby'];
                                }
                        }

                        if ( $options['show_alternatives_only'] == 'yes' ) {
                                if ( get_post_type() == 'hosting_providers' ) {
                                        $args['post__not_in'] = [ get_the_ID() ];
                                        $args['orderby']      = 'rand';
                                        $args['meta_query']   = [
                                                'relation' => 'AND',
                                                [
                                                        'key'     => $category_mod . '_rank',
                                                        'value'   => [ '', 'NULL', 0 ],
                                                        'compare' => 'NOT IN',
                                                        'type'    => 'NUMERIC'
                                                ],
                                                [
                                                        'key'     => $category_mod . '_rank',
                                                        'value'   => 5,
                                                        'compare' => '<=',
                                                        'type'    => 'NUMERIC'
                                                ]
                                        ];

                                        if ( $options['rank_per_category'] == 'yes' ) {

                                                $args['meta_query'] = [
                                                        'relation'    => 'AND',
                                                        'rank_clause' => [
                                                                'key'     => "ranking_%_rank_in_category",
                                                                'value'   => [ '', 0 ],
                                                                'compare' => 'NOT IN',
                                                                'type'    => 'NUMERIC'
                                                        ],
                                                        [
                                                                'key'     => "ranking_%_rank_in_category",
                                                                'value'   => 5,
                                                                'compare' => '<=',
                                                                'type'    => 'NUMERIC'
                                                        ],
                                                        [
                                                                'key'   => "ranking_%_rank_category",
                                                                'value' => $category_object->term_id
                                                        ],
                                                ];

                                                $args['meta_key'] = 'ranking_%_rank_in_category';
                                        }
                                }
                        }


                        if ( $options['type_of_loading'] == 'preload' && $options['load_more_button'] === 'on' ) {
                                $args['posts_per_page'] = '-1';
                        }

                        $hosting_posts_query = new WP_Query( $args );

Here is the final request:

SELECT SQL_CALC_FOUND_ROWS  sjwd_20_posts.ID FROM sjwd_20_posts  LEFT JOIN sjwd_20_term_relationships ON (sjwd_20_posts.ID = sjwd_20_term_relationships.object_id) INNER JOIN sjwd_20_postmeta ON ( sjwd_20_posts.ID = sjwd_20_postmeta.post_id )  INNER JOIN sjwd_20_postmeta AS mt1 ON ( sjwd_20_posts.ID = mt1.post_id )  INNER JOIN sjwd_20_postmeta AS mt2 ON ( sjwd_20_posts.ID = mt2.post_id ) WHERE 1=1  AND ( 
  sjwd_20_term_relationships.term_taxonomy_id IN (2686)
) AND ( 
  sjwd_20_postmeta.meta_key = 'ranking_{e68324214471d5a342430315d96f93c2ce2dae368d572a9c62f70c65ed9ba7a0}_rank_in_category' 
  AND 
  ( 
    ( mt1.meta_key = 'ranking_{e68324214471d5a342430315d96f93c2ce2dae368d572a9c62f70c65ed9ba7a0}_rank_in_category' AND CAST(mt1.meta_value AS SIGNED) > '0' ) 
    AND 
    ( mt2.meta_key = 'ranking_{e68324214471d5a342430315d96f93c2ce2dae368d572a9c62f70c65ed9ba7a0}_rank_category' AND mt2.meta_value = '2686' )
  )
) AND sjwd_20_posts.post_type = 'hosting_providers' AND (sjwd_20_posts.post_status = 'publish' OR sjwd_20_posts.post_status = 'acf-disabled' OR sjwd_20_posts.post_status = 'private') GROUP BY sjwd_20_posts.ID ORDER BY CAST(mt1.meta_value AS SIGNED) ASC LIMIT 0, 150

Change History (7)

#1 follow-up: @jeremyescott
3 years ago

So you might want to review the whys being 4.8.3's security patch.

https://make.wordpress.org/core/2017/10/31/changed-behaviour-of-esc_sql-in-wordpress-4-8-3/

This isn't a bug, I'm pretty sure.

Version 1, edited 3 years ago by jeremyescott (previous) (next) (diff)

#2 in reply to: ↑ 1 ; follow-up: @johnjamesjacoby
3 years ago

  • Keywords 2nd-opinion added

Replying to jeremyescott:

So you might want to review the whys behind 4.8.3's security patch.

https://make.wordpress.org/core/2017/10/31/changed-behaviour-of-esc_sql-in-wordpress-4-8-3/

This isn't a bug, I'm pretty sure.

The hashing itself is intentional, but if you're seeing them, either something is wrong or you've hooked in to the query filter before they're swapped back.

See this comment from @otto42 specifically, which describes exactly the issue you're seeing:

https://make.wordpress.org/core/2017/10/31/changed-behaviour-of-esc_sql-in-wordpress-4-8-3/#comment-33156

I wasn't a huge fan of this approach for the reasons outlined above, but I also don't have a better solution yet either.

Suggest to close as wontfix, but I've added the 2nd Opinion keyword to get others to chime in. cc @pento.

#3 @jeremyfelt
3 years ago

  • Focuses multisite removed

#4 in reply to: ↑ 2 @hostingreview
3 years ago

Replying to johnjamesjacoby:

Replying to jeremyescott:

So you might want to review the whys behind 4.8.3's security patch.

https://make.wordpress.org/core/2017/10/31/changed-behaviour-of-esc_sql-in-wordpress-4-8-3/

This isn't a bug, I'm pretty sure.

The hashing itself is intentional, but if you're seeing them, either something is wrong or you've hooked in to the query filter before they're swapped back.

See this comment from @otto42 specifically, which describes exactly the issue you're seeing:

https://make.wordpress.org/core/2017/10/31/changed-behaviour-of-esc_sql-in-wordpress-4-8-3/#comment-33156

I wasn't a huge fan of this approach for the reasons outlined above, but I also don't have a better solution yet either.

Suggest to close as wontfix, but I've added the 2nd Opinion keyword to get others to chime in. cc @pento.

I've hooked into this with this:

<?php

add_filter( 'posts_where', 'where_rank_by_category', 9999999 );

/**
 * Filter for Hosting Companies Listing, for special ranking functionality.
 *
 * @param $where
 *
 * @return mixed|string
 */
function where_rank_by_category( $where ) {
        global $wpdb;
        if ( strpos( $where, "ranking_" ) !== false ) {
                $where = str_replace( "meta_key = 'ranking_%", "meta_key LIKE 'ranking_%", $wpdb->remove_placeholder_escape( $where ) );
                $where .= ' AND LEFT(mt1.meta_key, LOCATE(\'_rank\', mt1.meta_key)) = LEFT(mt2.meta_key, LOCATE(\'_rank\', mt2.meta_key))';
        }

        return $where;
}

$wpdb->remove_placeholder_escape wasn't there before. I've added it after reading this: https://make.wordpress.org/core/2017/10/31/changed-behaviour-of-esc_sql-in-wordpress-4-8-3/#comment-33136

Its working, but not sure if its best for the performance, because I will need to add more of there. Thinking of implementing Eloquent or some other ORM to replace this query builder.

Last edited 3 years ago by hostingreview (previous) (diff)

#5 @Clorith
3 years ago

  • Version changed from 4.9 to 4.8.3

#6 @dd32
3 years ago

  • Keywords 2nd-opinion removed
  • Milestone Awaiting Review deleted
  • Resolution set to duplicate
  • Status changed from new to closed

Unfortunately we've not come up with any other solutions than the one implemented, so I don't think we're going to be changing the implementation any time soon.

It's now expected, that if you're filtering SQL directly, that the SQL may change, and/or have these placeholders in it. If you need to filter SQL often, rather than just suffixing extra SQL conditions, that you should probably consider opening a Trac ticket to discuss how to remove the need for SQL filtering.

For the purpose of meta_key LIKE's we've got #42409

#7 @dd32
3 years ago

#42746 was marked as a duplicate.

Note: See TracTickets for help on using tickets.