WP_User_Query is broken in 4.8.3

[amityweb]

We have a custom search and results function using WP_User_Query. It was working fine up to 4.8.2. Upgrading to 4.8.3 has resulted in no results in the search but it should return results.

Upon further investigation, the SQL generated in 4.8.3 is no longer correct, in fact it contains some strange strings instead of the % for a LIKE. It contains the following string instead of a %:
822ca44cdf3a7a6a970c2de8f6572380778bc20bd09bc0858b11cea8b4230d96

In 4.8.2 the SQL generated from our WP_User_Query( $args ) was as follows:

SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.* FROM wp_users LEFT JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id )  LEFT JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id )  LEFT JOIN wp_usermeta AS mt2 ON (wp_users.ID = mt2.user_id AND mt2.meta_key = 'show_in_directory' )  LEFT JOIN wp_usermeta AS mt3 ON ( wp_users.ID = mt3.user_id )  LEFT JOIN wp_usermeta AS mt4 ON (wp_users.ID = mt4.user_id AND mt4.meta_key = 'show_member_in_directory' ) WHERE 1=1 AND ( 
  ( 
    ( wp_usermeta.meta_key LIKE 'subject_specialism_%_subject' AND wp_usermeta.meta_value LIKE '%Economics%' ) 
    AND 
    ( 
      ( mt1.meta_key = 'show_in_directory' AND mt1.meta_value = 'Yes' ) 
      OR 
      mt2.user_id IS NULL
    ) 
    AND 
    ( 
      ( mt3.meta_key = 'show_member_in_directory' AND mt3.meta_value = 'Yes' ) 
      OR 
      mt4.user_id IS NULL
    )
  )
) ORDER BY RAND()

In 4.8.3 the SQL generated is as follows, and so our custom search function is no longer returning results:

SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.* FROM wp_users LEFT JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id )  LEFT JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id )  LEFT JOIN wp_usermeta AS mt2 ON (wp_users.ID = mt2.user_id AND mt2.meta_key = 'show_in_directory' )  LEFT JOIN wp_usermeta AS mt3 ON ( wp_users.ID = mt3.user_id )  LEFT JOIN wp_usermeta AS mt4 ON (wp_users.ID = mt4.user_id AND mt4.meta_key = 'show_member_in_directory' ) WHERE 1=1 AND ( 
  ( 
    ( wp_usermeta.meta_key LIKE 'subject_specialism_{822ca44cdf3a7a6a970c2de8f6572380778bc20bd09bc0858b11cea8b4230d96}_subject' AND wp_usermeta.meta_value LIKE '{822ca44cdf3a7a6a970c2de8f6572380778bc20bd09bc0858b11cea8b4230d96}Antiques{822ca44cdf3a7a6a970c2de8f6572380778bc20bd09bc0858b11cea8b4230d96}' ) 
    AND 
    ( 
      ( mt1.meta_key = 'show_in_directory' AND mt1.meta_value = 'Yes' ) 
      OR 
      mt2.user_id IS NULL
    ) 
    AND 
    ( 
      ( mt3.meta_key = 'show_member_in_directory' AND mt3.meta_value = 'Yes' ) 
      OR 
      mt4.user_id IS NULL
    )
  )
) ORDER BY RAND()

For reference this is our full search

// Get Search Terms
if(isset($_GET['subject']) && $_GET['subject'] != '')
{
	// Strip hyphen and numbers from end, just in case its a duplicate
	$search_term = preg_replace("/-\d+$/","",$_GET['subject']);
}

// Get thesaurus terms from $search_term
$thesaurus = get_field('subject_thesaurus', 'option');
foreach($thesaurus as $key => $value)
{
	if ((strcasecmp($value['alternative_search_term'], $search_term) == 0))
	{
		$search_term = $value['equivalent_search_term'];
		break;
	}
}


// Get any URL variables for filters
$search = array();
$search_results = false;

