Custom query for Author results in a WordPress database error

[hyperionjrw]

With a custom query for an Author's posts WordPress will throw a DB error due to malformed SQL.

Before your post_where filter runs the query looks like this:
' AND wp_posts.post_author IN (10202675) AND wp_posts.post_type = \'post\' AND ((wp_posts.post_status = \'publish\'))'

After it looks like this:
' AND ( wp_posts.post_author IN (10202 OR (wp_term_taxonomy.taxonomy = \'author\' AND wp_term_taxonomy.term_id = \'48333\'))675) AND wp_posts.post_type = \'post\' AND ((wp_posts.post_status = \'publish\'))'

You'll notice that the user ID goes from 10202675 to 10202--SQL--675

The problem is down to a bug in the posts_where_filter method when setting $id.
If it's not an author page it is set to '\d+' to look for digits when passed to the regex but before then that $id is passed to get_user_by which will fail and then the value is set to 0. That 0 is then passed into the regex and ends up splitting longer user_ids at one position after the last zero.

regex: post_author\s*IN\s*(.*0(?:.*private\')?.)

So:
$id = is_author() ? get_queried_object_id() : '\d+';
if ( false === get_user_by( 'id', $id ) ){
$id = 0;
}

Needs to become:
$id = is_author() ? get_queried_object_id() : '\d+';
if ( false === get_user_by( 'id', $id ) ){
$id = '\d+';
}

Although I think there will still be a problem if you try to query a different author's posts on another author's page as the id will always come from the page rather than the custom query.

[rebeccahum]

@hyperionjrw Do you have any steps on how to reproduce the issue?

[hyperionjrw]

With a clean WordPress install do the following:

• Run this SQL to make sure the next user created has an ID that'll cause a problem:
  ALTER TABLE wp_users AUTO_INCREMENT = 8088888888;
• Create a new user
• Create a post with that user account or change an existing post over to that user.
  We basically need the id in the post_author column.
• Activate the plugin
• Make sure to add this user as a secondary user on a post so that terms are created.
• Run the following query in theme/index.php replacing the author ID with your new one.
  $x = new WP_Query( array ( 'post_status' => 'publish', 'author' => 8088888888 ) );

You need to make sure that the user ID has a zero in it and it doesn't have a zero in the last two chars otherwise the problem won't surface.

This is all down to the fact that the new $id=0; line produces valid regex that's looking for \s*(.*0.)/

[rebeccahum]

Thanks for providing detailed steps, @hyperionjrw! Much appreciated -- I've been able to reproduce now. I've taken a look at the PR and I'm happy to merge once the changes have been completed. Thanks again!

[rebeccahum]

Closing per #628.