User search has problems with no role on large usersets

[Otto42]

Tested on bbPress 1.1-alpha-2855 (have not tested trunk).

For large user-lists, user search on the bb-admin/users.php screen doesn't work unless you choose a role. This is caused by a couple layers of code expecting something other than what they're getting.

Searching for "otto" for example, produces a URL like this:

bb-admin/users.php?usersearch=otto&userrole[]=

In users.php, this produces an input of $_GETusersearch? = "otto" and $_GETuserrole? = array(0=>). In other words, the userrole is not an empty array, but an array with a single empty string in it.

This in turn passes to BB_User_Search like so:

$bb_user_search = new BB_User_Search( @$_GET['usersearch'], @$_GET['page'], @$_GET['userrole'] );

Which then becomes the same non-empty array passed down through this code:

$roles = (array) $roles;
$_roles = array();
foreach ( $roles as $role ) {
	if ( false !== $role ) {
		$_roles[] = stripslashes( $role );
	}
}
$this->roles = empty( $_roles ) ? false : $_roles;

Which then is passed to the bb_user_search function in functions.bb-core.php. Since the $roles is not empty, it doesn't pass false. End result is that it ends up querying the usermeta table for meta_value LIKE '%%'.

In MySQL, this basically returns every row with capabilities for the site in question. If the number of users returned happens to be vary large, then this bit:

if ( $user_ids ) {
	$user_ids_sql = "AND ID IN (". join(',', $user_ids) . ")";
}

Will produce a query way too big for mySQL to handle.

In the case I'm testing, it produced a query with about 1.7 million users in that IN statement. You can see the problem, I'm sure.

Using this code in a plugin fixed the problem by converting the "no-role" case into an empty case, thus preventing the role from playing any part in the query:

$role = @$_GET['userrole'];
if ( is_array( $role ) && $role[0] == '' ) {
        unset($_GET['userrole']);
}

However, this is a hacky workaround. The query shouldn't be pulling the user list and stuffing into an IN selection like that. A JOIN would be better, or at least a LIMIT selection based on the viewed page would be preferable.

[Nightgunner5]

Wouldn't the same problem happen if you searched in a role with tons of users? +1 on not sending data from the database to the database.

[johnjamesjacoby]

Makes sense. Moving to 1.1 milestone.

[johnjamesjacoby]

Moving to 1.1-alpha Version as part of trac triage.

[johnjamesjacoby]

Moving to 1.2 milestone to release 1.1 for BuddyPress 1.5 compat.