if(isset($search_term) && $search_term != '')
{
	$search_results = true;
	$search[] = array(
		'key'	 	=> 'subject_specialism_%_subject',
		'value'	  	=> stripslashes($search_term),
		'compare' 	=> 'LIKE',
	);
}
if(isset($_GET['publication_type']) && $_GET['publication_type'] != '')
{
	$search_results = true;
	$search[] = array(
		'key'	 	=> 'publication_type_%_publication_type',
		'value'	  	=> stripslashes($_GET['publication_type']),
		'compare' 	=> 'LIKE',
	);
}

$search[] = array(
	'relation'		=> 'OR',
	array(
		'key'	 	=> 'show_in_directory',
		'value'	  	=> 'Yes',
		'compare' 	=> '=',
	),
	array(
		'key'	 	=> 'show_in_directory',
		'compare' 	=> 'NOT EXISTS',
	),
);

$search[] = array(
	'relation'		=> 'OR',
	array(
		'key'	 	=> 'show_member_in_directory', // Admin settings
		'value'	  	=> 'Yes',
		'compare' 	=> '=',
	),
	array(
		'key'	 	=> 'show_member_in_directory',
		'compare' 	=> 'NOT EXISTS',
	),
);
	
// Create args array for get_users from the Pro members IDs
$args = array(
	'meta_query'	=> array(
		'relation'		=> 'AND',
		$search
	),      
	'include'      => $member_ids,
);

// Create random function because WP_User_Query does not have it
if($search_results)
{
	$randomize_func = create_function( '&$query', '$query->query_orderby = "ORDER BY RAND()";' );
	add_action( 'pre_user_query', $randomize_func );
}

$user_query = new WP_User_Query( $args );

[amityweb]

P.S. Ignore the search string Economics in the first, and then Antiques in the second query, this is not related to the issue, and was just different tests as part of my investigation.

[amityweb]

Sorry if this is a duplicate, as I have found this discussed here ​https://make.wordpress.org/core/2017/10/31/changed-behaviour-of-esc_sql-in-wordpress-4-8-3/ and bug here #41925, but they do not mention WP_User_Query being affected or how to deal with it.

[Otto42]

All queries in WordPress will be affected by this.

WordPress core does not support, and has never supported, meta_key's that contain wildcards because WordPress always uses an = comparison for the meta_key, never a LIKE comparison.

It is true that some users use plugins or other custom code to convert that = into a LIKE for those % cases. The change to the way queries work in 4.8.3 will probably break that existing code. This cannot be avoided. The plugin or custom code will need to be changed to accommodate this.

Note that plugins or custom code that uses the "query" filter can see the full SQL string, as it will run, without the new changes in 4.8.3. This will include the percent signs as previously. Code that uses this filter will be unaffected and continue to work correctly. Code that used other filters will not see % signs in their filter input any longer, and should be modified to not be looking for the % sign specifically.

In other words, whatever special you have that originally made this:

'key' => 'subject_specialism_%_subject',

become this:

wp_usermeta.meta_key LIKE 'subject_specialism_%_subject'

Won't work anymore, because WordPress does not store that % in the string any longer. You have some code somewhere that looks for that percent and changes the = into a LIKE. WordPress has no code that will put a LIKE onto a meta_key like that. So, you have something custom, some plugin, or some custom function, that does that. That is what needs to be repaired.

[SergeyBiryukov]

Follow-up: #42409

[amityweb]

I overlooked the function we used to change the LIKE as you stated sorry, for the record we have this code in a functions file and its to do with Advanced Custom Fields.

function acf_posts_where( $where )
 {
	$where->query_where = str_replace( "meta_key = 'subject_specialism_%", "meta_key LIKE 'subject_specialism_%", $where->query_where );
	$where->query_where = str_replace( "meta_key = 'publication_type_%", "meta_key LIKE 'publication_type_%", $where->query_where );
	return $where;
}

If you are saying this is not a Wordpress issue, and is to do with our code, then I will have to look into how to correct this. For time being we cannot update to 4.8.3 and so left with an insecure 4.8.2 as I have not found a solution yet.

Great that you have made the suggestion on the Follow-up: #42409 I will follow that. LIKE is after all a very common SQL operator!