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

[hostingreview]

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

[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.

[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.

[hostingreview]

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.

[dd32]

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

[dd32]

#42746 was marked as a duplicate.